Drop All Databases in MySQL
The Following command drops all databases in the mysql dbms except mysql, information_schema,test and OLD db’s.
The command is pretty handy when one needs to drop all the databases in one go:
# 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
What this does is
- connect to a mysql dbms server and execute the command for showing all databases
- Omit lines that match “Database” while printing.
- Omit lines with mysql,infomation_schema and test.
- use gawk to print out the words “drop database” followed by the daabase name (which is in $1) and then a semicolon. Call sleep command.
- pipe all of above back to the mysql dbms to drop all those databases
Also,
mysql -uroot -pxxxxx -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);"}' > droppeddatabases.sql
this saved me a lot of time. thanks.
This was very, very useful for a server migration I just did where I had to create all of the new databases and set up the proper grants. Thank you for saving me a ton of time!
you are welcome 🙂
You could also do the following to skip out some steps:
for i in $(mysql -e “show databases” | egrep -v “Database|mysql|information_schema|test”); do mysql -e “drop database $i”; done
Cheers,
Charlie
Thanks a lot for Sharing this, helped me save a lot of time !!!
If you’re really attached to greping out things you don’t want; check out GNU egrep. It will allow you to utilized regex. gawk (not normally a built in package AFAIK) also allows regex “grep”
This should work, I haven’t tested it:
$ mysql -uroot -p -e “show databases” | egrep -v “^Database$|^mysql$|^information_schema$|^test$|^OLD$” | awk ‘{print “drop database “, $1 }’ | mysql -uroot -p
I’d rather let awk do all the heavy lifting, it makes it a little easier to read.
$ mysql -u root -p -e “show databases\G” | awk ‘!/\*|schema$|mysql$/ { print “drop database “, $2 } ‘ | mysql -u root -p
If you are really attached to your method of greping out things you don’t want; check out GNU egrep. It will allow you to utilized regex. gawk (not normally a built in package) also allows regex “grep”
I’d rather let awk do all my heavy lifting:
$ mysql -uroot -p -e “show databases\G” | awk ‘!/\*|schema$|mysql$/ { print “drop database “, $2 } ‘ | mysql -uroot -p
for a in `mysql -uroot -proot -e “show databases” | grep -v Database | grep -v mysql| grep -v information_schema`; do mysql -uroot -proot -e “drop database $a”; done
table names with dots are not well handled in my latest command.
This one works : for a in `mysql -uroot -proot -e “show databases” | grep -v Database | grep -v mysql| grep -v information_schema`; do mysql -uroot -proot -e “drop database \`$a\`”; done
It doesn’t require gawk to be installed.
instead of cascading grep command, you can use it like grep -v “key1\|key2\|key3”