Run a MySQL database server with rootless Podman

What you will set up

A managed database service gives you an address, a user name and a password, and looks after the rest. This guide builds the same on your own server: MySQL with one database and one account per app, settings sized for the server, TLS, nightly backups, and access only from this server and from the servers your apps run on, over WireGuard. The database ports are never open to the internet.

MySQL is developed by Oracle Corporation in the United States, and MySQL is Oracle's trademark. MySQL Community Server, used here, is open source under the GPL version 2; Oracle also sells an Enterprise Edition with extra tools and support. The container image is the official mysql image on Docker Hub, built by Docker's Official Images project from Oracle's packages. On our test server MySQL made no outgoing connections.

Here MySQL runs as a single container under a user of its own called mysql, set up as in the Podman guide. It needs no Caddy: apps talk to it directly, over the tunnel.

Every step below was run on a fresh Melonslab VC-P Alloy (2 vCPU, 8 GB) with Debian 13:

• Swedish text such as Åsa Öberg-Ärlig, piped into the mysql client in the container, was stored as UTF-8.
• MySQL 9.7.2, the current long-term support (LTS) release, started with a root password from a Podman secret, and root could only log in from inside the container.
• An app account, from an app server at the other end of a WireGuard tunnel, could create tables in its own database, but not open another database, create databases or read files on the server. Without TLS, it was refused.
• The mysql client from Debian and a Python app connected over TLS 1.3 and checked the server's certificate. A client with the wrong certificate authority was refused.
• Nothing listened on the server's public IPv4 or IPv6 address, and from the internet, ports 3306 and 33060 did not answer.
• A sysbench run before and after the memory settings.
• A nightly dump was restored next to the real database, and into an empty server with its accounts. A physical backup with Percona XtraBackup was restored over the live data.
• A major upgrade from MySQL 8.4 to 9.7 by changing the image tag.
• Everything came back by itself after a reboot, including the tunnel address.

After a reboot, the whole server used 1.2 GB of memory, of which the MySQL container used 830 MB. MySQL's cache takes more as it fills with your data, up to the 4 GB set in step 4.

Before you start

You need:

• a server set up as in the Podman guide, with ufw on as in Secure a new Debian server;
• WireGuard on the server, with the tunnel address 10.8.0.1, and each app server added as a device, as in Adding more devices. The examples use 10.8.0.2 for the app server.

An app server that is another Melonslab server in the same network needs Connect two servers first, so WireGuard can reach it. The tunnel then adds encryption and a fixed address for each server.

The examples use appdb and appuser for the app's database and account, and 203.0.113.10 for the database server's public address. Replace them with your own throughout. MySQL uses port 3306, as MariaDB does, so run one of them per server.

1. Create the user

As root:

useradd -m -s /bin/bash mysql
loginctl enable-linger mysql

MySQL will listen on the tunnel address, 10.8.0.1. At boot, the user's services can start before WireGuard has created that address, and MySQL then stops with Can't start server: Bind on TCP/IP port: Cannot assign requested address. This setting lets programs listen on an address the server does not have yet, so MySQL starts no matter which comes first:

echo "net.ipv4.ip_nonlocal_bind = 1" > /etc/sysctl.d/60-nonlocal-bind.conf
sysctl --system

Then switch to the new user:

machinectl shell mysql@

Everything up to step 7 runs as mysql.

2. Create the secrets

openssl rand -base64 24 | tr -d '\n' | podman secret create mysql-root-password -
pw=$(podman secret inspect --showsecret --format '{{.SecretData}}' mysql-root-password)
printf '[client]\nuser=root\npassword="%s"\ndefault-character-set=utf8mb4\n' "$pw" | podman secret create mysql-root-client -
openssl rand -base64 24 | tr -d '\n' | podman secret create appdb-password -

The first is MySQL's root password. The second holds the same password as a client settings file, which the mysql command inside the container reads, so you can log in as root without typing it. default-character-set=utf8mb4 makes that client send text as UTF-8: without it, it uses latin1 when SQL is piped in, and a name such as Åsa is stored as Ã…sa. The third is the password of your app's account. Podman stores them, so they are in no file below. To see one, for your password manager or the app's settings:

