CODEWITHSUNDEEP
mysql
Sukhjinder Singh
Sukhjinder Singh

Reputation: 1825

Drop all databases in MySQL

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?

Upvotes: 28

Views: 109815

Answers (5)

Timofey Drozhzhin
Timofey Drozhzhin

Reputation: 4714

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

Upvotes: 4

Fabian Pijcke
Fabian Pijcke

Reputation: 3210

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.

Upvotes: 12

Sukhjinder Singh
Sukhjinder Singh

Reputation: 1825

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

Upvotes: 39

Sheldon Juncker
Sheldon Juncker

Reputation: 617

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>

Upvotes: 2

Up_One
Up_One

Reputation: 5271

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

Upvotes: 0

Related Questions