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.

Answer 3

Both of these approaches remove all databases, besides mysql, information_schema and performance_schema.

Method 1. MYSQL Approach

Inspired by Fabian's answer, but lighter and catches empty databases. This approach generates the script via MYSQL query, then feeds it back to MYSQL. This method will likely be most compatible in terms of bash.

mysql -uroot -pMY_PASSWORD -s -N -e "SELECT GROUP_CONCAT(CONCAT('DROP DATABASE ', schema_name, ';') SEPARATOR ' ') FROM information_schema.schemata WHERE schema_name NOT IN ('mysql', 'information_schema', 'performance_schema');" | grep -v "NULL" | mysql -uroot -pMY_PASSWORD

Update. added -v "NULL" to avoid MYSQL errors in case there were no tables.

---

Method 2. Bash Approach

Inspired by Sukhjinder's answer, but tuned for better compatibility. This approach requests database names in raw format, does its bash magic, then feeds the code back to MYSQL. This method gives you more control over the code.

mysql -uroot -pMY_PASSWORD -e "show databases" -s -N | egrep -v "mysql|information_schema|performance_schema" | while read x ; do echo "DROP DATABASE $x;" ; done| tr '\n' ' ' | mysql -h"mysql" -uroot -pMY_PASSWORD

Answer 4

If you have cross-database foreign keys, or database names using MySQL reserved words such as 'order', this variation of Sukhjinder Singh's answer will work.

mysql -u<user> -p<password> -e "show databases" | grep -v Database | grep -v mysql | grep -v information_schema | gawk '{print "SET FOREIGN_KEY_CHECKS = 0;drop database" $1 ";select sleep(0.1);"}' | mysql -u<user> -p<password>

Answer 5

Follow this link - it demonstrate how to do this !

The used scrip is :

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