Self-host MySQL

Create a VM#

On your computer (pick another name if my-mysql is taken):

ssh exe.dev new --name=my-mysql
ssh my-mysql.exe.xyz

Adding MySQL next to an app? Skip this step and run the rest on the app's VM. All remaining commands run on the VM, as exedev.

Install MySQL 9.7#

Ubuntu's own mysql-server is MySQL 8.0, which reached end of life in April 2026. Oracle's MySQL APT repository carries the current releases. Add it with Oracle's signing key, on the 9.7 LTS track:

sudo install -d -m 755 /etc/apt/keyrings
curl -fsSL https://repo.mysql.com/RPM-GPG-KEY-mysql-2025 | sudo gpg --dearmor --yes -o /etc/apt/keyrings/mysql.gpg
echo "deb [signed-by=/etc/apt/keyrings/mysql.gpg] http://repo.mysql.com/apt/ubuntu/ $(. /etc/os-release; echo $VERSION_CODENAME) mysql-9.7-lts" |
  sudo tee /etc/apt/sources.list.d/mysql.list
sudo apt-get update
apt-cache policy mysql-server | head -n 3

The candidate should be 9.7.x-1ubuntu24.04. (The repository's packages are signed, and apt checks them with this key.) Install the server. With no root password set, MySQL's root signs in only through the socket, with sudo:

sudo DEBIAN_FRONTEND=noninteractive apt-get install -y mysql-server jq
sudo mysql -NBe 'SELECT version()'
systemctl is-enabled mysql

Expect 9.7.x and enabled: it starts at boot. Now make your Linux user a MySQL admin that signs in through the socket, so mysql works with no options or password:

sudo mysql -e "CREATE USER IF NOT EXISTS '$USER'@'localhost' IDENTIFIED WITH auth_socket;
  GRANT ALL ON *.* TO '$USER'@'localhost' WITH GRANT OPTION"
mysql -NBe 'SELECT current_user()'

Expect exedev@localhost.

Size it for the VM#

MySQL's defaults suit a tiny machine, and it listens on every network interface. This script gives InnoDB's buffer pool, MySQL's cache, half the VM's RAM, keeps 7 days of binary logs instead of 30, logs queries slower than a second, and keeps MySQL on localhost. Run it again after resizing the VM.

mkdir -p -m 700 ~/mysql
cat > ~/mysql/tune.sh <<'EOF'
#!/usr/bin/env bash
# Size MySQL for this VM's memory and restart it. Run again after
# resizing the VM.
set -euo pipefail
mem=$(awk '/^MemTotal:/ {print int($2 / 1024)}' /proc/meminfo)  # MB
redo=$((mem / 8 < 256 ? 256 : mem / 8 > 2048 ? 2048 : mem / 8))
sudo tee /etc/mysql/mysql.conf.d/10-exe-dev.cnf >/dev/null <<CONF
# Written by ~/mysql/tune.sh for $mem MB of RAM. Rerunning it replaces
# this file; put your own settings in 50-local.cnf, and don't repeat
# these there. Who MySQL listens to is in 20-listen.cnf.
[mysqld]
innodb_buffer_pool_size = $((mem / 2))M
innodb_redo_log_capacity = ${redo}M
max_connections = 151
mysqlx_bind_address = 127.0.0.1
binlog_expire_logs_seconds = 604800
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
CONF
[ -e /etc/mysql/mysql.conf.d/20-listen.cnf ] ||
  printf '[mysqld]\nbind_address = 127.0.0.1\n' | sudo tee /etc/mysql/mysql.conf.d/20-listen.cnf >/dev/null
sudo systemctl restart mysql
mysql -e 'SELECT @@innodb_buffer_pool_size DIV 1048576 AS buffer_pool_mb, @@innodb_redo_log_capacity DIV 1048576 AS redo_mb, @@bind_address, @@mysqlx_bind_address'
EOF
chmod +x ~/mysql/tune.sh
~/mysql/tune.sh
ss -ltn | grep -E ':3306|:33060'

The table shows the new values; on an 8 GB VM the buffer pool is about 3900 MB. ss should show MySQL on 127.0.0.1:3306 and 127.0.0.1:33060 only.

MySQL's packages don't rotate its logs, so add that too:

sudo tee /etc/logrotate.d/mysql-exe-dev >/dev/null <<'EOF'
/var/log/mysql/*.log {
    su mysql adm
    weekly
    rotate 8
    compress
    missingok
    notifempty
    copytruncate
}
EOF
sudo logrotate --debug /etc/logrotate.d/mysql-exe-dev 2>&1 | grep '^error' || echo ok

Expect ok.

Create a database for your app#

This script creates a database and a user with the same name, gives the user everything in that database, and writes its settings to ~/mysql/<name>.env. It never touches an existing one:

cat > ~/mysql/create-db.sh <<'EOF'
#!/usr/bin/env bash
# Usage: create-db.sh NAME
# Create a MySQL database and user called NAME, and write its settings
# to ~/mysql/NAME.env (mode 600).
set -euo pipefail
db=${1:?usage: create-db.sh NAME}
[[ $db =~ ^[a-z_][a-z0-9_]{0,31}$ ]] || { echo "Use up to 32 lowercase letters, digits, and _." >&2; exit 1; }
env="$HOME/mysql/$db.env"
if [ -e "$env" ]; then echo "$env already exists; leaving $db alone."; exit 0; fi
pass=$(openssl rand -hex 24)
mysql -e "CREATE DATABASE \`$db\`;
  CREATE USER '$db'@'localhost' IDENTIFIED BY '$pass';
  GRANT ALL ON \`$db\`.* TO '$db'@'localhost';"
(umask 077; cat > "$env" <<ENV
DATABASE_URL=mysql://$db:$pass@127.0.0.1:3306/$db
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=$db
DB_USERNAME=$db
DB_PASSWORD=$pass
ENV
)
echo "$env"
EOF
chmod +x ~/mysql/create-db.sh
~/mysql/create-db.sh app

Check that the app's user can log in over TCP and sees only its database:

. ~/mysql/app.env
MYSQL_PWD=$DB_PASSWORD mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USERNAME" -NBe 'SELECT current_user(); SHOW DATABASES' "$DB_DATABASE"
stat -c '%a %n' ~/mysql/app.env

Expect app@localhost, app, information_schema, and performance_schema, then 600.

Add a web admin#

Adminer is a database admin in a single PHP file. It runs on PHP's built-in web server, as in Adminer's official Docker image, behind exe.dev's login. A small wrapper signs you in as exedev over the socket, and only for the emails in allowed-emails.

sudo apt-get install -y php-cli php-mysql
mkdir -p ~/mysql/adminer && cd ~/mysql/adminer
curl -fsSL -o adminer.php https://github.com/vrana/adminer/releases/download/v6.2.0/adminer-6.2.0-mysql-en.php
[ -s allowed-emails ] || curl -fsS https://reflection.int.exe.xyz/email | jq -r .email > allowed-emails
cat allowed-emails

The last line should print your exe.dev email. If it doesn't, put your email in ~/mysql/adminer/allowed-emails yourself. Now the wrapper:

cat > ~/mysql/adminer/index.php <<'EOF'
<?php
// Adminer behind exe.dev login. exe.dev's proxy sends X-ExeDev-Email for
// signed-in visitors; only emails listed in allowed-emails get in, and they
// are connected as the MySQL user exedev over the local socket.
$email = strtolower($_SERVER['HTTP_X_EXEDEV_EMAIL'] ?? '');
if ($email === '') {
    header('Location: /__exe.dev/login?redirect=' . urlencode($_SERVER['REQUEST_URI']));
    exit;
}
$allowed = array_map('strtolower', array_map('trim',
    file(__DIR__ . '/allowed-emails', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES) ?: []));
if (!in_array($email, $allowed, true)) {
    http_response_code(403);
    header('Content-Type: text/plain');
    exit("$email isn't in ~/mysql/adminer/allowed-emails on this VM.\n");
}
if (!isset($_GET['username']) && empty($_POST)) {
    $_POST['auth'] = ['driver' => 'server', 'server' => '', 'username' => 'exedev', 'password' => '', 'db' => ''];
}

function adminer_object() {
    class ExeDevAdminer extends Adminer\Adminer {
        function credentials() { return ['localhost', 'exedev', '']; }
        function login($login, $password) { return true; }
        function verifyLoginToken() { return false; }
    }
    return new ExeDevAdminer;
}
require __DIR__ . '/adminer.php';
EOF

Run it as a service, as exedev. On a VM where an app uses port 8000, or will, set port=8001 in the first line; Adminer is then at https://<vm>.exe.xyz:8001/.

port=8000
sudo tee /etc/systemd/system/adminer.service >/dev/null <<EOF
[Unit]
Description=Adminer web admin for MySQL, behind exe.dev login
After=mysql.service

[Service]
User=$USER
WorkingDirectory=$HOME/mysql/adminer
Environment=PHP_CLI_SERVER_WORKERS=4
ExecStart=/usr/bin/php -S 0.0.0.0:$port index.php
Restart=always

[Install]
WantedBy=multi-user.target
EOF
sudo systemctl daemon-reload
sudo systemctl enable --now adminer

Check the three cases: no email (sent to exe.dev login), someone else (refused), and you (Adminer's database list):

port=$(grep -o ':[0-9]* ' /etc/systemd/system/adminer.service | tr -d ': ')
email=$(head -n 1 ~/mysql/adminer/allowed-emails)
timeout 30 sh -c "until curl -so /dev/null http://localhost:$port/; do sleep 1; done"
curl -s -o /dev/null -w '%{http_code}\n' http://localhost:$port/
curl -s -o /dev/null -w '%{http_code}\n' -H 'X-ExeDev-Email: someone@example.com' http://localhost:$port/
curl -sL -b /tmp/adminer.cookies -c /tmp/adminer.cookies -H "X-ExeDev-Email: $email" http://localhost:$port/ | grep -o '<title>[^<]*</title>'

Expect 302, 403, and <title>Select database - Adminer</title>.

Set up nightly backups#

This script dumps every database with mysqldump, in one consistent transaction per database, and checks each dump is complete. Beside the dumps it saves globals.sql, which recreates the databases, the users (with their password hashes), and their grants, and the .env files. It keeps the 14 newest backups:

cat > ~/mysql/backup.sh <<'EOF'
#!/usr/bin/env bash
# Back up every database, the users and their grants, and ~/mysql/*.env
# into ~/mysql/backups/<UTC time>/. Keeps the 14 newest backups.
set -euo pipefail
umask 077
dest="$HOME/mysql/backups"
keep=14
mkdir -p "$dest"
out="$dest/$(date -u +%Y%m%d-%H%M%S)"
while [ -e "$out" ] || [ -e "$out.part" ]; do sleep 1; out="$dest/$(date -u +%Y%m%d-%H%M%S)"; done
trap 'rm -rf "$out.part"' EXIT
mkdir "$out.part"

dbs=$(mysql -NBr -e "SELECT schema_name FROM information_schema.schemata
  WHERE schema_name NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')")
users=$(mysql -NBr -e "SELECT CONCAT(QUOTE(user), '@', QUOTE(host)) FROM mysql.user
  WHERE user NOT IN ('root', '$USER') AND user NOT LIKE 'mysql.%'")
{
  for db in $dbs; do
    mysql -NBr -e "SHOW CREATE DATABASE IF NOT EXISTS \`$db\`" | cut -f2- | sed 's/$/;/'
  done
  for u in $users; do
    mysql -NBr -e "SET print_identified_with_as_hex = ON; SHOW CREATE USER $u" |
      sed 's/^CREATE USER /CREATE USER IF NOT EXISTS /; s/$/;/'
    mysql -NBr -e "SHOW GRANTS FOR $u" | sed 's/$/;/'
  done
} > "$out.part/globals.sql"

for db in $dbs; do
  mysqldump --single-transaction --routines --triggers --events --hex-blob \
    --set-gtid-purged=OFF "$db" | gzip > "$out.part/$db.sql.gz"
  zcat "$out.part/$db.sql.gz" | tail -n 1 | grep -q '^-- Dump completed' ||
    { echo "$db: dump incomplete" >&2; exit 1; }
done
cp -p "$HOME"/mysql/*.env "$out.part/" 2>/dev/null || true

mv "$out.part" "$out"
ls -1d "$dest"/2*/ | head -n -"$keep" | xargs -r rm -rf --
echo "$out"
EOF
chmod +x ~/mysql/backup.sh

Schedule it, then take a first backup:

sudo tee /etc/systemd/system/mysql-backup.service >/dev/null <<EOF
[Unit]
Description=Back up MySQL to ~/mysql/backups
After=mysql.service

[Service]
Type=oneshot
User=$USER
ExecStart=$HOME/mysql/backup.sh
EOF
sudo tee /etc/systemd/system/mysql-backup.timer >/dev/null <<'EOF'
[Unit]
Description=Back up MySQL every night

[Timer]
OnCalendar=*-*-* 03:30:00
RandomizedDelaySec=30m
Persistent=true

[Install]
WantedBy=timers.target
EOF
sudo systemctl daemon-reload
sudo systemctl enable --now mysql-backup.timer
sudo systemctl start mysql-backup.service
systemctl list-timers mysql-backup.timer
ls -l ~/mysql/backups/*/

Expect a next run time, and globals.sql, app.env, and app.sql.gz. For failures: journalctl -u mysql-backup.service.

Test a backup#

Put a row with some non-ASCII text in a scratch database, take a backup, and restore it into a scratch MySQL server on port 3307, exactly as you would on a new VM:

mysql -e "CREATE DATABASE restore_check;
  CREATE TABLE restore_check.t (id int PRIMARY KEY, note varchar(50));
  INSERT INTO restore_check.t VALUES (1, 'Grüße ✓')"
latest=$(~/mysql/backup.sh)
s=/tmp/mysql-scratch
mkdir -m 700 $s
/usr/sbin/mysqld --no-defaults --initialize-insecure --datadir=$s/data 2>/dev/null
/usr/sbin/mysqld --no-defaults --daemonize --datadir=$s/data --socket=$s/mysqld.sock \
  --port=3307 --bind-address=127.0.0.1 --mysqlx=OFF --pid-file=$s/mysqld.pid --log-error=$s/error.log
scratch() { mysql --no-defaults --socket=$s/mysqld.sock --user=root "$@"; }
scratch < "$latest/globals.sql"
for f in "$latest"/*.sql.gz; do zcat "$f" | scratch "$(basename "$f" .sql.gz)"; done
scratch -NBe 'SELECT note FROM restore_check.t'
. "$latest/app.env"
MYSQL_PWD=$DB_PASSWORD mysql --no-defaults -h 127.0.0.1 -P 3307 -u app -NBe 'SELECT @@port, current_user(), database()' app

Expect Grüße ✓, then 3307 app@localhost app: the restored user still logs in with the password from the backup. Stop the scratch server, then remove it and the scratch database:

scratch -e 'SHUTDOWN'
timeout 60 sh -c "while [ -e $s/mysqld.pid ]; do sleep 1; done"
rm -rf $s
mysql -e 'DROP DATABASE restore_check'
mysql -NBe 'SHOW DATABASES'

The list should show app and MySQL's own information_schema, mysql, performance_schema, and sys. Installation done; continue with Open Adminer.

On the VM, ~/mysql/backup.sh takes an extra backup and prints its folder. Each folder has globals.sql (the databases, users, and grants), a .sql.gz per database, and the .env files.

Undo a mistake#

Dropped the wrong table? Restore a backup next to the live database. On the VM, pick a backup folder from the list:

ls ~/mysql/backups/
when=20261030-033012
db=app
mysql -e "CREATE DATABASE ${db}_restored"
zcat ~/mysql/backups/$when/$db.sql.gz | mysql ${db}_restored
mysql -e "SHOW TABLES" ${db}_restored

Copy a single table back with mysqldump app_restored mytable | mysql app (for a table that's gone from app), and mysql -e 'DROP DATABASE app_restored' when you're done.

To roll the whole database back instead, stop your app, keep a copy of its current state, and load the backup in its place. Users and their grants stay as they are:

when=20261030-033012
db=app
mysqldump --single-transaction --routines --events --set-gtid-purged=OFF $db | gzip > ~/$db-before-rollback.sql.gz
mysql -e "DROP DATABASE \`$db\`; CREATE DATABASE \`$db\`"
zcat ~/mysql/backups/$when/$db.sql.gz | mysql $db
mysql -e "SHOW TABLES" $db

Start the app again, and delete ~/app-before-rollback.sql.gz once you're happy.

Restore everything on a new VM#

On a new VM, follow Install by hand up to and including "Size it for the VM", but don't create the app database. Then copy a backup folder over. On your computer:

scp -r mysql-backups/20261030-033012 my-mysql.exe.xyz:restore

On the VM, load the databases, users, and grants, then each database's data:

mysql < ~/restore/globals.sql
for f in ~/restore/*.sql.gz; do zcat "$f" | mysql "$(basename "$f" .sql.gz)"; done
cp --update=none ~/restore/*.env ~/mysql/
chmod 600 ~/mysql/*.env
mysql -e 'SHOW DATABASES; SELECT user, host FROM mysql.user'

Every database and user should be listed. Apps keep their old passwords, so the .env files work unchanged. Continue Install by hand from "Create a database for your app" (it leaves the existing app alone), and rm -r ~/restore when you're done. If the old VM had Docker or Tailscale access, redo its 20-listen.cnf too; the users for it came back with the rest.

More often#

Nightly dumps can lose up to a day of changes. To back up hourly and keep two days, on the VM:

sudo sed -i 's/^OnCalendar=.*/OnCalendar=hourly/; s/^RandomizedDelaySec=.*/RandomizedDelaySec=5m/' /etc/systemd/system/mysql-backup.timer
sed -i 's/^keep=.*/keep=48/' ~/mysql/backup.sh
sudo systemctl daemon-reload && sudo systemctl restart mysql-backup.timer
systemctl list-timers mysql-backup.timer

Each backup is a full dump, so this suits databases up to a few GB. exe.dev already replicates the VM's disk to other machines, which guards against hardware failure, not against a bad DELETE. For bigger databases, MySQL Shell's dump utilities dump and load in parallel, and Shelley can set them up for you.

Updates don't install themselves on this VM, so put them on your calendar. Oracle ships MySQL fixes, including security fixes, every January, April, July, and October.

Minor updates#

On the VM, back up, then upgrade just the MySQL packages. The upgrade restarts MySQL, so apps see a few seconds of errors:

~/mysql/backup.sh
sudo apt-get update
sudo DEBIAN_FRONTEND=noninteractive apt-get install --only-upgrade -y -o Dpkg::Options::=--force-confold \
  $(dpkg-query -W -f='${db:Status-Abbrev} ${Package}\n' 'mysql-*' | awk '$1 == "ii" {print $2}')
mysql -NBe 'SELECT version()'

If apt-get update warns that the MySQL key has expired (EXPKEYSIG), Oracle has published a renewed one: rerun the curl ... gpg --dearmor line from Install MySQL 9.7, with the newest RPM-GPG-KEY-mysql-<year> file listed at https://repo.mysql.com/.

To update Adminer, download the new release's adminer-<version>-mysql-en.php from its releases over ~/mysql/adminer/adminer.php, then repeat the three-case check from Add a web admin.

Major upgrades#

9.7 is supported until April 2034, so there's no rush. The next long-term release is planned for April 2028 (as 28.4, in MySQL's new year-and-month numbering); the quarterly releases in between are "Innovation" releases, each supported only until the next. Upgrade from one LTS to the next once its release notes look good and your app supports it.

