Drop / Delete Mysql Database or Drop All Tables

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:

Now drop database:

Now create database again:

Exit and close the session:

Or you can also use mysqladmin to drop the database (in this case called “cloud”)

mysqladmin status -p
mysqladmin drop cloud -p

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.

Simply use above script as follows to empty a database called quiz with username tom and password jerry:

Download MySQL empty database drop script

You can download complete script with error checking below:



Leave a Reply