MySQL backup lab, part 9 – the harness that records evidence

This is part 9 of nine. The whole series builds one MySQL backup verification lab and explains every playbook, template and script in it.

---

Eight parts have quoted measured values as though they were simply available. They are available because the lab was built so that running a scenario and recording its evidence are the same act. There is no path that executes a test without writing down what it saw.

This last part is that machinery: the shared role every scenario includes, the failure policy, the generated report, and the offline tests that check the lab’s claims about itself — including its documentation.

One role, one playbook, per scenario

ansible/playbooks/tests/t01-backup-full.yml … t12-restore-as-replica.yml
ansible/playbooks/tests/all.yml       # the ordered suite + report
ansible/playbooks/tests/report.yml    # re-render a run's report
ansible/roles/test_common/            # health gate, result recording, mc, PITR timeline
ansible/roles/t01_backup_full/ … t12_restore_as_replica/
ansible/roles/test_report/

Every scenario playbook is the same eleven lines:

- name: "t01 — on-demand full backup"
  hosts: localhost
  gather_facts: false
  vars:
    repo_root: "{{ (playbook_dir ~ '/../../..') | realpath }}"
    kubeconfig_path: "{{ (playbook_dir ~ '/../../..') | realpath }}/.kube/config"
  environment:
    PATH: "/opt/homebrew/bin:/usr/local/bin:{{ lookup('env', 'PATH') }}"
    KUBECONFIG: "{{ (playbook_dir ~ '/../../..') | realpath }}/.kube/config"
  roles:
    - test_common
    - t01_backup_full

ansible/playbooks/tests/t01-backup-full.yml

All the logic is in the role; the playbook is an entry point. That split is what allows the same role to run standalone (./scripts/lab.sh t01) and inside the ordered suite (./scripts/lab.sh test-backups) with no duplication and no flag deciding which mode it is in.

gather_facts: false because nothing here needs the laptop’s facts and gathering them costs a second per scenario. The environment block is the same safety property from part 1: a repo-local kubeconfig, so the suite cannot aim a destructive scenario at a production cluster.

test_common: the four shared includes

# test_common ships shared includes (health_gate.yml, record_result.yml, mc.yml)
# and the results defaults. Listing it as a role only announces the run.
- name: Announce the run
  ansible.builtin.debug:
    msg: "run_id={{ run_id }} results={{ results_file }}"

ansible/roles/test_common/tasks/main.yml

An unusual role: its main.yml does almost nothing. Its value is in defaults/ and in three task files that other roles include explicitly.

run_id: manual
results_dir: "{{ repo_root }}/docs/results"
results_file: "{{ results_dir }}/{{ run_id }}.jsonl"
health_timeout_retries: 60
health_timeout_delay: 10

ansible/roles/test_common/defaults/main.yml

run_id: manual as the default means a scenario run without -e run_id=… still records — into docs/results/manual.jsonl. The wrapper script always supplies a UTC timestamp, so an accidental unnamed run is the exception, not the rule.

health_gate.yml — refusing to test a broken lab

Included before every scenario, and after every scenario that changed anything.

# Asserts the lab is usable. Included before and after every scenario so a
# scenario that damages the cluster fails loudly instead of poisoning the next.

ansible/roles/test_common/tasks/health_gate.yml

That comment is the whole design rationale. Scenarios in this suite are destructive: t07 and t08 roll the cluster back, t09 and t10 rewind it to a recovered point, t12 creates and destroys a second cluster. Without a gate, a scenario that leaves the lab broken produces a cascade of failures in unrelated scenarios, and the actual cause is four reports up.

The gate is a single shell script whose exit status is the verdict:

