Restore Testing, RPO/RTO Measurement, Corruption Drills, and Backup Automation

Learning outcomes

The most dangerous backup system is one that produces green “backup succeeded” messages for months but has never restored into a clean server. ServiceHub needs a recovery service, not a file-production service. The final lesson defines acceptance tests, measures actual RPO/RTO, injects safe failures, and turns the results into routine operational evidence.

RPO and RTO are measured properties of a recovery design

RPO answers “how much committed work can we lose?” If the newest recoverable point is 14:20 and the incident happened at 14:35, the observed RPO gap is 15 minutes. RTO answers “how long until the required service is usable again?” It begins at the agreed incident/recovery start and ends only when the acceptance criteria are satisfied—not merely when mysqld starts.

Build a recovery manifest before automation

-- Run on a disposable local MySQL 8.4 LTS instance.CREATE DATABASE IF NOT EXISTS servicehub_recovery_lab  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;CREATE TABLE IF NOT EXISTS servicehub_recovery_lab.sites (  site_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  site_code VARCHAR(20) NOT NULL UNIQUE,  site_name VARCHAR(120) NOT NULL) ENGINE=InnoDB;CREATE TABLE IF NOT EXISTS servicehub_recovery_lab.work_orders (  work_order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  site_id BIGINT UNSIGNED NOT NULL,  status ENUM('OPEN','IN_PROGRESS','DONE','CANCELLED') NOT NULL DEFAULT 'OPEN',  summary VARCHAR(180) NOT NULL,  opened_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  closed_at TIMESTAMP(6) NULL,  CONSTRAINT fk_recovery_work_order_site    FOREIGN KEY (site_id) REFERENCES servicehub_recovery_lab.sites(site_id)) ENGINE=InnoDB;CREATE TABLE IF NOT EXISTS servicehub_recovery_lab.recovery_markers (  marker_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  marker_name VARCHAR(80) NOT NULL UNIQUE,  marker_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  note VARCHAR(255) NOT NULL) ENGINE=InnoDB;INSERT IGNORE INTO servicehub_recovery_lab.sites(site_code,site_name)VALUES ('BAKU-01','Baku Central Service Site'),       ('BAKU-02','Baku East Service Site');INSERT IGNORE INTO servicehub_recovery_lab.recovery_markers(marker_name,note)VALUES ('CH13_BASELINE','Known recovery invariant before Chapter 13 drills');SELECT COUNT(*) AS sites FROM servicehub_recovery_lab.sites;SELECT COUNT(*) AS markers FROM servicehub_recovery_lab.recovery_markers;

SELECT VERSION() AS server_version,       @@GLOBAL.log_bin AS log_bin,       @@GLOBAL.gtid_mode AS gtid_mode,       @@GLOBAL.binlog_format AS binlog_format;SHOW BINARY LOG STATUS;SELECT COUNT(*) AS sites FROM servicehub_recovery_lab.sites;SELECT COUNT(*) AS work_orders FROM servicehub_recovery_lab.work_orders;SELECT COUNT(*) AS markers FROM servicehub_recovery_lab.recovery_markers;SELECT CONSTRAINT_NAME,TABLE_NAME,CONSTRAINT_TYPEFROM INFORMATION_SCHEMA.TABLE_CONSTRAINTSWHERE TABLE_SCHEMA='servicehub_recovery_lab'ORDER BY TABLE_NAME,CONSTRAINT_NAME;

A recovery manifest should also record artifact filename, checksum, creation start/end, tool versions, source server identity/version, backup type, binary-log start coordinate, encryption/key reference (not the key itself), storage location, retention class, and the last restore-test result.

Measure backup and restore locally

The course intentionally refuses to publish “mysqldump takes X seconds per GB.” Hardware, cache state, row width, indexes, compression, network, concurrency, and storage all matter. Measure on the learner's system and label the result as a local observation.

START=$(date +%s)mysqldump -h 127.0.0.1 -u root -p --single-transaction \  --databases servicehub_recovery_lab > servicehub_restore_test.sqlEND=$(date +%s)echo "Local backup elapsed seconds: $((END-START))"# Time the restore into a disposable target using the same discipline.

