Skip to content

Database backup and restore

Task-oriented commands for the Amazon RDS MySQL database. Concepts, retention, and recovery limits are on Backup.

Placeholders: <rds-endpoint> is the instance endpoint, <env> is dev / staging / prod. Credentials are the DB_USER / DB_PASSWORD values in that environment's .env — never paste them into a repo, a ticket, or this site.

All mysql / mysqldump commands must run from a host whose security group is allowed inbound on 3306, since the instance is not publicly accessible.

Take a manual snapshot before risky work

Do this before a schema migration, a data backfill, or a cutover.

  1. RDS → Databases → select verilib-mysql-<env>.
  2. Actions → Take snapshot.
  3. Name it after the change, for example verilib-mysql-<env>-pre-sql26.
  4. Wait for status Available before proceeding.

Manual snapshots outlive the automated retention window, so this is the cheap insurance step.

Export a logical dump

Portable copy for seeding a test stack or moving between environments.

mysqldump -h <rds-endpoint> -P 3306 -u verilib -p \
  --single-transaction --routines --triggers --events \
  --set-gtid-purged=OFF \
  verilib > verilib-$(date +%Y%m%d).sql

--single-transaction gives a consistent InnoDB snapshot without locking writers, so this is safe to run against a live instance.

Verify before trusting the file:

ls -lh verilib-*.sql

mysql -h <rds-endpoint> -P 3306 -u verilib -p verilib -e "
SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY table_name;
"

Spot-check the tables that matter: repos, codes, atoms, atomsdependencies, atomsnippets, certificates.

Import a dump

Into a non-production target unless you are performing a planned recovery.

mysql -h <target-endpoint> -P 3306 -u verilib -p verilib < verilib-YYYYMMDD.sql

If the dump came from a database with a different name, create that database first or add a USE verilib; header — the import does not rename anything for you.

After importing an older dump, apply the migrations it predates (sql/*.sql, plus the atomizer's additive SQL) — see Scheme → Migrations.

Restore from a snapshot

A restore does not replace the running instance; it creates a new one.

  1. RDS → Snapshots → select the snapshot.
  2. Actions → Restore snapshot.
  3. Set the new instance identifier, and select the same VPC, subnet group (verilib-rds-subnet-<env>), security group (verilib-rds-mysql-<env>), and parameter group (verilib-mysql8-<env>) as the original — the wizard does not always default to them.
  4. Keep Public access: No.
  5. Wait for Available, then copy the new endpoint.
  6. Verify the data before any traffic switch:
mysql -h <new-endpoint> -P 3306 -u verilib -p verilib -e "
SELECT COUNT(*) AS repos FROM repos;
SELECT COUNT(*) AS atoms FROM atoms;
SELECT COUNT(*) AS certs FROM certificates;
"

The master password is the one that was in effect when the snapshot was taken, which may not be the password currently in .env.

Point in time recovery

Same shape as a snapshot restore, but you choose a timestamp.

  1. RDS → Databases → select the instance.
  2. Actions → Restore to point in time.
  3. Choose latest restorable time or a custom timestamp within the retention window.
  4. Configure networking as in the snapshot restore, then create and verify.

Cut the application over to a restored instance

Recovery is only finished once the application is pointed at the new endpoint.

  1. Stop the DB-writing workers so nothing writes to the old instance mid-switch: upload_response, atomize_response, validate_response, livelog, validate_livelog.
  2. Update the application env on the host:
DB_HOST=<new-endpoint>
DB_PORT=3306
DB_NAME=verilib
DB_USER=verilib
DB_PASSWORD=<password-for-that-instance>
  1. Restart PHP so the new values are read — the env file alone is not enough:
sudo systemctl restart php-fpm
sudo systemctl reload httpd
  1. Restart the workers.
  2. Smoke test: log in, open a repo, atomize a small repo, certify a small atomized repo.
  3. Keep the previous instance (or its snapshot) until the new one has run clean.

Check for a duplicate DB_PORT after editing .env

The PDO layer ignores DB_PORT while the auth API uses it, so a leftover second DB_PORT= line breaks login while ordinary queries still succeed. Details: Managed → Connecting the application.

Copy a snapshot to another Region

Needed when the application tier moves Region, since an RDS instance cannot be relocated in place.

  1. RDS → Snapshots → select → Actions → Copy snapshot.
  2. Set the destination Region and a name.
  3. Switch the console to the destination Region and wait for Available.
  4. Create the security group, subnet group, and parameter group in that Region first — none of them cross Region boundaries.
  5. Restore the copied snapshot there and repoint the application.
  6. Delete or stop the original instance once the new one is validated, to avoid paying twice.