MySQL Empty Database / Delete or Drop All Tables

by on May 1, 2007 · 43 comments· LAST UPDATED November 13, 2008

in , ,

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:

TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

{ 43 comments… read them below or add one }

1 Jon March 11, 2008 at 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]…

Reply

2 romina May 16, 2008 at 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.

Reply

3 Colin July 1, 2008 at 3:22 pm

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

Reply

4 google pagerank August 29, 2008 at 6:41 am

Really informative post..thanks..:)

Reply

5 Mas February 2, 2009 at 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..

Thanks

Reply

6 OnkelBeh March 11, 2009 at 12:10 pm

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

Reply

7 sergeysynergy February 28, 2013 at 9:57 am

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

Reply

8 fritig March 9, 2014 at 6:10 pm

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

Reply

9 ddb May 14, 2009 at 5:20 am

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

Reply

10 Lon F. Binder September 22, 2009 at 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

Reply

11 Russell July 21, 2010 at 10:08 pm

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

Reply

12 Manasi April 19, 2011 at 10:00 pm

this was very useful….works great ! thanks !

Reply

13 james August 16, 2012 at 5:57 am

Perfect worked

Reply

14 Harshit December 15, 2009 at 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..

EVERYONE BE CAREFUL WHILE DOING THIS

Reply

15 Angie May 14, 2010 at 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?

Reply

16 ANgie May 14, 2010 at 2:39 am

I have an apple laptop i was the person earlier

Reply

17 Clemens May 20, 2010 at 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

Reply

18 Morten Vinding March 27, 2014 at 2:16 pm

That’s the most elegant solution IMHO!

Reply

19 Anonymous May 31, 2010 at 5:04 pm

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

Reply

20 Jack July 15, 2010 at 5:42 pm

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

Reply

21 Shweta September 6, 2010 at 9:29 am

thanks for the valuable information :)

Reply

22 herik October 18, 2010 at 2:44 am

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

Reply

23 Ashok yadav October 31, 2010 at 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 .

Reply

24 Sutharsan January 19, 2011 at 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

Reply

25 Thiyagarajan Veluchamy February 11, 2011 at 4:45 pm

Thanks for nice ppost

Reply

26 PrinCe May 6, 2011 at 9:55 am

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

Reply

27 Sonet June 1, 2011 at 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\`”

Reply

28 Frank November 14, 2011 at 8:57 am

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

Reply

29 Jason Goodwin August 5, 2011 at 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.

Reply

30 JP Lew October 22, 2011 at 5:06 pm

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

Reply

31 risdiyanto January 28, 2012 at 4:38 am

is it possible to use simple truncate on database?

Reply

32 Andrea De Pirro February 29, 2012 at 12:47 pm

little mod for remote hosts:

#!/bin/bash
MUSER="$1"
MPASS="$2"
MDB="$3"
HOST="$4"
# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)
if [ $# -ne 4 ]
then
	echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name} {MySQL-Database-Host}"
	echo "Drops all tables from a MySQL"
	exit 1
fi
TABLES=$($MYSQL -h $HOST -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 -h $HOST -u $MUSER -p$MPASS $MDB -e "drop table $t"
done

Reply

33 August Detlefsen May 10, 2012 at 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:

#!/bin/bash
usage()
{
cat <<EOF
usage: $0 options
WARNING! This script will drop all tables from a MySQL database
OPTIONS:
   -u      Database username
   -d      Database/schema name
   -h      Host name (optional)
   -p      Port (optional)
EOF
}
USERNAME=
DBSCHEMA=
HOST=
PORT=
# Read options from command line
while getopts “u:d:h:p” OPTION
do
     case $OPTION in
	 u)
	     USERNAME=$OPTARG
             ;;
	 d)
	     DBSCHEMA=$OPTARG
             ;;
         h)
	     HOST=$OPTARG
             ;;
         p)
             PORT=$OPTARG
             ;;
         ?)
             usage
             exit
             ;;
     esac
done
# Make sure all required options are supplied
if [[ -z $USERNAME ]] || [[ -z $DBSCHEMA ]]
then
     usage
     exit 1
fi
# 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 ]]
then
    MYSQL_OPTS="$MYSQL_OPTS --host=$HOST"
fi
if [[ ! -z $PORT ]]
then
    MYSQL_OPTS="$MYSQL_OPTS --port=$PORT"
fi
# 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
mysql $MYSQL_OPTS -BNe "$TABLES" $DBSCHEMA

Reply

34 GSC July 21, 2012 at 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]
———————-

Reply

35 Fernando Camargo September 13, 2012 at 5:37 pm

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

Reply

36 Ebad March 30, 2013 at 3:19 am

JUST BE CAREFUL!

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 ?

Reply

37 Bhaveshkumar July 23, 2013 at 12:31 pm

Great! Keep it up

Reply

38 jeremia katambo July 30, 2013 at 10:11 pm

wooow, thank you soo much this really helped me

Reply

39 shir October 6, 2013 at 1:59 pm

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

Reply

40 bangscherang December 5, 2013 at 11:24 am
SET FOREIGN_KEY_CHECKS=0;
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;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS=1;

Source here

Reply

41 leekh March 24, 2014 at 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.
Thanks.
patricklee

Reply

42 Luis Pichardo June 11, 2014 at 3:22 pm

The shell script worked like a charm. Thanks!

Reply

43 Henry Crutcher June 17, 2014 at 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…

#!/bin/bash
HOST="$1"
MUSER="$2"
MDB="$3"
MPASS="$4"
# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)
if [ $# -ne 4 ]
then
    echo "Usage: $0 {MySQL-Host-Name} {MySQL-User-Name} {MySQL-Database-Name} {MySQL-User-Password}"
    echo "Drops all tables from a MySQL"
    exit 1
fi
TABLES=$($MYSQL -h $HOST -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 -h $HOST -u $MUSER -p$MPASS $MDB -e "drop table $t"
done

Reply

Leave a Comment

Tagged as: , , , , , , , ,

Previous Faq:

Next Faq: