Drop all databases in MySQL

Question

We have many databases in our MySQL and we want to shrink/purge ibdata1 file in MySQL.

How can we drop all the databases from the MySQL except information_schema and mysqld databases?

Answer 1

The following command drops all databases in the mysql dbms except mysql, information_schema and performance_schema dbs.

mysql -uroot -p<password> -e "show databases" | grep -v Database | grep -v mysql| grep -v information_schema| gawk '{print "drop database `" $1 "`;select sleep(0.1);"}' | mysql -uroot -p<password>

Thanks to Mohinish's Blog post

Answer 2

You can build a series of DROP DATABASE queries directly using MySQL :

-- Prevent truncation
SET SESSION group_concat_max_len = 1000000;

SELECT GROUP_CONCAT(
  DISTINCT CONCAT('DROP DATABASE ', table_schema, ';')
  SEPARATOR ''
)
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema');

Then execute the resulting string.

This solution has the virtue of not requiring a connection to the host serving the MySQL database.