|
Sat
31
Jan '09
|
|
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:
- http://www.thingy-ma-jig.co.uk/blog/10-10-2006/mysql-drop-all-tables
- http://www.cyberciti.biz/faq/how-do-i-empty-mysql-database/
- http://knaddison.com/technology/mysql-drop-all-tables-database-using-single-command-line-command
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.
Won’t a rm -rf /var/lib/mysql just do, too? maybe exclude the mysql database if you want to keep the users…
ah, whoops, deleting the Tables, not the Databases… one could copy a virgin database over all files, though… something like will sure work
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.
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.