MySQL on Kubernetes: StatefulSet and GTID Replication

I’ve spent my career writing database scripts that have kept DBAs up at night. I’ve watched them configure replication, and I’ve never been allowed to touch it myself.

This is the same database that ran StockAlgo v1 on Amazon RDS, the 35+ table schema from the first post. On AWS I made the decision a replica wasn’t necessary since I was okay with downtime if something crazy happened. I took daily snapshots, so my data was safe, and as an individual, I was okay with the uptime of a single RDS instance. I know it goes against every corporate tech ideology, but this is a side project and I don’t have the funds of a corporation. Migrating the RDS instance from AWS to my homelab meant taking on the maintenance AWS handled for me before. Since I have ample space and compute power, I decided to have a primary/replica database setup. The only piece that came from AWS was the mysqldump and everything else was written from scratch. In this blog I’ll talk about how I built a K8s StatefulSet, setup config files for primary and replica pods, and got to see what happens when a GTID replication pod dies and comes back to life. Most importantly, this blog also highlights plenty of my mistakes.

All the YAML is the real config, pulled from the repo, trimmed for readability. It all runs on the lab.

Why a StatefulSet and not a Deployment

A Deployment doesn’t care about a pod’s identity. Any pod can serve any request, replicas are anonymous, and a replacement pod is a brand new individual with a brand new, randomized name. It’s the cheap, right answer for a system that needs a pile of identical pods doing the same job off shared or temporary storage. As you can imagine, deployments are not great for running databases. Databases want their own dedicated storage and their own constant, unique name. That’s exactly what a StatefulSet does.

A StatefulSet gives a replicated database the three things it can’t live without:

Stable identity. Pods are named by ordinal: mysql-0, mysql-1. When mysql-1 dies, its replacement is also mysql-1, not mysql-asdas. Role assignment can hang off the name, and you’ll see below mine does.

Per-pod storage. Deployment pods can mount persistent storage, and my StockAlgo application pods share an NFS PVC just fine. What a Deployment can’t do is give each pod its own isolated volume that follows it across restarts. The StatefulSet’s volumeClaimTemplates allocates one isolated volume per pod:

volumeClaimTemplates:
  - metadata:
      name: fast-nfs-pvc
    spec:
      accessModes: ["ReadWriteOnce"]
      resources:
        requests:
          storage: 1Ti

mysql-0 gets its own data directory, and mysql-1 gets its own data directory too. If mysql-1 goes down, the new mysql-1 pod reattaches to its old data directory, not a new one. The alternative is two MySQL servers pointed at one data directory, which isn’t replication, it’s corruption with extra steps.

Ordered startup. mysql-0 comes up before mysql-1. When one of them is the primary the other needs to connect to, the order matters.

Pod identity picks the config

The primary and replica need different server configs, but a StatefulSet gives every pod the same template, so the role decision has to happen inside the pod. We can use something called an init container. It has the exact same purpose as __init__ in a Python class, to initialize and set up anything before the main code is run. For me, the init container runs before MySQL starts, reads the pod’s own hostname, and copies the right config:

initContainers:
  - name: primary-secondary-cnf-picker
    image: mysql:8.0.42
    command:
      - sh
      - "-c"
      - |
        pod_hostname=$(cat /etc/hostname)
        if [ "$pod_hostname" = "mysql-0" ]; then
          cp /mnt/mconfig/map/primary.cnf /etc/mysql/conf.d/
        else
          cp /mnt/mconfig/map/replica.cnf /etc/mysql/conf.d/
        fi

Both .cnf files live in one ConfigMap, mounted as read-only at /mnt/mconfig/map. If the pod’s hostname is mysql-0, the init container copies the primary.cnf into an emptyDir volume, else we’re dealing with the replica pod, so it copies the replica.cnf into emptyDir. Then the MySQL container mounts that same emptyDir at /etc/mysql/conf.d and now each pod comes up with exactly the config its role needs. ConfigMap mounts are read-only and hold every variant; the emptyDir holds exactly one, chosen at startup. The database container stays the stock mysql:8.0.42 image without customization.

