Securing MariaDB and MySQL: the steps after installation

A freshly installed database on a root server is rarely as insecure as older guides claim, and rarely as secure as the package system suggests. Between those two extremes sit exactly the steps this article describes: what mysql_secure_installation actually does, why the unix_socket question has a different answer on modern systems than it did in 2015, what an application user with minimal privileges looks like, and how to keep passwords out of the process list.

All commands run as root. If you work as a regular user, put sudo in front of them. The output shown here comes from Debian 13 and Debian 12 as well as Ubuntu 24.04 and Ubuntu 22.04.

Starting point: which package runs on which distribution

The first stumbling block comes before the first hardening step. Debian has not shipped a package of its own called mysql-server for years. The name only survives there as a virtual package without a version of its own, visible solely through the dependencies of mariadb-server:

apt-cache policy mysql-server

mysql-server:
  Installed: (none)
  Candidate: (none)
  Version table:

So this version comparison only returns a result on Ubuntu, where it reports 8.0.46 on both 24.04 and 22.04. On Debian you use apt-cache showpkg mysql-server instead, which makes the virtual nature of the name visible.

On Debian the database is therefore always MariaDB. On Ubuntu both exist and you have to pick one. The versions in the current distributions:

Installation works as usual:

apt update
apt install -y mariadb-server

Whether you really got the version you expected is not settled by the package version alone, but by the running server:

mariadb -e "SELECT @@version, @@version_comment;"

The second stumbling block is the program name. As of MariaDB 11.0, the MySQL compatible names no longer live in the main package but in mariadb-client-compat and mariadb-server-compat. On Debian 13 a mysql --version may therefore either answer with a warning or not exist at all:

mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
bash: mysql: command not found

If you write scripts that have to run on all four systems, use mariadb, mariadb-dump and mariadb-secure-installation consistently under MariaDB. These names have existed since MariaDB 10.5, so they are available on Ubuntu 22.04 as well.

mysql_secure_installation step by step

The tool goes by different names depending on the server. Under MariaDB the canonical name is mariadb-secure-installation, under MySQL 8.0 it stays mysql_secure_installation. Under MariaDB the old name still exists as a symlink on Debian 12 as well as on Ubuntu 24.04 and 22.04, but no longer on Debian 13: there only mariadb-secure-installation exists.

A look at the options is worthwhile, because the program accepts --defaults-file, --socket and --protocol, among others. That overview is only available in the manual, though:

man mariadb-secure-installation

There is no --help. The script does not evaluate arguments at all: it swallows the unknown switch silently and starts the interactive procedure right away. If a real console is missing, for example inside a pipeline or in a container without a terminal, it repeats the password prompt endlessly and never returns on its own. So call it without arguments and from an interactive shell:

mariadb-secure-installation

On Debian 13 the script puts a clear notice in front of the procedure:

NOTE: MariaDB is secure by default in Debian. Running this script is useless at best,
and misleading at worst. This script will be removed in a future MariaDB release in Debian.

Debian therefore considers the run superfluous and will remove the script in a future version, as documented in /usr/share/doc/mariadb-server/README.Debian.gz. The following steps are still worth going through, because they show what gets checked and why there is hardly anything left to change on a current system.

The procedure starts by asking for the password for root. The exact wording depends on the version of the script:

Enter current password for root (enter for none):

Newer versions ask this instead:

Enter root user password or leave blank:

Both mean the same thing. On a fresh installation there is no password, so simply press Enter.

The actual decisions follow after that. Order and wording differ between MariaDB and MySQL, but the same five points are covered.

The unix_socket question

Under MariaDB the next prompt is:

Switch to unix_socket authentication [Y/n]

This question causes confusion because it is already answered on every system covered here. Since MariaDB 10.4, root@localhost is protected by the unix_socket plugin out of the box, and that applies to 10.6 on Ubuntu 22.04 just as much as to 11.8 on Debian 13. Under MySQL 8.0 the counterpart is called auth_socket, and the wizard says so openly:

Skipping password set for root as authentication with auth_socket is used by default.

In practice this means: if you are logged in as the system user root, mariadb gets you into the database without a password. If you are not, you do not get in at all, not even with the correct password. That is not a shortcoming, it is the stronger variant. There is no password that can leak from a backup, a configuration file or a screenshot. The answer to the question is therefore yes, keep it, and an additional root password is unnecessary on a single application server.

