MySQL is a popular RDBMS that powers many websites and applications. It can be deployed in a wide range of environments, including cloud VPS and bare-metal servers.
Since MySQL is compatible with so many different operating systems and configurations, chances are you’ll find yourself working in an environment where you might have access to GUI-based tools.
This article covers the scenario where you’d need to create a database in MySQL using the command line. Next, we’ll cover some related database management tasks that you can carry out once you’ve created the database.
Table Of Content
Let’s start with the prerequisites.
Prerequisites
Our test server for this tutorial:
- A VPS running Ubuntu 20.04
- A user with root privileges
The good thing about this tutorial is that you can apply the steps of this tutorial in almost all MySQL installations with similar results.
Let’s start with creating a MySQL database.
Create A Database In MySQL Using Command Line
The first step is to log in to the MySQL server. Launch the terminal and use the following command to Log into your MySQL server from the command line using the following command.
Important: Use the -u flag to set the user as root and the -p flag to get the prompt for the password.
#mysql -u root -p
You will be prompted to enter your current password. Once you have entered your password, you will be logged in to the MySQL server (you can notice that the prompt has changed from the terminal to MySQL>).
View All Existing Databases
Before creating a new database, it’s always a good idea to view the list of existing databases because you cannot have two databases with the same name.
Use the following command to quickly see the list of databases, use the following command:
mysql> SHOW DATABASES;
You will see all the available databases listed on the screen.
Create A Database Using The Command Line
Now, I’d show you how you can create a database using a simple command. To demonstrate, I’ll create a database named Redswitches_database with the following command:
mysql> CREATE DATABASE Redswitches_database;
You’ll not see a detailed message, except a Query OK. To see if the database has been created, you can again check out the available databases on the server. For this, we use the SHOW DATABASES; command. Here is the resulting output:
As you can see, the list now includes Redswitches_database in the results.
At this point, you have a fresh database ready for use.
How To Delete A Database In MySQL Using Command Line
In addition to creating databases, a related task is deleting databases. Like the command for creating a database, MySQL provides a way of deleting databases with a command.
For deleting a database in MySQL using the command line, use the following command:
mysql> DROP DATABASE Redswitches_database;
Important: This action is permanent, and recovering data from deleted databases can prove challenging. So use extreme caution with this command.
Given the seriousness of the action, the command might ask you for confirmation. Press ENTER to continue.
After deleting a database, you should always verify the deletion with the SHOW DATABASE command.
Conclusion
This article covered the MySQL commands for creating databases using the command line. This is a quick way of creating databases without going through the GUI. We also mentioned how you could view the list of available databases and remove databases from the server.
