Throw out MySQL databases matching some patterns?

Question

I am running mySQL on a server where I need to dump a lot of databases (after some testing with the server). All databases that I need to delete have the same prefix "Independent of _".

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

I will do this work quite a few times, so I was wondering what would be the best way to do this?

EDIT: I can use any language or connect to mysql ... so we can do this in some way. Right now, I asked the guy who generates the databases to give me .txt with every name in the line ... so im encoding a fast php that will take the file and delete all the databases in it, later I will try% answer (if it works, it certainly takes the right answer). In any case, I would like to make it easier, because I can not support this code (other guys will be and you know ...)

edit 2: Using a wildcard did not help: # 1008 - Unable to delete database "whatever_%"; database does not exist

Answer 1

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

Here's a quick and dirty solution using a 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 @@" 

This will print all shell commands to drop tables starting with "Whatever_". If you want it to actually execute these commands, delete the word echo.

EDIT : I forgot to explain the above! I do not know how familiar you are with the shell script, but here goes:

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

Lists all of your tables with the heading "Tables_in_your_database_name". The output from this channel is transmitted through the channel (the symbol | means "connected", as in the transmitted one) through the following command:

 egrep "^Whatever_" 

searches for any beginning lines (^ characters mean "creatures with") the word "Whatever_" and only prints them. Finally, we pass this list of "Whatever_ *" tables through the command:

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

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

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

So, if you have a bunch of tables named "Whatever_1", "Whatever_2", "Whatever_3", the generated commands are:

 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" 

To deduce 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 there were enough details, and I’m not just beating someone on the head with too much information. Good luck, and be careful when using the DROP TABLE command!

Answer 2

The scraimer answer principle is correct, but since the question was to delete the database, not the table in the database, the correct command should be:

 mysql -u your_username -p'your password' -e 'show databases' | grep Whatever_* | xargs -I "@@" mysql -u your_username -p'your password' -e "DROP database \`@@\`" 

For an explanation of the command, see the scraimer explanation.

The last bit ... \ `@@ \` we have our resulting database name specified in bacticks (`) if our database name has special characters, such as` -`

Greetings

Answer 3

We can do this using stored procedures. Below is below:

 drop procedure if exists droplike; delimiter // create procedure droplike(pattern varchar(20)) begin set group_concat_max_len = 65535; select @drop:= concat( 'drop table ', group_concat(table_name) , ';' ) from information_schema.tables where table_schema = "database_name" and table_name like pattern; prepare statement from @drop; execute statement; end // delimiter ; 

Replace data_name with the database name (write permission required). To delete tables with the XYZ template, call the procedure with input as XYZ followed by a wild card, as shown below:

 call droplike("XYZ%"); 

Answer 4

I think you cannot delete multiple databases in MySql.

But I have a very ugly solution. You can program in C / C ++ / C # / JAVA multiple prints of " DROP DATABASE WHATEVER_<name>; " in notepad or any text editor. After that, you can copy the paste at the command prompt of the MySql client and there you go. don't forget the " ; " after each DROP command.

I think this is possible. try to write this.

Answer 5

how about this (jython)?

 rs = stmt.executeQuery( "SHOW DATABASES LIKE 'Whatever_%'" ) databases_for_dropping = [] while rs.next(): databases_for_dropping.append( rs.getString( 1 )) for database_for_dropping in databases_for_dropping: stmt.execute( "DROP DATABASE %s" % database_for_dropping ) 

Answer 6

In case someone is looking for a simple answer that reflects scraimer and Explorer , but is written in Java using JDBC (I know that I was), here is a quick and dirty one that did this work for me:

 import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; public class DatabaseDeleter { // Pass your database wildcard as the only argument to program ( "%MY_CRAZY_PATTERN%") public void main(String[] args) throws SQLException, ClassNotFoundException{ Class.forName("com.mysql.jdbc.Driver"); Connection connection = DriverManager.getConnection("jdbc:mysql://databaseURL"); PreparedStatement pst = connection.prepareStatement("show databases like ?"); pst.setString(1, args[0]); ResultSet rs = pst.executeQuery(); ArrayList<String> databases = new ArrayList<String>(); while (rs.next()) databases.add(rs.getString(1)); for (String s : databases){ Statement st = connection.createStatement(); st.execute("drop database " + s); } connection.close(); } } 

Warning. Do not use this for clients or other developers unless you want your SQL server to be searched.

Answer 7

Sorry, we cannot delete multiple databases at once using sql commands

Answer 8

 DROP DATABASE `any-database_name`; 

I just use this ` (backtick) character before and after my database name.

Answer 9

You can try the general purpose mysql 5 stupid library (can be downloaded from here ). Using it, you can delete several tables that match the regular expression. I suggest carefully checking the regex to prevent the tables you need from falling.

Answer 10

Use

 DROP {DATABASE | SCHEMA} [IF EXISTS] db_name 

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.