DB-Ex

MySQL – a replication lab with failover, backups, and scale-out on Kubernetes

Published By Krzysztof Książek Lab

Lab write-up. A hands-on environment built and run by the author, not a customer engagement.

Environment described in the article: Percona Server 8.4.10-10, Percona Operator for MySQL (Helm chart 1.2.0), local kind Kubernetes cluster.

Reading about replication failover is not the same as watching it happen. You learn something different when you delete the source pod yourself, watch Orchestrator pick a new primary, and then check whether the old source comes back as a well-behaved replica or as a second writable server. The same goes for backups: a backup you have never restored is a hypothesis, not a backup.

So this is a lab built to exercise all four of those things on a single Docker host – replication, failover, backup and restore, and scale-out – with Ansible as the installer and Kubernetes as the runtime source of truth. Every count, log line, and topology dump below came out of an actual run.

The shape of it

Local Kuberneteskind, 1 control-plane + 2 workers
MySQLPercona Server 8.4.10-10, async GTID replication, 1 source + 1 replica
ProxyHAProxy in front of the cluster
FailoverOrchestrator (automatic primary election)
OperatorPercona Operator for MySQL (ps-operator), Helm chart 1.2.0
Datasysbench oltp_read_write, 10 tables × 200,000 rows
BackupsPercona XtraBackup on a schedule, stored in in-cluster MinIO
Monitoringkube-prometheus-stack + mysqld_exporter sidecars

The choice of operator matters here and it is easy to get wrong. Percona ships two: the PXC operator, which is Galera, and the ps-operator, which is classic MySQL. This lab is about binlog and GTID streams from a writable source to read-only replicas, so it needs the second one with mysql.clusterType: async plus Orchestrator. Group Replication is not in play either. If you substitute the PXC operator you will get a working cluster that teaches you an entirely different set of lessons.

The whole lab — Ansible roles, custom resources, playbooks and tests — lives in a public repository: github.com/ghkrzysztof-ksiazek/database-labs. This one is the 20260910-mysql-replication-setup directory, and every command in this post is run from inside it:

git clone https://github.com/ghkrzysztof-ksiazek/database-labs.git
cd database-labs/20260910-mysql-replication-setup

Four commands to a running cluster with data in it:

./scripts/lab.sh bootstrap
./scripts/lab.sh deploy
./scripts/lab.sh load-data
./scripts/lab.sh verify

The deploy playbook finishes with ok=40 failed=0. Grafana answers 200 on http://127.0.0.1:3001/login and Prometheus reports ready. Host ports are deliberately 3307 / 3001 / 9091 rather than the obvious 3306 / 3000 / 9090, so this lab can run alongside a ClickHouse lab on the same machine without a fight.

The cluster definition

The PerconaServerMySQL custom resource is where the interesting decisions live. The replication configuration is unremarkable in the best way:

spec:
  mysql:
    clusterType: async
    autoRecovery: true
    size: 2
    configuration: |
      [mysqld]
      gtid_mode=ON
      enforce_gtid_consistency=ON
      log_bin=ON
      binlog_format=ROW
      binlog_expire_logs_seconds=86400
      innodb_buffer_pool_size=256M
      max_connections=80
      skip_name_resolve

Two things need an override. The operator strongly prefers three MySQL members and exactly does not want a single Orchestrator, so a two-member lab has to say so explicitly:

  unsafeFlags:
    mysqlSize: true
    orchestratorSize: true
    proxySize: true

The flag name is honest – this is unsafe for production. Two members means losing one leaves you with no redundancy at all, and one Orchestrator means your failover manager is a single point of failure. For a laptop where the whole point is to break things on purpose, it is the right trade.

Orchestrator itself is tuned to be impatient, because waiting the production defaults out would make the demo tedious:

  orchestrator:
    enabled: true
    size: 1
    configuration: '{"InstancePollSeconds": 2, "RecoveryPeriodBlockSeconds": 15}'

Each MySQL pod gets a mysqld_exporter sidecar on port 9104, scraped through a ServiceMonitor named mysql.

The dataset

A Kubernetes Job runs sysbench oltp_read_write prepare against HAProxy:

