How to empty a MySQL database from the shell (drop all tables)

1

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

(You might not need the username and password fields - depends on your setup)

2

mysql -h mysql.domain.com -u uname -p[PASSWORD] dbname -e "show tables" | grep -v Tables_in | grep -v "+" | gawk '{print "drop table " $1 ";"}' | mysql -h mysql.domain.com -u uname -p[PASSWORD] dbname

What this does is

• connect to a specific mysql database and execute the command for showing tables
• find lines that match “Tables_in” and not show them
• find lines that match the + character and not show them
• use gawk to print out the words “drop table” followed by the table name (which is in $1) and then a semicolon
• pipe all of that back to the database you got the list from to drop those tables

There is a very good reason to use 'show tables' and not mysqldump, as tip #1. If you have a large database 'show tables' will offer a superior performance.

(You might not need the hostname, username and password fields - depends on your setup)

3

You can also login into mysql and then using DROP and CREATE database.

# mysql -uroot -p
mysql> DROP DATABASE dbname;
mysql> CREATE DATABASE dbname;

although it doesn't preserve any settings applied to the database (for example, character set or collation)

4

Using phpMyAdmin - There's an option to Select All tables, then apply "DROP" command. It deletes all tables in the database. But that would not be cool, right?