Securing MariaDB on a VPS: local listening, least privilege and backups

A database exposed to the Internet is one of the first things bots look for: port 3306 is scanned constantly, and databases left open with a weak password get emptied or held to ransom. This guide installs MariaDB and locks it down. The commands also apply to MySQL, give or take a few file names.

1. Install and secure the installation

apt update && apt install -y mariadb-server
mariadb-secure-installation

Answer the questions as follows:

• Switch to unix_socket authentication: n if the question appears, since this is already the case on Debian and Ubuntu.
• Change the root password: n. MariaDB's root account authenticates through the system's root account, with no network password: that is safer than a password.
• Remove anonymous users, Disallow root login remotely, Remove test database, Reload privilege tables: Y each time.

Then connect as the system root user with the simple command mariadb.

2. Listen locally only

On Debian and Ubuntu, MariaDB listens on the local address only by default. Check it, because a past change or another distribution may have altered it:

ss -tlnp | grep -E "3306|mariadbd|mysqld"

You should see 127.0.0.1:3306. If you see 0.0.0.0:3306 or *:3306, fix it in /etc/mysql/mariadb.conf.d/50-server.cnf:

[mysqld]
bind-address = 127.0.0.1
skip-name-resolve

systemctl restart mariadb

skip-name-resolve avoids a DNS lookup on every connection: it is faster, and accounts must then be declared with an IP address rather than a hostname. If your application runs in Docker, see the Docker paragraph below.

3. One user per application

Never use the root account in an application. Create one database and one user per site or game server, with rights on its own database only:

CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'shop'@'localhost' IDENTIFIED BY 'GENERATED_PASSWORD';
GRANT ALL PRIVILEGES ON shop.* TO 'shop'@'localhost';
FLUSH PRIVILEGES;

Generate passwords with openssl rand -base64 24. If one application is compromised, the attacker only reaches its own database. For a tool that only reads, such as a dashboard, even limit the rights to SELECT.

Docker: a container does not connect from localhost but from the Docker network address. The simplest approach is to run the database in a container in the same Compose file, without publishing its port. See Docker and Docker Compose.

4. Audit existing accounts

On a server already in service, list the accounts and where they can connect from:

SELECT user, host FROM mysql.user ORDER BY user;

Any account whose host column is % accepts connections from anywhere. If it is not essential, delete it or restrict it:

DROP USER 'old'@'%';
RENAME USER 'app'@'%' TO 'app'@'localhost';

Also check an account's rights with SHOW GRANTS FOR 'app'@'localhost';. An application account must never have ALL PRIVILEGES ON *.*.

5. Remote access without exposing the database

To use a graphical tool such as DBeaver, HeidiSQL or MySQL Workbench from your computer, go through an SSH tunnel: the database stays closed to the Internet.

ssh -N -L 3307:127.0.0.1:3306 alex@VPS_IP

While this command runs, connect your software to 127.0.0.1 on port 3307. Most tools can also create this tunnel themselves, in an "SSH" tab of the connection settings.

6. Tune memory

The setting that matters most is innodb_buffer_pool_size, the memory where MariaDB keeps the most-used data and indexes. The default, 128 MB, is too low as soon as the database grows. On a VPS that also hosts the website or game server, give it about a quarter of the RAM:

[mysqld]
innodb_buffer_pool_size = 1G     # for a 4 GB VPS
max_connections = 100

To estimate the need, compare with the actual size of your data:

SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024) AS size_mb
FROM information_schema.tables WHERE engine = 'InnoDB';

There is no point going beyond that size plus a margin for growth.

7. Back up

Do not copy the files in /var/lib/mysql while the database is running: the copy would be inconsistent. Export instead:

mariadb-dump --all-databases --single-transaction --routines --events | gzip > /var/backups/mariadb.sql.gz

--single-transaction produces a consistent export without blocking applications. Automate the export and send it off the server: see backups with Borg. To restore: gunzip -c mariadb.sql.gz | mariadb.

8. Updates

MariaDB maintenance releases arrive through normal system updates. If you enabled the automatic security updates described in first steps, they install themselves. Before moving to a new major version, take a full export, then run mariadb-upgrade after the update if it did not do so itself.

Frequently asked questions