MySQL upgrades its data in place the first time the new version starts, and can't go back, so your backup is the way back. On the VM, find the new track's name (such as mysql-9.7-lts) in the repository's list of components, then, with new set to it:

new=mysql-28.4-lts
~/mysql/backup.sh
sudo sed -i "s/ mysql-[0-9.]*-lts$/ $new/" /etc/apt/sources.list.d/mysql.list
sudo apt-get update
sudo DEBIAN_FRONTEND=noninteractive apt-get install -y -o Dpkg::Options::=--force-confold mysql-server
mysql -NBe 'SELECT version()'
sudo grep -i -E 'upgrad|error' /var/log/mysql/error.log | tail -n 20

Stop your apps first and start them afterwards. Then check your app, and run ~/mysql/tune.sh again in case the new version's defaults changed.

On the VM, MySQL's error log usually has the answer:

systemctl status mysql
sudo tail -n 50 /var/log/mysql/error.log

• Can't connect to local MySQL server through socket: MySQL isn't running. Start it with sudo systemctl start mysql and read the error log if it stops.

• It won't start after making the VM smaller: its buffer pool is too big now. Run ~/mysql/tune.sh.

• Access denied for user 'app'@'localhost': the password doesn't match the .env file. Set a new one in both places, then restart the app:

  pass=$(openssl rand -hex 24)
  for host in $(mysql -NBe "SELECT host FROM mysql.user WHERE user = 'app'"); do
    mysql -e "ALTER USER 'app'@'$host' IDENTIFIED BY '$pass'"
  done
  sed -i -e "s|//app:[^@]*@|//app:$pass@|" -e "s|^DB_PASSWORD=.*|DB_PASSWORD=$pass|" ~/mysql/app.env
  

  The loop also covers the extra logins for Docker or Tailscale ('app'@'172.16.0.0/12' and so on), so they keep the same password.

