Linux & software2 min read

Install MariaDB, secure it and create databases

Install MariaDB on Debian or Ubuntu, secure it with mariadb-secure-installation, create a database and user, access it safely from outside and create backups.

MariaDB is the free continuation of MySQL and the default database on Debian. WordPress, Nextcloud, shops and many custom applications store their data in it.

Install

sudo apt update
sudo apt install mariadb-server
systemctl status mariadb

Secure it

sudo mariadb-secure-installation

Recommended answers:

Question Answer
Switch to unix_socket authentication n (Debian already sets it up that way)
Change the root password n (root logs in via the system user)
Remove anonymous users y
Disallow root login remotely y
Remove test database y
Reload privilege tables y

Afterwards you get in as root without a password, but only with sudo:

sudo mariadb

Create a database and user

Give every application its own database and its own user, never the root account:

CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'myapp'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT ALL PRIVILEGES ON myapp.* TO 'myapp'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Generate a good password with openssl rand -base64 24.

utf8mb4 stores all characters including emoji. The older utf8 cannot.

Access from outside

By default MariaDB only listens on 127.0.0.1. That is a good thing. If you need to work from your PC with a tool such as DBeaver or HeidiSQL, do not open the database to the internet; use an SSH tunnel instead:

ssh -L 3307:127.0.0.1:3306 user@YOUR-SERVER-IP

While the connection is open, your tool connects to 127.0.0.1:3307 and ends up safely in the database on the server. Many programs have a built-in "SSH tunnel" option for this.

Backups

A dump of a single database:

sudo mariadb-dump --single-transaction myapp | gzip > myapp-$(date +%F).sql.gz

--single-transaction creates a consistent snapshot without locking the database. Restore:

gunzip < myapp-2026-10-29.sql.gz | sudo mariadb myapp

To run it automatically every night, use a cron job or timer, see Cron jobs and systemd timers, and store it off-site safely with restic.

Useful commands

Command Effect
SHOW DATABASES; all databases
SELECT user, host FROM mysql.user; all users
SHOW GRANTS FOR 'myapp'@'localhost'; a user's privileges
ALTER USER 'myapp'@'localhost' IDENTIFIED BY 'new'; change a password
sudo journalctl -u mariadb log

Memory

On small servers (1 to 2 GB) the default setting is fine. On larger ones, give the buffer more room, roughly half of the free memory. In /etc/mysql/mariadb.conf.d/50-server.cnf under [mysqld]:

innodb_buffer_pool_size = 1G

Then sudo systemctl restart mariadb.

Written by TryNxt team

We build and run TryNxt ourselves and write down here what we learn along the way.