How to Create a Database in MySQL Using Command Line

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.