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

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.


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.


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