The state can only be verified against the real table. The obvious query SELECT user, host, plugin FROM mysql.user is misleading here: for root it shows the value mysql_native_password, even though unix_socket is what actually applies. In MariaDB 10.4 and later, mysql.user is nothing more than a view on mysql.global_priv, and that view knows only a single authentication method per account. Rely on it and you will wrongly believe the server is password authenticated. The complete rule sits in the JSON field Priv of the real table:

mariadb -e "SELECT User, Host, JSON_DETAILED(Priv) FROM mysql.global_priv;"

For root on localhost, MariaDB stores something along these lines:

{"plugin":"mysql_native_password","authentication_string":"invalid","auth_or":[{},{"plugin":"unix_socket"}]}

The first entry is the password method checked against the unusable hash of the string invalid, which nobody can match. The second entry under auth_or is the socket authentication that actually applies. A more compact way to query both:

mariadb -e "SELECT User, Host, JSON_VALUE(Priv,'$.plugin') AS plugin, JSON_QUERY(Priv,'$.auth_or') AS auth_or FROM mysql.global_priv;"

For root and localhost, expect a unix_socket, either in the plugin column or under auth_or. If there is nothing but a password method and nothing under auth_or, the account works purely with a password. MySQL 8.0 has no mysql.global_priv: there mysql.user is a real table and the plugin column has to show auth_socket. Incidentally, you can still read from the MariaDB view, you just cannot write to it any more.

Only switch over when a tool strictly needs a password, for example monitoring that does not run as root. Even then it makes more sense to create a second administrative user instead of repurposing root. In MariaDB 11.6 and later, which includes 11.8 on Debian 13, an account can additionally be tied to a specific system user:

CREATE USER 'dbadmin'@'localhost' IDENTIFIED VIA unix_socket AS 'deploy';

This lets the system user deploy log in as the database user dbadmin without the names having to match. On Debian 12 and the Ubuntu versions, the string after AS is still ignored, so there the system user and the database user must have the same name.

The four remaining questions

The rest is uncontroversial and gets a yes throughout: remove anonymous users, disallow remote root login, remove the test database together with its privileges, reload the privilege tables. On a current Debian or Ubuntu, anonymous users and the test database usually do not exist in the first place, so the script simply reports that there was nothing to do.

MySQL 8.0 adds one question that MariaDB does not have:

Would you like to setup VALIDATE PASSWORD component?

This component enforces minimum requirements on every password set from then on. It is useful when several people create users. It is a nuisance when a provisioning script generates random passwords that happen to contain no special character. The script then aborts with:

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

If you enable it, adjust the password generator first. The default level is MEDIUM and requires at least eight characters, upper and lower case letters, a digit and a special character.

When it goes wrong: getting back into the database

The most common way to lock yourself out is the well meant switch from unix_socket to a password that then gets lost. Or the other way round: an old script recreates /etc/mysql/debian.cnf and suddenly nothing fits together any more. The error messages people search for at that point are:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded

Error 1698 means the account expects unix_socket but you are not the matching system user. A sudo in front of the command often solves it already. Error 1045 means a password is expected and yours is wrong.

If you cannot get in at all any more, start the server without privilege checks. Under MariaDB this works cleanly through the environment variable that the bundled systemd unit evaluates, with no need to edit packaged files:

systemctl stop mariadb
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking"
systemctl start mariadb

Now connect with mariadb -u root and reset the state. The FLUSH PRIVILEGES up front matters, because ALTER USER fails while the privilege tables are not loaded:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket;

Clean up afterwards without fail, otherwise the server starts unprotected after every reboot:

systemctl stop mariadb
systemctl unset-environment MYSQLD_OPTS
systemctl start mariadb

On MySQL 8.0 under Ubuntu this route does not work, because the unit evaluates no such variable and because ALTER USER comes with additional pitfalls under --skip-grant-tables. There you use an init file instead. It has to sit in a directory that AppArmor grants the server process, otherwise the start fails with Can't open file. /var/lib/mysql-files is allowed, /tmp is not:

systemctl stop mysql
echo "ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;" > /var/lib/mysql-files/reset.sql
chown mysql:mysql /var/lib/mysql-files/reset.sql
systemctl edit mysql

