How to create a new database on MySQL and MariaDB

Setting up a new database in MySQL or MariaDB is a crucial step when starting a new project or organizing data for an application. Databases are the foundation of any data-driven system, and creating one is simple using basic commands from the command line or within the database shell. Both MySQL and MariaDB use almost identical syntax, making the process interchangeable between the two platforms.

A new database provides a clean environment to store and manage your data, whether for a web application, development, or testing. Additionally, you can assign users with specific permissions to control access to this database, which is important for security and data management. It's easy to set up both the database and the necessary users with privileges.

This guide covers how to create a database on both MySQL and MariaDB, including optional steps for creating users and granting access, ensuring you have a fully functioning database ready to use.

1. Log in to the MySQL or MariaDB shell with administrative privileges.

   $ mysql -u root -p
   Enter password: ********

2. Create a new database by issuing the CREATE DATABASE command.

   mysql> CREATE DATABASE new_database;
   Query OK, 1 row affected (0.01 sec)

   Replace “new_database” with the name of the database you want to create.

3. Verify that the new database was successfully created.

   mysql> SHOW DATABASES;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | new_database       |
   | mysql              |
   +--------------------+
   3 rows in set (0.00 sec)

4. Optionally, create a new user to manage the database.

   mysql> CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
   Query OK, 0 rows affected (0.02 sec)

5. Assign all privileges on the new database to the new user.

   mysql> GRANT ALL PRIVILEGES ON new_database.* TO 'new_user'@'localhost';
   Query OK, 0 rows affected (0.01 sec)

6. Apply the changes to the user privileges.

   mysql> FLUSH PRIVILEGES;
   Query OK, 0 rows affected (0.00 sec)

7. Exit the MySQL or MariaDB shell.

   mysql> EXIT;
   Bye

   After creating the new database and assigning privileges, you can start using the database for your application or further configuration.