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:
You should follow me on twitter here or grab rss feed to keep track of new changes.
Featured Articles:
- 30 Handy Bash Shell Aliases For Linux / Unix / Mac OS X
- Top 30 Nmap Command Examples For Sys/Network Admins
- 25 PHP Security Best Practices For Sys Admins
- 20 Linux System Monitoring Tools Every SysAdmin Should Know
- 20 Linux Server Hardening Security Tips
- Linux: 20 Iptables Examples For New SysAdmins
- Top 20 OpenSSH Server Best Security Practices
- Top 20 Nginx WebServer Best Security Practices
- 20 Examples: Make Sure Unix / Linux Configuration Files Are Free From Syntax Errors
- 15 Greatest Open Source Terminal Applications Of 2012

- My 10 UNIX Command Line Mistakes
- Top 10 Open Source Web-Based Project Management Software
- Top 5 Email Client For Linux, Mac OS X, and Windows Users
- The Novice Guide To Buying A Linux Laptop











{ 33 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]Worked purfect 4 me, tnx. What a clean and tidy script.
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
This worked perfecly. Save me the monotony of deleting 300+ tables from a Magento Install
this was very useful….works great ! thanks !
Perfect worked
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
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?
I have an apple laptop i was the person earlier
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
You should disable FOREIGN_KEY_CHECKS. I like Lon F. Binder solution.
How can i change this script so that it erases the rows from some defined tables?
thanks for the valuable information :)
Thank’s A lot. For method#2 : it works!
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 .
Call me paranoid, but I don’t like to put my database password in scripts. So I replaced the definition of MPASS with
Thanks for nice ppost
Had to put the table name in quotes in the for loop:
$MYSQL -u $MUSER -p$MPASS $MDB -e “drop table \`$t\`”
Yep, me too. Wish I had read this far down the comments lol
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.
thanks, exactly what I was looking for. I used this to replace my Wordpress database.
is it possible to use simple truncate on database?
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" doneCombined 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" $DBSCHEMAThe 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]
———————-
Nice script. I changed it to drop all the views from a database. If you wanna see it, I posted at:
stackoverflow.com
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 ?