MySQL backup lab, part 3 – data, and proof the lab is worth testing

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

---

A backup test against an empty database proves nothing. So does a backup test against a database you cannot describe precisely: “the restore worked” is only meaningful if you can state what “worked” means in numbers that were true before and are true again after.

This part builds both halves of that. First a dataset with exact, known dimensions. Then a verification playbook whose job is to decide — with assertions, not eyeballs — whether the lab is currently in the state the rest of the series assumes.

The dataset: 10 tables, 200,000 rows, about 500 MB

sysbench_tables: 10
sysbench_table_size: 200000
sysbench_database: sbtest
data_size_min_mb: 400
data_size_max_mb: 700

ansible/inventory/group_vars/all.yml

sysbench’s oltp_read_write workload in prepare mode creates sbtest1 … sbtest10, each with an integer primary key, a secondary index and padding columns. It is not chosen because it resembles any particular application. It is chosen because it is:

• deterministic in shape — ten tables, 200,000 rows each, every time;
• big enough to be slow — around 500 MB means a restore takes minutes, not milliseconds, so restore duration is a real measurement;
• small enough to fit — on a laptop with 8 GB allocated to Docker;
• trivially checkable — COUNT(*) and CHECKSUM TABLE per table give an exact fingerprint, which is precisely what part 8 compares between a live cluster and a restored one.

That last property is the reason this dataset exists at all. The entire argument of this lab ends in a table of checksums.

Why the 400–700 MB band rather than a fixed number: data_length + index_length from information_schema is an InnoDB estimate. It moves with page fill factor and with whatever the scenarios have written. A band asserts “this is the right dataset” without being brittle. The recorded validation run measured SIZE_MB=504 before any scenario ran and RESTORED_SIZE_MB=511 after a whole-cluster restore — both inside the band, and the difference between them is exactly why the band is there.

Loading it

- name: Wait until a writable MySQL primary answers
  kubernetes.core.k8s_exec:
    namespace: "{{ mysql_namespace }}"
    pod: "{{ mysql_cluster_name }}-mysql-0"
    container: mysql
    command: >
      mysql -uroot -p{{ mysql_root_password }} -N -e "SELECT 1"
  register: mysql_ping
  until: mysql_ping.rc == 0
  retries: 30
  delay: 5

ansible/roles/load_sysbench/tasks/main.yml

The cluster reported ready at the end of part 2, but readiness and accepting queries are not the same instant. SELECT 1 in a retry loop is the cheapest possible statement that settles the question.

Note container: mysql. The pod has four containers (mysql, exporter, pt-heartbeat, xtrabackup), so every exec in this lab names one. Omitting it gets you whichever container Kubernetes considers default, which is not a guarantee worth relying on.

- name: Recreate sysbench database on the source
  kubernetes.core.k8s_exec:
    pod: "{{ mysql_cluster_name }}-mysql-0"
    container: mysql
    command: >
      mysql -uroot -p{{ mysql_root_password }} -e
      "DROP DATABASE IF EXISTS {{ sysbench_database }};
      CREATE DATABASE {{ sysbench_database }}"

DROP DATABASE before loading makes load-data idempotent in the only sense that matters: running it twice produces the same dataset, not a doubled one. It is also the reset button. After a suite run has left markers, canaries and probe tables scattered through sbtest, ./scripts/lab.sh load-data returns the lab to a known state without rebuilding the cluster.

It runs against lab-mysql-0 directly rather than through HAProxy, because dropping a database is an operation you want aimed at a server you named.

The prepare Job

- name: Delete previous sysbench prepare job if present
  kubernetes.core.k8s:
    state: absent
    api_version: batch/v1
    kind: Job
    name: sysbench-prepare
    wait: true
    wait_timeout: 60

