Skip to main content

Back up and restore MySQL

The mysql service stores accounts, saved workspaces, versions, tags, and other application records. A SQL dump preserves the database independently of its container.

This procedure uses the supplied Docker Compose deployment and MySQL 8.4. Run the commands in Bash from the deployment directory. The commands use the database name and credentials already present inside the MySQL container.

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

This stops browser and API writes, preview requests, and application migrations. Keep other database clients from changing the schema during the dump.

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.

--single-transaction provides a consistent snapshot for InnoDB tables. --quick reads rows incrementally. The other options include stored objects and omit tablespace and replication-state statements. See the MySQL mysqldump reference.

The dump includes the application database and its migration history. It excludes MySQL server accounts and grants, Redis data, and deployment configuration. The supplied MySQL container creates its application account from .env when it initializes an empty data directory.

3. Record versions and resume service​

Record the image references and MySQL version with the backup:

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. The previous MySQL data directory remains available because the procedure selects a new directory.

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"

Use the application image and MySQL version recorded with that backup for the initial restoration. Starting a newer API image can apply migrations before you inspect the restored data.

2. Select an empty MySQL data directory​

Save 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. Keep the previous directory until the restoration is complete.

Start only the data services:

docker compose up -d --wait mysql redis-sessions redis-cache

The MySQL image initializes the new directory with the database and credentials from .env. 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"

The dump contains its database selection. The mysql client executes those SQL statements. See Reloading SQL-format backups.

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

This prevents sessions and cached responses from referring to data newer than the restored database. These commands apply to the dedicated Redis services in the supplied Compose file.

5. Verify the restored records​

Inspect the restored tables and key 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 the application with the restored configuration and recorded image version:

./start.sh
docker compose ps
docker compose logs --tail=100 api
  1. Verify that the services become healthy.
  2. Sign in with an existing account.
  3. Open a known saved workspace and inspect its versions, inputs, and visibility.
  4. Save a separate example and reopen it to verify database writes.
  5. Restore normal user access.

start.sh pulls the configured images before it starts services. For an offline installation, use the locally available images as described in network configuration.

Rehearse a database restoration​

A database-only rehearsal needs the MySQL service, deployment configuration, and backup. It does not need the regex101 application services or an application license check.

Use a separate deployment directory and a separate MySQL data directory. Start only mysql, then perform the import and record checks above. Keep the rehearsal database separate from the production application network.

A full application recovery also depends on its hostname, identity-provider configuration, image versions, and machine license. See the backup overview for those parts of recovery.