Drop MySQL databases matching some wildcard?

Question

Im runing mySQL in a server where i need to drop tons of databases (after some testing with the server). All databases that i need to drop have the same prefix "Whatever_".

After the prefix, the names are random. So you have your Whatever_something, Whatever_232, Whatever_blabla, .... , Whatever_imthelast.

I will be doing this job quite some times so i was wondering what would be the best way to do this?

EDIT: I can use any kind of language or plug in for mysql... so we CAN do this in some ways. Right now, i asked the guy that is generating the databases to give me a .txt with each name in a line... so im coding a quick php that will take a file and delete all the databases in it, later i will try the % answer(if it works, it takes the correct answer for sure its the easier way). Anyway i would like to do this the easier way coz i wont be able to support this code(other guys will and you know... )

edit 2: The use of a wildcard didnt work: #1008 - Can't drop database 'whatever_%'; database doesn't exist

Answer 1

You can try the mysql 5 general purpose routine library (downloadable from here). Using it, you can drop multiple tables which match a regular expression. I suggest checking the regular expression carefully in order to prevent dropping tables you do need.

Answer 2

Sorry we cannot drop multiple database at a time using sql commands

Answer 3

Use the

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

statement as described here.

I don't know if you can use wildcars to do something like

DROP DATABASE Whatever_%

but I think you should try it.

Answer 4

The basic idea is to run "show tables" in your database, and use the results from that to select the tables you want. I don't think MySQL lets you do anything with the resultset from "show tables", but I'm probably wrong.

Here's a quick-and-dirty solution using the shell:

mysql -u your_user -D your_database_name -e "show tables" -s | 
  egrep "^Whatever_" | 
  xargs -I "@@" echo mysql -u your_user -D your_database_name -e "DROP TABLE @@"

That will print out all the shell commands to drop the tables beginning with "Whatever_". If you want it to actually execute those commands, remove the word "echo".

EDIT: I forgot to explain the above! I don't know how familiar you are with shell scripting, but here goes:

mysql -u your_user -D your_database_name -e "show tables" -s

prints out a list of all your tables, with the header "Tables_in_your_database_name". The output from that is piped (the | symbol means "piped", as in passed-on) through the next command:

egrep "^Whatever_"

searches for any lines that begin (that ^ symbols means "beings with") the word "Whatever_" and only prints those. Finally, we pipe that list of "Whatever_*" tables through the command:

xargs -I "@@" echo mysql -u your_user -D your_database_name -e "DROP TABLE @@"

which takes each line in the list of table names, and inserts it instead of the "@@" in the command

echo mysql -u your_user -D your_database_name -e "DROP TABLE @@"

So if you had a bunch of tables named "Whatever_1", "Whatever_2", "Whatever_3", the generated commands would be:

echo mysql -u your_user -D your_database_name -e "DROP TABLE Whatever_1"
echo mysql -u your_user -D your_database_name -e "DROP TABLE Whatever_2"
echo mysql -u your_user -D your_database_name -e "DROP TABLE Whatever_3"

Which would output the following:

mysql -u your_user -D your_database_name -e "DROP TABLE Whatever_1"
mysql -u your_user -D your_database_name -e "DROP TABLE Whatever_2"
mysql -u your_user -D your_database_name -e "DROP TABLE Whatever_3"

I hope that was enough detail, and that I'm not just beating anyone over the head with too much information. Good luck, and be careful when using the "DROP TABLE" command!