- name: Run sysbench oltp prepare (10 tables, ~500MB)
  kubernetes.core.k8s:
    state: present
    definition:
      apiVersion: batch/v1
      kind: Job
      metadata: { name: sysbench-prepare, namespace: "{{ mysql_namespace }}" }
      spec:
        backoffLimit: 1
        ttlSecondsAfterFinished: 86400
        template:
          spec:
            restartPolicy: Never
            containers:
              - name: sysbench
                image: "{{ sysbench_image }}"        # ubuntu:24.04
                env:
                  - name: MYSQL_PWD
                    valueFrom:
                      secretKeyRef: { name: "{{ mysql_secrets_name }}", key: root }
                command:
                  - /bin/bash
                  - -c
                  - |
                    set -euo pipefail
                    export DEBIAN_FRONTEND=noninteractive
                    apt-get update -qq
                    apt-get install -y -qq sysbench mysql-client
                    sysbench oltp_read_write \
                      --db-driver=mysql \
                      --mysql-host={{ mysql_cluster_name }}-haproxy \
                      --mysql-port=3306 \
                      --mysql-user=root \
                      --mysql-password="$MYSQL_PWD" \
                      --mysql-db={{ sysbench_database }} \
                      --tables={{ sysbench_tables }} \
                      --table-size={{ sysbench_table_size }} \
                      --mysql-storage-engine=innodb \
                      prepare

A Job inside the cluster, not sysbench on the laptop. The load generator runs next to the database, on the cluster network, so the measurement is not shaped by a NodePort hop and no host-side tooling is required. It is also the same shape you would use to load data into a real cluster you cannot reach directly.

--mysql-host=lab-haproxy — the load goes through the proxy, deliberately. That exercises the path a real client uses and means the load does not break if the source is not lab-mysql-0.

ubuntu:24.04 plus apt-get install sysbench is the honest trade. A purpose-built image would start faster; a stock base image with a visible two-line install means anyone can read exactly what is running and reproduce it without a registry. The cost is one apt-get per load, which happens once per lab lifetime.

MYSQL_PWD from a secretKeyRef rather than --mysql-password=mysql on the command line. A password in command: is visible in kubectl describe pod and in the process list inside the container. The environment variable is the conventional MySQL client mechanism and keeps it out of both.

backoffLimit: 1 — if sysbench fails, it is a real failure (wrong credentials, no such host, out of disk). Retrying five times just delays the error message.

ttlSecondsAfterFinished: 86400 — the finished Job and its logs stick around for a day, so kubectl logs job/sysbench-prepare still works when you want to know how long the load took, then it cleans itself up.

set -euo pipefail is in every inline script in this repo. Without -e, a failed apt-get is followed by a sysbench: command not found whose exit code becomes the Job’s — and the real error scrolls past.

Waiting, then asserting — again

- name: Wait for sysbench prepare job
  kubernetes.core.k8s_info:
    api_version: batch/v1
    kind: Job
    name: sysbench-prepare
  register: sb_job
  until:
    - sb_job.resources | length == 1
    - sb_job.resources[0].status.succeeded | default(0) | int == 1
      or (sb_job.resources[0].status.failed | default(0) | int) > 0
  retries: 120
  delay: 15

- name: Fail if sysbench prepare did not succeed
  ansible.builtin.assert:
    that:
      - sb_job.resources[0].status.succeeded | default(0) | int == 1
    fail_msg: >-
      sysbench-prepare job failed. Inspect logs with
      kubectl logs -n {{ mysql_namespace }} job/sysbench-prepare

The same wait-for-terminal-then-assert split from part 2, with 30 minutes of patience (120 × 15s) because a first run downloads packages before it writes a single row. And note the fail_msg: it contains the exact command to run next. An error message that tells you what to type is worth the two lines it costs.

The ANALYZE TABLE nobody expects

- name: Analyze sysbench tables so information_schema sizes are current
  kubernetes.core.k8s_exec:
    command: >
      mysql -uroot -p{{ mysql_root_password }} -e
      "ANALYZE TABLE sbtest.sbtest1, …, sbtest.sbtest10"

Without this, the size assertion that follows is a coin flip. InnoDB’s information_schema.tables.data_length is served from cached statistics that are refreshed on a sampling schedule, and immediately after a bulk load they can still report near-zero. ANALYZE TABLE forces a refresh so the next query sees the data that is actually on disk.

