How to Create a Database in MySQL Using Command Line

This article shows you how to use the MySQL CREATE DATABASE statement to create a new database on a MySQL server.

Each MySQL installation includes the mysql tool, which can be used to interact with the database directly from the terminal.

In other words, the mysql command-line utility allows you to send commands or queries to the server and manage data in databases stored on the server.

MySQL CREATE DATABASE Statement

Here is a generic CREATE DATABASE statement syntax.

CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name]Code language: CSS (css)

The database name must be unique within the MySQL server instance. If you try to create a database with a name that already exists, MySQL issues an error. To avoid such an error, specify the “IF NOT EXISTS” option if you accidentally try to create an existing database.

So, when “IF NOT EXISTS” is used, the database is created only if the given name does not conflict with an existing database’s name.

Related: How to Create a Database in MySQL with MySQL Workbench

Furthermore, you can also specify the character set and the collation for the new database at the creation time. However, if you omit the “CHARACTER SET” and “COLLATE” clauses, MySQL will use the default character set and collation for the new database.

Creating a Database Using mysql Command

To create a database, youโ€™ll have to open the MySQL command line interface and enter your database commands while the server runs.

First, log into the MySQL server as the root user.

mysql -u root -p

You will be prompted to enter your password. Type the MySQL root user password and hit “Enter.”

Now we are ready to create the new database in MySQL. We decide to name it “animals.” The command would be:

CREATE DATABASE animals;
Query OK, 1 row affected (0.001 sec)Code language: CSS (css)

Congratulations! Your database has been created.

Finally, you can see a list of existing databases by running the following SQL command:

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| animals            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

As you can see, our newly created MySQL database named “animals” is listed.

However, if your MySQL server has multiple databases, execute the following statement to start work with the database you’ve created:

use animals;Code language: PHP (php)

Conclusion

After completing this tutorial, you should know how to create a database in MySQL using the command line. As we have seen above, it is pretty straightforward.

In addition, you can use the following article to learn how to back up and restore the MySQL database from the command line in Linux.

For more information about MySQL databases, see the MySQL documentation.

Bobby Borisov

Bobby Borisov

Bobby, an editor-in-chief at Linuxiac, is a Linux professional with over 20 years of experience. With a strong focus on Linux and open-source software, he has worked as a Senior Linux System Administrator, Software Developer, and DevOps Engineer for small and large multinational companies.

Think You're an Ubuntu Expert? Let's Find Out!

Put your knowledge to the test in our lightning-fast Ubuntu quiz!
Ten questions to challenge yourself to see if you're a Linux legend or just a penguin in the making.

1 / 10

Ubuntu is an ancient African word that means:

2 / 10

Who is the Ubuntu's founder?

3 / 10

What year was the first official Ubuntu release?

4 / 10

What does the Ubuntu logo symbolize?

5 / 10

What package format does Ubuntu use for installing software?

6 / 10

When are Ubuntu's LTS versions released?

7 / 10

What is Unity?

8 / 10

What are Ubuntu versions named after?

9 / 10

What's Ubuntu Core?

10 / 10

Which Ubuntu version is Snap introduced?

The average score is 68%