How to create a new database on MySQL and MariaDB

Creating a dedicated database in MySQL or MariaDB keeps application data isolated from system schemas, making permission boundaries cleaner and maintenance tasks like backups and restores far less exciting.

Both servers store data in schemas (often casually called “databases”) and enforce access through accounts written as 'user'@'host', with privileges granted at the schema, table, or column level. The mysql client opens an interactive SQL shell where commands like CREATE DATABASE, CREATE USER, and GRANT apply changes immediately.

Avoid using the root account for applications, prefer utf8mb4 for full Unicode, and treat broad grants (especially users that can connect from '%') as security-sensitive configuration. Naming rules, default character sets, and available collations can vary slightly by server version, so verifying the created schema and grants prevents surprises later.

1. Log in to the MySQL or MariaDB SQL shell using an administrative account.

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

   Some installations authenticate root via a local socket, so sudo mysql or sudo mariadb may be used instead of a password prompt.

2. Create the database with an explicit utf8mb4 character set and collation.

   mysql> CREATE DATABASE IF NOT EXISTS new_database
       ->   CHARACTER SET utf8mb4
       ->   COLLATE utf8mb4_unicode_ci;
   Query OK, 1 row affected (0.01 sec)

   Replace new_database with the desired schema name, and use backticks in SQL (`name-with-dash`) when special characters are required.

3. Confirm the database exists via INFORMATION_SCHEMA.

   mysql> SELECT SCHEMA_NAME
       -> FROM INFORMATION_SCHEMA.SCHEMATA
       -> WHERE SCHEMA_NAME = 'new_database';
   +-------------+
   | SCHEMA_NAME |
   +-------------+
   | new_database|
   +-------------+
   1 row in set (0.00 sec)

4. Show the database definition to verify default CHARACTER SET and COLLATION.

   mysql> SHOW CREATE DATABASE new_database\G
   *************************** 1. row ***************************
          Database: new_database
   Create Database: CREATE DATABASE `new_database` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
   1 row in set (0.00 sec)

   The displayed SQL can include version-specific annotations, and the effective defaults should still reflect the chosen character set and collation.

5. Create a dedicated user scoped to local connections.

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

   Using '%' instead of 'localhost' allows network logins and should be paired with tight firewall rules and TLS to avoid exposing credentials over the network.

6. Grant the user privileges on the new database.

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

   Prefer least privilege for long-lived application accounts, and consider separate users for schema migrations (DDL) and runtime access (DML).

7. Review the effective privileges granted to the user.

   mysql> SHOW GRANTS FOR 'new_user'@'localhost';
   +-----------------------------------------------------------------------+
   | Grants for new_user@localhost                                          |
   +-----------------------------------------------------------------------+
   | GRANT USAGE ON *.* TO `new_user`@`localhost`                           |
   | GRANT ALL PRIVILEGES ON `new_database`.* TO `new_user`@`localhost`     |
   +-----------------------------------------------------------------------+
   2 rows in set (0.00 sec)

8. Exit the administrative SQL session.

   mysql> EXIT;
   Bye

9. Connect as the new user with new_database selected as the default schema.

   $ mysql -u new_user -p new_database
   Enter password: ********
   mysql>

10. Verify the current database in the user session.

    mysql> SELECT DATABASE();
    +------------+
    | DATABASE() |
    +------------+
    | new_database|
    +------------+
    1 row in set (0.00 sec)

11. Exit the user session.

    mysql> EXIT;
    Bye