This is a small thing that cost real debugging time, and it is the kind of detail that only shows up when you make your setup assert its own success instead of printing a number for a human to glance at.

- name: Measure sysbench data size on the source
  kubernetes.core.k8s_exec:
    command: >
      mysql -uroot -p{{ mysql_root_password }} -N -e "SELECT COUNT(*) FROM information_schema.tables
      WHERE table_schema='{{ sysbench_database }}' AND table_name LIKE 'sbtest%';
      SELECT ROUND(SUM(data_length+index_length)/1024/1024)
      FROM information_schema.tables
      WHERE table_schema='{{ sysbench_database }}'"
  register: size_query

- name: Require 10 sysbench tables and size in the documented band
  ansible.builtin.assert:
    that:
      - (size_query.stdout_lines[0] | trim | int) == (sysbench_tables | int)
      - (size_query.stdout_lines[1] | trim | int) >= (data_size_min_mb | int)
      - (size_query.stdout_lines[1] | trim | int) <= (data_size_max_mb | int)

-N suppresses column headers so stdout_lines[0] and [1] are the two values, in order. Two statements in one -e produce two lines. It is terse, and it is the pattern the whole lab uses for reading values out of MySQL.

load-data that ends in an assertion is the point. It does not report that it ran; it reports that the dataset exists with the right dimensions. Everything downstream is allowed to assume that.

The verify playbook

./scripts/lab.sh verify

load-data proves the data got in. verify answers a broader question: is this lab currently in a state where testing backups means anything? It is the command to run after a reboot, after an interrupted suite, or any time you are not sure what state you left things in.

- name: Ensure kubeconfig exists
  ansible.builtin.stat:
    path: "{{ kubeconfig_path }}"
  register: kubeconfig

- name: Fail if cluster was not created
  ansible.builtin.assert:
    that: kubeconfig.stat.exists
    fail_msg: "Missing {{ kubeconfig_path }}. Run `make deploy` first."

ansible/roles/verify_cluster/tasks/main.yml

The cheapest possible check first, with an actionable message. Everything after it would fail with a connection error that says nothing useful.

Discovering the topology instead of assuming it

pods=$(kubectl -n "$NS" get pods -l app.kubernetes.io/instance=lab,app.kubernetes.io/component=database \
  -o jsonpath='{range .items[*]}{.metadata.name}{"\n"}{end}')
replica_ok=0
primary=""
for p in $pods; do
  ro=$(kubectl -n "$NS" exec "$p" -c mysql -- mysql -uroot -pmysql -N -e "SELECT @@read_only" | tr -d '\r')
  host=$(kubectl -n "$NS" exec "$p" -c mysql -- mysql -uroot -pmysql -N -e "SELECT @@hostname" | tr -d '\r')
  echo "POD $p hostname=$host read_only=$ro"
  if [ "$ro" = "0" ]; then
    primary="$p"
  else
    io=$(kubectl -n "$NS" exec "$p" -c mysql -- mysql -uroot -pmysql -N -e \
      "SELECT SERVICE_STATE FROM performance_schema.replication_connection_status LIMIT 1" | tr -d '\r')
    sql=$(kubectl -n "$NS" exec "$p" -c mysql -- mysql -uroot -pmysql -N -e \
      "SELECT SERVICE_STATE FROM performance_schema.replication_applier_status LIMIT 1" | tr -d '\r')
    echo "POD $p replica_io=$io replica_sql=$sql"
    if [ "$io" = "ON" ] && [ "$sql" = "ON" ]; then
      replica_ok=$((replica_ok+1))
    fi
  fi
done
echo "PRIMARY=$primary"
echo "REPLICA_OK=$replica_ok"

Three design decisions in that loop, all of which recur in every scenario.

The primary is discovered by SELECT @@read_only, never assumed. lab-mysql-0 is usually the source. It is not necessarily the source — Orchestrator can fail over, and a restore rebuilds the topology. A test that hard-codes the primary eventually writes its canary to a read-only replica and reports a confusing failure. Asking the server which role it is playing costs one query.