podman secret inspect --showsecret --format '{{.SecretData}}' appdb-password

3. Make a TLS certificate

WireGuard already encrypts the traffic between your servers. TLS adds a check that the app is talking to your database and not to something else on the tunnel, and keeps the connection encrypted if it ever takes another path. Since nobody outside your servers connects, you need no public certificate: make your own small certificate authority, and a certificate for the server signed by it.

mkdir -p ~/mysql/ca ~/mysql/tls ~/mysql/conf.d
cd ~/mysql/ca
openssl req -x509 -newkey ec -pkeyopt ec_paramgen_curve:P-256 -nodes -days 3650 \
  -subj "/CN=MySQL CA" -addext "basicConstraints=critical,CA:TRUE" \
  -addext "keyUsage=critical,keyCertSign,cRLSign" -keyout ca-key.pem -out ca.pem
openssl req -newkey ec -pkeyopt ec_paramgen_curve:P-256 -nodes -subj "/CN=10.8.0.1" \
  -addext "subjectAltName=IP:10.8.0.1,IP:127.0.0.1" -keyout ../tls/server-key.pem -out server.csr
openssl x509 -req -in server.csr -CA ca.pem -CAkey ca-key.pem -days 3650 -copy_extensions copy -out ../tls/server-cert.pem
cp ca.pem ../tls/
podman unshare chown 999:999 ../tls/server-key.pem

• ca.pem is the certificate authority. Every app server gets a copy, to check the server with.
• ca-key.pem signs certificates. It stays in ~/mysql/ca, which the container never sees.
• The server's certificate names the addresses apps connect to, 10.8.0.1 and 127.0.0.1. Clients compare it with the address they used.
• Both are valid for ten years. Put a reminder in your calendar, and repeat the last four commands before then.
• The last line gives the server's key to the user MySQL runs as inside the container, user ID 999, so MySQL can read it and other users on the server cannot.

Without these files, MySQL makes its own self-signed certificate when it starts, but clients have nothing to check it against.

4. Size it for the server

MySQL's defaults are made to start on any machine, with 128 MB for its own cache. Create ~/mysql/conf.d/server.cnf:

[mysqld]
# Listen on this server and on the WireGuard tunnel only
bind_address = 127.0.0.1,10.8.0.1
mysqlx = OFF

# TLS for connections over the network
ssl_ca = /etc/mysql/tls/ca.pem
ssl_cert = /etc/mysql/tls/server-cert.pem
ssl_key = /etc/mysql/tls/server-key.pem

# Sized for 2 vCPU and 8 GB of memory
innodb_buffer_pool_size = 4G
innodb_redo_log_capacity = 1G
max_connections = 200
tmp_table_size = 64M
max_heap_table_size = 64M
binlog_expire_logs_seconds = 604800

• bind_address makes MySQL listen on the server itself and on the tunnel, and nowhere else. Even with the firewall off, nothing listens on the public address.
• mysqlx = OFF switches off the X Protocol, a second interface for MySQL Shell and document-store apps. By default it listens on port 33060 on every address. Ordinary apps and clients do not use it.
• innodb_buffer_pool_size is MySQL's cache for tables and indexes. MySQL reads past the system's file cache, so on a server that only runs the database, half to two thirds of the memory is the usual size. 4 GB leaves room for the system, connections and a few other containers.
• innodb_redo_log_capacity is the redo log, where changes go before they are written to the tables. A larger log than the default 100 MB smooths out heavy writing.
• max_connections is raised from the default 151. Each connection uses a little memory of its own, a few MB at most for ordinary queries.
• tmp_table_size and max_heap_table_size let a query keep larger temporary tables in memory before it moves them to disk.
• MySQL keeps a binary log of every change, which replication and restoring to a moment in time use. It keeps 30 days of it by default, which can take a lot of disk on a busy server. binlog_expire_logs_seconds keeps seven days. Plan disk for it all the same: in our sysbench tests, seven days of binary log grew to 2.5 GB. podman exec mysql mysql -e "SHOW BINARY LOGS" lists the files and their sizes.

