Back up and restore MySQL
This procedure backs up saved workspaces and accounts, then restores them into a new database directory.
This procedure uses the supplied Docker Compose deployment and MySQL 8.4. Run the commands in Bash from the deployment directory.
Create a database backup
1. Stop application writes
Schedule this operation during a maintenance window. Stop the application services while MySQL stays available:
docker compose stop nginx preview ssr api
Keep other database clients from changing the database during the backup.
2. Export the application database
Run this block:
(
set -eu
umask 077
mkdir -p backups
backup="backups/regex101-$(date -u +%Y%m%dT%H%M%SZ).sql"
docker compose exec -T mysql sh -c '
exec mysqldump --user=root --password="$MYSQL_ROOT_PASSWORD" \
--single-transaction --quick \
--routines --events --triggers \
--no-tablespaces --set-gtid-purged=OFF \
--databases "$MYSQL_DATABASE"
' > "$backup.partial"
test -s "$backup.partial"
mv "$backup.partial" "$backup"
sha256sum "$backup" > "$backup.sha256"
printf 'Backup: %s\n' "$backup"
)
A failed dump leaves a .partial file. Only a successful, nonempty dump receives the final .sql name.
3. Record versions and resume service
Record the image references and MySQL version with the backup. Use specific release tags rather than moving tags such as 13:
docker compose images
docker compose exec -T mysql mysql --version
Restart the existing application containers:
docker compose start api ssr preview nginx
docker compose ps
Move the completed dump, checksum, and configuration backup to your backup storage. The SQL file contains workspace and account data, including private workspaces.
Restore the database
The following steps replace the active database with the backup. Changes after the backup time will not appear in the restored instance. Keep the previous MySQL data directory until recovery is complete.
1. Stop the application and select the backup
Stop the services that use the database:
docker compose stop nginx preview ssr api
Set restore_file to the completed dump and verify its checksum:
restore_file=backups/regex101-20260909T120000Z.sql
sha256sum -c "$restore_file.sha256"
Set the image tags to the releases recorded with the backup. Verify that those images are available locally.
Do not use a moving application tag such as 13: it can select a different release.
2. Select an empty MySQL data directory
Keep a copy of the current configuration, then stop MySQL:
cp -p .env .env.before-restore
docker compose stop mysql
In .env, change MYSQL_DATA_DIR to a new, empty directory:
MYSQL_DATA_DIR=./mysql/restored-data
Keep MYSQL_DATABASE equal to the database name in the dump. Use the recorded MYSQL_IMAGE_TAG.
The new directory must be empty.
Start only the data services:
docker compose up -d --pull never --wait mysql redis-sessions redis-cache
If initialization fails, inspect docker compose logs --tail=100 mysql before continuing.
3. Import the dump
Verify that the target application database has no tables:
docker compose exec -T mysql sh -c '
exec mysql --user=root --password="$MYSQL_ROOT_PASSWORD" \
--database="$MYSQL_DATABASE" --execute="SHOW TABLES"
'
The result must contain no table names. If tables appear, stop here and select the intended empty data directory.
Import the dump:
docker compose exec -T mysql sh -c '
exec mysql --user=root --password="$MYSQL_ROOT_PASSWORD"
' < "$restore_file"
If the import reports an error, keep the application stopped. Resolve the cause and repeat the restoration into another empty directory.
4. Discard stale sessions and cache entries
These commands clear the two dedicated Redis databases in the supplied deployment. All users must sign in again.
docker compose exec -T redis-sessions redis-cli FLUSHDB
docker compose exec -T redis-cache redis-cli FLUSHDB
5. Verify the restored records
Inspect the restored tables and record counts:
docker compose exec -T mysql sh -c '
exec mysql --user=root --password="$MYSQL_ROOT_PASSWORD" \
--database="$MYSQL_DATABASE" --execute="
SHOW TABLES;
SELECT COUNT(*) AS workspaces FROM permalink;
SELECT COUNT(*) AS versions FROM permalink_version;
SELECT COUNT(*) AS accounts FROM user;
"
'
Start with the recorded release images already on the host:
docker compose up -d --pull never
docker compose ps
docker compose logs --tail=100 api
If LICENSE_FILE is set, include its Compose override in the startup command.
- Verify that services run and available health checks pass.
- If accounts are enabled, sign in with an existing account.
- Open a known saved workspace and inspect its versions, inputs, and visibility.
- Save a separate example and reopen it to verify database writes.
- Restore normal user access.
Rehearse a database restoration
Verify your backup with a separate deployment directory and an empty database directory before you need recovery. Keep that deployment separate from production.