Drop all tables from a MySQL DB without dropping the database itself

Mar 6, 11:13 AM by Grigorios Prasinos

Restoring a database to an earlier version using a dump is easy: drop the database, recreate it and load the dump. But what happens if you don’t have the necessary rights on the database server for droping and creating databases. In that case you have to drop the tables one by one before loading the dump. How can we do this efficiently?

Edward A. Webb has a nice one-liner in his post :

mysqldump -uuser -ppassword --no-data --add-drop-table database \
 | grep ^DROP | mysql  -uuser -ppassword database

Still, mysqldump returns more data than we need (all these “CREATE TABLE” statements).
I think we can do it more efficiently by asking the server directly:

mysql -uuser -ppass -e "show tables" --skip-column-names database \
 | sed -e "s/.*/DROP TABLE IF EXISTS \`&\`;/" \
 | mysql -uuser -ppass database

The show tables statement returns all the tables of the database in a column with a title like “Tables_in_database”, which we don’t need. We can get rid of it by either piping the result first to tail -n +2 - or by using the --skip-column-names option of the mysql client. We can then use sed to construct the correct statement: the "&" refers to the string that matched in the first part of the search and replace expression, which in this case is the table name.

Instead of sed we may use xargs (and maybe it’s a bit more efficient):

mysql -uuser -ppass -e "show tables" --skip-column-names database \
 | xargs -L 1 -I %t echo DROP TABLE IF EXISTS \`%t\`\; \
 | mysql -uuser -ppass database