On the Titanium plan, with 16 GB, use innodb_buffer_pool_size = 10G.

5. Run MySQL

mkdir -p ~/.config/containers/systemd

Create ~/.config/containers/systemd/mysql.container:

[Unit]
Description=MySQL database server

[Container]
ContainerName=mysql
Image=docker.io/library/mysql:9.7
Network=host
Volume=mysql-data:/var/lib/mysql
Volume=mysql-run:/var/run/mysqld
Volume=%h/mysql/conf.d:/etc/mysql/conf.d:ro
Volume=%h/mysql/tls:/etc/mysql/tls:ro
Secret=mysql-root-password
Secret=mysql-root-client,target=/root/.my.cnf,mode=0400
Environment=MYSQL_ROOT_PASSWORD_FILE=/run/secrets/mysql-root-password MYSQL_ROOT_HOST=localhost
HealthCmd=mysqladmin ping -h 127.0.0.1 --silent
HealthInterval=10s
Notify=healthy
AutoUpdate=registry

[Service]
Restart=always
TimeoutStartSec=300

[Install]
WantedBy=default.target

What the lines do:

• mysql:9.7 is the current LTS release, with Oracle's premier support until April 2031. AutoUpdate=registry installs its bug and security fixes, such as 9.7.2 to 9.7.3, but never moves you to a new major version. The mysql:latest and innovation tags follow the innovation releases instead, which get new features every quarter and are supported only until the next one.
• Network=host puts MySQL on the server's own network, like Caddy. It then listens on exactly the addresses in step 4, and sees each client's real address, which the accounts in step 6 depend on.
• The root password comes from the secret. MYSQL_ROOT_HOST=localhost creates root only for connections from inside the container. Without it, the image also creates a root account that may log in from any address.
• mysql-root-client becomes /root/.my.cnf inside the container, so podman exec mysql mysql logs you in as root.
• The mysql-run volume holds MySQL's socket, which the physical backup in step 10 connects through.
• Notify=healthy makes the service count as started only when MySQL answers, so everything that waits for it waits long enough.

Start it, and switch on automatic updates:

systemctl --user daemon-reload
systemctl --user start mysql
systemctl --user enable --now podman-auto-update.timer
podman exec mysql mysql -e "SELECT VERSION(), @@innodb_buffer_pool_size, @@bind_address"

VERSION()   @@innodb_buffer_pool_size   @@bind_address
9.7.2   4294967296  127.0.0.1,10.8.0.1

The first start downloads the 952 MB image and takes about a minute.

6. Create a database and an account for each app

Give every app its own database and its own account, so a leak in one app does not open the others. Create them as root in MySQL, with the password from step 2:

pw=$(podman secret inspect --showsecret --format '{{.SecretData}}' appdb-password)
podman exec -i mysql mysql <<SQL
CREATE DATABASE appdb;
CREATE USER 'appuser'@'10.8.0.2' IDENTIFIED BY '$pw' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, REFERENCES
  ON appdb.* TO 'appuser'@'10.8.0.2';
SQL

• 'appuser'@'10.8.0.2' is an account that can only log in from the app server's tunnel address. The same name from any other address is refused.
• REQUIRE SSL refuses the account without TLS.
• The rights cover what an app and its migrations need in its own database: read and write rows, and create, change and remove tables and indexes. The account cannot create databases or accounts, or read files on the server.

We checked from the app server, logged in as appuser:

$ mysql ... mysql
ERROR 1044 (42000): Access denied for user 'appuser'@'10.8.0.2' to database 'mysql'
$ mysql ... -e "CREATE DATABASE other"
ERROR 1044 (42000) at line 1: Access denied for user 'appuser'@'10.8.0.2' to database 'other'

LOAD_FILE('/etc/passwd') returned nothing. Repeat the block for each app, with its own names and a secret of its own.

The address in the account is where the connection comes from:

• an app on another server, over the tunnel: its tunnel address, such as 10.8.0.2;
• an app on this server, outside a container, or through an SSH tunnel (step 7): 127.0.0.1;
• an app in a container on this server, under another user: 10.8.0.1, because it reaches MySQL through the tunnel address and comes from there.

