MariaDB Databases and Users

Connect to MariaDB

On Debian and Ubuntu, root signs in through the system account, so no password is needed:

sudo mariadb

With a password-based admin account:

mariadb -u adminuser -p

MariaDB running in Docker:

docker exec -it <container-name> mariadb -u root -p

When the MariaDB [(none)]> prompt appears, you're in. Every SQL command must end with a semicolon ;.

Create the database

CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

utf8mb4 supports every language and emoji. Almost every modern application (WordPress, Nextcloud and others) expects it.

List all databases:

SHOW DATABASES;

Create the user

A MariaDB user is a name plus the host it connects from. Pick the host that matches where the application runs:

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'a-strong-password';

List all users:

SELECT User, Host FROM mysql.user;

Give the user access to the database

GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';

Permissions from GRANT apply immediately, so FLUSH PRIVILEGES is not needed. You only need it if you edit the mysql.* tables directly.

Check the user's permissions:

SHOW GRANTS FOR 'appuser'@'localhost';

Change a user's password

ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'new-strong-password';

Remove a user or database

DROP USER 'appuser'@'localhost';
DROP DATABASE appdb;

Allowing remote connections

By default MariaDB only listens on 127.0.0.1. To accept connections from other machines:

1. Edit /etc/mysql/mariadb.conf.d/50-server.cnf and change bind-address = 127.0.0.1 to the server's IP address (or 0.0.0.0).
2. Restart MariaDB: sudo systemctl restart mariadb
3. Create the user with a matching host part (see the table above).
4. Allow TCP port 3306 in the firewall, from the application server only.