How to drop all MySQL tables from the command-line?

Question

Usually I open Terminal.app and connect to a remote MySQL database.

Then I use this command to drop a table:

mysql> drop table [table name];

But what I need is the command line to drop all tables in the database.

If I use:

mysql> drop database [database name];

I'll destroy the database completely and I won't be able to create tables again. Am I right?

Answer 1

You can drop the database then immediately recreate it:

mysql> drop database [database name];
mysql> create database [database name];

Or you could use a script to drop each table in the database.

Answer 2

You can try the following command:

mysqldump --no-data --add-drop-table DB_NAME | grep ^DROP | mysql -v DB_NAME

Or:

mysql --silent --skip-column-names -e "SHOW TABLES" DB_NAME | xargs -L1 -I% echo 'DROP TABLE `%`;' | mysql -v DB_NAME

Where DB_NAME is your database name. Database credentials you can specify either in ~/.my.cnf or adding them to the command (e.g. -uroot -proot).

This method has some advantages over dropping and creating the database in case your database user doesn't have permission to drop it.

Answer 3

mysql -u USERHERE -pPASSWORDHERE --silent --skip-column-names -e "SHOW TABLES" DATABASENAMEHERE | xargs -L1 -I% echo 'SET FOREIGN_KEY_CHECKS = 0; DROP TABLE%; SET FOREIGN_KEY_CHECKS = 1;' | mysql -u USERHERE -pPASSWORDHERE -v DATABASENAMEHERE