Unix + mySQL one-liner to drop tables matching a specific pattern

Okay, this has been a holy grail for me for a while, and just came to a head today as I accidentally copied a database containing about 15 separate WordPress installations (with different table prefixes) into what was supposed to be a dedicated single WP database. Ouch.

A little while ago I blogged about how you can avoid this situation altogether by only mysqldumping selected tables based on a pattern match of their prefix. Well, guess who didn’t drink his own milk (is that even an expression)?

So there I was, with a database full of wp_ and wpclientA_ wpclientB_ zg_ etc. All I wanted was to keep the original wp_ tables and just drop the hell out of the rest of ‘em.

Enter xargs

Oh yeah, xargs and I have been eyeing each other across the bar for many a night now, wonder who was going to ask whom for their number. I occasionally asked someone about it, creeped its facebook page, you know, just nosed around. Xargs for all its self-confidence was bony, had protusions in all the wrong places, and quite frankly, very little social skills. I finally crossed the room.

Here’s the result of our ungodly union. May the devil take the hindmost. Or the most hind.

mysql -uUSERNAME -pPASSWORD DATABASENAME --skip-column-names -e "select table_name from information_schema.tables where table_schema not like 'information_schema' and table_name not like 'wp\_%';" | xargs -t -I {} mysql -uUSERNAME -pPASSWORD DATABASENAME -e "drop table {};"

Some additional nuances

The magic is in the -I {} part of xargs. This switch (-I) allows you to specify the substitution string that the result from your piped command will be replaced with. Unfortunately $ breaks things (it would have been nice to have something like $TABLE_NAME, but that just farted in my face and laughed about it.) The default is {}, which leads me to wonder why you still need to provide -I {}, but hey, with Unix I’ve just learned to hide my crying eyes behind dark sunglasses and a couple of martinis. So here I use {} to insert each table name (from the previous command, before the pipe) into my drop table statement.

The -t switch is totally optional, but because I’m new to all this, I wanted to see the command that was being output for each line. It’s intensely satisfying to see all these lines just spit out (there were about 200 of them). If only doing lines on the chalkboard in class (anyone old enough to remember doing that??) was as easy. I will not use DOS anymore. I will not use DOS anymore. I will not use DOS anymore.

A final gotcha: I originally tried to list all tables not like ‘wp_%’ but forgot that ‘_’ is actually a wildcard match character in this poor, sad, stunted little pattern-matching homunculus called mySQL ‘like’. So it needs to be escaped: ‘wp\_%’. So do I.