Automated, Tested Backup/DR Pipeline
Planned: nightly backups taken from a replica, a retention policy, and an automated restore test every night that proves each backup actually works, because replicas are not backups.
Status: planned, not started. Every command on this page is the plan. None of it has run yet. The page will be rewritten with real output once it's built.
Turn the one-off backup/restore drill from the performance project into a scheduled pipeline:
- nightly backups with a retention policy,
- stored off the database host's disk,
- and an automated restore test that proves each backup actually works.
Why it matters here: the home server has no automated backups today. The replicas are not backups, because they copy deletes and bad writes within a second.
Tools (planned)
| Tool | Will be used for |
|---|---|
| Apache Airflow (existing) | Nightly backup DAG and the restore-verification DAG |
mysqldump --single-transaction (first), Percona XtraBackup (stretch goal) | Logical backup first, then a physical one to compare restore times |
| MinIO (existing), new bucket + dedicated key | Backup storage, with a key scoped to that one bucket |
| A throwaway MySQL 8.4 container | Restore target for verification |
| Uptime Kuma push monitor → Discord | Alert when a backup or restore test fails or doesn't run |
Plan
1. Nightly backup, taken from a replica
What it will do: dump all application databases from a read-only replica, so the primary never pays for the backup. Record the GTID position, compress, checksum.
docker exec mysql-replica2 sh -c 'mysqldump --single-transaction --source-data=2 --routines --triggers \
-uroot -p"$MYSQL_ROOT_PASSWORD" --databases …' | gzip > nightly-$(date +%F).sql.gz
sha256sum nightly-$(date +%F).sql.gz > nightly-$(date +%F).sha256
Success check: the file and its checksum land in MinIO every night.
2. Retention
What it will do: keep 7 dailies, 4 weeklies and 3 monthlies, and prune anything older, per tier.
Success check: the bucket listing never holds more than the policy allows.
3. Automated restore test
What it will do: each night, pull the newest backup, verify its checksum, restore it into a throwaway container, and compare table counts against the source at the backup's GTID position. Then delete the container and its volume.
docker run -d --name restore-test -e MYSQL_ROOT_PASSWORD=… mysql:8.4
gunzip -c nightly-*.sql.gz | docker exec -i restore-test mysql -uroot -p…
# compare per-table counts, then:
docker rm -fv restore-test
Success check: a pass/fail line per night. Restore time is recorded as the real recovery time objective (RTO).
4. Prove the alarm works
What it will do: deliberately corrupt one test backup and confirm the verification job fails and Discord is notified, instead of quietly passing.
5. Point-in-time recovery drill
What it will do: restore last night's backup, then replay binlogs up to one minute before a deliberate "bad" DELETE, and measure how much data is recovered.
Known risks (from earlier projects)
- Leaked volumes: an earlier sandbox test leaked 80 anonymous Docker volumes (~17 GB) and filled the disk to 100%. Restore tests must use
docker rm -fv, and the disk alert now exists. - Silent no-op restores:
docker execwithout-isilently restores nothing (performance project), so verification must count rows, not trust exit codes. - Disk space: the disk is shared with Kafka and three MySQL servers, so backups should leave the host. MinIO on the same disk is only a first step.