≡ Menu

MySQL Empty Database / Delete or Drop All Tables

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.

# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)
if [ $# -ne 3 ]
	echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name}"
	echo "Drops all tables from a MySQL"
	exit 1
TABLES=$($MYSQL -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )
for t in $TABLES
	echo "Deleting $t table from $MDB database..."
	$MYSQL -u $MUSER -p$MPASS $MDB -e "drop table $t"

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:

Share this tutorial on:

Your support makes a big difference:
I have a small favor to ask. More people are reading the nixCraft. Many of you block advertising which is your right, and advertising revenues are not sufficient to cover my operating costs. So you can see why I need to ask for your help. The nixCraft, takes a lot of my time and hard work to produce. If you use nixCraft, who likes it, helps me with donations:
Become a Supporter →    Make a contribution via Paypal/Bitcoin →   

Don't Miss Any Linux and Unix Tips

Get nixCraft in your inbox. It's free:

{ 49 comments… add one }
  • Jon March 11, 2008, 10:25 am

    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]…

  • romina May 16, 2008, 8:41 pm

    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.

  • Colin July 1, 2008, 3:22 pm

    Unfortunately this is no use if you don’t have permissions for create database (like the situation i am currently in).

  • google pagerank August 29, 2008, 6:41 am

    Really informative post..thanks..:)

  • Mas February 2, 2009, 12:14 am

    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..


  • OnkelBeh March 11, 2009, 12:10 pm

    What about :
    mysqldump -u[user] -p[ssht...] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[user] -p[sshht...] [DATABASE]

    • sergeysynergy February 28, 2013, 9:57 am

      Worked purfect 4 me, tnx. What a clean and tidy script.

    • fritig March 9, 2014, 6:10 pm

      YES it works, I was searching for that since 24 HOURS ! Thank you very much

    • Marco Cacchiani June 22, 2015, 1:24 pm

      GENIUS … this is the word!
      Thank you (even with 6 years delay!)

  • ddb May 14, 2009, 5:20 am

    You can use also:
    mysql> use database;
    mysql> truncate tablename;
    to empty a table!

  • Lon F. Binder September 22, 2009, 8:34 pm

    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

    • Russell July 21, 2010, 10:08 pm

      This worked perfecly. Save me the monotony of deleting 300+ tables from a Magento Install

    • Manasi April 19, 2011, 10:00 pm

      this was very useful….works great ! thanks !

    • james August 16, 2012, 5:57 am

      Perfect worked

    • Vijayan May 4, 2015, 5:24 pm

      Thank you so much. its working perfectly….

    • Niek Oost August 25, 2015, 1:08 pm

      When the database contains tables with names that are the same as the MySQL reserved keywords (for instance ‘right`), you need a minor addition:

      mysql --user=YOUR_USERNAME --password=YOUR_PASSWORD -BNe "show tables" YOUR_DBSCHEMA_NAME | tr '\n' ','  | sed -e 's/,/`,`/g' | sed -e 's/,`$//' | 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

      This adds back-ticks around the table names

  • Harshit December 15, 2009, 3:50 pm

    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..


  • Angie May 14, 2010, 2:38 am

    I want to erase everything that has been looked up on google and everything that has already been looked up like a restart how can i reboot?

  • ANgie May 14, 2010, 2:39 am

    I have an apple laptop i was the person earlier

  • Clemens May 20, 2010, 10:42 am

    I like this even more.

    echo “select concat(‘drop table ‘, table_name, ‘;’) from information_schema.tables where table_schema=schema()” | mysql CONNECT | grep “^drop table ” | mysql CONNECT

    Make sure you connect to the right schema :p

    • Morten Vinding March 27, 2014, 2:16 pm

      That’s the most elegant solution IMHO!

  • Anonymous May 31, 2010, 5:04 pm

    You should disable FOREIGN_KEY_CHECKS. I like Lon F. Binder solution.

  • Jack July 15, 2010, 5:42 pm

    How can i change this script so that it erases the rows from some defined tables?

  • Shweta September 6, 2010, 9:29 am

    thanks for the valuable information :)

  • herik October 18, 2010, 2:44 am

    Thank’s A lot. For method#2 : it works!

  • Ashok yadav October 31, 2010, 9:33 am

    To empty the database in MySql just go to the database and select the all tables (checkbox on of all tables) then select the “empty” action just down from the table names, then click “GO” button just right side of action selected .

  • Sutharsan January 19, 2011, 9:41 am

    Call me paranoid, but I don’t like to put my database password in scripts. So I replaced the definition of MPASS with

    read -s -p "Your database password: " MPASS
  • Thiyagarajan Veluchamy February 11, 2011, 4:45 pm

    Thanks for nice ppost

  • PrinCe May 6, 2011, 9:55 am

    simply rite click on databas and delete and chek on close connection :)

  • Sonet June 1, 2011, 4:06 am

    Had to put the table name in quotes in the for loop:

    $MYSQL -u $MUSER -p$MPASS $MDB -e “drop table \`$t\`”

    • Frank November 14, 2011, 8:57 am

      Yep, me too. Wish I had read this far down the comments lol

  • Jason Goodwin August 5, 2011, 2:08 pm

    I use the script all the time to save me from needing to add privileges again if I’m clearing out a db. Thanks a lot.

  • JP Lew October 22, 2011, 5:06 pm

    thanks, exactly what I was looking for. I used this to replace my Wordpress database.

  • risdiyanto January 28, 2012, 4:38 am

    is it possible to use simple truncate on database?

  • Andrea De Pirro February 29, 2012, 12:47 pm

    little mod for remote hosts:

    # Detect paths
    MYSQL=$(which mysql)
    AWK=$(which awk)
    GREP=$(which grep)
    if [ $# -ne 4 ]
    	echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name} {MySQL-Database-Host}"
    	echo "Drops all tables from a MySQL"
    	exit 1
    TABLES=$($MYSQL -h $HOST -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )
    for t in $TABLES
    	echo "Deleting $t table from $MDB database..."
    	$MYSQL -h $HOST -u $MUSER -p$MPASS $MDB -e "drop table $t"
  • August Detlefsen May 10, 2012, 9:10 pm

    Combined Lon F. Binder, Sutharsan, and Andrea De Pirro’s suggestions into one script, using getopt to make the DB host and port optional:

    cat <<EOF
    usage: $0 options
    WARNING! This script will drop all tables from a MySQL database
       -u      Database username
       -d      Database/schema name
       -h      Host name (optional)
       -p      Port (optional)
    # Read options from command line
    while getopts “u:d:h:p” OPTION
         case $OPTION in
    # Make sure all required options are supplied
    if [[ -z $USERNAME ]] || [[ -z $DBSCHEMA ]] 
         exit 1
    # Read password from stdin
    read -s -p "Your database password: " PASSWORD
    # Construct MySQL command line options
    MYSQL_OPTS="--user=$USERNAME --password=$PASSWORD"
    if [[ ! -z $HOST ]]
        MYSQL_OPTS="$MYSQL_OPTS --host=$HOST"
    if [[ ! -z $PORT ]]
        MYSQL_OPTS="$MYSQL_OPTS --port=$PORT"
    # Get the table names
    TABLES=$(mysql $MYSQL_OPTS -BNe "show tables" $DBSCHEMA | tr '\n' ',' | sed -e 's/,$//' | awk '{print "SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS " $1 ";SET FOREIGN_KEY_CHECKS = 1;"}')
    # Actually drop the tables
  • GSC July 21, 2012, 2:22 pm

    The script method worked like a charm on cento6.

    In case the
    $ ./drop.table.sh tom jerry quiz
    doesnt work, since don’t have root access, you can also do it with the simple sh command.

    [user@servername] sh drop.table.sh tom jerry quiz [enter]

  • Fernando Camargo September 13, 2012, 5:37 pm

    Nice script. I changed it to drop all the views from a database. If you wanna see it, I posted at:

  • Ebad March 30, 2013, 3:19 am


    I just wanted to delete my records from the tables not the tables itself and stored procedures! But I have deleted the whole database with this command.
    Is there any way to recover ?

  • Bhaveshkumar July 23, 2013, 12:31 pm

    Great! Keep it up

  • jeremia katambo July 30, 2013, 10:11 pm

    wooow, thank you soo much this really helped me

  • shir October 6, 2013, 1:59 pm

    thanks for sharing.. i’ve been searching for the answer for months

  • bangscherang December 5, 2013, 11:24 am
    SET @tables = NULL;
    SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables
      FROM information_schema.tables 
      WHERE table_schema = 'database-name-here'; -- specify DB name here.
    SET @tables = CONCAT('DROP TABLE ', @tables);
    PREPARE stmt FROM @tables;
    EXECUTE stmt;

    Source here

  • leekh March 24, 2014, 7:28 am

    I have an old Staff Attendance Program. There is a Master Program and all connected to msyql database. When I tried to open the Staff Attendance Program a message box pops up…”This program was last run on 12-Feb-2029. Current server date is 20-Mar-2014. Admin should change the server date.” When I opened the mysql tables to check, I can see a calendar table for year 2029. How to go about solving the problem please?
    I am the headmaster of the school and I dont have any technical knowledge. Help please.

  • Luis Pichardo June 11, 2014, 3:22 pm

    The shell script worked like a charm. Thanks!

  • Henry Crutcher June 17, 2014, 4:18 pm

    Worked perfectly. I made a few tweaks — added the hostname and put the password as the last argument. The modified one is below in case it is interesting to someone…

    # Detect paths                                                                                                                                                                                         
    MYSQL=$(which mysql)
    AWK=$(which awk)
    GREP=$(which grep)
    if [ $# -ne 4 ]
        echo "Usage: $0 {MySQL-Host-Name} {MySQL-User-Name} {MySQL-Database-Name} {MySQL-User-Password}"
        echo "Drops all tables from a MySQL"
        exit 1
    TABLES=$($MYSQL -h $HOST -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )
    for t in $TABLES
        echo "Deleting $t table from $MDB database..."
        $MYSQL -h $HOST -u $MUSER -p$MPASS $MDB -e "drop table $t"
  • sam July 18, 2014, 9:49 pm

    If you want to force delete the tables even if they are any foreign key relation exists , just update the line

    $MYSQL -u $MUSER -p$MPASS $MDB -e “drop table $t”

    $MYSQL -u $MUSER -p$MPASS $MDB -e “SET FOREIGN_KEY_CHECKS = 0;drop table $t”

  • Dallas G. December 9, 2014, 6:07 pm

    In case you don’t want to post your database password in clear text in the terminal (also added requirement for host designation:

    # Detect paths
    MYSQL=$(which mysql)
    AWK=$(which awk)
    GREP=$(which grep)
    if [ $# -ne 3 ]
            echo "Usage: $0 {MySQL-User-Name} {MySQL-Database-Name} {MySQL-Database-Server}"
            echo "Drops all tables from a MySQL"
            exit 1
    echo -e "Please enter MySQL password for $MUSER"
    read -s MPASS
    TABLES=$($MYSQL -u $MUSER -p$MPASS -D $MDB -h $MDBS -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )
    for t in $TABLES
            echo "Deleting $t table from $MDB database..."
            $MYSQL -u $MUSER -p$MPASS -D $MDB -h $MDBS -e "drop table $t"
  • lee goddard May 22, 2015, 9:02 am

    Don’t forget about procedures, triggers, events…

Leave a Comment

You can use these HTML tags and attributes: <strong> <em> <pre> <code> <a href="" title="">

   Tagged with: , , , , , , , ,