# How to install and secure MariaDB on Ubuntu 24.04

> Install MariaDB on Ubuntu 24.04, answer mariadb-secure-installation correctly, give each app its own user and keep port 3306 private.

Source: https://vpsnine.com/tutorials/install-mariadb-ubuntu · Updated: 2026-10-21

Difficulty: beginner · Time: about 15 minutes · Tested on: Ubuntu 24.04 LTS, Debian 12 Oldstable

Prerequisites:

- A server running Ubuntu 24.04 or Debian 12
- Root or a user with sudo
- An SSH session to the server

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:

| What | In this tutorial |
| --- | --- |
| The server's public IPv4 address | `203.0.113.10` |
| Your login on the server | `deploy` |
| The app's database and user | `appdb` and `appuser` |
| A user for nightly dumps | `backup` |

## Install MariaDB

```bash
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](https://mariadb.org/download/?t=repo-config); otherwise stay with your distribution's package.

### Check it worked

```bash
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:

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

```text
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:

```bash
sudo mariadb-secure-installation
```

Answer like this:

| Prompt | Answer | Why |
| --- | --- | --- |
| `Enter current password for root (enter for none):` | Enter | Root has no password; the socket login is used. |
| `Switch to unix_socket authentication [Y/n]` | `n` | It is already on. The script says so: "You already have your root account protected, so you can safely answer 'n'." |
| `Change the root password? [Y/n]` | `n` | A root password adds something to guess and to leak. With the socket login, `sudo mariadb` is how admins get in. |
| `Remove anonymous users? [Y/n]` | `Y` | Deletes accounts with an empty user name, if any. |
| `Disallow root login remotely? [Y/n]` | `Y` | Deletes any `root` account for a host other than `localhost`, `127.0.0.1` or `::1`. |
| `Remove test database and access to it? [Y/n]` | `Y` | Drops `test` and the rule that lets anyone use `test_*` databases. |
| `Reload privilege tables now? [Y/n]` | `Y` | Applies the changes. |

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:

```sql
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:

```sql
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:

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

### Check it worked

```bash
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:

```sql
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:

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

```bash
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:

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

Then, in another terminal on the laptop:

```bash
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](https://vpsnine.com/tutorials/wireguard-vpn-server-ubuntu), 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](https://vpsnine.com/help/firewall-with-ufw).

## 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](https://vpsnine.com/tutorials/install-php-fpm-nginx-ubuntu), or follow our [WordPress on a VPS](https://vpsnine.com/help/wordpress-on-a-vps) guide.
- Ship the nightly dump off the server with [restic to S3-compatible storage](https://vpsnine.com/tutorials/restic-backup-s3) or [borgmatic to a second server](https://vpsnine.com/tutorials/borgmatic-backup-setup); borgmatic can run the dump for you.
- Prefer PostgreSQL? [Install PostgreSQL on Ubuntu 24.04](https://vpsnine.com/tutorials/install-postgresql-ubuntu).
- MariaDB's own [security documentation](https://mariadb.com/kb/en/securing-mariadb/) covers TLS and auditing when you need them.