Chapter 13 · Backup, mariadb-backup, Restore, and Point-in-Time Recovery
Backup Automation, Offloading to Replicas, Restore Drills, RPO/RTO, and Corruption Tests
Operate MariaDB recoverability as a measured system: automate inventories and retention, understand replica-offload caveats, protect off-site copies and keys, rehearse restores, and test corruption detection against explicit RPO/RTO objectives.
Learning outcomes
ServiceHub's engineers can now perform a manual restore, but disaster recovery still depends on remembering commands under pressure. A reliable backup program must answer operational questions continuously: Did today's backup finish? Is the off-site copy complete? Which base backup is still connected to retained binlogs? Can the team decrypt it? How long does a restore actually take? What happens if an incremental or manifest is corrupt?
Recovery Point Objective (RPO) is the maximum acceptable data-loss interval. Recovery Time Objective (RTO) is the target time to restore usable service after disruption. They are business requirements, not backup settings. Backup frequency influences RPO; restore throughput, log replay, validation and operational coordination influence RTO.
Design backup inventory and retention around recoverable chains rather than isolated files.
Explain when backup offload to a replica helps and how lag, filters and topology metadata can invalidate assumptions.
Protect backups with checksums, encryption, off-site/immutable copies and independently recoverable keys.
Measure restore drills and compare actual recovery behavior with RPO/RTO targets.
Simulate missing/corrupt backup pieces safely and require automation to fail closed instead of silently promoting bad artifacts.
This lesson intentionally does not prescribe “7 daily / 4 weekly / 12 monthly” or any other universal values. Derive retention from legal/business requirements, backup cadence, binlog volume, recovery objectives, storage cost and restore-chain complexity. Then test the chosen policy.
1. Treat a backup set as a graph of dependencies
A physical full backup can stand alone for its snapshot time. An incremental depends on a base and possibly earlier incrementals. PITR additionally depends on continuous binary logs after the base coordinate. An encrypted artifact depends on keys and decryptor/plugin support. Therefore retention should delete a recoverability set only after a newer validated set supersedes it.
| Artifact | Depends on | Useful evidence |
|---|---|---|
| logical full dump | compatible client/server import path | checksum, versions, object inventory |
| physical full | mariadb-backup/server compatibility | xtrabackup_info/checkpoints/binlog info |
| incremental inc2 | base + inc1 | from_lsn/to_lsn continuity |
| PITR logs | base start coordinate + every needed log | ordered file list/GTID coverage |
| encrypted archive | key + decryptor/plugin | key ID/version, test decrypt record |
2. Build a portable manifest and checksum inventory
A small cross-platform Python tool can inventory artifacts without assuming Bash or PowerShell. It does not replace MariaDB validation; it verifies that bytes have not changed since the manifest was created.
from pathlib import Pathimport hashlib, json, sys, timeroot = Path(sys.argv[1]).resolve()manifest = { "created_epoch": int(time.time()), "root": str(root), "files": []}for p in sorted(x for x in root.rglob("*") if x.is_file()): h = hashlib.sha256() with p.open("rb") as f: for chunk in iter(lambda: f.read(1024 * 1024), b""): h.update(chunk) manifest["files"].append({ "path": str(p.relative_to(root)), "bytes": p.stat().st_size, "sha256": h.hexdigest() })print(json.dumps(manifest, indent=2))python backup_manifest.py /var/mariadb/backups/full-001 > /var/mariadb/backups/full-001.manifest.jsonsha256sum /var/mariadb/backups/full-001.manifest.jsonpython .\backup_manifest.py C:\MariaDBBackups\full-001 | Set-Content -Encoding utf8 C:\MariaDBBackups\full-001.manifest.jsonGet-FileHash C:\MariaDBBackups\full-001.manifest.json -Algorithm SHA256Store the manifest separately enough that an attacker or accidental overwrite cannot modify both backup and expected checksum without detection. Cryptographic checksums detect byte change; they do not prove logical correctness, freshness or decryptability.
3. Scheduling: make failure visible and idempotent
A production job should create a unique target, capture start/end timestamps and versions, fail on nonzero exit, prepare/verify the backup, build a manifest, copy it off-host, confirm the remote copy, and only then mark the run successful. Never overwrite the last known-good backup in place.
1. acquire backup-job lease / prevent overlapping run2. create unique run_id and empty target directory3. record server/tool versions + effective source identity4. run mariadb-backup or mariadb-dump; capture stdout/stderr/exit code5. prepare physical backup (or restore-test logical backup)6. record xtrabackup/binlog coordinates or dump master-data metadata7. hash artifacts + manifest8. copy to independent/off-site destination9. verify remote object sizes/checksums10. emit success metric only after every required step passes11. retention deletes only sets superseded by a validated newer chain12. release job leaseOn Linux/macOS, cron/systemd timers can launch the job; on Windows, Task Scheduler can. The scheduler is not the hard part. The contract between exit state, monitoring and artifact promotion is.
4. Replica offloading: less primary I/O, more state to prove
Taking a backup from an asynchronous replica can reduce read/I/O pressure on the primary and isolate long snapshots, but the replica may be behind, filtered or unhealthy. Its backup represents the replica's applied state, not automatically the primary's latest committed state.
| Risk | Required evidence before accepting replica backup |
|---|---|
| apply lag | replica IO/apply status and GTID/coordinate state at backup time |
| replication filters | documented filters; prove required schemas/tables are present |
| broken replication | last error/state + freshness checks |
| local writes on replica | topology policy and GTID/domain interpretation |
| backup metadata options |
verify required privileges for
--slave-info/--galera-info on
target version
|
Replica offloading is optional and requires a multi-node topology. The mandatory lab remains single-node/local. Chapter 14 teaches MariaDB asynchronous replication, GTIDs and parallel apply in depth; do not create a replica solely to satisfy this backup lesson.
5. Off-site and immutable copies: survive host and credential failure
A backup stored on the same filesystem, host and administrator credential plane as production can disappear in the same incident. Maintain at least one independently protected copy appropriate to your threat model. “Off-site” can mean another physical location/account/provider; “immutable” means retention/object-lock controls prevent ordinary credentials from rewriting/deleting data during the protected period.
Encryption is necessary when backups contain sensitive data, but key handling determines whether it improves or destroys recoverability. Keep key identifiers and recovery procedure in the manifest/runbook; keep secret key material in a separate controlled system with tested break-glass access.
| Control | Failure it addresses | Test |
|---|---|---|
| off-host copy | database-host loss | restore with source host unavailable |
| immutable retention | ransomware/admin deletion | attempt delete with normal backup credentials |
| encryption | backup disclosure | decrypt on clean recovery host |
| separate key escrow | key loss with database host | recover key through documented emergency path |
| checksums | silent transfer/media corruption | rehash after remote retrieval |
6. Restore drills turn RPO/RTO into measured numbers
Run drills on clean infrastructure. Start the timer at a defined incident point, not after the backup has already been downloaded. Include artifact discovery, key retrieval, decompression/decryption, prepare, copy-back/import, binlog replay, startup, schema checks and application smoke tests.
drill_id: 2026-08-servicehub-01incident_declared: 10:00:00backup_selected: 10:04:30artifact_download_complete: 10:18:10decryption_complete: 10:22:55prepare/import_complete: 10:47:20binlog_replay_complete: 10:55:40DB validation complete: 11:03:15application smoke test complete: 11:09:40Measured RTO: 1h 09m 40sLast safely recovered commit: 09:58:42Incident time: 10:00:00Measured data-loss window: 1m 18s (compare with RPO)Do not subtract time spent waiting for an operator or obtaining keys; those delays are part of operational RTO. Likewise, the RPO result comes from the last verified recoverable transaction, not the nominal backup schedule.
7. Create an independent corruption-drill artifact
This lesson can be run without any earlier chapter artifacts. Create a tiny disposable schema and dump it to a local drill file.
DROP DATABASE IF EXISTS servicehub_drill_lab;CREATE DATABASE servicehub_drill_lab;CREATE TABLE servicehub_drill_lab.marker ( id INT PRIMARY KEY, note VARCHAR(100) NOT NULL) ENGINE=InnoDB;INSERT INTO servicehub_drill_lab.marker VALUES (1,'known-good backup drill marker');mkdir -p ./backup-drillmariadb-dump --single-transaction servicehub_drill_lab > ./backup-drill/drill-source.sql8. Safe corruption tests: damage copies, never the only backup
Create two copies of the small disposable dump. Keep one pristine, truncate the other, and prove checksum/restore validation rejects it.
from pathlib import Pathimport hashlibsrc = Path("backup-drill/drill-source.sql")good = Path("drill-good.sql")bad = Path("drill-corrupt.sql")good.write_bytes(src.read_bytes())bad.write_bytes(src.read_bytes())raw = bad.read_bytes()bad.write_bytes(raw[:-128]) # corrupt only the disposable copydef sha(p): return hashlib.sha256(p.read_bytes()).hexdigest()print("good", sha(good), good.stat().st_size)print("bad ", sha(bad), bad.stat().st_size)assert sha(good) != sha(bad)
The correct automation outcome is “artifact invalid; do not
promote as successful.” A more realistic drill removes
inc-001 from a copied incremental chain or moves
the first required binlog into quarantine and verifies the
recovery planner detects the missing dependency before touching
a restore target.
Choosing the newest timestamped artifact without verifying chain completeness, checksum, decryptability and base-to-binlog continuity can select an unrecoverable set. “Recent” and “recoverable” are different properties.
9. Backup observability and alerting
| Signal | Alert condition example | Why it matters |
|---|---|---|
| last validated backup age | older than RPO-derived threshold | backup job may be running but validation is failing |
| off-site copy lag | local success but remote confirmation missing | host loss would remove only usable copy |
| restore drill age | no drill within policy interval | tooling/credentials may have drifted |
| binlog coverage gap | oldest required coordinate no longer retained | PITR window silently broken |
| backup size/duration anomaly | unexpected jump/drop from workload baseline | capacity issue or missing data/object coverage |
| key/certificate expiry | approaching recovery-policy threshold | decrypt/remote transfer can fail during incident |
These are conditions, not universal numeric thresholds. Calibrate them from business objectives and normal workload measurements.
10. Chapter 13 recovery acceptance matrix
| Capability | Accept when | Reject when |
|---|---|---|
| logical backup | required objects/data restore into clean server | dump file exists but routines/events/invariants are missing |
| physical backup | matching tool, prepared backup, clean copy-back and startup | raw directory copied without prepare/validation |
| incrementals | LSN chain complete and merge tested | any dependency is missing/corrupt |
| PITR | base coordinate through safe transaction boundary is continuous | binlog gap or unverified time-only stop |
| off-site protection | retrieval/decryption tested independently | keys or credentials live only on failed host |
| RPO/RTO | measured drill results meet objectives | objectives inferred from schedule/vendor promises |
Check your understanding
- How do RPO and RTO differ?
- Why can a backup taken from a replica be stale even when the backup itself is internally consistent?
- What does a SHA-256 manifest prove, and what does it not prove?
- Why must retention understand incremental/binlog dependencies?
- What should happen when a corruption drill detects a mismatched checksum?
Review the answers
RPO limits acceptable data loss; RTO limits acceptable service restoration time. A replica may lag or filter data, so its consistent snapshot can still be behind the primary. A checksum detects byte changes relative to the manifest but does not prove logical correctness, freshness or decryptability. Incrementals and PITR are dependency chains, so deleting one parent can invalidate later artifacts. A checksum mismatch must fail the artifact closed, alert operators and select/retrieve another validated recovery set rather than being ignored.
11. Cleanup and bridge to replication
Remove only disposable drill copies and lab instances after recording lessons learned. Keep manifests and drill measurements as operational evidence. Review any temporary backup users, option files and keys created for the lab.
rm -f ./drill-good.sql ./drill-corrupt.sql ./backup-drill/drill-source.sql# Remove lab containers/volumes only if you created and no longer need them.# Revoke/drop disposable backup accounts and servicehub_drill_lab after tests.Chapter 13 established that backups and binlogs form a tested recovery system. Chapter 14 now explores asynchronous replication. Replicas can provide read scaling, offloaded backups and alternative recovery sources, but they introduce send/relay/apply lag, GTID state and promotion/fencing concerns; replication is not a replacement for independent backups.