Replication health comes from performance_schema, not SHOW REPLICA STATUS. replication_connection_status.SERVICE_STATE is the receiver thread; replication_applier_status.SERVICE_STATE is the applier. Both are single-value queries returning ON or OFF. SHOW REPLICA STATUS\G returns fifty fields of Name: value text that has to be grepped, which is fine for a human and poor for an assertion. (Part 8 does grep SHOW REPLICA STATUS — because it needs Seconds_Behind_Source and the error fields, which the performance_schema tables do not present as conveniently.)

tr -d '\r' on every captured value. kubectl exec without a TTY still yields carriage returns in some environments, and "ON\r" != "ON" is a comparison failure that looks like a replication failure. It appears everywhere in this lab for that reason.

Structured output, then parsed

- name: Parse topology lines
  ansible.builtin.set_fact:
    primary_name: "{{ topology.stdout_lines | select('match', '^PRIMARY=') | map('regex_replace', '^PRIMARY=', '') | first }}"
    replica_ok:   "{{ topology.stdout_lines | select('match', '^REPLICA_OK=') | map('regex_replace', '^REPLICA_OK=', '') | first }}"
    sysbench_table_count: "{{ topology.stdout_lines | select('match', '^SYSBENCH_TABLES=') | ... | first }}"
    size_mb: "{{ topology.stdout_lines | select('match', '^SIZE_MB=') | ... | first }}"

The shell script prints KEY=value lines; Ansible selects them by prefix. This convention runs through the entire lab — every scenario emits KEY=value evidence — and it buys three things at once:

1. the script can be run by hand and read by a human;
2. Ansible can parse it without JSON plumbing inside a shell heredoc;
3. the same lines become the recorded evidence in the run report (part 9) verbatim, so what the documentation quotes is literally what the command printed.

The assertions

- name: Require a writable source and at least one applying replica
  ansible.builtin.assert:
    that:
      - primary_name | length > 0
      - (replica_ok | int) >= 1
    fail_msg: "Replication is not healthy. Output:\n{{ topology.stdout }}"

- name: Require sysbench 10 tables and size around 500MB
  ansible.builtin.assert:
    that:
      - (sysbench_table_count | int) == (sysbench_tables | int)
      - (size_mb | int) >= (data_size_min_mb | int)
      - (size_mb | int) <= (data_size_max_mb | int)

Note that fail_msg embeds the full captured output. When this fails you get the per-pod detail immediately, without re-running anything.

The end-to-end check

- name: Check Prometheus scrape of mysqld
  ansible.builtin.uri:
    url: "http://127.0.0.1:{{ kind_host_prometheus_port }}/api/v1/query?query=up%7Bjob%3D%22mysql%22%7D"
    return_content: true
  register: prom_up

- name: Require at least one MySQL Prometheus target up
  ansible.builtin.assert:
    that:
      - (prom_up.json.data.result | selectattr('value.1', 'equalto', '1') | list | length) >= 1
    fail_msg: "Prometheus is not scraping job=mysql with up=1. Body={{ prom_up.content }}"

This single HTTP request validates a surprising amount at once: the kind port mapping from part 1, the NodePort service, Prometheus itself, the ServiceMonitor, the headless metrics service, the exporter sidecar, and the monitor account’s password. It is the only check in the lab that runs from outside the cluster, over the same path a human uses — and that is deliberate. Everything else talks to the Kubernetes API; this one proves the lab is reachable the way the README says it is.

The recorded validation run’s health log ends with exactly that:

replica_io=ON replica_sql=ON
Prometheus mysql up ok

Monitoring, and why it is only three files

- name: Install kube-prometheus-stack
  kubernetes.core.helm:
    name: kps
    chart_ref: prometheus-community/kube-prometheus-stack
    chart_version: "{{ kube_prometheus_chart_version }}"     # 88.5.2
    release_namespace: "{{ monitoring_namespace }}"
    wait: true
    wait_timeout: "15m0s"
    values_files:
      - "{{ repo_root }}/k8s/monitoring/kube-prometheus-values.yaml"

ansible/roles/monitoring/tasks/main.yml

