MySQL backup lab, part 6 – restoring in place, and what it costs

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

---

A restore is the same shape as a backup — an object, and an operator that reacts:

apiVersion: ps.percona.com/v1
kind: PerconaServerMySQLRestore
metadata:
  name: t07-restore
  namespace: mysql
spec:
  clusterName: lab
  backupName: t01-full

What it does is not remotely the same shape. A backup is a read that runs alongside a working database. An in-place restore stops the cluster, replaces the data directory of the source, brings MySQL back, and rebuilds the replicas from the restored source.

The cluster is down for the whole of it. That is not a limitation of this lab; it is what “restore this database over itself” means. The recorded durations below are the honest cost on a laptop for a 500 MB dataset, and the only reasonable use for them is as a shape — restores take minutes, not seconds, and they scale with data.

Two scenarios: t07 proves a restore genuinely replaces state rather than merging into it, and t08 does the version you will actually need at 02:00. Measured: 161 s and 160 s.

The plain operator path: make restore

The shipped restore_cluster role is the on-demand restore, and it is built around one idea that the scenarios then formalise: a restore is only verified if something that existed before it is gone afterwards.

- name: Find the current writable source
  ansible.builtin.shell: |
    set -euo pipefail
    export KUBECONFIG="{{ kubeconfig_path }}"
    NS="{{ mysql_namespace }}"
    pods=$(kubectl -n "$NS" get pods -l app.kubernetes.io/instance={{ mysql_cluster_name }},app.kubernetes.io/component=database \
      -o jsonpath='{range .items[*]}{.metadata.name}{"\n"}{end}')
    for p in $pods; do
      ro=$(kubectl -n "$NS" exec "$p" -c mysql -- mysql -uroot -p{{ mysql_root_password }} -N -e "SELECT @@read_only" 2>/dev/null | tr -d '\r')
      if [ "$ro" = "0" ]; then echo "$p"; exit 0; fi
    done
    exit 1

ansible/roles/restore_cluster/tasks/main.yml

The primary is discovered, not assumed — for the reasons part 3 gave, and with an extra one here: this role runs after backup scenarios that may have triggered a topology change. exit 1 if no writable pod exists means “there is no primary” fails loudly rather than writing the canary somewhere useless.

The canary

CREATE TABLE IF NOT EXISTS sbtest.canary_after_backup (id INT PRIMARY KEY, note VARCHAR(64));
INSERT INTO sbtest.canary_after_backup VALUES (1, 'post-backup canary')
  ON DUPLICATE KEY UPDATE note=VALUES(note);

A table created after the backup was taken. It cannot be in the backup. So after a successful restore it must not exist — and if it does, the restore did not actually replace the data directory.

ON DUPLICATE KEY UPDATE makes the write idempotent, so repeated runs do not fail on a primary-key collision.

- name: Require canary before restore
  ansible.builtin.assert:
    that:
      - (canary_before.stdout_lines | select('match', '^[0-9]+$') | first | int) == 1

The canary is confirmed present before the restore. Without this check, a canary that silently failed to be created would produce an “absent afterwards” result that proves nothing at all. Verifying the precondition is what turns the observation into evidence.

The restore, and waiting for it

- name: Delete previous restore object if present
  kubernetes.core.k8s:
    state: absent
    api_version: ps.percona.com/v1
    kind: PerconaServerMySQLRestore
    name: lab-restore
    wait: true
    wait_timeout: 120

- name: Wait for restore to succeed
  kubernetes.core.k8s_info:
    api_version: ps.percona.com/v1
    kind: PerconaServerMySQLRestore
    name: lab-restore
  register: rst
  until:
    - rst.resources | length == 1
    - (rst.resources[0].status.state | default('') | lower) in ['succeeded', 'ready', 'done', 'failed', 'error']
  retries: 120
  delay: 15

Restore objects are immutable like backup objects, so the same delete-first pattern applies. 120 × 15s is thirty minutes of patience — a restore that includes stopping pods, pulling a backup from object storage, and rebuilding a replica is genuinely slow, and a too-short timeout produces a “failure” in a run that was working.

- name: Wait for MySQL pods after restore
  kubernetes.core.k8s_info:
    kind: Pod
    label_selectors:
      - app.kubernetes.io/instance={{ mysql_cluster_name }}
      - app.kubernetes.io/component=database
  register: mysql_pods
  until:
    - mysql_pods.resources | length >= 2
    - mysql_pods.resources | rejectattr('status.phase', 'equalto', 'Running') | list | length == 0
  retries: 90
  delay: 10

