Drop all tables from a mysql database
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.