How to empty a MySQL database

Emptying a databases involves dropping (deleting) all its tables. If you have a hundred tables in your database, the task becomes cumbersome. 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. In that situation, you can try the following code:

mysqldump -u USER -pPASS --add-drop-table --no-data DATABASE | grep ^DROP | mysql -u USER -pPASS DATABASE

You would need to replace USER with your username, PASS with your password, and DATABASE with your database name.

Alternately, you can use gawk to achieve the result:

mysql -u USER DATABASE -e "show tables" | grep -v Tables_in | grep -v "+" | gawk '{print "drop table " $1 ";"}' | mysql -u USER DATABASE
</pre></blockquote>