How to empty a MySQL database

Emptying a databases involves dropping (deleting) all its tables. The easiest way to do this is to drop and recreate a database:

mysql> drop database mole;
mysql> create database mole;

Depending on the system you are working, the database administrator might not give the rights to drop or create databases. In such cases, you have no choice but to drop and recreate each table manually or with a script. I found a couple of really nifty shell scripts to do this at this site. The code is as follows:

$ mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

or you can use gawk

$ mysql -u uname dbname -e "show tables" | grep -v Tables_in | grep -v "+" | gawk '{print "drop table " $1 ";"}' | mysql -u uname dbname