One catch in that snippet. It’s sh with [ ], not bash with [[ ]]. The official MySQL image doesn’t ship bash, and the first version of this script found that out at runtime.

Two services, and the label I shouldn’t have touched

A StatefulSet needs a headless service, and the reason is DNS. A normal ClusterIP service gets one virtual IP that spreads connections evenly across the pods behind it. A database must write to the primary and only the primary. Trying to write to a super-read-only replica doesn’t usually end well. Setting clusterIP: None skips the virtual IP and gives every pod its own DNS record. mysql-0.mysql resolves to mysql-0, no matter how many times the pod has been rescheduled or what its IP happens to be today.

apiVersion: v1
kind: Service
metadata:
  name: mysql
spec:
  ports:
    - name: mysql
      port: 3306
  clusterIP: None
  selector:
    app: mysql

The subtle part is that matching labels alone don’t create those per-pod records. The StatefulSet has to declare serviceName: mysql in its own spec. Labels route traffic. serviceName is what registers the pods with DNS. Miss it and the headless service exists, the selector matches, and mysql-0.mysql resolves to nothing, which was a fun hour of debugging I’d like back.

The second service, mysql-read, routes read traffic to the replica. On my first attempt I replaced the app=mysql label on mysql-1 with app=mysql-read. The StatefulSet reported 1/2 pods ready. The pod was healthy, MySQL was serving, replication was flowing, and the controller had lost the ability to see any of it. The selector label is how a StatefulSet recognizes its own pods and I disowned one. That’s how I learned selector labels don’t get edited.

Every StatefulSet pod automatically carries statefulset.kubernetes.io/pod-name, a label the controller maintains for you, so the read service just selects it:

apiVersion: v1
kind: Service
metadata:
  name: mysql-read
spec:
  ports:
    - name: mysql
      port: 3306
  selector:
    app: mysql
    statefulset.kubernetes.io/pod-name: mysql-1

The GTID part

Here are both configs, which is where the actual replication decisions live:

# primary.cnf
[mysqld]
bind-address=0.0.0.0
log-bin
server-id=1
innodb_buffer_pool_size=4G
gtid_mode=ON
enforce-gtid-consistency=ON

# replica.cnf
[mysqld]
bind-address=0.0.0.0
super-read-only
server-id=2
innodb_buffer_pool_size=4G
gtid_mode=ON
event_scheduler=OFF
enforce-gtid-consistency=ON
log-replica-updates=ON
skip-replica-start=ON

The primary’s half is short: log-bin turns on the binary log, which is the feed replication reads from, and server-id has to be unique per server because every binlog event carries the ID of the server that originated it. That’s how a server recognizes its own transactions if they ever come back around.

The replica’s half is more interesting:

gtid_mode=ON + enforce-gtid-consistency=ON. Every transaction gets a globally unique ID: the source server’s UUID plus a sequence number. The replica tracks the full set of GTIDs it has applied, and the connection handshake is effectively “here’s everything I have, send me everything I don’t.” Compare that to the old MySQL replication method, which was a binlog file name plus byte offset, coordinates you had to capture at exactly the right instant and it turned to garbage once the primary rotated its logs or you restored from the wrong snapshot. Coordinates like that don’t survive a pod reschedule. A GTID set does.

super-read-only. Plain read-only still lets superusers write, and every accidental write to a replica is a replication error waiting to happen. super-read-only closes the loophole.

event_scheduler=OFF. This one is personal. My v1 pipeline’s entire scheduler was MySQL scheduled events, those EVERY 2 SECOND events gated by GET_LOCK. Event definitions replicate to the replica like any other DDL, so the replica holds my whole hand-rolled scheduler in its schema. If its event scheduler were on, it would try to run the jobs on a super-read-only server, which means a pile of errors at best and different data images at worst. The replica keeps the events off.

