Managing MySQL databases using the command line

In this tutorial, I will demonstrate how to manage MySQL databases using the command line on Ubuntu. If you prefer not to use the command line method, you can also manage your databases using PHPMyAdmin. However, managing databases is just as easy with the command line.

To follow this guide, you must have MySQL installed on your Linux VPS and have access to the MySQL server. If you have access to the server and MySQL is installed on it, you can proceed with the tutorial.

Firstly, log in to your MySQL server using the following command. You will be prompted to enter the MySQL root password. Make sure to enter the correct password, and you will be logged in to the MySQL server installed on your machine.

$ mysql -uroot -p;

Creating a Database in MySQL via Command Line

Creating a database in MySQL is an incredibly simple task. It only requires a query with three words. Let’s say we want to create a database named “Test”. To create a database named “Test”, execute the following command in your MySQL command line interface.

mysql> CREATE DATABASE Test;

You can replace “Test” with the desired name for your database. In MySQL or any other DBMS, you cannot create a duplicate database on the same installation/instance/machine. Sometimes, you may want to create a database only if it doesn’t already exist. To create a database if it doesn’t already exist, execute the following command.

mysql> CREATE DATABASE IF NOT EXISTS Test;

There is another method to create a database from the command line. You can also use mysqladmin to create and manage databases. To create a database in MySQL using mysqladmin, exit from the MySQL CLI and execute the following command on your machine.

$ mysqladmin -uroot -p create Test;

These are the methods for creating databases in MySQL using the command line. Now, let’s see how to list and search for databases in MySQL.

Listing and Using Databases in MySQL

Now, let’s learn how to list databases in MySQL. We will also see how to perform actions on specific databases and how to check if a specific database exists on our server.

Firstly, let’s list all the databases created on our machine. To do this, log in to your MySQL command line interface using the command mentioned above, and then execute the following command.

mysql> SHOW DATABASES;

This should give you an output similar to the one shown below.

+--------------------+
| Database           |
+--------------------+
| information_schema |
| Test               |
| Test2              |
| if                 |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
8 rows in set (0.00 sec)

Now, in order to perform any kind of action, such as creating a database, we first need to select that database. To select a database, execute the following command in your MySQL command line interface.

mysql> use test;

Make sure to replace the name of the database in the command. Now, to see all the tables created within the database, execute the following command.

mysql> SHOW TABLES;

You should see an output similar to the following.

+---------------------------+
| Tables_in_test            |
+---------------------------+
| users                     |
| test                      |
+---------------------------+
2 rows in set (0.00 sec)

Next, let’s see how we can delete a specific database in MySQL using the command line.

Dropping a Database in MySQL

In this section of the guide, you will learn how to drop a database in MySQL. Dropping a database in MySQL means deleting it. Dropping a database is as easy as creating one. To drop a specific database, execute the following command.

mysql> DROP DATABASE test;

Sometimes, you may want to drop a database only if it exists. In that case, you can use the “IF EXISTS” option in the query. To drop a database in MySQL only if it exists, execute the following command.

mysql> DROP DATABASE IF EXISTS test;

You can also drop the database using mysqladmin. To drop a MySQL database using mysqladmin, execute the following command in your console.

$ mysqladmin -uroot -p drop test;

These are the methods you can use to drop a database in MySQL using the command line. Now, let’s see how to rename a database in MySQL.

Renaming a Database in MySQL

MySQL does not provide any built-in query or command to directly rename a database. However, we can easily export and import databases. This means that the best method to rename a database is to export the old database to an SQL file, create a new database, and import the SQL file into the new database.

For example, let’s say we want to rename our database from ‘test’ to ‘test2’. To do this, we first need to dump the database contents into an SQL file. Execute the following command to do so.

$ mysqldump -uroot -p test > dbdump.sql

Once done, create a new database with the new name using the following command. You can also log in to MySQL to create a database, but the mysqladmin method will be easier in this case.

$ mysqladmin -uroot -p create test2;

Once the database is ready, execute the following command to import the database from the SQL file into the new database.

$ mysql -uroot -p test2 < dbdump.sql

This is how you can rename a database in MySQL.

Conclusion: Managing databases in MySQL via the command line is very straightforward. The commands/queries are easy to read and remember, with almost no optional arguments or options. Creating, deleting, renaming, and listing databases in MySQL are the most important tasks, and that’s all you need to know about managing databases in MySQL. However, this guide does not cover MySQL user and privilege management. Without proper users and privileges management, databases can be very insecure or vulnerable.

We will create another guide in which we will show you how to securely manage MySQL users and privileges on the databases. If you get stuck or have any questions, please feel free to use the comment section below.