In some cases, we need to drop 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:
- Syntax of the Drop Database statement
- Creating a database using MySQL Workbench and command prompt.
- Dropping a database using MySQL Workbench and command prompt.
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.
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:
A new Apply SQL Script to Database window opens. There, you can see the Create Schema script. Click Apply to create 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.
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:
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:
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.
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):
We have created the EltechDB database, and the eltechDB directory has been created too:
Drop the Database Using MySQL 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
Thus, the EltechDB has been dropped.
Drop Database Using MySQL Workbench.
Open MySQL Workbench > Right-click on the EltechDB schema > select Drop Schema:
Before dropping the database, you will get a confirmation dialog:
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:
- 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:
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:
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.