sysbench oltp_read_write \
  --db-driver=mysql \
  --mysql-host=lab-haproxy --mysql-port=3306 \
  --mysql-user=root --mysql-password=mysql \
  --mysql-db=sbtest --tables=10 --table-size=200000 \
  prepare

Ten tables of 200,000 rows each lands in the 400–700 MB band – big enough that a backup and a restore take real time and a replica has visible work to do, small enough not to fill a laptop.

This produced the first genuinely useful surprise of the lab. Straight after the load, information_schema reported the sbtest schema at about 109 MB. That is not a small error, it is off by more than a factor of four. After ANALYZE TABLE:

SYSBENCH_TABLES=10
SIZE_MB=471

InnoDB’s data_length and index_length come from sampled statistics, and immediately after a bulk load those statistics are stale. If you are sizing storage, planning a backup window, or writing a capacity alert off information_schema.tables, run ANALYZE TABLE first or you will be reasoning about a number that is simply wrong.

Failover

The interesting question about failover is not whether a replica gets promoted – it is what happens to the old source when it comes back. A former primary that returns writable is how you get split-brain and two divergent GTID histories.

The playbook force-deletes the current source pod so it is genuinely unreachable, then asks Orchestrator to recover:

kubectl delete pod lab-mysql-2 --force --grace-period=0
curl "http://lab-orc:3000/api/recover/lab-mysql-2.lab-mysql.mysql/3306"

The StatefulSet then recreates the deleted pod, and the playbook waits until that member is Running, read-only, and following the new source. The measured result:

PRIMARY_BEFORE=lab-mysql-2
PRIMARY_AFTER=lab-mysql-0
OLD_READ_ONLY=1
OLD_REPLICA_IO=ON
OLD_REPLICA_SQL=ON
OLD_SOURCE_HOST=lab-mysql-0.lab-mysql.mysql
FOLLOWER lab-mysql-1 read_only=1 replica_io=ON replica_sql=ON source=lab-mysql-0.lab-mysql.mysql
FOLLOWER lab-mysql-2 read_only=1 replica_io=ON replica_sql=ON source=lab-mysql-0.lab-mysql.mysql
FOLLOWERS_OK=2
REJOIN_OK=1

That is the outcome you want: lab-mysql-0 is writable, the former source lab-mysql-2 is back with @@read_only=1 and both replication threads applying the new source’s GTID stream, and the untouched replica lab-mysql-1 repointed itself without waiting for the old pod to finish catching up.

One approach that did not work is worth recording. Sending SIGSTOP to PID 1 – the usual trick for simulating a frozen rather than a dead server – did not freeze mysqld in this image, because PID 1 is not the database process. The shipped playbook force-deletes the pod instead, which is a blunter failure but an unambiguous one.

Backup, and a restore that proves itself

Backups go to MinIO running inside the cluster. XtraBackup in the operator wants S3-compatible object storage, and a laptop lab should not need an AWS account, so MinIO fills that slot with path-style addressing and TLS verification off:

  backup:
    schedule:
      - name: hourly-full
        schedule: "15 * * * *"
        keep: 3
        storageName: minio
        type: full
    storages:
      minio:
        type: s3
        verifyTLS: false
        s3:
          bucket: mysql-lab-backups
          credentialsSecret: minio-backup-credentials
          endpointUrl: http://minio.mysql.svc.cluster.local:9000
          region: us-east-1
          prefix: lab

Taking the backup

The schedule gives you an hourly full backup, but for a demo you want one on demand. That is a single custom resource:

apiVersion: ps.percona.com/v1
kind: PerconaServerMySQLBackup
metadata:
  name: lab-ondemand
  namespace: mysql
spec:
  clusterName: lab
  storageName: minio
  type: full

The playbook first records what it expects to get back, so the comparison after the restore is against something real rather than memory:

kubectl -n mysql exec lab-mysql-0 -c mysql -- \
  mysql -uroot -pmysql -N -e \
  "SELECT table_name, table_rows FROM information_schema.tables
   WHERE table_schema='sbtest' AND table_name LIKE 'sbtest%'
   ORDER BY table_name"

