How to Drop a Database in MySQL:
A Complete Guide

The DROP DATABASE statement is the simplest way to delete an existing MySQL database. It is one of the key operations in database management and, at the same time, one that causes an irreversible action that might cause data loss; and thus it needs to be applied carefully.

What does dropping a database in MySQL mean?

To drop a MySQL database means to remove all tables, data, and files associated with the said database.

Whether you have copied a database to a new environment and now you need to delete it from the old one, or you simply need to free up some space and remove a database that is no longer relevant, you can do it with ease.

Key precautions before dropping a MySQL database

Since dropping a database means permanently deleting all of the data stored in it, you need to take several precautions.

• To drop a MySQL database, you must have the corresponding user privileges.
• Make sure you are targeting the correct database. For instance, if you have identically named databases on different connections, deleting the wrong one might be a trouble.
• Just in case, you might want to back up the database you're about to delete.

How to drop a database in MySQL: General syntax

The SQL syntax for this operation is very simple. You can run it from any MySQL management tool that allows writing and executing queries.

DROP {DATABASE | SCHEMA} [IF EXISTS] database_name;

That's it! Yet, despite the simplicity, there are quite a few additional tips we'd like to share.

• In MySQL, DROP SCHEMA is a synonym for DROP DATABASE. They can be used interchangeably.
• The IF EXISTS condition is used to prevent an error from occurring if the database does not exist.
• If the default database is dropped, the default database is unset (the DATABASE() function returns NULL).
• If you run the MySQL DROP DATABASE command on a symbolically linked database, both the link and the original database will be deleted.
• DROP DATABASE deletes files and directories created by MySQL and associated with the dropped database. This includes files with the following extensions: .bak, .dat, .hsh, .mrg, .myd, .myi, .cfg, .db, .idb, and .ndb.
• Also note that if other files or directories remain in the database directory after the DROP operation, the said directory will not be removed; in this case, you need to remove them manually and run your DROP DATABASE statement once again.
• Upon execution, DROP DATABASE returns the number of tables that were removed.
• Once you drop a database, you can double-check it by running the SHOW DATABASES; command. If your database has been dropped successfully, it will not be on the list of existing databases.

Drop a MySQL database from the command line

There's an alternative way; you can delete MySQL databases from the command line.

1. First, you need to log in; to do this, make sure your account has sufficient privileges to drop databases. Then, open the mysql shell and enter the following command.

mysql -u {username} -p'{password}' -h {hostname} -P {port}

In this syntax:

• -u is the user name
• -p is the password; make sure there are no spaces in this construct
• -h is the host name or IP
• -P is the port of your remote server

2. Once you log in, you will use SQL to manage your databases. You might as well start with the SHOW DATABASES; command to get a list of available databases. It is an optional step, yet it's better to make sure that you have logged in correctly and your database is on the list.

3. To remove MySQL databases, you can use the same DROP DATABASE statement. Before you do that, take note that there will be no "Are you sure?" message, and your command will be executed immediately.

DROP DATABASE database_name;

Afterwards, you can run SHOW DATABASES; once again to make sure your database has been deleted.

How to drop a MySQL database with dbForge Studio for MySQL

You can perform a variety of operations with your databases using dbForge Studio for MySQL, a comprehensive IDE that provides you with everything you need to design, develop, and manage MySQL and MariaDB databases.

Accessing and deleting your database with the Studio is extremely simple.

1. Open the Studio and connect to the required instance with the help of a convenient wizard. Simply put, you need to enter your credentials and hit Connect.

2. Click New SQL on the toolbar. This will open the Studio's SQL Editor, where you'll need to enter your DROP DATABASE statement. Note how the Studio's code completion suggests the required database as you type. After you enter the name, you only need to run the query.

Troubleshooting common issues while removing MySQL databases

Error: "Access Denied"

This error may occur in case your user account has insufficient privileges.

Error: "The Database Does Not Exist"

This error will occur if the database you want to delete does not exist.

To prevent this error, simply add the IF EXISTS condition to your query.

Alternatives to dropping a database in MySQL

Dropping a database isn't always the optimal way. Sometimes, you'd rather need to rename or export it.

To learn all about renaming MySQL databases, refer to our blog post How to Rename a MySQL Database.

In some cases, you might want to export and archive your MySQL database instead of just deleting it. To explore different ways of exporting a database using dbForge Studio, refer to How to Export and Import a MySQL Database.

And if you are dealing with database migration, you might want to check another post, Best Way to Copy MySQL Databases.

Conclusion

Now you know how to delete a database in MySQL; and we suggest you get some firsthand experience with database management using dbForge Studio. It boasts quite a few capabilities that can further enhance your work with MySQL and MariaDB databases.

• Intelligent code completion, formatting, refactoring, and debugging
• Comparison and synchronization of database schemas and table data
• Visual database design and query building
• Query optimization
• Data import and export supporting up to 14 data formats
• Generation of meaningful test data
• Management of user accounts and privileges
• Database migration, backup and recovery
• Pivot tables for data analysis
• Generation of full database documentation and data reports
• ...and much more!

You can give it a go by downloading the Studio for a free 30-day trial. Feel free to explore it and see how effectively it can simplify and automate your daily MySQL management.