How to create a database in MySQL or MariaDB

Creating a dedicated database in MySQL or MariaDB separates application data from system schemas, makes backups and restores easier to target, and lets you hand database access to a specific account instead of reusing broad administrative credentials.

Both products treat a database as a schema container created with CREATE DATABASE. New tables inherit the database's default character set and collation unless you override them later, and access is controlled separately through the full 'user'@'host' account identity plus privileges granted on the new schema.

Current MySQL and MariaDB releases still use the same core SQL flow, but their default utf8mb4 collations are no longer identical, so it is safer to choose the character set explicitly and verify the generated definition after creation. Use the exact host entry that matches how the client connects, and reserve FLUSH PRIVILEGES for cases where grant tables were edited directly rather than through CREATE USER or GRANT.

1. Connect with the mysql or mariadb client using an administrative account.

   $ mysql --user=root --password
   Enter password:
   mysql>

   On MariaDB systems the client binary may be named mariadb, and some Linux installations use local socket authentication for root so sudo mysql or sudo mariadb can replace password-based login.

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

   mysql> CREATE DATABASE new_database CHARACTER SET utf8mb4;
   Query OK, 1 row affected (0.00 sec)

   utf8mb4 stores full Unicode, including emoji and four-byte characters. Add an explicit COLLATE clause only when the workload needs a specific comparison or sort order, and use backticks in SQL if the schema name contains dashes or reserved words.

3. Inspect the generated database definition to confirm the effective 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_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
   1 row in set (0.00 sec)

   Current MySQL 8.4 commonly shows utf8mb4_0900_ai_ci here, while current MariaDB 11.8 commonly shows utf8mb4_uca1400_ai_ci. Verify the generated definition instead of assuming the same default collation across both products.

4. Create a dedicated account for the new database.

   mysql> CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'Use-A-Long-Random-Password!';
   Query OK, 0 rows affected (0.01 sec)

   'localhost' is not the same account as '127.0.0.1' or a remote host entry such as '198.51.100.%'. Create the exact 'user'@'host' combination that matches the client connection path.

   Related: How to create a user in MySQL or MariaDB
   Related: How to enable remote access to MySQL or MariaDB server

5. Grant the account the privileges it needs on the new database.

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

   ALL PRIVILEGES is reasonable for a schema-owner or migration account, but most application runtime accounts should receive a narrower set such as SELECT, INSERT, UPDATE, DELETE.

   Related: How to grant privileges to a MySQL or MariaDB user

6. Review the effective grants for the new account.

   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)

   CREATE USER and GRANT apply immediately in normal use, so FLUSH PRIVILEGES is only needed after direct edits to the mysql grant tables. MariaDB can include the account's authentication hash in the GRANT USAGE line.

   Related: How to view permissions for a MySQL or MariaDB user

7. Connect as the new account with the new database selected as the default schema.

   $ mysql --user=new_user --password new_database
   Enter password:
   mysql>

   Use mariadb --user=new_user --password new_database on hosts where the MariaDB client is installed under the mariadb name.

8. Confirm that the new session is using the intended database.

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