How to Create a Database in MySQL Using Command Line

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]

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)

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;

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 is a Linux professional with over 20 years of experience. With a strong focus on Linux and open-source software, Bobby has worked as a Linux System Administrator, Software Developer, and DevOps Engineer for small and large multinational companies.

Leave a Reply

Your email address will not be published. Required fields are marked *