How to Delete Entire MySQL Database

In some cases, we need to delete the entire database from the MySQL community server. This is simple but irreversible. Thus, the DROP DATABASE command must be used very carefully. The current article will highlight the specificities of the task and explain how to safely drop the MySQL database manually with the help of specialized tools. The following topics will be in the focus:

  1. Syntax of the Drop Database statement
  2. Creating a database using MySQL Workbench and command prompt.
  3. Dropping a database using MySQL Workbench and command prompt.
CodingSight - How to Delete Entire MySQL Database

Let’s start!

Syntax of the DROP DATABASE Command

The syntax is quite straightforward:

Drop database | schema [DatabaseName | SchemaName]

DatabaseName | Drop Schema specifies the name of that database you want to drop. Drop schema is the alternative command to drop the database.

Permission

To drop the database, a user must have the DROP DATABASE privilege on the database. When we drop the database, the privileges assigned to the user do not drop automatically – one has to drop them manually.

Note also that the drop database command does not drop temporary tables created by users.

Demo

For demonstration purposes, I have installed the MySQL Server community server on my workstation. Also, I have created a database named EltechDB with the following query:

mysql> create database EltechDB;
Query OK, 1 row affected (0.01 sec)

Alternatively, we can create the database using MySQL workbench or dbForge Studio for MySQL. I am using the MySQL Workbench solution, but dbForge Studio for MySQL ensures the same results and provides lots of additional helpful functionality.

Open MySQL workbench > Connect to the MySQL database server with appropriate credentials > right-click on the Schemas pane in the Navigator > select Create Schema.

Select Create Schema when  connecting to the MySQL database server with appropriate credentials

The window will open. Specify the desired database/schema in the Name field.

You can choose the desired character set and collation from the drop-down menu. In our case, the schema name is EltechDB. I have chosen the default character set and collation:

the schema name is EltechDB

A new Apply SQL Script to Database window opens. There, you can see the Create Schema script. Click Apply to create the database.

Create Schema script when creating the database

The script execution log is displayed in the Message Log textbox after successful execution. You can open it by clicking Show Logs.

Click on Finish to complete the process.

Apply SQL Script to database

When we create a database, MySQL creates a directory with the same name in the default data directory location. In our case, the path is C:\ProgramData\MySQL\MySQL Server 8.0\Data. To view that location, open MySQL Workbench and connect to MySQL using the root user. In the menu bar, click Server > Server Status:

MySQL Workbench

The Server Status tab opens to show the MySQL Server health details, features, and configuration settings. In the configuration list, you can view the Data Directory location:

Data Directory location in MySQL Workbench

Alternatively, you can view the configuration from the my.ini file located in the [InstallationDrive:]\ ProgramData\MySQL\MySQL Server 8.0 directory. In our case, the file is located on C:\ProgramData\MySQL\MySQL Server 8.0.

view the configuration from the my.ini file

To view the configuration, open that file with notepad or other text editors. There, you will see the data directory path specified in the Datadir parameter (C:/ProgramData/MySQL/MySQL Server 8.0\Data in our case):

data directory path specified in the Datadir parameter

We have created the EltechDB database, and the eltechDB directory has been created too:

the eltechDB directory has been created

MySQL Delete Database Using Command-line Utility

Important: As the DROP DATABASE statement is irreversible, make sure to take precautions before executing – create a backup of the database to save it.

The first option is to drop the database using the MySQL command-line utility.

Open the command prompt and specify the below command to connect to the MySQL database server (we are using root users to connect, so please specify the appropriate password):

C:\Users\Nisarg>mysql -u root -p
Enter password:

Once connected, run the following command to drop the database:   

mysql> drop database EltechDB;
Query OK, 0 rows affected (0.07 sec)

The query returns Query OK, 0 rows affected (0.07 sec) indicating that the database has been dropped. You can run the show databases command to check it:

mysql> show databases;

Output

EltechDB has been dropped

Thus, the EltechDB has been dropped.

Drop Database Using MySQL Workbench.

Open MySQL Workbench > Right-click on the EltechDB schema > select Drop Schema:

Drop Database Using MySQL Workbench.

Before dropping the database, you will get a confirmation dialog:

Review SQL

You can choose between the following options:

  • Review SQL – get the specific command to drop the database by the MySQL Workbench. You can copy that command and paste it into the MySQL native client or query editor to drop the schema:
Review the SQL Code to Execute
  • Drop Now – drop the database directly.

Note: To drop the database, choose the Review SQL option – it gives an additional confirmation dialog box and helps you to review the database that you are dropping.

Once the task is completed, the schema will disappear from the Navigator’s Schema pane:

schema is disappeared from the Navigator’s Schema pane

Objects that are Deleted

When we drop the database, it deletes the directory in which the database files reside. In our case, the Drop database command deletes the EltechDB directory:

Drop database command deletes the EltechDB directory

Summary

Thus, we’ve learned how to create a database using MySQL Workbench and DOS commands. Then, we’ve explored different ways of how to delete a database in MySQL with the help of MySQL command-line utility and MySQL Workbench.

Nisarg Upadhyay

Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.