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>
$ 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]

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.