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.
For the community
Know your stuff? Write a guide.
Share what you know: we review every submitted guide, publish it under your name and say thank you with credit for your services.