$backupTime = Measure-Command {  cmd.exe /c "mysqldump -h 127.0.0.1 -u root -p --single-transaction --databases servicehub_recovery_lab > servicehub_restore_test.sql"}$backupTime.TotalSeconds

Password prompting inside automation is deliberately awkward because unattended jobs require a secret-management design. Chapter 12 covered login paths, protected option files, and external secret stores. Reuse that discipline rather than hard-coding a password in a script.

Acceptance test: restore on a clean target

A clean target catches dependencies hidden by an existing server: missing users, missing routines, incompatible SQL modes, absent keyring material, collation/version differences, or forgotten plugins. For the course lab, a disposable schema reset on an isolated local instance is sufficient; production should use a clean recovery environment representative of the intended target.

SELECT COUNT(*) AS orphan_work_ordersFROM servicehub_recovery_lab.work_orders wLEFT JOIN servicehub_recovery_lab.sites s ON s.site_id=w.site_idWHERE s.site_id IS NULL;CHECK TABLE servicehub_recovery_lab.sites,            servicehub_recovery_lab.work_orders,            servicehub_recovery_lab.recovery_markers;SHOW CREATE TABLE servicehub_recovery_lab.work_orders;SELECT marker_name,marker_time,noteFROM servicehub_recovery_lab.recovery_markersORDER BY marker_time DESC,marker_id DESCLIMIT 20;

CHECK TABLE is one engine-level signal, not a substitute for business tests. A syntactically healthy table can still be missing yesterday's orders. Acceptance must compare known invariants and expected recovery time.

Safe corruption drill: damage the copy, not the source

python -c "p='servicehub_restore_test.sql'; d=open(p,'rb').read(); open('servicehub_restore_test.corrupt.sql','wb').write(d[:-min(2048,max(1,len(d)//10))])"# Compare the recorded checksum before restore.# If checksum mismatches, automation should mark the artifact FAILED and stop.

Do not intentionally corrupt InnoDB files or valuable binary logs to practice recovery. The learning objective is operational behavior: detect the bad artifact, preserve evidence, select another valid recovery point, and document the resulting RPO impact.

Missing-chain drill: quantify the RPO consequence

Copy a set of archived binary logs into a disposable recovery directory, remove one middle file, and ask the runbook to enumerate the required sequence from the full-backup boundary to the target point. The runbook must refuse to claim continuous PITR. Then calculate the newest earlier recovery point for which the chain is complete. That difference—not the configured backup schedule—is the real incident RPO.

Automation architecture

Automation must be idempotent where practical and explicit about partial failure. For example, a local dump that succeeds but an off-host copy that fails is not a successful protected backup. A restore test that imports data but fails application checks is not a successful recovery.

Backup retention and binary-log retention must agree

If you retain weekly full backups for six weeks but binary logs for only three days, only the newest few days have continuous fine-grained PITR. That can be a valid policy if explicitly intended, but it is misleading to advertise six weeks of point-in-time recovery. Build retention from named recovery tiers, such as recent granular PITR plus older full-backup restore points.

SHOW GLOBAL VARIABLES LIKE 'binlog_expire_logs_seconds';SHOW GLOBAL VARIABLES LIKE 'binlog_expire_logs_auto_purge';SHOW BINARY LOGS;

Final Chapter 13 recovery scorecard

Summary and bridge to Chapter 14

Chapter 13 ends with a tested recovery system: consistent backup boundaries, protected artifacts, known binary-log/GTID state, position-based PITR, clean-target validation, and measured RPO/RTO. Chapter 14 uses many of the same primitives—binary logs, GTIDs, topology state, lag, and promotion—but applies them to asynchronous replication and operational failover.

Authoritative references

• MySQL 8.4 — Backup and Recovery
• MySQL 8.4 — Establishing a Backup Policy
• MySQL 8.4 — Binary Log
• MySQL 8.4 — Point-in-Time Recovery
• MySQL Shell 8.4 — Dump and Load Utilities