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