MySQL: Drop ALL tables of a database

After looking around for a while, here is a little recipe to efficiently drop all tables from a MySQL database.

Background story: If you have sufficient access rights to the MySQL server, you could easily drop database dbname; and create database dbname;

If circumstances don’t allow you to drop and create this way, here are two ways on how you could do it from the (Gnu/Linux) shell:

$ mysql -u<dbuser> -p<dbpass> -e “show tables” -N <dbname> | sed -e “s/.*/DROP TABLE IF EXISTS \`&\`;/” | mysql -u<dbuser> -p<dbpass> <dbname>

or

$ mysqldump -u<dbuser> -p<dbpass> –no-data –add-drop-table <dbname> | grep ^DROP | mysql -u<dbuser> -p<dbpass> <dbname>

Of course, you have to replace the following:

<dbuser>: Database user; e.g. “testuser” (without the quotes)
<dbpass>: Database password; e.g. “test1ng” (without the quotes)
<dbname>: Database name; e.g. “testdatabase” (without the quotes)

Cheers!

PS: [Compiled from post and comments under http://edwardawebb.com/linux/drop-tables-mysql-database-deletion]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>