7. Let your apps in

Go back to root with exit, and open port 3306 on the tunnel only:

ufw allow in on wg0 to any port 3306 proto tcp

Copy the certificate authority, /home/mysql/mysql/ca/ca.pem, to each app server, for example to /etc/ssl/certs/appdb-ca.pem. It is not secret.

With the mysql client. Debian has no client from Oracle; its mysql command comes from MariaDB, and works with MySQL. On the app server, apt install mariadb-client, then:

mysql -h 10.8.0.1 -u appuser -p --ssl-ca=/etc/ssl/certs/appdb-ca.pem appdb \
  -e "SELECT CURRENT_USER(); SHOW STATUS LIKE 'Ssl_version'"

CURRENT_USER()
appuser@10.8.0.2
Variable_name   Value
Ssl_version TLSv1.3

From an app. In most apps, the connection string is mysql://appuser:PASSWORD@10.8.0.1:3306/appdb, with the path to ca.pem in its TLS settings. In Python, with PyMySQL (apt install python3-pymysql):

import os
import pymysql

conn = pymysql.connect(
    host="10.8.0.1",
    user="appuser",
    password=os.environ["DB_PASSWORD"],
    database="appdb",
    ssl_ca="/etc/ssl/certs/appdb-ca.pem",
    ssl_verify_cert=True,
    ssl_verify_identity=True,
)
with conn.cursor() as cur:
    cur.execute("SELECT COUNT(*) FROM notes")
    print(cur.fetchone()[0])
conn.close()

ssl_verify_cert and ssl_verify_identity make PyMySQL check that the certificate is signed by your authority and names 10.8.0.1. Give both: with only ssl_verify_identity, the version in Debian 13 stops with Cannot set verify_mode to CERT_NONE when check_hostname is enabled. MySQL's default password method, caching_sha2_password, works in PyMySQL over TLS. Our test app read back 1,000 rows over TLS 1.3, and with another authority's ca.pem it was refused with certificate verify failed.

From your own computer. An SSH tunnel needs no change on the server. On your computer:

ssh -N -L 3307:localhost:3306 root@203.0.113.10

While it runs, a database tool on your computer connects to 127.0.0.1 port 3307, and MySQL sees the connection come from 127.0.0.1, so it needs an account for that address (step 6).

8. Check what others see

On the server:

ss -tlnp | grep mysqld

LISTEN 0      4096        10.8.0.1:3306      0.0.0.0:*    users:(("mysqld",pid=5679,fd=18))
LISTEN 0      4096       127.0.0.1:3306      0.0.0.0:*    users:(("mysqld",pid=5679,fd=15))

Nothing on the public address, nothing on IPv6, and no port 33060. From a computer that is not on the tunnel, try the public address:

nc -vz -w 5 203.0.113.10 3306

nc: connect to 203.0.113.10 port 3306 (tcp) timed out: Operation now in progress

ufw drops the attempt without an answer, and on the server journalctl -k | grep 'DPT=3306' shows it as [UFW BLOCK]. Over the tunnel, the app server only gets in with the accounts you created. Our tests got:

--skip-ssl            ERROR 1045 (28000): Access denied for user 'appuser'@'10.8.0.2' (using password: YES)
-u root               ERROR 1045 (28000): Access denied for user 'root'@'10.8.0.2' (using password: YES)
another CA            ERROR 2026 (HY000): TLS/SSL error: self-signed certificate in certificate chain

9. Measure

We measured with sysbench on a 1.9 GB test database, eight tables of a million rows, 16 clients for 60 seconds, from the server itself. Each test ran twice:

With the default 128 MB cache, the results varied a lot between runs. With the settings from step 4, read-only roughly doubled and both tests gave steady results. To run it yourself, install sysbench on the server, create a sbtest database and an account for 127.0.0.1, and use sysbench oltp_read_write --mysql-host=127.0.0.1 --tables=8 --table-size=1000000 --threads=16 --time=60 with prepare, then run. Remove the database and account afterwards.