test -n "$primary"
tables=$(kubectl -n "$NS" exec "$primary" -c mysql -- mysql … -N -e \
  "SELECT COUNT(*) FROM information_schema.tables
     WHERE table_schema='sbtest' AND table_name REGEXP '^sbtest[0-9]+$'")
echo "SYSBENCH_TABLES=$tables"
test "$tables" -eq {{ sysbench_tables }}
test "$replica_ok" -eq {{ mysql_size | int - 1 }}
echo "PRIMARY=$primary"
echo "REPLICA_OK=$replica_ok"
bs=$(kubectl -n "$NS" get pods -l app.kubernetes.io/name=binlog-server \
  -o jsonpath='{range .items[*]}{.metadata.name}={.status.phase}{"\n"}{end}' 2>/dev/null | tr '\n' ' ')
echo "BINLOG_SERVER=${bs:-none}"
{% if pitr_enabled %}
echo "$bs" | grep -F '{{ mysql_cluster_name }}-binlog-server-0=Running' >/dev/null
{% endif %}

Details that matter:

• REGEXP '^sbtest[0-9]+$' rather than LIKE 'sbtest%'. The scenarios create helper tables — sbtest.t03_delta, t04_stream_probe, post_backup_writes_… — and LIKE 'sbtest%' would count some of them, so the table count would drift upward through a run and the gate would fail for the wrong reason.
• test "$replica_ok" -eq {{ mysql_size | int - 1 }} — an exact count, not “at least one”. With mysql_size: 2 that is exactly one applying replica.
• The {% if pitr_enabled %} guard around the binlog server check means the gate adapts to the lab’s configuration rather than hard-coding an assumption. With PITR off, its absence is not a failure.
• The status line is always printed, even when the check that follows fails, so a failure report contains the observed state.

  register: health_out
  changed_when: false
  retries: 10
  delay: 15
  until: health_out.rc == 0

Ten retries at fifteen seconds. A cluster that has just finished a restore is briefly not healthy in a way that resolves itself; a cluster that is actually broken is still broken after two and a half minutes.

- name: Publish the health summary
  ansible.builtin.set_fact:
    health_summary: "{{ health_out.stdout_lines | join(' | ') }}"

That fact becomes an evidence line in the scenario’s record — which is why part 6 and part 7 could quote:

HEALTH=SYSBENCH_TABLES=10 | PRIMARY=lab-mysql-0 | REPLICA_OK=1 | BINLOG_SERVER=lab-binlog-server-0=Running

And it is also how the gate does double duty: scenarios read PRIMARY= out of health_out rather than running their own discovery query.

record_result.yml — one JSON object per attempt

- name: Append the scenario result
  ansible.builtin.lineinfile:
    path: "{{ results_file }}"
    create: true
    mode: "0644"
    insertafter: EOF
    line: >-
      {{
        {
          'scenario': scenario_id,
          'title': scenario_title,
          'status': scenario_status,
          'started': scenario_started | int,
          'finished': (lookup('pipe', 'date -u +%s') | int),
          'duration_seconds': ((lookup('pipe', 'date -u +%s') | int) - (scenario_started | int)),
          'evidence': scenario_evidence | default([]),
        } | to_json
      }}

ansible/roles/test_common/tasks/record_result.yml

JSONL — one JSON object per line — rather than a single JSON document. The reason is failure. A suite that crashes halfway through leaves a JSON array without its closing bracket, which is unparseable, so the evidence from the nine scenarios that did run is lost exactly when you most want it. Append-only lines are individually valid; a partial file is still a usable file.

Duration is computed from a clock started at the top of each role:

- name: Start the t01 clock
  ansible.builtin.set_fact:
    t_start: "{{ lookup('pipe', 'date -u +%s') }}"

— which is the first task in every scenario, outside the block:, so a scenario that fails in its first action still has a start time to record against.

evidence is a list of the literal KEY=value lines the commands printed. That is the convention introduced in part 3 and used everywhere since, and it is the reason this series could quote measured output rather than paraphrasing it: the documentation and the run report read the same strings.

The failure policy: block / rescue

Every scenario has the same skeleton:

- name: Start the t01 clock
  ansible.builtin.set_fact:
    t_start: "{{ lookup('pipe', 'date -u +%s') }}"

- block:
    - # health gate, the actual work, assertions
    - name: Record t01 as passed
      ansible.builtin.include_role: { name: test_common, tasks_from: record_result.yml }
      vars:
        scenario_status: passed
        scenario_evidence: "{{ t01_evidence }}"

  rescue:
    - name: Record t01 as failed
      ansible.builtin.include_role: { name: test_common, tasks_from: record_result.yml }
      vars:
        scenario_status: failed
        scenario_evidence: >-
          {{ [ansible_failed_result.msg | default('see play output') | string]
             + (ansible_failed_result.stdout_lines | default([]))
             + (ansible_failed_result.stderr_lines | default([])) }}

    - name: Fail the play after recording t01
      ansible.builtin.fail:
        msg: "t01 failed: {{ ansible_failed_result.msg | default('see above') }}"

Three properties, in order of importance:

1. A failure is recorded with its evidence — the message, plus whatever the failing command wrote to stdout and stderr — before anything else happens.
2. The play then fails anyway. rescue is not “continue on error”. It is “record, then stop”. There is deliberately no continue-on-error switch: running destructive restore scenarios against a dataset a previous scenario damaged produces results that mean nothing, and the suite refuses to generate them.
3. Scenarios that hold cluster state hostage use always: as well — t05 and t06 restore the backup schedule they patched, t12 deletes its verification cluster — so cleanup happens on the failure path too. Part 5 and part 8 covered both.

The ordered suite

# Ordered backup/restore/PITR suite. Order is load-bearing: every restore
# needs a good backup behind it, and destructive scenarios must not strand
# the lab. Run with: ./scripts/lab.sh test-backups
  roles:
    - test_common
  tasks:
    - block:
        - ansible.builtin.include_role: { name: t01_backup_full }
        - ansible.builtin.include_role: { name: t02_backup_from_replica }
        …
        - ansible.builtin.include_role: { name: t12_restore_as_replica }
      always:
        - ansible.builtin.include_role: { name: test_report }

ansible/playbooks/tests/all.yml

The whole suite is a block: with the report in always:. A failed run still renders its partial report — which is how the shakeout failures that shaped parts 7 and 8 were diagnosed in the first place.

The order encodes the dependency graph from parts 4 to 8:

The generated report

- name: Read the run results
  ansible.builtin.slurp:
    src: "{{ results_file }}"
  register: results_raw

- name: Parse the run results
  ansible.builtin.set_fact:
    results: >-
      {{ (results_raw.content | b64decode).splitlines()
         | select('match', '^\s*\{')
         | map('from_json') | list }}

- name: Render the run report
  ansible.builtin.template:
    src: run.md.j2
    dest: "{{ results_dir }}/{{ run_id }}-run.md"

ansible/roles/test_report/tasks/main.yml

select('match', '^\s*\{') tolerates blank lines and anything non-JSON that might have reached the file, so a stray line cannot break the report.

The template is deliberately unglamorous, and two lines in it are doing careful work:

Coverage: {{ results | map(attribute='scenario') | unique | list | length }}/12 distinct scenarios recorded.
A partial or repeated run is not a complete suite pass.

unique on the scenario IDs, because rows are attempts, not scenarios. Re-running t09 against the same run_id appends a second row. Counting rows would make three attempts at one scenario look like three passes. The report says so in its own text, so a reader cannot be misled by a table that looks comprehensive.

Passed {{ results | selectattr('status', 'equalto', 'passed') | list | length }} of {{ results | length }}.
Total scenario time: {{ results | map(attribute='duration_seconds') | sum }}s.

Then a detail section per attempt, with the evidence lines in a fenced block, verbatim.

The report is regenerable without re-running anything:

RUN_ID=20260912T101400Z-validation ./scripts/lab.sh report

The recorded runs, including the failures

docs/results/ contains more than the good run, on purpose:

early-*                       evidence written under an incorrect results path, preserved unchanged
shakeout*                     development runs that exposed harness and recovery defects
recovery-*                    restores used to return the source to the known-good dataset
storage-codex-*, pitr-codex-*, t12-codex-*   targeted validation while fixing those defects
20260912T101000Z-final        an earlier interrupted attempt — its report correctly shows failure at t04
20260912T101400Z-validation   the complete ordered run: 12/12

The results/README.md is blunt about what the files mean, including this:

Reusing a run ID appends attempts, so a development run can contain both failed and passing attempts for the same scenario. Do not treat that as one clean suite run.

and this, about a run whose name is misleading:

20260912T101000Z-final: an earlier interrupted attempt; despite its chosen run name, its report correctly shows failure at t04.

Keeping the failures is not humility for its own sake. shakeout2’s t09 — markers correct, TABLES_AFTER_RESTORE=9 — is the evidence for the recovery-history problem in part 7, and shakeout2’s t12 failing in 37 s with backup not found in storage is the evidence for the storage-prefix trap in part 8. A lab that deletes its failed runs cannot explain why its code looks the way it does.

The final numbers:

Coverage: 12/12 distinct scenarios recorded.
Passed 12 of 12.
Total scenario time: 1472s.

The offline tests

./scripts/lab.sh test      # no cluster required

Four files, and they test different things:

tests/test_lab_config.py           structural: group_vars and rendered templates
tests/test_playbook_semantics.py   failures valid YAML cannot catch
tests/test_storage_retention.py    the t05/t06 watcher logic, in isolation
tests/test_scenario_walkthrough.py the documentation against the roles

They need no Docker, no cluster and no network, and they finish in under a fifth of a second — the validation run’s log recorded Ran 35 tests in 0.196s, and the suite has grown since. That speed is the point: they catch the class of mistake that would otherwise cost a two-hour suite run to discover.

Structural tests

def render_template(path, extra=None):
    vars_ = load_group_vars()
    vars_.update({"repo_root": str(ROOT), "kubeconfig_path": …, "playbook_dir": …})
    env = Environment(loader=FileSystemLoader(str(path.parent)),
                      undefined=StrictUndefined, autoescape=False)
    return env.get_template(path.name).render(**vars_)

tests/test_lab_config.py

StrictUndefined turns a typo’d variable into an immediate error rather than an empty string. Without it, {{ mysql_clustr_name }} renders as nothing, the manifest is applied with an empty name, and the failure arrives from the API server twenty minutes into a deploy.

The templates are rendered with the real group_vars, so these are tests of the shipped configuration, not of a fixture. They check that the cluster template enables GTID, backups and Orchestrator; that the secret lists only operator-known users; that the NodePort selectors match the operator’s labels; that the PITR block renders when enabled and is absent when disabled; that every scenario has a role, a playbook and a make target; and that the docs contain no unfilled placeholders.

Semantic tests

The most interesting file, because every test in it is a scar.

CONDITIONALS = {"when", "until", "failed_when", "changed_when"}

def invalid_conditionals(node, location="root"):
    """A condition may be scalar or a flat list of scalar expressions."""
    …
    if isinstance(expression, (dict, list)):
        errors.append(f"{child}[{index}]: {expression!r}")

tests/test_playbook_semantics.py

This catches a YAML trap that part 8 ran into. Write this:

until:
  - result.stdout is search('Replica_IO_Running: Yes')

…and YAML sees Replica_IO_Running: Yes — a key: value pair — and parses the list item as a mapping, not a string. Ansible then evaluates a dict as a condition. The playbook is valid YAML, passes --syntax-check, and behaves wrongly at runtime. Quoting the expression fixes it; this test finds every unquoted case across all roles and playbooks in milliseconds.

The file’s docstring is the design statement: “Regression checks for failures that valid YAML and syntax checks can miss.” Its other tests assert that the ordered suite runs all twelve and always reports; that t12 only passes after cleanup; that PITR markers identify their own attempt and are written after the base; that each PITR scenario switches archive prefix before creating its base; and that GTID capture handles multi-UUID output without escaped newlines. Every one of those is a bug from parts 7 and 8, pinned so it cannot return.

Watcher logic tests

def test_scheduled_success_requires_exact_cluster_schedule_and_new_name(self)
def test_failed_or_incomplete_backups_do_not_count_as_produced(self)
def test_minio_error_cannot_be_mistaken_for_empty_prefix(self)
def test_mc_script_is_separated_from_kubectl_flags(self)

tests/test_storage_retention.py

The t05 and t06 watchers are plain Python with their logic in importable functions, precisely so their decisions can be tested against synthetic data without a cluster. Feed eligible() a backup from the wrong schedule and it must say no. That is a one-millisecond test for a property that would otherwise need a three-minute cron tick to exercise.

Tests that read the documentation

"""Role source vs reader-facing walkthrough: every t01–t12 scenario.

Drives the shipped Ansible roles and docs/blog-draft.md. Distinctive
kubectl / mysql / mc / CR strings are taken from the role files, not from
hardcoded essay text.
"""

tests/test_scenario_walkthrough.py

This one is unusual enough to be worth the space. It asserts that the written walkthrough:

• has a section for each of t01–t12, carrying the catalogue’s title;
• contains paragraph prose, at least 180 characters, not just bullets and code fences;
• shows a by-hand path for every scenario;
• quotes strings that actually appear in the role — sourcePod, incrementalBaseBackupName, CHANGE REPLICATION SOURCE TO, 2001-01-01T00:00:00 and so on, extracted from the role source rather than hard-coded in the test;
• expands PITR to “point-in-time recovery” near t09–t11;
• expands GTID, MinIO, XtraBackup, sourcePod, keep, “binlog server” and gtid_purged on use.

The fourth item is the clever one. The needles come from the role files, so if a scenario’s implementation changes and the prose does not, the test fails. Documentation drift becomes a test failure rather than something a reader discovers eighteen months later.

What the whole thing establishes, and what it does not

Established, by twelve scenarios in one ordered run on 12 September 2026, 12/12 passing, 1472 seconds of scenario time:

• backups are produced on demand, from a replica, as incrementals, on a schedule, and are pruned by retention with their data;
• the objects in storage are real, and the binlog archive grows in response to writes;
• restores roll the cluster back, recover a dropped table, and land on a chosen timestamp or GTID;
• an impossible recovery target is reported rather than silently downgraded;
• a backup restored into a separate cluster replicates from the live source, catches up, and matches it on every row of every table.

Not established, stated as plainly as the catalogue states it:

• incremental restore chains — t03 creates and measures an incremental, and nothing in the suite replays one;
• Orchestrator failover — installed, not the subject;
• anything about production scale. Durations here are laptop timings including deliberate waits, not RTO targets;
• application-level correctness. Matching checksums prove the bytes are the same; they do not prove your application is happy with them.

The takeaway checklist

• Do not treat a backup CR’s state: Succeeded as recoverability.
• Restore into a separate cluster so the source keeps running.
• Point the restore at object storage (backupSource.destination), not at the source cluster’s backup CR name.
• Preserve status.storage from the successful backup, including the S3 prefix.
• Copy the restored cluster’s credentials: the user secret, the operator’s internal-<cluster> secret, and TLS material if replication uses SSL.
• Assert server_uuid and server_id differ before START REPLICA.
• Use SOURCE_AUTO_POSITION=1; never copy binary-log coordinates by hand.
• Write a post-backup marker on the live source. It must be absent on the restore and present after catch-up.
• Require both replication threads running and matching COUNT(*) and CHECKSUM TABLE — not lag alone.
• Record restore duration, catch-up duration, and the GTID gap replayed.
• Destroy the verification cluster, then confirm the source is still healthy.
• Give every backup schedule a matching restore-test schedule.
• Do not claim incremental restore works because an incremental backup was small.
• For PITR on this build: UTC timestamps YYYY-MM-DDTHH:MM:SS with no Z, and a fresh archive prefix for every recovery history.

Reproducing all of it

cd 20260911-mysql-replication-backups
./scripts/lab.sh bootstrap
./scripts/lab.sh deploy
./scripts/lab.sh load-data
./scripts/lab.sh verify

RUN_ID=my-validation ./scripts/lab.sh test-backups
RUN_ID=my-validation ./scripts/lab.sh report

./scripts/lab.sh teardown

The full suite takes a couple of hours, most of it spent restoring. Individual scenarios run alone — ./scripts/lab.sh t12 takes its own base and needs no predecessor. ./scripts/lab.sh test is the offline check and needs no cluster at all.

Where to go from here

• Reference material: docs/backup-architecture.md for the mechanics, docs/test-catalogue.md for what each scenario proves and how to run it alone, and docs/results/ for every recorded run including the failures.
• The published narrative: MySQL – test your backup by restoring it as a replica.
• The lab it grew from: MySQL – a replication lab with failover, backups, and scale-out on Kubernetes.

And the one sentence the whole series exists to support:

A green backup job proves the backup process finished. A backup is verified only when it has been restored and the restored copy has been shown to be a consistent, usable database.

Restore, catch up, verify, record the result, repeat on a schedule.