Sat
31
Jan '09
Drop all tables from a mysql database
by schlumpf filed under all, Computer, Work

Sometimes you want to reset a database to its virgin state, without actually deleting and re-creating the whole database (perhaps because your user doesn’t have the right to create a database). There are a lot of links out there that give you a quick answer on how to drop all tables from a database in a single-line shell script. Some examples:

However this does not work if there are foreign key constraints between the tables (because the tables constraining others need to be deleted first). Here’s the advanced version that solves this problem:

#!/bin/bash
USERNAME=myUser
PASSWORD=myPassword
HOSTNAME=dbHost
DATABASE=mydb
while (true) ; do
    TABLES=`mysql -h $HOSTNAME -u $USERNAME -D $DATABASE --password=$PASSWORD 
                  --batch -e "show tables" | grep -v Tables_in`
    if [ -z $TABLES ] ; then break; fi
    for i in $TABLES ; do
        mysql -h $HOSTNAME -u $USERNAME -D $DATABASE --password=$PASSWORD  -e "drop table $i"
    done
done

I agree this is not nice because it’s a brute force approach – but hey, it works! And resetting a database is most probably not a performance-critical task anyway.


4 Responses to “Drop all tables from a mysql database”

  1. 1

    Won’t a rm -rf /var/lib/mysql just do, too? maybe exclude the mysql database if you want to keep the users…

    Julian (February 1st, 2009 at 03:04)
  2. 2

    ah, whoops, deleting the Tables, not the Databases… one could copy a virgin database over all files, though… something like will sure work

    Julian (February 1st, 2009 at 03:05)
  3. 3

    Hi Julian, thanks for the comments!
    In my case, the reason why I don’t simply delete the whole database is because I can’t – I only have remote access to this one database, no permission to create or drop databases and no shell access to the server… and the software using the database keeps filling it up will all kinds of strange data and the easiest way to fix it is to delete everything.

    schlumpf (February 1st, 2009 at 22:03)
  4. 4

    Hi Julian, thanks for the comments!
    In my case, the reason why I don’t simply delete the whole database is because I can’t – I only have remote access to this one database, no permission to create or drop databases and no shell access to the server… and the software using the database keeps filling it up will all kinds of strange data and the easiest way to fix it is to delete everything.

    Bruce (May 19th, 2010 at 21:57)

Any comments? Or questions? Just leave a Reply: