How to install and secure MariaDB on Ubuntu 24.04

To install MariaDB on Ubuntu 24.04, run sudo apt install mariadb-server: you get MariaDB 10.11, running, listening on 127.0.0.1 only, with the root account reachable only by the system's root user through the Unix socket. Securing it then means three things: answering mariadb-secure-installation without setting a root password you don't need, giving each app its own user limited to its own database, and reaching the database from your laptop through an SSH tunnel rather than opening port 3306.

The same steps work unchanged on Debian 12, which ships the same 10.11 series. The examples use these names:

Install MariaDB#

sudo apt update
sudo apt install -y mariadb-server

Ubuntu 24.04 and Debian 12 both install MariaDB 10.11, a long-term-support series. Security fixes come through apt upgrade. If an app needs a newer series (11.4 or 11.8, also long-term support), MariaDB publishes its own repository at mariadb.org/download; otherwise stay with your distribution's package.

Check it worked#

sudo systemctl status mariadb --no-pager
sudo mariadb -e "SELECT VERSION();"

The second command prints something like 10.11.14-MariaDB-0ubuntu0.24.04.1 (Debian 12: 10.11.19-MariaDB-0+deb12u1; the last digits move with security updates). Notice that it didn't ask for a password: that is the Unix socket login, explained next.

Know what is already secure#

Before you run any hardening script, look at what the package set up. Two queries show it:

sudo mariadb -e "SHOW GRANTS FOR 'root'@'localhost';"
sudo mariadb -e "SELECT @@bind_address;"

GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION
...
@@bind_address
127.0.0.1

• root logs in with unix_socket. MariaDB asks the kernel which Linux user is on the other end of the socket. Only the system's root user (directly or through sudo) gets in as the database root. The password half says 'invalid', so there is no root password anyone can guess, locally or over the network.
• bind-address = 127.0.0.1. Set in /etc/mysql/mariadb.conf.d/50-server.cnf. Nothing outside the server can reach port 3306.
• No anonymous users and no test database on a fresh Ubuntu 24.04 or Debian 12 install.

That is already the state that mariadb-secure-installation exists to reach. Running it is still a good habit, because it checks and doesn't depend on how the package was built.

Run mariadb-secure-installation with the right answers#

mysql_secure_installation is now called mariadb-secure-installation (the old name still works). Run it:

sudo mariadb-secure-installation

Answer like this:

If you can't run it interactively (a setup script, for example), these statements do the same three clean-ups. They are what the script itself runs:

DELETE FROM mysql.global_priv WHERE User='';
DELETE FROM mysql.global_priv WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;

Old tutorials for MySQL 5.x run UPDATE mysql.user SET ... instead. On MariaDB 10.4 and later, mysql.user is a view and those statements fail with ERROR 1356 (HY000) ... View 'mysql.user' references invalid table(s) or column(s); use the script, the statements above, or ALTER USER.

Check it worked#

sudo mariadb -e "SELECT 1;" still works without a password. As any other Linux user, mariadb -u root fails with ERROR 1698 (28000): Access denied for user 'root'@'localhost'. That error is the security working, not a fault.

Create a database and a user for your app#

Each app gets its own database and its own user, with rights on that database only. Open a root session with sudo mariadb, then:

CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'replace-with-a-long-random-password';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';

openssl rand -base64 24 makes a good password. utf8mb4 stores every Unicode character, emoji included (plain utf8 in MariaDB is a three-byte subset).

• 'appuser'@'localhost' can only log in from the server itself. MariaDB treats connections over the socket and over 127.0.0.1 as localhost, so both localhost and 127.0.0.1 work as the database host in your app.
• ALL PRIVILEGES ON appdb.* lets the app create and change its own tables (most apps run migrations), and nothing outside appdb. It can't see other databases, create new ones or manage users.

If the app only reads and writes rows and its migrations run separately, grant less:

GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'localhost';

Check it worked#

mariadb -u appuser -p -h 127.0.0.1 appdb -e "SHOW GRANTS;"

It asks for the password and prints the two grants. Trying CREATE DATABASE other as this user fails with ERROR 1044 (42000): Access denied for user 'appuser'@'localhost' to database 'other', as it should.

Add a user for backups#

Your nightly dump shouldn't run as root or as the app. A backup user needs read access plus the rights mariadb-dump uses for a consistent copy:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'another-long-random-password';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT ON *.* TO 'backup'@'localhost';

Keep its password in a file only root can read, /root/.my.cnf, so the dump command doesn't carry it:

[mariadb-dump]
user=backup
password=another-long-random-password

sudo chmod 600 /root/.my.cnf
sudo mariadb-dump --single-transaction --routines --events appdb | gzip > appdb.sql.gz

--single-transaction takes a consistent snapshot of InnoDB tables without locking the app out. Restore with gunzip < appdb.sql.gz | sudo mariadb appdb.

Connect from your laptop over an SSH tunnel#

Leave bind-address at 127.0.0.1 and don't open port 3306: database ports are scanned and password-guessed around the clock. Instead, forward a local port over SSH. On your laptop:

ssh -N -L 3307:127.0.0.1:3306 deploy@203.0.113.10

Then, in another terminal on the laptop:

mariadb -h 127.0.0.1 -P 3307 -u appuser -p appdb

Use -h 127.0.0.1, not localhost: the client treats localhost as "use the local socket" and never touches the tunnel. GUI tools (DBeaver, HeidiSQL, TablePlus, MySQL Workbench) have an SSH tab: give it the server, your SSH user and key, and 127.0.0.1 port 3306 as the database.

When an app on another server needs the database, connect the two over a private network or a WireGuard tunnel, set bind-address to the server's private address, create the user as 'appuser'@'10.8.0.%' (that network only), and allow 3306 from that range only in your firewall.

Troubleshooting#

ERROR 1698 (28000): Access denied for user 'root'@'localhost'. You ran mariadb -u root as a normal user. Use sudo mariadb. If an old tool insists on a root password, give it its own user with the rights it needs instead of adding a password to root.

ERROR 1045 (28000): Access denied for user 'appuser'@'localhost' (using password: YES). Wrong password, or the user was created for a different host ('appuser'@'%' and 'appuser'@'localhost' are two separate accounts). sudo mariadb -e "SELECT User, Host FROM mysql.user;" lists them; reset with ALTER USER 'appuser'@'localhost' IDENTIFIED BY '...';.

ERROR 2002 (HY000): Can't connect to local server through socket '/run/mysqld/mysqld.sock' (2). MariaDB isn't running. sudo systemctl start mariadb, then sudo journalctl -u mariadb -n 50 for the reason it stopped (often a full disk).

ERROR 2002 (HY000): Can't connect to server on '127.0.0.1' (115) on your laptop. Nothing is listening on the local port: the SSH tunnel isn't running, or it exited because the SSH login failed. Start it again and watch for errors.

ERROR 2013 (HY000): Lost connection to server at 'handshake: reading initial communication packet' through the tunnel. The tunnel is up, but its far end points somewhere MariaDB isn't listening. Check the forward is 127.0.0.1:3306 on the server side.

Next steps#

• Run PHP apps on it: install PHP-FPM with nginx on Ubuntu 24.04, or follow our WordPress on a VPS guide.
• Ship the nightly dump off the server with restic to S3-compatible storage or borgmatic to a second server; borgmatic can run the dump for you.
• Prefer PostgreSQL? Install PostgreSQL on Ubuntu 24.04.
• MariaDB's own security documentation covers TLS and auditing when you need them.