Fifteen minutes of timeout because a first install pulls Prometheus, Grafana, node-exporter, kube-state-metrics and the operator.

grafana:
  adminPassword: admin
  service: { type: NodePort, nodePort: 30300 }

alertmanager:
  enabled: false

prometheus:
  service: { type: NodePort, nodePort: 30091 }
  prometheusSpec:
    retention: 2d
    serviceMonitorSelectorNilUsesHelmValues: false
    podMonitorSelectorNilUsesHelmValues: false
    ruleSelectorNilUsesHelmValues: false
    serviceMonitorNamespaceSelector: {}
    ruleNamespaceSelector: {}

kubeEtcd:          { enabled: false }
kubeControllerManager: { enabled: false }
kubeScheduler:     { enabled: false }

ansible/roles/monitoring/templates/kube-prometheus-values.yaml.j2 (abridged)

alertmanager.enabled: false — nobody is on call for a laptop. This lab’s sibling ClickHouse lab does route alerts to Slack; here it would be pure overhead.

retention: 2d — long enough to look back over a full suite run (about 25 minutes of scenario time), short enough that Prometheus never becomes the biggest thing on the disk.

The four …SelectorNilUsesHelmValues: false settings are the ones that actually matter and the ones most people hit. By default the chart makes Prometheus discover only ServiceMonitors carrying its own Helm release labels. Ours lives in a different namespace and describes a database, not a chart component. Setting these to false, plus empty namespace selectors, tells Prometheus to consider every ServiceMonitor in the cluster. Without them the lab deploys cleanly, Grafana loads, every panel is empty, and nothing anywhere says why.

kubeEtcd, kubeControllerManager, kubeScheduler disabled — on kind these control-plane components do not expose metrics endpoints the chart’s default scrape config can reach, so leaving them on produces three permanently red targets. Three false alarms teach you to ignore red targets, which is the exact habit a monitoring setup should not teach.

Connecting Prometheus to MySQL

apiVersion: monitoring.coreos.com/v1
kind: ServiceMonitor
metadata:
  name: mysql
  namespace: mysql
  labels: { release: kps, app: mysql-lab }
spec:
  namespaceSelector: { matchNames: [mysql] }
  selector:
    matchLabels: { app: mysql-metrics }
  endpoints:
    - port: metrics
      path: /metrics
      interval: 15s
      honorLabels: true
      relabelings:
        - action: replace
          targetLabel: job
          replacement: mysql

ansible/roles/monitoring/templates/servicemonitors.yaml.j2

It selects the headless mysql-metrics service from part 2, so Prometheus scrapes each MySQL pod individually rather than a load-balanced endpoint.

The relabeling rewrites the job label to the literal mysql. Without it the job name is derived from the service name and would change if the service were ever renamed — and the verify playbook’s up{job="mysql"} query, the Grafana dashboard’s panels, and every future alert would all quietly stop matching. One relabel rule buys a stable contract.

The dashboard ships as a ConfigMap with grafana_dashboard: "1", which the chart’s sidecar watches for:

apiVersion: v1
kind: ConfigMap
metadata:
  name: grafana-mysql-lab
  labels:
    grafana_dashboard: "1"
data:
  mysql-lab.json: |
    { … "panels": [ … ] }

Five panels: up{job="mysql"}, mysql_up, mysql_slave_status_slave_io_running, rate(mysql_global_status_questions[1m]) and mysql_slave_status_seconds_behind_master. Dashboard-as-a-ConfigMap means it is version-controlled and redeployed with everything else, rather than being something someone clicked together and will lose on the next rebuild.

One Jinja detail, since this file is a template that contains Grafana templating:

"legendFormat": "{{ '{{' }} instance {{ '}}' }}"

Grafana’s legend syntax and Jinja’s expression syntax both use double braces, so the braces are emitted as literals. The same escaping trick appeared in part 1 for docker inspect --format.

Doing it by hand

export KUBECONFIG="$PWD/.kube/config"

# 1. reset and load
kubectl -n mysql exec lab-mysql-0 -c mysql -- \
  mysql -uroot -pmysql -e "DROP DATABASE IF EXISTS sbtest; CREATE DATABASE sbtest"

