MySQL is no doubt one of the most popular and loved Relational Database Management system in the world. Majority of the websites you have visited till now stores the data in MySQL database. And you too are here to learn how to manage databases in MySQL via command line in Ubuntu.
In this guide, I will show you how to manage MySQL databases via Command line in Ubuntu. You can also manage your databases using PHPMyAdmin if you don’t want to go with the command line method. However, managing databases is easy with the command line method too.
To follow this tutorial, you must have MySQL installed on your Linux VPS and you must have access to the MySQL server. If you have access to the server and also to the MySQL installed on the server, You can continue with the tutorial.
First of all, Log in to your MySQL server using the command given below. It will ask you for MySQL root password, enter the correct password and you will be logged in to the MySQL server installed on your machine.
$ mysql -uroot -p;
Create Database in MySQL on Command Line
Creating a database in MySQL is the easiest task ever. It just takes a query with three words. Let’s assume we want to create a database with name “Test”. To create a database with name “Test”, execute the following command into your MySQL command line interface.
mysql> CREATE DATABASE Test;
You can obviously replace “Test” with the name of the database you desire to create. In MySQL or any other DBMS, you cannot create a duplicate database on the same installation/instance/machine. Sometimes, you want to create a database only if it does not already exist. To create a database if it does not already exists, execute the following command.
mysql> CREATE DATABASE IF NOT EXISTS Test;
There is one more method to create a database from command line. We 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 in your machine.
$ mysqladmin -uroot -p create Test;
So, these are the methods to create databases in MySQL using command line. Now, Let’s see how to list and search databases in MySQL.
List and Use Database in MySQL
Now, Let’s see how to list databases in MySQL. We will also see how to perform actions in specific database and we will also see how to check if specific database exist on our server.
First of all, Let’s list all the databases created on our machine. To perform this task, login to your MySQL command line interface using the command I mentioned above. And then execute the following command.
mysql> SHOW DATABASES;
You should get an output similar to the one given below.
+--------------------+ | Database | +--------------------+ | information_schema | | Test | | Test2 | | if | | mysql | | performance_schema | | sys | | test | +--------------------+ 8 rows in set (0.00 sec)
Now, to perform any kind of action like creating a database, we first have to select that database. To select a database, execute the following command in your MySQL command line interface.
mysql> use test;
Do not forget to replace the name of the database in the command. Now, to see all the tables created inside the database, execute the following command.
mysql> SHOW TABLES;
You should see the output similar to the following.
+---------------------------+ | Tables_in_test | +---------------------------+ | users | | test | +---------------------------+ 2 rows in set (0.00 sec)
Now, Let’s see how we can delete a specific database in MySQL using command line.
Drop Database in MySQL
In this section of the guide, you will learn how to Drop a database in MySQL. In MySQL, Dropping means deleting. Dropping a database is as easy as creating a database. To drop a specific database, execute the following command.
mysql> DROP DATABASE test;
Now, Sometimes you might want to drop a database only if it exists. In that case, you can use the “IF EXIST” 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;
So, these are the methods you can use to drop the database in MySQL using command line. Now, Let’s see how to rename a database in MySQL.
Rename a Database in MySQL
MySQL does not provide any inbuilt query or command to directly rename a database. However, we can export and import databases easily. It means that the best method to rename a database is to export the old database to SQL, create a new database and import SQL file in the new database.
For example, Let’s say we want to rename our database from ‘test’ to ‘test2’. To perform this action, we first have to dump database contents in an SQL file. Execute the following command to do so.
$ mysqldump -uroot -p test > dbdump.sql
Once done, create a new database with a new name using the command given below. You can also login to MySQL to create a database but mysqladmin method will be easier in this case.
$ mysqladmin -uroot -p create test2;
Once the database is ready, execute the following command to import a database from SQL file to a new database.
$ mysql -uroot -p test2 < dbdump.sql
So, this is how you can rename a database in MySQL.
Conclusion: Managing databases in MySQL via command line is very easy. The commands/queries are easy to read and remember with almost no optional arguments and options. Creating, Deleting, Renaming and Listing databases in MySQL are the most important tasks and that’s all you will need to know about managing databases in MySQL. However, This guide does not cover MySQL users and privileges management, without users and privileges management, databases are very insecure or vulnerable.