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.

#!/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:

• MySQL Drop All Tables Shell Script Utility

• Tweet
• 
• 

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