2026-08-09: Updated to MariaDB-compatible commands

For some reason I always have to search the internet for the steps required for creating a database and grant privileges to it in the MariaDB command line. Usually the how-tos found on the net are a bit complicated or they are missing some commands that I like to do when I create databases and users.

Log in to MariaDB CLI

mariadb -u root -p

mariadb is the command line tool for accessing a MariaDB server.

• -u root defines the user for logging in, in this case user root
• -p enables password prompting from terminal
• Optional: -D databasename can be used to define database to select
• Optional: -h can be used to define a host

Creating an empty database

You can create an empty database with the following SQL query:

CREATE DATABASE databasename;

After hitting enter, you should receive Query OK response, similar to this:

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0,002 sec)

Granting privileges to a user & setting a password

This step is usually the one that gets unnecessary complicated on most of the online tutorials. My use case for using MariaDB databases is for testing out new software which require a database to function. Instead of deploying a new server for every software as documentation in many softare recommends, I will just create a new database on some existing MariaDB server and define a user with privileges to only that specific database. As a bonus I can define a password for the database user at the same time, which saves some extra hassle :)

GRANT ALL PRIVILEGES ON testdb.* TO testuser@localhost IDENTIFIED BY ‘passwd’;

The SQL query should return again Query OK:

MariaDB [(none)]> GRANT ALL PRIVILEGES ON testdb.* TO testuser@localhost IDENTIFIED
BY 'super-secret-password';
Query OK, 0 rows affected (0,017 sec)

Now you can leave the session with the quit command.

Testing the user login

Test the new login:

❯ mariadb -u testuser -p -D testdb
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 12.3.2-MariaDB-ubu2404 mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [testdb]> 

Now your new database and user is ready to use!

Sources

• Mariadb Command-Line Client documentation