Then it deletes any previous lab-ondemand object, applies the manifest, and polls the CR until the operator reports a terminal state:

kubectl -n mysql delete perconaservermysqlbackup lab-ondemand --ignore-not-found --wait
kubectl -n mysql apply -f k8s/backup/ondemand.yaml

kubectl -n mysql get perconaservermysqlbackup lab-ondemand \
  -o jsonpath='{.status.state}{"\n"}'

The Ansible version retries that last call 90 times at 10-second intervals and then asserts the state is one of succeeded / ready / done — an explicit assert, because a CR that is merely present tells you nothing. You can watch the underlying job while it runs:

kubectl -n mysql get pods -w | grep xb-
kubectl -n mysql logs -f job/xb-lab-ondemand

The full status when it finishes:

kubectl -n mysql get perconaservermysqlbackup lab-ondemand -o yaml
backupSource: lab-mysql-0.lab-mysql.mysql
destination: s3://mysql-lab-backups/lab/lab-2026-09-10-19:19:11-full
state: Succeeded

Why Succeeded is not enough

Succeeded is where most tutorials stop. It is also where a backup is still only a hypothesis: the operator is telling you that a job exited zero and wrote an object, not that the object can rebuild your database.

The restore playbook makes the test falsifiable with a canary table. It writes a row that exists only after the backup was taken, so a genuine restore must destroy it. First it finds the writable member, because after a failover the source is not necessarily mysql-0:

NS=mysql
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}')

for p in $pods; do
  ro=$(kubectl -n "$NS" exec "$p" -c mysql -- \
    mysql -uroot -pmysql -N -e "SELECT @@read_only" 2>/dev/null | tr -d '\r')
  if [ "$ro" = "0" ]; then echo "$p"; break; fi
done

Then it plants the canary on that source:

kubectl -n mysql exec lab-mysql-0 -c mysql -- \
  mysql -uroot -pmysql -N -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, 'post-backup canary')
     ON DUPLICATE KEY UPDATE note=VALUES(note)"

And — this part matters — it asserts the canary is actually there before restoring. A canary that was never created would also be absent afterwards, and would give you a false pass:

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'"
# must return 1

Restoring

The restore is another one-object custom resource, pointing at the backup by name:

apiVersion: ps.percona.com/v1
kind: PerconaServerMySQLRestore
metadata:
  name: lab-restore
  namespace: mysql
spec:
  clusterName: lab
  backupName: lab-ondemand
kubectl -n mysql delete perconaservermysqlrestore lab-restore --ignore-not-found --wait
kubectl -n mysql apply -f k8s/backup/restore.yaml

kubectl -n mysql get perconaservermysqlrestore lab-restore \
  -o jsonpath='{.status.state}{"\n"}'

This takes noticeably longer than the backup — the playbook polls 120 times at 15-second intervals — because the operator stops MySQL on every member, runs the restore job, and then starts the cluster back up. Watching it is more informative than waiting on the status field:

kubectl -n mysql get pods -w
kubectl -n mysql logs -f job/xb-restore-lab-restore

Once the CR reports success, wait for the database pods to come back Running before asking them anything:

kubectl -n mysql wait --for=condition=Ready pod \
  -l app.kubernetes.io/instance=lab,app.kubernetes.io/component=database \
  --timeout=900s

The verdict

Three questions in one round trip — how many sysbench tables, how big, and is the 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 IFNULL(ROUND(SUM(data_length+index_length)/1024/1024),0)
     FROM information_schema.tables WHERE table_schema='sbtest';
   SELECT COUNT(*) FROM information_schema.tables
     WHERE table_schema='sbtest' AND table_name='canary_after_backup'"
10
493
0

Ten sysbench tables, 493 MB, and a canary count of 0, with the cluster back in state=ready. The canary disappearing is the proof: the data now on disk genuinely came from the MinIO object and not from whatever happened to be sitting in the data directory. If the canary had survived, the restore would have been a no-op and the green Succeeded would have been meaningless.

The playbook encodes exactly that as an assertion rather than leaving it to the reader’s eye — table count equals 10, size is above the 400 MB floor, canary count is 0:

