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

  1. connect to a mysql dbms server and execute the command for showing all databases
  2. Omit lines that match “Database” while printing.
  3. Omit lines with mysql,infomation_schema and test.
  4. 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.
  5. 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

12 comments

  1. Rob Yates

    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!

  2. Charlie de C

    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

  3. David Campbell

    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

  4. David Campbell

    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

  5. Pingback: automating mysql backups | antipaucity

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s