--- title: "Self-host MySQL" description: "Run MySQL 9.7 LTS on an exe.dev VM: nightly tested backups, a private web admin, SSH tunnels, and connections from your apps and other VMs." url: https://exe.dev/deploy/mysql category: "Databases" updated: 2026-10-30 --- # Self-host MySQL Your own MySQL on a real disk, private by default, with backups you've restored. - **Software:** MySQL (https://www.mysql.com) - **Source:** https://github.com/mysql/mysql-server - **License:** GPL-2.0 - **Time:** 10 minutes - **Example VM name:** `my-mysql` (yours will differ; on the VM, `hostname` prints it) - **Machine-readable:** https://exe.dev/deploy/mysql/manifest.json ## What you get - MySQL 9.7 LTS, started at boot, listening on localhost only and sized for the VM's memory. - An `app` database and user, with its connection settings in `~/mysql/app.env`. - Adminer at the VM's private URL, open only to the owner's exe.dev email. - Nightly backups of every database and user, one restored and checked. Running on the VM: - **MySQL 9.7 LTS**: From Oracle's MySQL APT repository, sized for the VM, port 3306, data in `/var/lib/mysql` - **Adminer 6**: Web admin, behind exe.dev login and an email allowlist, port 8000 - **Nightly backups**: Every database and user, 14 nights kept, data in `~/mysql/backups` How it is reached: - https://my-mysql.exe.xyz/ (private): Adminer, signed in as the MySQL admin user ## Deploy with Shelley Open https://exe.dev/new?prompt=Set%20up%20MySQL%20on%20this%20VM%20by%20following%20the%20exe.dev%20recipe%20at%20https%3A%2F%2Fexe.dev%2Fdeploy%2Fmysql.md%20from%20top%20to%20bottom.%0A%0AThe%20recipe%27s%20example%20VM%20name%20is%20my-mysql.%20This%20VM%27s%20name%20is%20the%20output%20of%20%60hostname%60%3A%20use%20it%20wherever%20the%20recipe%20writes%20my-mysql.%20Skip%20the%20step%20that%20creates%20the%20VM%3B%20you%20are%20on%20it.%0A%0AYou%20are%20done%20when%20all%20of%20these%20are%20true.%20Check%20each%20one%20yourself%3A%0A-%20MySQL%209.7%20LTS%2C%20started%20at%20boot%2C%20listening%20on%20localhost%20only%20and%20sized%20for%20the%20VM%27s%20memory.%0A-%20An%20%60app%60%20database%20and%20user%2C%20with%20its%20connection%20settings%20in%20%60~%2Fmysql%2Fapp.env%60.%0A-%20Adminer%20at%20the%20VM%27s%20private%20URL%2C%20open%20only%20to%20the%20owner%27s%20exe.dev%20email.%0A-%20Nightly%20backups%20of%20every%20database%20and%20user%2C%20one%20restored%20and%20checked.%0A%0ARead%20the%20whole%20recipe%2C%20including%20the%20closed%20details.%20For%20initial%20setup%2C%0Afollow%20%22Install%20by%20hand%22%20from%20top%20to%20bottom%3A%20the%20MySQL%20repository%20and%0Aserver%2C%20the%20admin%20user%2C%20tuning%2C%20the%20app%20database%2C%20Adminer%20on%20port%208000%2C%0Athe%20backup%20timer%2C%20and%20the%20scratch-server%20restore%20test.%20The%20walkthrough%0Asections%20are%20for%20the%20owner%20afterwards%3A%20don%27t%20import%20data%2C%20set%20up%0ATailscale%20or%20Docker%20access%2C%20or%20run%20the%20Restore%20or%20Update%20instructions%0Aduring%20initial%20setup.%20Never%20overwrite%20an%20existing%20~%2Fmysql%2F%2A.env%20file.%0AAllow%20only%20the%20owner%27s%20email%20in%20Adminer%2C%20read%20from%20the%20reflection%0Aintegration%3B%20if%20that%20fails%2C%20ask%20the%20owner%20for%20it.%20Check%20Adminer%20on%0Alocalhost%20with%20no%20email%2C%20a%20wrong%20email%2C%20and%20the%20owner%27s%20email.%0ALeave%20the%20VM%20private%20and%20do%20not%20reboot.%20Don%27t%20paste%20passwords%20into%20the%0Achat%3B%20say%20they%20are%20in%20~%2Fmysql%2Fapp.env.%0A%0AFinish%20with%20the%20URLs%20I%20should%20open%20and%20these%20commands%2C%20which%20only%20I%20can%20run%2C%20from%20my%20own%20computer%20%28with%20this%20VM%27s%20name%20in%20them%29%3A%0A%20%20ssh%20my-mysql.exe.xyz%20cat%20mysql%2Fapp.env%0A%20%20ssh%20-N%20-L%203307%3Alocalhost%3A3306%20my-mysql.exe.xyz%0A to create a VM whose agent, Shelley, follows this recipe. From a terminal: ```sh curl -fsSL https://exe.dev/deploy/mysql/prompt.txt | ssh exe.dev new --prompt=/dev/stdin ``` Then, on your own computer: ```sh ssh my-mysql.exe.xyz cat mysql/app.env ssh -N -L 3307:localhost:3306 my-mysql.exe.xyz ``` Or follow the steps below by hand. MySQL runs a large share of the web: WordPress, Laravel and Rails apps, and plenty of software that expects nothing else. Here it gets a VM of its own (or moves in next to your app), with memory settings to match, nightly backups you've seen restored, and a web admin only you can open. Nothing listens on the internet: your apps reach MySQL on the VM, over SSH, or over your private Tailscale network. ## Open Adminer When Shelley finishes, open the URL it gives you. It'll look like `https://my-mysql.exe.xyz/`. Sign in to exe.dev and you land in [Adminer](https://www.adminer.org/), already connected as `exedev`, the MySQL admin user. Browse tables, run SQL, export a table as CSV. Only the exe.dev emails listed in `~/mysql/adminer/allowed-emails` get in, even if you share the VM. Add a teammate's email, one per line, and they're in; no restart needed. Anyone on that list can change or drop anything, so keep it short. Adminer trusts exe.dev's login header, so keep untrusted software off this VM. ## Connect an app on this VM Your app's database is called `app`, with a MySQL user of the same name that can do anything in it and nothing outside it. Its settings are in `~/mysql/app.env`, readable only by you: ```text DATABASE_URL=mysql://app:@127.0.0.1:3306/app DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=app DB_USERNAME=app DB_PASSWORD= ``` Prisma, Django with `dj-database-url`, and most Node and Go libraries take the `DATABASE_URL` as is. Laravel reads the `DB_` lines (add `DB_CONNECTION=mysql`). Rails wants the URL to start with `trilogy://` or `mysql2://`, after its adapter, and SQLAlchemy with `mysql+pymysql://`. Anything else takes the separate settings. **`127.0.0.1`, not `localhost`.** Most MySQL clients read the host `localhost` as "use the Unix socket", where the app's password login works too, but some clients look for the socket in the wrong place. The IP address always means TCP, and it works everywhere. Want one database per app? On the VM: ```bash ~/mysql/create-db.sh blog cat ~/mysql/blog.env ``` Each database gets its own user and password, and the nightly backup picks it up automatically. For admin work on the VM, just type `mysql`: your Linux user `exedev` is also a MySQL user with every privilege, signed in through the socket with no password (MySQL's `auth_socket`). That's how the backups and Adminer connect. Apps should use their own, less powerful user. ### Run it next to your app Already have an app on a VM, say from a [framework page](/deploy)? Run [Install by hand](#create-the-vm) on that VM, skipping only "Create a VM". Put Adminer on port 8001 instead, as that step explains, because port 8000 is your app's. Then create the app's database and add its settings to the app's environment file (remove any older `DATABASE_URL` or `DB_` lines from it first): ```bash ~/mysql/create-db.sh myapp cat ~/mysql/myapp.env >> ~/app.env ``` If the app runs under systemd, add `After=mysql.service` to its `[Unit]` section, so that it starts after the database at boot. ### From a Docker container Inside a container, `127.0.0.1` is the container itself. Let MySQL listen beyond localhost, and let the app's user in from Docker's networks, on the VM: ```bash printf '[mysqld]\nbind_address = *\n' | sudo tee /etc/mysql/mysql.conf.d/20-listen.cnf sudo systemctl restart mysql . ~/mysql/app.env mysql -e "CREATE USER '$DB_USERNAME'@'172.16.0.0/12' IDENTIFIED BY '$DB_PASSWORD'; GRANT ALL ON \`$DB_DATABASE\`.* TO '$DB_USERNAME'@'172.16.0.0/12'" ``` Then start containers with `--add-host=host.docker.internal:host-gateway` (in Compose: `extra_hosts: ["host.docker.internal:host-gateway"]`) and use `host.docker.internal` in place of `127.0.0.1`. MySQL users belong to a host as well as a name, so `app` from `172.16.0.0/12` is a second login with the same password; other users still get in only from the VM itself, and nothing outside the VM can reach port 3306 anyway. ## Connect from your computer Port 3306 isn't on the internet, so you bring it to your computer through SSH. exe.dev's SSH forwards ports like any SSH server. On your computer, print the connection settings, then open a tunnel and leave it running: ```bash ssh my-mysql.exe.xyz cat mysql/app.env ssh -N -L 3307:localhost:3306 my-mysql.exe.xyz ``` In another terminal, connect to port **3307**, so a MySQL on your computer doesn't get in the way. Use `127.0.0.1`, since `localhost` would mean your computer's own socket: ```bash mysql -h 127.0.0.1 -P 3307 -u app -p app ``` Desktop clients such as MySQL Workbench, TablePlus, DBeaver, and DataGrip can open the tunnel themselves. Choose their SSH option, with SSH host `my-mysql.exe.xyz`, user `exedev` and your exe.dev SSH key, then MySQL host `127.0.0.1`, port `3306`, and the app's user and password. For admin work, skip the tunnel: `ssh -t my-mysql.exe.xyz mysql`. ## Connect from another VM exe.dev VMs don't share a private network, and exe.dev's [VM-to-VM integration](/docs/integrations-vm-to-vm) carries HTTPS only. The dependable way to reach MySQL from another VM is to put both VMs on your [Tailscale](https://tailscale.com/) network. On **both** VMs, start Tailscale (it's preinstalled) and open the login URL it prints. To join without a login link, use exe.dev's [Tailscale integration](/docs/integrations-tailscale-wif) instead. ```bash sudo systemctl enable --now tailscaled sudo tailscale up --hostname=$(hostname) ``` Then, on the **database** VM, let MySQL listen beyond localhost, and let the app's user in from Tailscale's IPv4 and IPv6 addresses: ```bash printf '[mysqld]\nbind_address = *\n' | sudo tee /etc/mysql/mysql.conf.d/20-listen.cnf sudo systemctl restart mysql . ~/mysql/app.env for net in 100.64.0.0/10 fd7a:115c:a1e0:%; do mysql -e "CREATE USER '$DB_USERNAME'@'$net' IDENTIFIED BY '$DB_PASSWORD'; GRANT ALL ON \`$DB_DATABASE\`.* TO '$DB_USERNAME'@'$net'" done ``` On the other VM, swap `127.0.0.1` for the database VM's name: `mysql://app:@my-mysql:3306/app`. Check it with `nc -zv my-mysql 3306`. Use [Tailscale's access rules](https://tailscale.com/kb/1018/acls) if not every device on your network should reach port 3306. You can also tunnel over SSH from VM to VM, as from your computer. The other VM then needs an SSH key added to your exe.dev account (`ssh exe.dev ssh-key add`), and that key opens **all** your VMs. What doesn't work: - A public `my-mysql.exe.xyz:3306`. There's no raw TCP port to open. - exe.dev share links and the VM-to-VM proxy. They speak HTTPS, and MySQL's protocol isn't HTTP. - Hosted tools that only take a host and port. Some can use an SSH tunnel or Tailscale instead; check their docs. ## Import a database Moving from another MySQL host? Dump the old database with `mysqldump` and load it into a fresh database here. Run the dump **on the VM**: its `mysqldump` reads MySQL 5.7 and later (and MariaDB), and the data goes straight from server to server. You need the old database's external host, port, user, password, and database name, from your current host's dashboard. ```bash mysqldump --host=old.example.com --port=3306 --user=olduser --password \ --single-transaction --routines --triggers --events --hex-blob \ --set-gtid-purged=OFF --no-tablespaces --column-statistics=0 --skip-masking-policies \ olddb | gzip > ~/import.sql.gz zcat ~/import.sql.gz | tail -n 1 ``` It asks for the password, and should print nothing else before the last line, `-- Dump completed`. The flags skip things a hosted database won't let a normal user dump. If `mysqldump` complains anyway, read the error: the dump may be missing a part. Now create a database for it and load the dump as `exedev`. The `sed` removes the old host's users from views, triggers, and routines (`DEFINER=...`), which don't exist here: ```bash db=myapp ~/mysql/create-db.sh $db zcat ~/import.sql.gz | sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' | mysql $db mysql -e "SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = '$db'" ``` The last command lists your tables with approximate row counts; compare exact counts of a few tables with the old database. If the load stops with an error, fix what it names, then empty the database (`mysql -e "DROP DATABASE myapp; CREATE DATABASE myapp"`) and run the `zcat` line again. Finally, point your app at `~/mysql/myapp.env` and delete `~/import.sql.gz`. Switching over for good? Stop writes on the old database first, take a final dump, load it, then move the app. ## Keep your data Every night, a backup of each database, plus the users, their grants, and the connection files, lands in `~/mysql/backups`; the newest 14 stay. A backup on the same disk won't survive losing the VM, so keep a copy **off the VM**. On your computer: ```bash scp -r my-mysql.exe.xyz:mysql/backups mysql-backups ``` These files hold all your data and the apps' passwords; keep them private. To undo a mistake or move to a new VM, see [Back up and restore](#back-up-and-restore). Before any upgrade, run `~/mysql/backup.sh` on the VM. Something odd? On the VM, `sudo tail -n 50 /var/log/mysql/error.log` usually explains it, or ask Shelley to look. ## The nuts and bolts Happy clicking Deploy? You can stop here. These are the same setup steps Shelley follows, plus the maintenance commands for later.
Install by hand ### Create a VM On your computer (pick another name if `my-mysql` is taken): ```bash 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: ```bash 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`: ```bash 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: ```bash 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. ```bash 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 </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: ```bash 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/.env`. It never touches an existing one: ```bash 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" < 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: ```bash cat > ~/mysql/adminer/index.php <<'EOF' '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://.exe.xyz:8001/`. ```bash port=8000 sudo tee /etc/systemd/system/adminer.service >/dev/null <[^<]*' ``` Expect `302`, `403`, and `Select database - Adminer`. ### 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: ```bash cat > ~/mysql/backup.sh <<'EOF' #!/usr/bin/env bash # Back up every database, the users and their grants, and ~/mysql/*.env # into ~/mysql/backups//. 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: ```bash sudo tee /etc/systemd/system/mysql-backup.service >/dev/null </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: ```bash 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: ```bash 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](#open-adminer).
Back up and restore 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: ```bash 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: ```bash 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](#create-the-vm) 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: ```bash 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: ```bash 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: ```bash 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](/docs/what-is-exe) to other machines, which guards against hardware failure, not against a bad `DELETE`. For bigger databases, MySQL Shell's [dump utilities](https://dev.mysql.com/doc/mysql-shell/en/mysql-shell-utilities-dump-instance-schema.html) dump and load in parallel, and Shelley can set them up for you.
Update MySQL 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: ```bash ~/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](#install-mysql-97), with the newest `RPM-GPG-KEY-mysql-` file listed at https://repo.mysql.com/. To update Adminer, download the new release's `adminer--mysql-en.php` from [its releases](https://github.com/vrana/adminer/releases) over `~/mysql/adminer/adminer.php`, then repeat the three-case check from [Add a web admin](#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](https://repo.mysql.com/apt/ubuntu/dists/noble/Release), then, with `new` set to it: ```bash 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.
Something not working? On the VM, MySQL's error log usually has the answer: ```bash 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: ```bash 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](#from-a-docker-container) or [Connect from another VM](#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`: ```sql 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; ```
## Frequently asked questions ### Is this a managed database? No, it's yours. You get root on the VM, the real MySQL configuration files, and every MySQL feature in the Community Edition. Updates are yours to apply, too; [Update MySQL](#update) shows how, and Shelley on the VM can do them for you. ### Can I connect to port 3306 from the internet? Not directly. exe.dev only lets HTTPS and SSH in, so you connect through an [SSH tunnel](#connect-from-your-computer), or from other machines on your [Tailscale network](#connect-from-another-vm). The database never faces the open internet. ### Why MySQL 9.7 and not 8.4? 9.7 is MySQL's newest long-term support release, supported until 2034. 8.4, the previous one, is supported until 2032; if an app isn't ready for 9.7, read `mysql-9.7-lts` as `mysql-8.4-lts` in the install step. MySQL 8.0 reached end of life in April 2026, so don't start new work on it. ### My app's driver fails with `caching_sha2_password` errors. What now? MySQL 9 signs users in with `caching_sha2_password` only; the older `mysql_native_password` is gone. Current drivers handle it. Older ones don't, notably Node's `mysql` package (use `mysql2`) and PyMySQL without the `cryptography` package. See [Something not working?](#troubleshooting) for the fixes. ### Do I get point-in-time recovery? Not out of the box. You get nightly dumps, so you could lose up to a day of changes; you can [back up hourly](#more-often) instead. MySQL also keeps a binary log of every change for 7 days in `/var/lib/mysql`, which an expert (or Shelley) can replay on top of a dump with `mysqlbinlog`. ### Can I use MariaDB instead? This page installs Oracle's MySQL. MariaDB is a separate fork with its own packages in Ubuntu (`mariadb-server`); most of this page applies to it, but the install, update, and authentication steps differ. ### Is MySQL free? Yes. MySQL Community Server is open source under the GPLv2, and Adminer is free too. You pay for the VM; see [exe.dev plans](https://exe.dev/pricing). ## Related - [Self-host PostgreSQL](https://exe.dev/deploy/postgres.md): Your own Postgres on a real disk, with backups you've actually restored. - [Deploy a Laravel app](https://exe.dev/deploy/laravel.md): Your Laravel app on a server of its own. git push to deploy. - [Deploy a Django app](https://exe.dev/deploy/django.md): Your Django app on a computer of its own. git push to deploy. - [Deploy a Rails app](https://exe.dev/deploy/rails.md): Your Rails app on a server of its own. git push, and it's live. - [Self-host n8n](https://exe.dev/deploy/n8n-2.md): Connect your apps. Let the boring bits run themselves. - [Run Claude Code on a remote server](https://exe.dev/deploy/claude-code.md): A computer for Claude. A breather for your laptop.