In the editor you add an override for the start command. The empty first line is necessary in order to clear the original value:

[Service]
ExecStart=
ExecStart=/usr/sbin/mysqld --init-file=/var/lib/mysql-files/reset.sql

After systemctl daemon-reload and a start, the account is reset. Then remove the override with systemctl revert mysql and delete the file. If you run databases on separate systems, you will find complementary notes on securing access in the article Hardening an SSH server.

One dedicated user per application instead of root

The most effective step appears in no wizard at all. Applications must not connect as root, and they do not need GRANT ALL either. A typical web application reads and writes rows, it does not create databases and it does not read files from the server.

CREATE DATABASE shopdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'shopapp'@'localhost' IDENTIFIED BY 'HierEinLangesZufallspasswort';
GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO 'shopapp'@'localhost';

Three things matter here. First the dot in shopdb.* instead of *.*: privileges on *.* are global privileges and cover mysql and information_schema as well. Second the @'localhost' part instead of @'%': this keeps the account usable locally only, even if the port ends up open one day. Third, WITH GRANT OPTION is missing, because an account that can pass privileges on is effectively an administrator.

Schema changes then go through a second account that is only used during deployment:

CREATE USER 'shopmigrate'@'localhost' IDENTIFIED BY 'EinAnderesLangesZufallspasswort';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, REFERENCES ON shopdb.* TO 'shopmigrate'@'localhost';

That sounds like extra work, and it is exactly the point at which an SQL injection in the application turns into an annoying data leak instead of a total loss. An account without DROP cannot delete a table.

The check is not made against the wording of the GRANT, but against the result:

SHOW GRANTS FOR 'shopapp'@'localhost';

Expect exactly two lines: a GRANT USAGE ON *.*, which merely represents the right to log in and means no data access whatsoever, and the line with the four privileges on shopdb.*. If ALL PRIVILEGES ON *.* shows up there, the dot was in the wrong place. The second and more meaningful test is a login with the new account followed by a SHOW DATABASES;. Only information_schema and shopdb may be visible.

A common error while creating the account:

ERROR 1396 (HY000): Operation CREATE USER failed for 'shopapp'@'localhost'

This almost always means the account already exists, often as a leftover from an earlier attempt. DROP USER 'shopapp'@'localhost'; and start over.

bind-address: where the database actually listens

On all four distributions the database listens on 127.0.0.1 only after the package installation. The line lives in /etc/mysql/mariadb.conf.d/50-server.cnf under MariaDB and in /etc/mysql/mysql.conf.d/mysqld.cnf under MySQL 8.0. Check instead of guessing:

grep -R "bind-address" /etc/mysql/

MySQL 8.0 has a second line that is easily overlooked: mysqlx-bind-address controls the X protocol on port 33060. If you only change bind-address, you may open just half the access path or leave the second port open.

The most reliable check is not the configuration file, but the kernel:

ss -lntp

LISTEN 0 80 127.0.0.1:3306 0.0.0.0:* users:(("mariadbd",pid=712,fd=22))

If it says 0.0.0.0:3306 or *:3306, the service is reachable over the network. In addition, the server supplies its own view:

mariadb -e "SELECT @@bind_address, @@port, @@skip_networking;"

Two traps are common here. The first: the files in mariadb.conf.d are read in alphabetical order, and the setting read last wins. So if you put your change into a file of your own, call it 99-eigene.cnf and not 10-eigene.cnf. This is exactly what most reports fail on in which MariaDB supposedly ignores bind-address. The advantage of a separate file: package updates do not ask you to resolve conflicts, because the shipped file stays untouched.

The second trap: if you do not need network access at all, go one step beyond bind-address and set skip-networking. Then no TCP port exists any more, only the Unix socket. That is the correct setting for the standard case of a web application and a database on the same server, but it costs nerves when an application has 127.0.0.1 in its configuration instead of localhost: with 127.0.0.1, the client libraries force TCP.

External access only when it is really necessary

A database port on the open internet is found within hours and probed continuously from then on. The best answer to the question of external access is therefore to avoid it. For occasional maintenance an SSH tunnel is enough, mapping a local port on your own machine to the database socket on the remote side. The database tool then connects to 127.0.0.1 without the server opening anything on the network.

For permanent connections between several servers, a WireGuard tunnel is the clean solution. The database then binds exclusively to the tunnel address, not to the public IP address.

