MariaDB / Advanced: evidence and reliable operations / Lesson 14
Restore rehearsals and point-in-time recovery
Build a recovery runbook that can stop before an accidental change. Preserve evidence, apply a verified backup chain and prove application correctness before cutover.
On this page
Lesson goals
- Explain prepare, restore and binary-log replay boundaries.
- Validate recovery without overwriting the incident source.
Preserve the source and define the target
After an accidental deletion, restarting the server is not a recovery strategy: valid committed deletion is normally recovered as committed work. Preserve available backups, binary logs, configuration and incident timing. Establish the desired last good transaction, not merely the moment someone noticed the problem. Restore into an isolated destination with application writes and scheduled jobs fenced. Keep the damaged source available as evidence until the recovery result is validated and the agreed retention policy permits cleanup.
A base backup needs its matching log position
Point-in-time recovery starts with a consistent base backup and replays binary-log events from its recorded boundary to just before the undesired transaction. A gap in logs breaks the chain even if later files exist. File names without positions are insufficient, and time-zone assumptions can make timestamp-only stopping ambiguous. MariaDB Backup records replication and binary-log metadata according to its options and version. Review that metadata alongside the artifact manifest before choosing replay boundaries.
SHOW BINARY LOGS;
SELECT @@global.log_bin, @@global.binlog_format;Read-only inventory examples, requiring appropriate monitoring privileges. They do not recover data or prove a complete archived chain.
Preparation precedes a normal physical restore
A physical backup may contain pages captured at different moments. The documented prepare phase applies recovery information to make the backup consistent; incremental chains need their own ordered preparation procedure. Restore with a compatible MariaDB release and tool combination, preserving an untouched artifact copy. Copy-back or move-back restores write database files and normally require a stopped server with an empty destination data directory, so they need a reviewed operational runbook. This theory lesson deliberately provides no executable filesystem-restoration command.
Validate a recovered business invariant
After restoring the synthetic recovery database, check more than row counts. A bookings table and booking_lines table should agree on totals at the intended grain. The query identifies mismatches and is expected to return no rows only when every stored booking total equals its lines, treating no lines as zero under this explicit training policy. That policy may differ from a real application with tax, rounding or discounts; adapt the invariant to the actual accounting definition before using it.
SELECT b.booking_id, b.total, COALESCE(l.line_total, 0) AS line_total
FROM recovered_lab.bookings AS b
LEFT JOIN (
SELECT booking_id, SUM(amount) AS line_total
FROM recovered_lab.booking_lines GROUP BY booking_id
) AS l ON l.booking_id = b.booking_id
WHERE b.total <> COALESCE(l.line_total, 0)
ORDER BY b.booking_id;This read-only check assumes non-NULL booking totals. Empty output is conditional validation, not a claimed observation.
Recovery ends with a controlled service decision
Verify expected pre-incident rows, excluded bad changes, constraints, routines, account access and application smoke tests. Measure restoration and replay time against RTO, and quantify the recovered boundary against RPO. Before cutover, define which endpoint accepts writes and how old writers are fenced to avoid split histories. Retain a rollback plan and clear evidence of validation. Replication and crash recovery improve availability but do not replace independently protected backups against logical mistakes.
Common errors and fixes
The restored service includes the accidental DELETE
- Why it happens
- Replay crossed the bad transaction boundary.
- How to fix it
- Restart the isolated recovery from the base and use a verified earlier stop boundary.
- Prevent it next time
- Inspect events and transaction boundaries instead of guessing detection time.
Replay cannot continue through a missing file
- Why it happens
- Binary-log retention or archival left a gap.
- How to fix it
- Find an independent complete archive or choose a recoverable earlier target.
- Prevent it next time
- Continuously verify chain completeness and alert before retention removes needed logs.
The backup artifact was overwritten during rehearsal
- Why it happens
- Preparation and restore used the only retained copy or wrong destination.
- How to fix it
- Recover from a protected independent copy and rebuild the rehearsal destination.
- Prevent it next time
- Separate immutable artifacts, working copies and destination identifiers in the runbook.
Check your understanding
1. A base backup ends at event boundary X. Logs X through Y are present, Y through Z are missing, and later logs exist. Can you promise recovery to a point after Z?
Show worked solution
No. The missing events prevent reconstructing the intervening committed history. Find a complete independent chain or a later consistent base backup whose required logs are intact. Otherwise report the latest provable recoverable point before the gap, including the associated data-loss limit. Later files alone cannot fill missing transactions.
2. The restored table count matches the source manifest. Give three stronger checks before cutover.
Show worked solution
Compare representative business invariants such as booking-line totals, verify specific known good and excluded bad transactions around the target boundary, and run application access checks for required views and routines using the intended service role. Also measure recovery time. Matching object counts alone cannot detect wrong contents, stale data or unusable definers.
Key takeaways
- Recover into isolation and prove the last good transaction boundary.
- A complete protected chain matters more than a successful file copy.