- 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) == 10
      - (restored.stdout_lines | select('match', '^[0-9]+$') | list | last | int) == 0
      - (restored.stdout_lines | select('match', '^[0-9]+$') | list)[1] | int >= 400

All of which is wrapped up as two commands if you just want to run it:

make backup    # or ./scripts/lab.sh backup
make restore   # or ./scripts/lab.sh restore

This is a whole-cluster restore, not a single-instance one. The operator paused MySQL, ran the xb-restore-lab-restore job, and brought both members back from the backup.

Scale-out

Adding a replica is one patch:

kubectl patch ps lab -n mysql --type=merge -p '{"spec":{"mysql":{"size":3}}}'

The operator provisions a new member, clones the data, and lets it catch up. After it settles:

MYSQL_PODS=3
POD lab-mysql-0 read_only=0 tables=10
POD lab-mysql-1 read_only=1 tables=10 replica_io=ON replica_sql=ON
POD lab-mysql-2 read_only=1 tables=10 replica_io=ON replica_sql=ON
REPLICA_OK=2

With the CR reporting mysql.ready=3 size=3 state=ready. The assertion that matters is not the pod count – it is that the new member has all ten tables and both replication threads running. A pod that is Running but not replicating is worse than no pod at all, because HAProxy will happily send reads to it.

Verification, and the checks that make it meaningful

The verify playbook walks every pod and reports topology rather than just liveness:

POD lab-mysql-0 hostname=lab-mysql-0 read_only=0
POD lab-mysql-1 hostname=lab-mysql-1 read_only=1 replica_io=ON replica_sql=ON
POD lab-mysql-2 hostname=lab-mysql-2 read_only=1 replica_io=ON replica_sql=ON
PRIMARY=lab-mysql-0
REPLICA_OK=2
SYSBENCH_TABLES=10
SIZE_MB=493
Prometheus mysql up ok

Exactly one writable server, every other member read-only with IO and SQL threads on, the expected data present, and Prometheus up{job="mysql"} returning 1 for all three exporter sidecars. The SQL behind it is nothing exotic:

SELECT @@hostname, @@read_only, @@gtid_mode;
SELECT SERVICE_STATE FROM performance_schema.replication_connection_status;
SELECT SERVICE_STATE FROM performance_schema.replication_applier_status;

SELECT COUNT(*) FROM information_schema.tables
WHERE table_schema='sbtest' AND table_name LIKE 'sbtest%';

Small things that cost time

mysqld_exporter v0.16 changed its interface. It wants MYSQLD_EXPORTER_PASSWORD in the environment plus --mysqld.username and --mysqld.address flags. The old DATA_SOURCE_NAME connection string that every older example uses is gone, and the failure mode is an exporter that starts cleanly and reports nothing.

Operator pod labels are role-based. They are component=database and component=proxy, not mysql and haproxy. Selectors written against the obvious names match zero pods, which in Ansible looks like an empty loop rather than an error.

Pick non-default host ports. 3307 / 3001 / 9091 instead of 3306 / 3000 / 9090 means this lab can coexist with anything else already bound on the machine.

Why bother

The value here is not the cluster – you can get a MySQL container in ten seconds. It is that the failure paths are cheap to run. You can force a failover, confirm the old primary came back read-only, restore a backup and prove it by watching a canary vanish, then tear the whole thing down:

./scripts/lab.sh teardown

The usual caveats apply: plain-text passwords, an unsafe member count, a single Orchestrator, no TLS, and local persistent volumes on one Docker host. This is a place to practise, not a template to copy into production. But the operational shapes – GTID replication, Orchestrator-driven promotion, XtraBackup to object storage, a whole-cluster restore – are the same ones you will be dealing with when it is not a laptop, and it is considerably nicer to meet them here first.

All of it is at github.com/ghkrzysztof-ksiazek/database-labs — the playbooks, the roles quoted above, and a sibling ClickHouse lab built the same way.

We remember your choice in this browser for up to 180 days using local storage, not a tracking cookie. You can change it at any time through Cookie settings in the footer. Rejecting analytics does not prevent you from using the website or contact form.

Privacy and cookie details