status.state: Succeeded on the restore object is not the end. The operator considers its work done; the pods are still coming back. Querying a database that is mid-restart produces connection errors that look like restore failures. Hence a second wait on the pods, and then this on the query itself:

- name: Measure restored sysbench dataset
  kubernetes.core.k8s_exec:
    …
  register: restored
  retries: 20
  delay: 6
  until: restored.rc == 0

Retries on the query, not just on the pods, because Running and “accepting connections” are still two different moments.

The final assertion checks three things at once:

- name: Require restored cluster has 10 sysbench tables and no canary
  ansible.builtin.assert:
    that:
      - (restored.stdout_lines | select('match', '^[0-9]+$') | list | first | int) == (sysbench_tables | int)
      - (restored.stdout_lines | select('match', '^[0-9]+$') | list | last | int) == 0
      - (restored.stdout_lines | select('match', '^[0-9]+$') | list)[1] | int >= (data_size_min_mb | int)

Ten tables (the data came back), no canary (the state was replaced), and a plausible size (the tables are not empty shells). All three are needed: any two of them pass in a scenario where the restore did something wrong.

t07 — Whole-cluster restore rolls back post-backup writes

Proves: a restore replaces cluster state rather than merging into it. Measured: 161 s.

The scenario is the make restore flow with the health gate and result recording wrapped around it (part 9), and one important addition: it gates on cluster health after the restore as well as before.

- name: Gate on cluster health after the restore
  ansible.builtin.include_role:
    name: test_common
    tasks_from: health_gate.yml

That gate requires a writable primary, ten sysbench tables, and — crucially — REPLICA_OK equal to mysql_size - 1. A restore rebuilds the replica from the restored source; a restore that left the replica broken would otherwise pass this scenario and then poison t08, t09 and everything after.

Evidence:

PRIMARY=lab-mysql-0
CANARY_BEFORE_RESTORE=1
RESTORE_BACKUP=t01-full
RESTORE_STATE=Succeeded
RESTORED_TABLES=10
RESTORED_SIZE_MB=511
CANARY_AFTER_RESTORE=0
HEALTH=SYSBENCH_TABLES=10 | PRIMARY=lab-mysql-0 | REPLICA_OK=1 | BINLOG_SERVER=lab-binlog-server-0=Running

RESTORED_SIZE_MB=511 against the 504 MB measured at backup time — inside the 400–700 MB band from part 3, and a good illustration of why that is a band. The figure is an InnoDB estimate over a freshly restored data directory with different page fill; expecting it to match to the megabyte would produce a test that fails for no reason.

The lesson in t07 is not that it passed. It is what “passed” means: every write that happened after t01-full is gone. The canary is the visible one. In a real incident, the invisible ones are four hours of customer transactions. That is the trade an in-place restore makes, and it is the reason part 7 exists.

t08 — Restore recovers a dropped table

Proves: the ordinary disaster is survivable. Measured: 160 s.

t07 is an argument. t08 is the Tuesday.

- name: Record sbtest10 before dropping it
  …
  "SELECT CONCAT('ROWS_BEFORE_DROP=', COUNT(*)) FROM sbtest.sbtest10;"

- name: Drop the table, as a tired human would at 02:00
  …
  "DROP TABLE sbtest.sbtest10;
   SELECT CONCAT('TABLES_AFTER_DROP=', COUNT(*)) FROM information_schema.tables
     WHERE table_schema='sbtest' AND table_name LIKE 'sbtest%';"

- name: Require the table to actually be gone
  ansible.builtin.assert:
    that:
      - ('TABLES_AFTER_DROP=' ~ ((sysbench_tables | int) - 1)) in t08_drop.stdout

ansible/roles/t08_restore_dropped_table/tasks/main.yml

COUNT(*), not table_rows. information_schema.table_rows is the InnoDB estimate from part 3 — fine for sizing, useless as an exact before/after comparison. The whole scenario turns on the row count being identical afterwards, so it pays for the full scan.

And, again, the damage is confirmed before the repair. TABLES_AFTER_DROP=9 is what makes the subsequent TABLES_AFTER_RESTORE=10 mean something.

The restore is the same object as t07’s, naming the same backup:

spec:
  clusterName: lab
  backupName: t01-full

Then:

- name: Require sbtest10 back with its rows
  ansible.builtin.assert:
    that:
      - ('TABLES_AFTER_RESTORE=' ~ sysbench_tables) in t08_after.stdout
      - (t08_after.stdout_lines | select('match', '^ROWS_AFTER_RESTORE=') | first).split('=')[1] | int
        == (t08_before.stdout_lines | select('match', '^ROWS_BEFORE_DROP=') | first).split('=')[1] | int

