MySQL DROP DATABASE

Sometimes you may need to delete database in MySQL. You can easily do this using MySQL DROP DATABASE command. Here’s how to delete database in MySQL.

How to Delete Database in MySQL

Here are the steps to DROP DATABASE in MySQL. We will use MySQL DROP DATABASE query to delete database.

Here’s the syntax of MySQL DROP DATABASE command

DROP DATABASE [IF EXISTS] database_name;

On successful execution, MySQL will return the number of tables deleted. In the above command, replace database_name with the database name that you want to delete.

If you try to delete a database that does not exist, then MySQL will show an error. So it is advisable to use the optional argument IF EXISTS

In MySQL, database is also known as SCHEMA. So you can also use DROP SCHEMA instead of DROP DATABASE.

DROP SCHEMA [IF EXISTS] database_name;

Bonus Read : MySQL DROP TABLE

MySQL DROP DATABASE from command line

Here’s an example of how to delete database from command line. You can use them to delete database from Ubuntu Linux CLI, and other linux systems.

Log into MySQL from command line. We use SHOW DATABASES command to list all the databases available for MySQL user.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dashboard          |
| fedingo            |
| mysql              |
| performance_schema |
| sample             |
| test               |
| test1              |
| test2              |
| test3              |
| test4              |
+--------------------+
11 rows in set (0.00 sec)

Next, we use MySQL DROP DATABASE command to delete database in MySQL

mysql> drop database test4;
Query OK, 0 rows affected (0.33 sec)

The output shows ‘0 rows affected’ since there are no tables in test4 database.

Bonus Read : MySQL DROP COLUMN

How To Drop All Databases in MySQL

Be careful before you delete multiple databases in MySQL. It cannot be reversed. By default, DROP DATABASE command allows you to delete only 1 database at a time. So if you want to delete multiple databases in MySQL, you need to run separate DROP DATABASE command for each database.

However, if you have many database tables and find it tedious, then here’s a script to drop all databases.

mysql -u<user> -p<password> -e "show databases" | 
grep -v Database | grep -v mysql | grep -v information_schema | 
gawk '{print "SET FOREIGN_KEY_CHECKS = 0;drop database
" $1 ";select sleep(0.1);"}' | mysql -u<user> -p<password>

In the above script, replace the parts in bold with your mysql username and password

Bonus Read : How to Get Records from Today in MySQL

Let us look at what each line does

mysql -u<user> -p<password> -e "show databases"

will log into MySQL and return a list of databases. Something like

Database
information_schema
my_db
another_db
api_platform
mysql
testdb
joomla
wordpress
dev
drupal

The first line Database is just a string and not a database name. The list of database names begin from line 2. Among them, information_schema is a system database and must not be deleted. So we remove these 2 lines from the above output

| grep -v Database | grep -v mysql | grep -v information_schema

Finally, we use gawk command to loop through the filtered list of database names, generate & run MySQL DROP DATABASE commands for each database name in the list.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.