• Host '...' is not allowed to connect: the client isn't on the VM, and there's no user for where it connects from. Add one, as in From a Docker container or Connect from another VM.

• Public Key Retrieval is not allowed (Java), cryptography package is required (PyMySQL), or ER_NOT_SUPPORTED_AUTH_MODE (Node's mysql): the driver needs help with MySQL's caching_sha2_password sign-in. In JDBC, add sslMode=REQUIRED (MySQL makes its own TLS certificate) or allowPublicKeyRetrieval=true to the URL; install cryptography next to PyMySQL; in Node, switch to mysql2.

• self-signed certificate in certificate chain: MySQL makes its own TLS certificate, and some clients, such as MariaDB's mariadb command, check it by default. On the VM and over Tailscale the network is already private; tell the client not to verify the certificate (--skip-ssl-verify-server-cert for mariadb), or copy /var/lib/mysql/ca.pem to the client and point it there.

• Plugin caching_sha2_password could not be loaded: that's MariaDB's mysql/mariadb client, which some images ship, and it can't sign in to MySQL 9. Install MySQL's own client instead, or use your app's driver.

• Too many connections: the app's connection pool is bigger than MySQL's limit of 151. Make the pool smaller, or raise max_connections in ~/mysql/tune.sh and run it.

• The tunnel says Address already in use: something on your computer has port 3307. Pick another, such as -L 3308:localhost:3306.

• SSH refuses the tunnel: try the form that always works, ssh -N -L 3307:localhost:3306 vm+my-mysql@vm.exe.xyz.

• Adminer says your email isn't allowed: add it to ~/mysql/adminer/allowed-emails. Adminer not loading at all? Check systemctl status adminer and journalctl -u adminer.

• Disk full: MySQL stops writing. Check df -h / and sudo du -sh ~/mysql/backups /var/lib/mysql. Binary logs (binlog.0* in /var/lib/mysql) are often the bulk; mysql -e 'PURGE BINARY LOGS BEFORE NOW() - INTERVAL 1 DAY' frees them. Then lower keep in ~/mysql/backup.sh, after copying old backups off the VM.

• Slow queries: queries over a second land in /var/log/mysql/slow.log. To see which ones take the most time overall, run this in mysql:

  SELECT ROUND(sum_timer_wait / 1e9) AS total_ms, count_star AS calls,
         LEFT(digest_text, 70) AS query
  FROM performance_schema.events_statements_summary_by_digest
  ORDER BY sum_timer_wait DESC LIMIT 10;