Drop all databases in MySQL

Question

We have many databases in our MySQL and we want to compress / clean the ibdata1 file in MySQL

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

Answer 1

The following command deletes all databases in mysql dbms except mysql, information_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 the Mohinish Post Blog

Answer 2

Follow this link - it demonstrates how to do it!

Used script:

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

Answer 3

- MySQL, "", .

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>