10. Back up every night

mysqldump writes a consistent copy of a database while apps keep using it. As mysql (machinectl shell mysql@), create ~/.local/bin/mysql-dump-all:

#!/bin/sh
# Dump the accounts and every database, and keep 14 days of dumps
set -e
umask 077
dir=$HOME/backup
day=$(date +%F)
podman exec mysql mysql -N -e "SELECT CONCAT('SHOW CREATE USER ', QUOTE(user), '@', QUOTE(host), '; SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user WHERE user NOT IN ('root', 'mysql.infoschema', 'mysql.session', 'mysql.sys')" \
    | podman exec -i mysql mysql -N --init-command="SET print_identified_with_as_hex = ON" | sed 's/$/;/' > "$dir/users-$day.sql"
for db in $(podman exec mysql mysql -N -e "SHOW DATABASES WHERE \`Database\` NOT IN ('information_schema','performance_schema','sys','mysql')"); do
    podman exec mysql mysqldump --single-transaction --routines --events --set-gtid-purged=OFF "$db" | gzip > "$dir/$db-$day.sql.gz"
done
find "$dir" -type f -mtime +13 -delete

chmod 755 ~/.local/bin/mysql-dump-all
mkdir -p ~/backup ~/.config/systemd/user

The users file holds every account except root, with its rights and password hash, which the database dumps leave out: the first mysql command writes a SHOW CREATE USER and SHOW GRANTS for each account, and the second runs them. --set-gtid-purged=OFF leaves out MySQL's replication positions, which would otherwise stop the restore with @@GLOBAL.GTID_PURGED cannot be changed. Only mysql can read the files. Run it every night with a systemd service and timer. Create ~/.config/systemd/user/mysql-dump.service:

[Unit]
Description=Dump MySQL databases

[Service]
Type=oneshot
ExecStart=%h/.local/bin/mysql-dump-all

And ~/.config/systemd/user/mysql-dump.timer:

[Unit]
Description=Dump MySQL databases every night

[Timer]
OnCalendar=*-*-* 02:30
Persistent=true

[Install]
WantedBy=timers.target

Switch it on, and run it once now:

systemctl --user daemon-reload
systemctl --user enable --now mysql-dump.timer
systemctl --user start mysql-dump.service
ls -l ~/backup

-rw------- 1 mysql mysql 5608 Oct 14 02:28 appdb-2026-10-14.sql.gz
-rw------- 1 mysql mysql  980 Oct 14 02:28 users-2026-10-14.sql

Test a restore. Restore into a new database, next to the real one:

podman exec mysql mysql -e "CREATE DATABASE appdb_restore"
gunzip -c ~/backup/appdb-2026-10-14.sql.gz | podman exec -i mysql mysql appdb_restore
podman exec mysql mysql -e "SELECT COUNT(*) FROM appdb_restore.notes; DROP DATABASE appdb_restore"

Use one of your own tables in the count. Our test table, notes, came back with all its 1,000 rows.

To rebuild a whole server, set it up with steps 1 to 5, then load the users file first and each database after it:

podman exec -i mysql mysql < ~/backup/users-2026-10-14.sql
podman exec mysql mysql -e "CREATE DATABASE appdb"
gunzip -c ~/backup/appdb-2026-10-14.sql.gz | podman exec -i mysql mysql appdb

We did this in an empty MySQL: the accounts came back with their addresses and rights, the app logged in with its old password, and the table had all its rows. Root keeps the password of the new server. Keep the passwords from step 2 in your password manager.

Copy ~/backup off the server every night, for example with restic. The dumps hold all your data.

Physical backups for large databases

A dump is restored by running every statement again, which takes long for databases of many GB. Percona XtraBackup, from Percona in the United States, open source under the GPL, copies the data files themselves while MySQL runs. It has its own image, and connects through the socket in the mysql-run volume:

podman run --rm --user 0 --volumes-from mysql --secret mysql-root-client,target=/root/.my.cnf,mode=0400 \
  docker.io/percona/percona-xtrabackup:9.7 xtrabackup --backup --stream=xbstream --target-dir=/tmp \
  2>~/backup/xtrabackup.log > ~/backup/mysql-physical.xb