If it really has to be an open port, four measures work together. The server binds to exactly one internal address. The firewall lets only the known source address through. The database account is bound to that same address, so 'shopapp'@'10.0.0.5' and never 'shopapp'@'%'. And encryption is enforced on the connection:

ALTER USER 'shopapp'@'10.0.0.5' REQUIRE SSL;

When testing from outside you will meet two errors that are often confused. The first means nobody answers, so it is the firewall or bind-address:

ERROR 2003 (HY000): Can't connect to MySQL server on '203.0.113.10:3306' (110)

The second means the server answers and deliberately rejects the connection, so the matching account for this origin is missing:

ERROR 1130 (HY000): Host '203.0.113.55' is not allowed to connect to this MariaDB server

Locally, in turn, the classic case is that the service is simply not running:

ERROR 2002 (HY000): Can't connect to local server through socket '/run/mysqld/mysqld.sock' (2)

Keep passwords off the command line

The call mariadb -u shopapp -pGeheim123 works and is a mistake nonetheless. The server says so itself:

Warning: Using a password on the command line interface can be insecure.

There are two reasons behind it. First, the line ends up in the shell history. Second, on a standard system the command line of a process is visible to every logged in user through ps. On a server with several customers or several services, that is a password grab with no effort at all.

The right way for humans is -p with nothing appended. The prompt is then interactive and nothing lands in the history:

mariadb -u shopapp -p shopdb

The right way for scripts and cron jobs is an option file with tight permissions. Create it with the correct mode in a single step:

install -m 600 /dev/null /root/.my.cnf

Contents:

[client]
user=backup
password=HierEinLangesZufallspasswort

After that, every client finds the credentials on its own. For separate tasks you create several files and point at them explicitly. One rule applies here that many people trip over: --defaults-extra-file and --defaults-file have to be the first option of the call, otherwise they are ignored without comment.

mariadb-dump --defaults-extra-file=/root/.my-backup.cnf --single-transaction shopdb

The environment variable MYSQL_PWD is not a solution. It sits in /proc and is therefore about as visible as the command line. MySQL 8.0 additionally offers mysql_config_editor, which writes a file ~/.mylogin.cnf. Its contents are obfuscated but not encrypted, and MariaDB does not know the tool. In mixed environments, the plain option file with mode 600 is the more reliable choice.

One last point concerns backups. A dump contains everything the application can see, and a backup account needs no write privileges for that. For mariadb-dump with --single-transaction, this is usually enough:

GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON shopdb.* TO 'backup'@'localhost';
GRANT PROCESS ON *.* TO 'backup'@'localhost';

Acceptance check: how to tell that it holds

A command that runs without an error proves nothing. These six tests do prove something:

1. The root account uses socket authentication: mariadb -e "SELECT User, Host, JSON_VALUE(Priv,'$.plugin') AS plugin, JSON_QUERY(Priv,'$.auth_or') AS auth_or FROM mysql.global_priv;" shows a unix_socket for root, either in the plugin column or under auth_or. The mysql.user view is not suitable for this, it only returns the first method. Under MySQL 8.0 it is the other way round: the plugin column in mysql.user is authoritative and has to say auth_socket.
2. There are no anonymous accounts: mariadb -e "SELECT user, host FROM mysql.user WHERE user = '';" returns an empty result set.
3. The test database is gone: mariadb -e "SHOW DATABASES LIKE 'test';" returns nothing.
4. The port is closed: ss -lntp shows either nothing at all for 3306 or exclusively 127.0.0.1.
5. The application account is restricted: a login with it shows only its own database in SHOW DATABASES;, and a DROP TABLE fails.
6. No password travelling in clear text: grep -rs "password" /etc/cron.d/ /etc/cron.daily/ /etc/cron.hourly/ /etc/cron.weekly/ /etc/cron.monthly/ finds nothing, and neither does crontab -l for root or a look into /var/spool/cron/crontabs/, and the option files have mode 600. The -s switch belongs there because a missing directory would otherwise abort the call with exit code 2: on Debian 13, /etc/cron.d does not exist after a pure database installation, because no cron package is installed there yet.

Working through these six points on a new server already rules out the vast majority of attacks against databases, without installing a single additional piece of software. The rest is update discipline and a working backup whose restore has been rehearsed at least once.