How to Create a Database in MySQL

MySQL is a free, open-source and one of the most popular relational database management system. Creating and managing databases, users, tables and interact with them is a day-to-day task of any database administrator. So you must have enough knowledge of how to interact with databases and users.

In this post, we will show you how to create, rename and remove a database in MySQL.

Prerequisite

• A server running Linux operating system.

• A MySQL or MariaDB server is installed.

Create a Database in MySQL

There are several ways you can create a database in MySQL.

Before starting, you will need to login to the MySQL shell with the following command:

mysql -u root -p

Provide your MySQL root password when prompt then, you can use a CREATE DATABASE command to create a database as shown below:

mysql> CREATE DATABASE db_name;

You should get the following output:

Query OK, 1 row affected, 1 warning (0.00 sec)

If you try to create a database that already exists, you should get the following output:

ERROR 1007 (HY000): Can't create database 'db_name'; database exists

In this case, you can use IF NOT EXISTS to avoid errors if the database with the same name exists.

mysql> CREATE DATABASE IF NOT EXISTS db_name;

You can also use CREATE SCHEMA instead of CREATE DATABASE to create a database:

mysql> CREATE SCHEMA db_name;

After creating a new database, the new database is not selected for use. You can select a database using the following command:

mysql> USE db_name;

You should get the following output:

Database changed

You can also select the database directly using the following command:

mysql -u root -p db_name

You can print a list of all databases using the SHOW DATABASES command:

mysql> SHOW DATABASES;

You should get all databases in the following output:

+--------------------+ | Database | +--------------------+ | information_schema | | db_name | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)

Rename a Database in MySQL

In order to rename a database in MySQL, you will need to backup an old database, create a new database and import a backup from the old database.

First, login to MySQL with the following command:

mysql -u root -p

Once connected, create a new database named newdb_name with the following command:

mysql> CREATE DATABASE newdb_name;

Next, dump the old database named db_name with the following command:

mysqldump -u root -p db_name > db_backup.sql

Next, import the database from the old database to the new database with the following command:

mysql -u root -p newdb_name < db_backup.sql

Finally, remove the old database with the following command:

mysql> DROP DATABASE db_name;

Remove a Database in MySQL

Before starting, you will need to list all available databases in your system. You can list all databases using the following command:

mysql> SHOW DATABASES;

You should get all databases in the following output:

+--------------------+ | Database | +--------------------+ | information_schema | | db_name | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)

In order to remove any database, you must have read and write privileges on that database. Otherwise, you can use the root user to remove any database.

Run the following query to delete a database named db_name as shown below:

mysql> DROP DATABASE db_name;

You should see the following output:

Query OK, 1 row affected (0.00 sec)

After removing the database, you can use the SHOW DATABASES command to confirm that your database has been deleted.

You can also use mysqladmin utility to delete a database as shown below:

mysqladmin -u root -p drop db_name

You will be prompt to enter your MySQL root password to delete the database.

Conclusion

In the above guide, you learned how to create, delete and rename a database in MySQL. I hope this guide will help you to perform your day-to-day database operations.