tail -1 ~/backup/xtrabackup.log

The last line of the log says completed OK!. To restore it, MySQL has to be stopped and its data replaced:

systemctl --user stop mysql
podman volume rm mysql-data
podman volume create mysql-data
podman run --rm -i --user 0 -v mysql-data:/var/lib/mysql docker.io/percona/percona-xtrabackup:9.7 sh -c \
  'mkdir /tmp/r && xbstream -x -C /tmp/r && xtrabackup --prepare --target-dir=/tmp/r && xtrabackup --copy-back --target-dir=/tmp/r --datadir=/var/lib/mysql && chown -R 999:999 /var/lib/mysql' \
  < ~/backup/mysql-physical.xb
systemctl --user start mysql

We deleted half of the test table after a physical backup, restored it this way, and all 1,000 rows were back. Two things to know: the image is 1.6 GB, and when we tested, the newest XtraBackup for 9.7 was a release candidate, 9.7.1-rc1, a step behind the server's 9.7.2. It worked, but each XtraBackup release is made for the MySQL versions out when it was built, so check the backup log after each MySQL update.

11. Update MySQL

Bug and security fixes, such as 9.7.2 to 9.7.3, come with podman auto-update once a day, and MySQL restarts. To see if one is waiting:

podman auto-update --dry-run

Major versions. A new LTS release comes about every two years, and you can upgrade from one LTS directly to the next. MySQL upgrades its data by itself when a new version starts on it. Run a backup, change the tag in mysql.container, and restart:

systemctl --user start mysql-dump.service
sed -i 's|mysql:9.7|mysql:NEW|' ~/.config/containers/systemd/mysql.container
systemctl --user daemon-reload
systemctl --user restart mysql
podman logs mysql 2>&1 | grep -i upgrade

Replace NEW with the next LTS version. We tested this from 8.4 to 9.7, with the same steps:

[Server] Data dictionary upgrading from version '80300' to '90200'.
[Server] Data dictionary upgrade from version '80300' to '90200' completed.
[Server] Server upgrade from '80411' to '90702' started.
[Server] Server upgrade from '80411' to '90702' completed.

It took 35 seconds, and the app connected again with its old account. Read the release notes first: each LTS removes things the previous one only warned about. A step back to the old version is not supported: restore the backup instead.

Troubleshooting

MySQL does not start after a reboot, and journalctl --user -u mysql says Bind on TCP/IP port: Cannot assign requested address. MySQL started before WireGuard, without the setting from step 1. sysctl net.ipv4.ip_nonlocal_bind should print 1.

Access denied for user 'appuser'@'10.8.0.2'. The password, the address or TLS is wrong. Check that the client uses TLS, since the account has REQUIRE SSL, and that an account exists for exactly that address: podman exec mysql mysql -e "SELECT user, host FROM mysql.user".

Host '10.8.0.1' is not allowed to connect to this MySQL server. The app runs in a container on this server, and comes from the tunnel address. Create its account for 10.8.0.1, as in step 6.

The container stops during its first start, with Access denied for user 'root'@'localhost' (using password: YES). The image's setup script read a password before root had one, for example from a MYSQL_PWD setting in the container. Remove it, then remove the half-made data with podman volume rm mysql-data and start again.

certificate verify failed: CA cert does not include key usage extension. The certificate authority was made without the keyUsage line in step 3, which Python 3.13 and newer require. Make it again with all of step 3, and copy the new ca.pem to the app servers.

TLS/SSL error: self-signed certificate in certificate chain. The client has another ca.pem than the one that signed the server's certificate. Copy ~/mysql/ca/ca.pem from the server again.

@@GLOBAL.GTID_PURGED cannot be changed while restoring a dump. The dump was made without --set-gtid-purged=OFF. Add it to the script in step 10 and dump again.

The app times out connecting to 10.8.0.1. Check that WireGuard is up on both servers (wg show), and that the ufw rule from step 7 is there: ufw status | grep 3306.