The row count after is compared to the row count captured before, not to the literal 200,000. Comparing against a constant would be a test of group_vars; comparing against the observed prior value is a test of the restore.

Measured:

ROWS_BEFORE_DROP=200000
TABLES_AFTER_DROP=9
RESTORE_STATE=Succeeded
TABLES_AFTER_RESTORE=10
ROWS_AFTER_RESTORE=200000
HEALTH=SYSBENCH_TABLES=10 | PRIMARY=lab-mysql-0 | REPLICA_OK=1 | BINLOG_SERVER=lab-binlog-server-0=Running

The uncomfortable part

t08 recovered sbtest10 by rolling the entire cluster back to t01-full. Every table. Every write since that backup, on every table, in every schema.

To recover one dropped table, the cluster lost everything newer. In this lab that is invisible, because the dataset is static between scenarios. In production that trade is often unacceptable, and the honest summary of parts 6 is:

An in-place restore from a full backup recovers the database as it was when the backup was taken. If what you need is “as it was one minute before the mistake”, a full backup alone cannot give it to you.

That gap is exactly the size of part 7.

What restores cost, measured

Read these as shape, not as targets. Three observations do hold:

• A plain restore of ~500 MB on a laptop is a couple of minutes, most of it spent stopping and restarting pods rather than moving bytes.
• PITR roughly doubles it, because the binlog replay is extra work on top of the same physical restore — and in this lab a chunk of each PITR scenario is a deliberate 120-second wait for binlogs to be checkpointed.
• Restoring into a separate cluster was the fastest of all, at 65 seconds, and it never stopped the live database for a moment. That is not a coincidence, and it is the argument part 8 makes.

Doing it by hand

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

# a canary the backup cannot contain
kubectl -n mysql exec lab-mysql-0 -c mysql -- mysql -uroot -pmysql -e \
  "CREATE TABLE IF NOT EXISTS sbtest.canary_after_backup (id INT PRIMARY KEY, note VARCHAR(64));
   INSERT INTO sbtest.canary_after_backup VALUES (1,'written after the backup')
     ON DUPLICATE KEY UPDATE note=VALUES(note);"

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='canary_after_backup'"   # expect 1

# restore over the cluster
kubectl -n mysql apply -f - <<'YAML'
apiVersion: ps.percona.com/v1
kind: PerconaServerMySQLRestore
metadata: { name: t07-restore, namespace: mysql }
spec:
  clusterName: lab
  backupName: t01-full
YAML

# watch it: the cluster goes away and comes back
kubectl -n mysql get ps-restore t07-restore -w
kubectl -n mysql get pods -w

kubectl -n mysql get ps-restore t07-restore \
  -o jsonpath='{.status.state} {.status.stateDescription}{"\n"}'

# verify: data back, canary gone
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 COUNT(*) FROM information_schema.tables
     WHERE table_schema='sbtest' AND table_name='canary_after_backup';"
# expect: 10, then 0

# and check the replica was rebuilt, not just the source
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"
# expect: ON, ON

Or:

./scripts/lab.sh restore   # the on-demand flow, canary-verified
./scripts/lab.sh t07
./scripts/lab.sh t08

Both scenarios are destructive to the lab’s current state — they roll the cluster back to t01-full. That is fine in a lab and is the reason the suite’s ordering is fixed (part 9).

What can go wrong here

• The restore reports Succeeded and the data is wrong. Check what you restored. backupName: t01-full restores the state at 10:13:48, not the state five minutes ago.
• Queries fail right after the restore succeeds. The pods are still restarting. Wait for Running, then retry the query — both waits exist in the role for this reason.
• The replica does not come back. The source restored and the rebuild failed. The health gate catches it; kubectl -n mysql logs lab-mysql-1 -c mysql says why. Continuing to the next scenario in this state is how one failure becomes five.
• The restore hangs in a non-terminal state. Look for the restore’s Job and read its logs; the most common cause is the backup’s data not being where the storage definition says it is.
• You restored the wrong cluster. clusterName is a field, and there is a second cluster in the namespace during part 8.

Next

An in-place restore can only take you back to the instant the backup was taken. Part 7 closes the gap: the binlog server that streams binary logs to object storage as they are written, recovery to a timestamp and to an exact transaction, a date format that cost real debugging time, and the subtle trap of replaying an archive that belongs to a history your database has already abandoned.