Q. How do I empty MySQL database? What SQL command needs to be executed in order to delete all (100s) of tables in MySQL database called atomstore?
A. You need to use DROP DATABASE sql command to drops all tables in the database/empty database and deletes the database. Be very careful with this statement! To use DROP DATABASE, you need the DROP privilege on the database
MySQL drop all tables syntax:
DROP DATABASE {mysql-database-name}
Method #1: Empty database with root user
In order to use this procedure you must have the drop and create database privilege (otherwise you will drop database but not able to create it again). Login as MySQL root or admin user to drop atomstore database:
$ mysql -u root -p
Now drop database:
mysql> DROP DATABASE atomstore;
Now create database again:
mysql> CREATE DATABASE atomstore;
Exit and close the session:
mysql> quit
Method #2: Drop all tables using shell script w/o root access
I've small handy shell script that removes all tables without dropping and creating MySQL database again.
#!/bin/bash MUSER="$1" MPASS="$2" MDB="$3" # Detect paths MYSQL=$(which mysql) AWK=$(which awk) GREP=$(which grep) if [ $# -ne 3 ] then echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name}" echo "Drops all tables from a MySQL" exit 1 fi TABLES=$($MYSQL -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' ) for t in $TABLES do echo "Deleting $t table from $MDB database..." $MYSQL -u $MUSER -p$MPASS $MDB -e "drop table $t" done
Simply use above script as follows to empty a database called quiz with username tom and password jerry:
$ ./drop.table.sh tom jerry quiz
Download MySQL empty database drop script
You can download complete script with error checking below:
Featured Articles:
- 20 Linux System Monitoring Tools Every SysAdmin Should Know
- My 10 UNIX Command Line Mistakes
- 10 Greatest Open Source Software Of 2009
- Top 5 Email Client For Linux, Mac OS X, and Windows Users
- Top 20 OpenSSH Server Best Security Practices
- Top 10 Open Source Web-Based Project Management Software
- Top 5 Linux Video Editor Software
- Email FAQ to a friend
- Download PDF version
- Printable version
- Comment RSS feed
- Last Updated: 11/13/08



{ 9 comments… read them below or add one }
Alternative methods:
In PHPMyAdmin, you can check all tables and then choose with selected: drop.
If you are doing it with scripting, You can use SHOW TABLES to list the tables, and use this to generate a query DROP TABLE [tablename],[tablename]…
You can have the drop privilege, but not the create database one!!
BE CAREFUL WHEN DOING THIS! You might end up with no DB at all.
Please don’t suggest this kind of procedures without explaining the whole nine yards.
Unfortunately this is no use if you don’t have permissions for create database (like the situation i am currently in).
Really informative post..thanks..:)
Great post and you do not need create database privileges for this. The method is only dropping the tables and not the database. The user should have create tables under the database..
Thanks
What about :
mysqldump -u[user] -p[ssht...] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[user] -p[sshht...] [DATABASE]You can use also:
mysql> use database;
mysql> truncate tablename;
to empty a table!
A little longer, but also a little more efficient because it does the drop (of all tables) in one shot:
mysql --user=YOUR_USERNAME --password=YOUR_PASSWORD -BNe "show tables" YOUR_DBSCHEMA_NAME | tr '\n' ',' | sed -e 's/,$//' | awk '{print "SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS " $1 ";SET FOREIGN_KEY_CHECKS = 1;"}' | mysql --user=YOUR_USERNAME --password=YOUR_PASSWORD YOUR_DBSCHEMA_NAME
I agree with Romina
I followed it and ended with completely deleting the database and not just the tables!! Your post title is absolutely incorrect. Please change it..
EVERYONE BE CAREFUL WHILE DOING THIS