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?

[MySQL Empty Database / Delete or Drop All Tables](http://www.cyberciti.biz/faq/how-do-i-empty-mysql-database/), [MySQL - Drop all Tables in a Database Using a Single Command Line Command](http://knaddison.com/technology/mysql-drop-all-tables-database-using-single-command-line-command), [Drop all tables from a MySQL Database without deletion](http://edwardawebb.com/linux/drop-tables-mysql-database-deletion), ... — slhck, Jul 08 '11 at 11:44

score 32 · Accepted Answer · answered Jul 08 '11 at 11:46

32

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.

answered Jul 08 '11 at 11:46

William Jackson

8,344
1
37
45

ok, just to be sure: I connect to remote database in Terminal with this line: **/Applications/MAMP/Library/bin/mysql -h 80.54.554.10 -u adm_user -p my_db**. After dropping and creating the database as you said will I need to change this connection values or they remain the same? – chefnelone Jul 08 '11 at 11:57
3

@chef - Dropping all tables and dropping the whole database does not have the same effect. If you drop only the tables any database wide permissions will remain, not so if you drop/recreate the database. – Nifle Jul 08 '11 at 21:16
10

@chefnelone : Be careful !!! Dropping a database drops stored procedures and user-defined views as well. – RolandoMySQLDBA Jul 09 '11 at 01:03
Quite clear for me. I'll go with the script to delete each table. – chefnelone Jul 11 '11 at 07:31
1

it's totally unsafe, see the comments [http://stackoverflow.com/a/12403742/1713660](http://stackoverflow.com/a/12403742/1713660) – vladkras Oct 29 '15 at 08:01

kenorb · Answer 2 · 2014-10-27T11:37:18.220

13

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.

edited Oct 27 '14 at 11:37

answered Oct 27 '14 at 11:26

kenorb

24,736
27
129
199

1

Confirmed second example, awesome. Add db credentials as additional flags before `--silent` and `-v` / `--verbose` respectively like: `--user=username --password=password --host=host.name` – here May 20 '15 at 03:41
Good idea. But have some problem when there is FOREIGN KEY. I add `| sort -r` to reverse line, but still not compatible some db. – Fancyoung Dec 03 '15 at 06:45
1

Thanks for the second example. I also needed to deactivate foreign key check before dropping tables: `mysql --silent --skip-column-names -e "SHOW TABLES" DB_NAME | xargs -L1 -I% echo 'DROP TABLE \`%\`;' | mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" -v DB_NAME` – Nico Mar 12 '21 at 15:46
Thanks for the second example, it is exactly what I need to delete my tables ! – FABBRj Jan 23 '23 at 09:57

score 2 · Answer 3 · edited Feb 08 '20 at 15:04

2

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

edited Feb 08 '20 at 15:04

Borut D.

3
2

answered May 29 '15 at 00:47

sveilleux2

121
2

2

Welcome to Super User! While this may answer the question, it would be a better answer if you could explain *why* it does so. We’re looking for comprehensive, high-quality answers that provide some explanation and context. Don’t just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don’t include explanations may be removed. Also, check your formatting. – G-Man Says 'Reinstate Monica' May 29 '15 at 01:20
Not forget set foreign_key_checks = 1 after drop tables: see http://stackoverflow.com/a/2873991/4306855 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 – toto21 Nov 14 '15 at 14:10

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

3 Answers3