Removing all the tables in the MySQL database

Recently I came into the problem, that I needed to clean up the MySQL database contents (to get rid of all the tables). Normally I'd go for the database drop and creation, but there are situations, where it's not feasible. The common case is that the user that has granted access to particular DB only. Can't create/recreate new one.

So I ended up (heavily inspired by some comments found here) with:

mysql -u <username> --password=<password> <database_name> -BNe "show tables" | awk '{print "drop table " $1 ";"}' | mysql -u <username> --password=<password> <database_name>

If you'd like to prevent having password stored in your shell history (and you're using file: .my.cnf to handle user/pass/database), you should go for much shorter one:

mysql -BNe "show tables" | awk '{print "drop table " $1 ";"}' | mysql

The switches explanation follows:

• -B --batch - tab delimited output, one line per record / no history
• -N --skip-column-names - do not write column names in results

As one of the users noted:

There is a very good reason to use show tables and not mysqldump. If you have a large database show tables will offer a superior performance.