kubectl -n mysql delete job sysbench-prepare --ignore-not-found
kubectl -n mysql apply -f - <<'YAML'
apiVersion: batch/v1
kind: Job
metadata: { name: sysbench-prepare, namespace: mysql }
spec:
  backoffLimit: 1
  ttlSecondsAfterFinished: 86400
  template:
    spec:
      restartPolicy: Never
      containers:
        - name: sysbench
          image: ubuntu:24.04
          env:
            - name: MYSQL_PWD
              valueFrom: { secretKeyRef: { name: lab-secrets, key: root } }
          command: ["/bin/bash","-c"]
          args:
            - |
              set -euo pipefail
              export DEBIAN_FRONTEND=noninteractive
              apt-get update -qq && apt-get install -y -qq sysbench mysql-client
              sysbench oltp_read_write --db-driver=mysql \
                --mysql-host=lab-haproxy --mysql-port=3306 \
                --mysql-user=root --mysql-password="$MYSQL_PWD" \
                --mysql-db=sbtest --tables=10 --table-size=200000 \
                --mysql-storage-engine=innodb prepare
YAML

kubectl -n mysql wait --for=condition=complete job/sysbench-prepare --timeout=30m

# 2. refresh statistics, then measure
kubectl -n mysql exec lab-mysql-0 -c mysql -- mysql -uroot -pmysql -e \
  "ANALYZE TABLE sbtest.sbtest1,sbtest.sbtest2,sbtest.sbtest3,sbtest.sbtest4,sbtest.sbtest5,
                 sbtest.sbtest6,sbtest.sbtest7,sbtest.sbtest8,sbtest.sbtest9,sbtest.sbtest10"

kubectl -n mysql exec lab-mysql-0 -c mysql -- mysql -uroot -pmysql -N -e \
  "SELECT COUNT(*) FROM information_schema.tables
     WHERE table_schema='sbtest' AND table_name LIKE 'sbtest%';
   SELECT ROUND(SUM(data_length+index_length)/1024/1024)
     FROM information_schema.tables WHERE table_schema='sbtest';"

# 3. verify by hand
for p in lab-mysql-0 lab-mysql-1; do
  echo -n "$p read_only="
  kubectl -n mysql exec $p -c mysql -- mysql -uroot -pmysql -N -e "SELECT @@read_only"
done
kubectl -n mysql exec lab-mysql-1 -c mysql -- mysql -uroot -pmysql -N -e \
  "SELECT SERVICE_STATE FROM performance_schema.replication_connection_status LIMIT 1;
   SELECT SERVICE_STATE FROM performance_schema.replication_applier_status LIMIT 1"

curl -s 'http://127.0.0.1:9091/api/v1/query?query=up%7Bjob%3D%22mysql%22%7D' | head -c 400

Expected from step 2:

10
504

What can go wrong here

• The size assertion fails with a small number. ANALYZE TABLE did not run, or ran before the load finished. The data is there; the statistics are stale.
• The size assertion fails with a large number. A previous suite run left probe tables behind — t03’s t03_delta, t04’s t04_stream_probe, t12’s post_backup_writes_*. load-data clears them.
• sysbench cannot resolve lab-haproxy. HAProxy is disabled, or the Job is in the wrong namespace. Service DNS is namespace-scoped.
• Grafana loads but every panel is empty. The ServiceMonitor selector settings. Check up{job="mysql"} in Prometheus first — if that is empty, the problem is discovery, not Grafana.
• verify says REPLICA_OK=0. The replica’s receiver or applier thread is stopped. kubectl -n mysql exec lab-mysql-1 -c mysql -- mysql -uroot -pmysql -e "SHOW REPLICA STATUS\G" has the error text.

Next

There is a database with a known shape, a replica applying its changes, and a check that says so. Part 4 starts taking backups — what the operator actually does when you create a PerconaServerMySQLBackup, how to push that work onto the replica instead of the source, what an incremental really contains, and how to confirm the bytes reached object storage rather than trusting a green status.