VPS Tutorials
MySQL 8.4 on a VPS: Docker, private access and tested backups
Deploy a fresh MySQL 8.4 instance with persistent Compose storage, SSH and TLS. Separate application roles, automate verified dumps and restore safely into an independent container.

Running MySQL in Docker is only the beginning. Before connecting a real application, establish who can reach port 3306, where the data survives container replacement, and whether an offsite backup can actually be restored. This guide takes a new MySQL 8.4 instance through those steps with Compose and an SSH tunnel.
The examples use the Docker Official Image mysql:8.4, rather than mysql/mysql-server. They target a fresh instance, not an existing MySQL or MariaDB data directory. Check migration compatibility separately. Resource figures are planning estimates, not measured throughput.
| Workload | Starting budget | Check before buying |
|---|---|---|
| Learning or occasional internal tool | 2 vCPU, 2 GB RAM, 30 GB SSD | Free memory, backup space and disk latency |
| Small website sharing the host | 2-4 vCPU, 4 GB RAM, 60 GB SSD | Application peaks, CPU contention and growth |
| Dedicated database with sustained writes | 4 vCPU, 8 GB RAM or more | IOPS, storage expansion, private networking and restore time |
These figures do not promise a connection count: a few slow queries can cost more than hundreds of short ones. Include offsite storage, egress and administration in the budget. Keep the application and database in a suitable nearby region and measure storage and memory before paying for a particular route.
mysql:8.4 follows the 8.4 release series. Neither latest nor lts establishes application compatibility. For production, review the available patched release, record its digest and test future updates in a restored instance. Consult the official image instructions for current tags and initialization variables.
Use supported Docker Engine and the Compose plugin; commands assume Ubuntu 24.04 and Bash. See Docker installation. The following host operations use an authorized root shell and /opt/mysql84.
Check that an existing service does not own 3306, then prepare the directory:
docker version
docker compose version
ss -lntp 'sport = :3306'
install -d -m 700 /opt/mysql84
cd /opt/mysql84
install -d -m 700 secrets backups
Generate separate administrator and application passwords only once in this new directory:
umask 077
openssl rand -hex 32 > secrets/mysql_root_password
openssl rand -hex 32 > secrets/mysql_app_password
printf '[client]\nuser=root\npassword=%s\nprotocol=socket\n' \
"$(cat secrets/mysql_root_password)" > secrets/mysql_admin.cnf
chmod 600 secrets/*
Store the following in /opt/mysql84/compose.yaml:
name: mysql84
services:
mysql:
image: mysql:8.4
restart: unless-stopped
stop_grace_period: 1m
environment:
MYSQL_ROOT_PASSWORD_FILE: /run/secrets/mysql_root_password
MYSQL_ROOT_HOST: localhost
MYSQL_DATABASE: appdb
MYSQL_USER: appuser
MYSQL_PASSWORD_FILE: /run/secrets/mysql_app_password
secrets:
- mysql_root_password
- mysql_app_password
- mysql_admin
ports:
- "127.0.0.1:3306:3306"
volumes:
- mysql_data:/var/lib/mysql
command:
- --character-set-server=utf8mb4
- --collation-server=utf8mb4_0900_ai_ci
- --require-secure-transport=ON
- --innodb-buffer-pool-size=256M
- --max-connections=50
- --binlog-expire-logs-seconds=604800
mem_limit: 1g
healthcheck:
test:
- CMD
- mysql
- --defaults-extra-file=/run/secrets/mysql_admin
- --batch
- --skip-column-names
- -e
- SELECT 1
interval: 15s
timeout: 5s
retries: 10
start_period: 90s
logging:
driver: json-file
options:
max-size: "10m"
max-file: "3"
volumes:
mysql_data:
secrets:
mysql_root_password:
file: ./secrets/mysql_root_password
mysql_app_password:
file: ./secrets/mysql_app_password
mysql_admin:
file: ./secrets/mysql_admin.cnf
mysql_data persists independently of the container. Do not add -v to docker compose down when troubleshooting: that requests volume deletion. File-backed Compose secrets are mounts, not an encrypted vault; root and Docker administrators can read them.
The administrator is limited to localhost; applications use their own account. Initialization only creates users and databases in an empty data directory. Changing a secret file does not rotate an existing database password. See the entrypoint implementation.
127.0.0.1:3306:3306 restricts host publication. Leave 3306 closed in cloud rules too. Use a supported Engine at least as new as 28.0.0: older releases have a localhost-port exception for hosts on the same layer-2 network, documented under port publishing. Trusted peers on the same Docker network can still connect; keep untrusted containers off that network.
cd /opt/mysql84
docker compose config --quiet
docker compose pull
docker compose up -d
docker compose ps
docker compose logs --tail=100 mysql
docker compose exec -T mysql \
mysql --defaults-extra-file=/run/secrets/mysql_admin \
-e 'SELECT VERSION(); SHOW DATABASES;'
docker image inspect mysql:8.4 --format '{{json .RepoDigests}}'
Allow initialization to finish and check healthy plus a real authenticated query. The resulting mysql@sha256:… identifies the value to pin in image; use the full actual digest, not the displayed ellipsis. This healthcheck also catches administrator-password mismatches.
On your computer, substitute your SSH user and VPS address:
ssh -N -T -o ExitOnForwardFailure=yes \
-o ServerAliveInterval=30 -o ServerAliveCountMax=3 \
-L 127.0.0.1:13306:127.0.0.1:3306 deploy@VPS_IP
Verify the SSH host fingerprint and keep the session open. In the database client, use host 127.0.0.1, port 13306, database appdb, user appuser and the password securely retrieved from secrets/mysql_app_password. With a GUI's integrated SSH tunnel, its database-side destination is 127.0.0.1:3306 on the VPS; do not stack it with an unnecessary second tunnel.
The server enables require_secure_transport, so TCP clients must request TLS. Test with a compatible MySQL 8.4 client:
mysql --protocol=TCP -h 127.0.0.1 -P 13306 \
-u appuser -p --ssl-mode=REQUIRED appdb
SHOW SESSION STATUS LIKE 'Ssl_cipher';
SELECT CURRENT_USER(), DATABASE();
Ssl_cipher should contain a cipher. REQUIRED encrypts the database connection without validating its hostname; in this layout, the verified SSH connection establishes the remote host identity. For direct cross-host application connections, use controlled private routing, a trusted CA, the real database hostname in the certificate and VERIFY_IDENTITY. Automatically generated self-signed certificates do not automatically meet hostname verification. See MySQL TLS.
An application on the same controlled Compose network usually uses mysql:3306. Its own 127.0.0.1 refers to that application container. Join the intended network and configure driver TLS explicitly. An ordinary HTTP reverse proxy cannot carry the MySQL protocol.
The initialized appuser has broad rights on appdb for creating tables. After initial migrations, separate that role from ordinary read/write access:
docker compose exec mysql \
mysql --defaults-extra-file=/run/secrets/mysql_admin
Replace the placeholder below with a different random password:
CREATE USER 'app_runtime'@'%'
IDENTIFIED BY 'REPLACE_WITH_A_NEW_RANDOM_PASSWORD' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app_runtime'@'%';
SHOW GRANTS FOR 'app_runtime'@'%';
Switch runtime configuration to app_runtime. Retain DDL privileges only for a deliberate migration account, accounting for applications that run migrations on startup. The account's % matches any source; it does not open a public port. Network boundaries and authentication still determine access.
MySQL 8.4 disables mysql_native_password by default. Upgrade incompatible drivers instead of copying --default-authentication-plugin=mysql_native_password from older tutorials. Read the authentication-plugin documentation.
The following exports appdb, including tables, triggers, routines and events. It does not save other databases or recreate account grants. It uses the container's administrator option file; a long-term backup role should receive only the privileges its actual export requires.
Save this as /opt/mysql84/backup.sh:
#!/usr/bin/env bash
set -euo pipefail
umask 077
cd /opt/mysql84
backup_stamp=$(date -u +%Y%m%dT%H%M%SZ)
backup_file="backups/appdb-${backup_stamp}.sql.gz"
test ! -e "$backup_file"
test ! -e "${backup_file}.partial"
docker compose exec -T mysql \
mysqldump --defaults-extra-file=/run/secrets/mysql_admin \
--single-transaction --quick --routines --events --triggers \
--hex-blob --no-tablespaces --set-gtid-purged=OFF \
--databases appdb | gzip > "${backup_file}.partial"
gzip -t "${backup_file}.partial"
mv "${backup_file}.partial" "$backup_file"
sha256sum "$backup_file" > "${backup_file}.sha256"
printf 'Backup ready: %s\n' "$backup_file"
chmod 700 /opt/mysql84/backup.sh
/opt/mysql84/backup.sh
pipefail stops a failed dump from being hidden by a successful gzip process. Incomplete output retains .partial. --single-transaction primarily provides consistency for InnoDB; avoid DDL during the dump. Nontransactional tables such as MyISAM need a separate locking or write-stop plan. See mysqldump.
After the manual run passes, schedule root's crontab -e, for example at 03:20 in the server's time zone:
20 3 * * * /bin/bash /opt/mysql84/backup.sh >> /opt/mysql84/backups/backup.log 2>&1
Monitor failures and the last successful backup time, rotate logs and prevent overlap if a dump can outlast its schedule. Send completed dumps, checksums, Compose configuration, image identity and necessary account-recreation SQL to encrypted offsite storage; keep decryption keys separately. A copy on the same VPS cannot survive loss of that machine. See automated backups.
Choose retention against your recovery point and capacity. Daily dumps can lose nearly a day's changes after a total failure. Point-in-time recovery additionally needs archived binlogs and their backup coordinates; this is not a complete PITR configuration.
Reserve extra RAM and disk. The test uses a separate volume, no network and no production port. Pick unused names; never delete a same-named resource before checking what it belongs to.
In /opt/mysql84, select an actual backup:
cd /opt/mysql84
backup_file='backups/appdb-YYYYMMDDTHHMMSSZ.sql.gz'
sha256sum -c "${backup_file}.sha256"
gzip -t "$backup_file"
restore_volume=$(docker volume create)
restore_image=$(docker compose images -q mysql)
test -n "$restore_image"
docker run -d --name mysql84-restore --network none --memory 1g \
--mount "type=volume,src=${restore_volume},dst=/var/lib/mysql" \
--mount "type=bind,src=$PWD/secrets/mysql_root_password,dst=/run/secrets/mysql_root_password,readonly" \
--mount "type=bind,src=$PWD/secrets/mysql_admin.cnf,dst=/run/secrets/mysql_admin,readonly" \
-e MYSQL_ROOT_PASSWORD_FILE=/run/secrets/mysql_root_password \
-e MYSQL_ROOT_HOST=localhost \
"$restore_image" --event-scheduler=OFF --innodb-buffer-pool-size=256M
docker logs --tail=50 mysql84-restore
This uses the production container's local image ID so that the moving 8.4 tag cannot silently select another patch. Wait for initialization and a successful query before importing:
docker exec mysql84-restore \
mysql --defaults-extra-file=/run/secrets/mysql_admin -e 'SELECT 1;'
set -o pipefail
gzip -dc "$backup_file" | docker exec -i mysql84-restore \
mysql --defaults-extra-file=/run/secrets/mysql_admin
docker exec mysql84-restore \
mysql --defaults-extra-file=/run/secrets/mysql_admin \
-e 'SHOW TABLES FROM appdb;'
Only restore trusted SQL: imports execute its statements. Keep the event scheduler disabled. Check exact business row counts, recent records and restoration time; SHOW TABLES alone does not demonstrate a usable application.
For integration testing, recreate required accounts/grants, check DEFINER references in views/routines/events, attach an isolated test network and disable outgoing mail, payments and other real actions. When finished, docker stop mysql84-restore releases runtime memory while leaving the test volume for inspection. See restore drills.
The 256-MB buffer pool and 1-GB container limit are small-instance starting values. Connections, sorts, temporary tables and other overhead consume additional memory; do not set the buffer pool equal to the container limit. Measure against InnoDB buffer pool guidance:
docker stats --no-stream
df -h
docker compose exec -T mysql \
mysql --defaults-extra-file=/run/secrets/mysql_admin \
-e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected','Max_used_connections','Slow_queries'); SHOW BINARY LOGS;"
Investigate connection pools and leaks before doubling max_connections. Binary logging is enabled by default; seven-day expiration limits age, not bytes, and heavy writes can fill disk sooner. Follow binlog retention and never manually remove database-directory log files.
Before updating, validate a backup, record the old digest, test the new patch with the restored application and schedule maintenance with writes stopped as required. A pinned deployment needs an explicit image change; pull alone cannot replace that digest.
For older-server migration, check supported upgrade paths and the applicable compatibility checks. A rollback rebuilds from the old image and pre-upgrade backup; do not open an upgraded data volume with an older server.
Initialization variables affect empty data only. Rotate the existing account with ALTER USER, then coordinate secrets and clients. Keep the data volume. See connection troubleshooting.
Check the live tunnel, local 13306, the appuser account instead of root, and TLS. SSH access does not mean database initialization has completed.
Compare server, account plugin and driver versions. New deployments should use a driver supporting caching_sha2_password; test old-account migrations before changing authentication.
It might start, but the sample container budget still requires memory for the host. Do not apply it unchanged to a machine with 1 GB total. For OOM or exit 137, inspect host memory and container limits; see low-memory troubleshooting.
A live file copy may be inconsistent. Start with tested logical backups for a small database; evaluate compatible physical tools and restore procedures for larger data. A local snapshot does not replace an offsite copy.
Same-host containers use a controlled Docker network, remote administration uses SSH, and cross-host applications should use private routing or a managed tunnel with TLS and restricted roles. Finish an independent restore before connecting production workloads.