log-replica-updates=ON. The replica writes replicated transactions into its own binlog. It costs some write amplification, but it buys promotability. A replica with a complete binlog and full GTID history can become the primary, and anything downstream can resync against it. Without this flag, promoting the replica means everything downstream starts over from a fresh copy.

skip-replica-start=ON. Replication doesn’t start on boot. You start it deliberately, which brings us to the wiring itself.

Wiring the replica, and the payoff of the DNS

With GTIDs and per-pod DNS, pointing the replica at the primary is three lines run once on mysql-1:

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'mysql-0.mysql',
    SOURCE_AUTO_POSITION = 1;
START REPLICA;

No IP address, because the headless service’s DNS record follows mysql-0 across every reschedule. No binlog file, no position, because SOURCE_AUTO_POSITION=1 means the GTID handshake computes the delta itself. Every difficult piece of classical replication config has been handed to a layer:

The infrastructure removed every coordinate I used to maintain by hand, and picking the source is still my job.

Verification is deliberately boring. SHOW REPLICA STATUS wants both Replica_IO_Running and Replica_SQL_Running at Yes and an empty error column; then compare gtid_executed on both servers until the sets match; then push live traffic, inserts, updates, and deletes on the primary, and watch the same rows materialize on the replica. I did all three before trusting it.

What actually happens when a pod dies

Kill mysql-1 and the machinery runs in order: the StatefulSet creates a new pod with the same name, the init container reads that name and installs the replica config, the PVC binding hands it the same data directory, DNS re-points mysql-1.mysql at the new pod IP, and replication resumes from the GTID handshake, applying only what it missed while it was gone. Nothing upstream needs to know a restart happened. I’ve killed the pod mid-traffic to watch it happen.

What this setup does not give you is automatic failover. If mysql-0 dies, mysql-1 does not promote itself. The StatefulSet will faithfully rebuild mysql-0 as the primary on the same volume, and for a single-writer workload that’s correct behavior. The primary is down for the seconds a reschedule takes, and no promotion means no promotion mistakes. Real failover is a dance, you must flip super-read-only off on the replica, repoint writers, and when the old primary returns, demote it into a replica of the new one. The reason GTIDs matter is that the last step is possible without reprovisioning. The demoted server auto-positions against the new primary and reconciles, because “which transactions do I have” is state the servers already carry. Orchestrating that dance automatically is exactly what the MySQL Operator, Orchestrator, and the control planes inside RDS-style services do for a living.

The obvious question is why hand-roll this instead of installing an operator or staying on RDS. Same answer as the v1 post. RDS and the operators automate the init containers, the role assignment, the promotion sequence, and the resync. That automation is what you’re paying for. Having built each piece by hand, I know what they’re doing, what they’re protecting me from, and which questions to ask when they misbehave.

Memory sizing

The pod requests 8Gi and is limited to 16Gi, with innodb_buffer_pool_size=4G inside, and those numbers are not independent. The memory limit is a hard ceiling the kernel’s OOM killer enforces, and the buffer pool is nowhere near MySQL’s whole footprint once you add per-connection buffers, temp tables, and the dictionary. Size the pool too close to the limit and the OOM killer SIGKILLs the process mid-write, and the database comes back through crash recovery.

The storage underneath

The volumes are pre-provisioned NFS PVs served over the lab’s 25G fabric, mounted with nconnect=8 so a single mount runs parallel TCP connections, and marked Retain so a deleted claim can never take the data with it. The primary’s data directory and the replica’s data directory live on two different ZFS pools, one NVMe, one SSD. Replication already protects against a MySQL process dying. Putting the two copies on separate pools means a storage level incident on one pool can’t reach both. Given that the NVMe pool once lost both mirror members to the same firmware bug in a single night, that separation has earned its keep.

Databases over NFS is a fight I’m not interested in having in the abstract. For this workload, on this network, with InnoDB’s durability machinery plus ZFS snapshots below it, it holds up, and I get storage level backup and rollbacks for free. On a heavier write path I’d want block storage local to the node.