RTO/RPO Recovery Drills for MySQL Point-in-Time Recovery #

Recovery time objective and recovery point objective are the two numbers every stakeholder quotes and almost no one measures. Quoted RTO is an estimate — “we can restore in about an hour” — and quoted RPO is a hope — “we lose at most a few seconds.” Neither survives contact with a real incident unless it has been produced by an actual restore-and-replay under drill conditions. This guide defines the drill harness that converts those two numbers from aspirations into monitored metrics: on a schedule, it provisions a throwaway target, restores the most recent base backup, replays the archived binary logs to a chosen point, verifies the GTID chain was gap-free, and records the wall-clock RTO and the observed RPO. A recovery program without drills is untested code shipped straight to the one moment it must not fail; this makes it a passing test that runs every night. It exercises the same execution engine described in mysqlbinlog Replay Scripting, under the orchestration defined in Point-in-Time Recovery Workflow Automation.

Visual Overview #

Core Concept & Prerequisites #

RTO and RPO measure different things and are produced by different mechanisms. RTO is a duration: the wall-clock time from “decide to recover” to “verified instance ready to promote.” A drill measures it directly by timestamping the restore-start and verify-complete boundaries. RPO is a data-loss window: the amount of committed work that would be lost if you recovered right now, which equals the age of the most recent transaction that is safely archived. You do not measure RPO with a stopwatch during a drill — you derive it continuously from the archiving pipeline’s lag, the delta between the newest committed GTID on the primary and the newest GTID confirmed in the archive. Deriving that number correctly is the whole of Deriving RPO Targets from Binlog Archiving Lag.

You need MySQL 8.0.22+, Python 3.10+, a base backup source (physical via a snapshot tool or logical), the archived segment manifest from Automated Binlog Archiving to Object Storage, and infrastructure to provision and destroy a throwaway target. The drill must run against a real restore, not a warm standby — a standby proves replication works, not that a cold archive can be reconstituted. The base-backup coordinate that the drill seeds must overlap the archived chain, per Base Backup Integration for PITR.

Production-Grade Python Implementation #

The harness is a single orchestrated run that records timestamps at each phase boundary and emits a structured drill report. It is fail-closed: a verification failure produces a red drill, not a swallowed exception, and always tears the target down to avoid cost leaks.

# drills/harness.py — Python 3.10+
from __future__ import annotations

import logging
import time
from contextlib import contextmanager
from dataclasses import dataclass, field

log = logging.getLogger("drills.harness")


@dataclass(slots=True)
class DrillReport:
    target_point: str                       # GTID or datetime the drill recovered to
    rto_seconds: float = 0.0
    rpo_seconds: float = 0.0                 # from archiving lag at drill start
    gap_free: bool = False
    phases: dict[str, float] = field(default_factory=dict)   # phase -> seconds
    ok: bool = False


@contextmanager
def _phase(report: DrillReport, name: str):
    t0 = time.monotonic()
    log.info("drill phase start: %s", name)
    try:
        yield
    finally:
        report.phases[name] = round(time.monotonic() - t0, 2)


def run_drill(target_point: str, backup, archive, target, verifier, rpo_probe) -> DrillReport:
    report = DrillReport(target_point=target_point)
    report.rpo_seconds = rpo_probe.current_lag_seconds()   # RPO is measured up front
    t_start = time.monotonic()
    try:
        with _phase(report, "provision"):
            target.provision()
        with _phase(report, "restore"):
            coord = backup.restore_latest(target)          # returns base gtid_purged
            target.seed_gtid_purged(coord)
        with _phase(report, "replay"):
            segments = archive.resolve(coord, target_point)
            target.replay(segments, target_point)
        with _phase(report, "verify"):
            report.gap_free = verifier.contiguous(target, coord, target_point)
        report.ok = report.gap_free
    finally:
        report.rto_seconds = round(time.monotonic() - t_start, 2)
        with _phase(report, "teardown"):
            target.destroy()
    log.info("drill %s: rto=%.1fs rpo=%.1fs gap_free=%s",
             "PASS" if report.ok else "FAIL", report.rto_seconds, report.rpo_seconds, report.gap_free)
    return report

The verifier.contiguous call is the gate that makes a drill meaningful: a fast restore that produced a chain with a hole is a failed drill, not a fast one. That check is the standalone subject of Detecting GTID Gaps Before PITR Replay. Scheduling the harness on a cadence is done with a systemd timer for the same execution-tracking reasons discussed in Scheduling Binlog Rotation with systemd Timers.

Configuration Reference #

Validation & Verification Gates #

• The drill restores from a real cold base backup, not a warm standby.
• gtid_purged on the target equals the base backup’s captured coordinate exactly.
• The replayed target’s gtid_executed reaches the drill’s target_point with no gap.
• Measured RTO is recorded per phase (provision, restore, replay, verify) for regression analysis.
• RPO is sampled from archiving lag at drill start and stored alongside the report.
• The target is destroyed even when the drill fails (teardown runs in finally).
• A drill that fails verification is reported red and pages, rather than being retried into a green.

Error Handling & Failure Modes #

A drill that “passes” in seconds is a red flag, not a win — it usually means the replay applied nothing because the resolved segment list was empty (a manifest or coordinate bug). Assert that the number of applied transactions is greater than zero and consistent with the archiving rate.

ERROR 1840 / 1841 (@@GLOBAL.GTID_PURGED can only be set when ...) occurs when seeding gtid_purged on a target whose gtid_executed is not empty — reset the target first. ERROR 3546 (@@GLOBAL.GTID_PURGED cannot be changed ...) on 8.0 variants signals a residual GTID state from a prior drill; a clean provision avoids it.

Cost leaks from orphaned targets are the most common operational failure of a drill program: a crash between provision and teardown strands an instance. The finally teardown and a separate reaper that destroys any drill target older than the maximum drill duration together prevent this.

Observability & Alerting #

Store every DrillReport as a time series keyed by target_point recency. Alert on three conditions: RTO regression (a rolling percentile crossing the SLA budget), any red drill (a verification failure is a recovery-readiness incident), and stale drills (no successful drill within the cadence window, which means the program silently stopped running). Publish the current derived RPO next to RTO so a single dashboard answers “how much would we lose and how long would it take” at any moment. The RPO derivation feeding that panel is detailed in Deriving RPO Targets from Binlog Archiving Lag, and how to react when a drill exposes an unrecoverable window is covered in Fallback Routing Strategies.

Frequently Asked Questions #

Related #

• Measuring RTO with Scheduled PITR Restore Drills — the concrete per-phase timing harness and how to shrink each phase.
• Deriving RPO Targets from Binlog Archiving Lag — turning archiving lag into a defensible RPO number.
• Recovery Validation Gates — the contiguity and checksum checks a drill must pass.
• Base Backup Integration for PITR — anchoring drills to a verifiable base-backup coordinate.

Back to Point-in-Time Recovery Workflow Automation.