Master MySQL Replication with Kubernetes StatefulSets: A Hands‑On Guide

Kubernetes normally creates stateless Pods via ReplicaSet, but StatefulSet provides the ability to manage stateful Pods that retain their identity, network ID, and storage when they are rescheduled. This article demonstrates how to set up a MySQL master‑slave cluster using StatefulSet and local storage for experimental purposes.

Local Storage Principle

To quickly build a test environment we use local storage, meaning Kubernetes uses a directory on the host node as a persistent volume. Because Pods may be scheduled on different nodes, we need a way to bind a Pod to a specific PV; this is achieved with a StorageClass that has no-provisioner and volumeBindingMode: WaitForFirstConsumer, enabling delayed binding.

kind: StorageClass
apiVersion: storage.k8s.io/v1
metadata:
  name: local-storage
provisioner: kubernetes.io/no-provisioner
volumeBindingMode: WaitForFirstConsumer

Experiment Environment

Kubernetes Master

Kubernetes Node (all replicas run on this node for the demo)

Kubernetes DNS service enabled

Experiment Objectives

Deploy a MySQL master‑slave (master‑slave) cluster

Allow horizontal scaling of slave nodes

Restrict all write operations to the master node

Allow read operations on both master and slave nodes

Synchronize data from master to slaves

Creating PersistentVolumes (Static Provisioning for Demo)

apiVersion: v1
kind: PersistentVolume
metadata:
  name: example-mysql-pv
spec:
  capacity:
    storage: 15Gi
  volumeMode: Filesystem
  accessModes:
  - ReadWriteOnce
  persistentVolumeReclaimPolicy: Delete
  storageClassName: local-storage
  local:
    path: /data/svr/projects/mysql
  nodeAffinity:
    required:
      nodeSelectorTerms:
      - matchExpressions:
        - key: kubernetes.io/hostname
          operator: In
          values:
          - 172.31.170.51

Repeat the above for example-mysql-pv-2 and example-mysql-pv-3 with different path values.

kubectl apply -f 01-persistentVolume-{1..3}.yaml

Creating the StorageClass

kind: StorageClass
apiVersion: storage.k8s.io/v1
metadata:
  name: local-storage
provisioner: kubernetes.io/no-provisioner
volumeBindingMode: WaitForFirstConsumer

kubectl apply -f 02-storageclass.yaml

Creating Namespace

apiVersion: v1
kind: Namespace
metadata:
  name: mysql
  labels:
    app: mysql

kubectl apply -f 03-mysql-namespace.yaml

ConfigMap for Master/Slave Config Files

apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql
  namespace: mysql
  labels:
    app: mysql
data:
  master.cnf: |
    [mysqld]
    log-bin=mysqllog
    skip-name-resolve
  slave.cnf: |
    [mysqld]
    super-read-only
    skip-name-resolve
    log-bin=mysql-bin
    replicate-ignore-db=mysql

kubectl apply -f 04-mysql-configmap.yaml

Secret for MySQL Root Password

apiVersion: v1
kind: Secret
metadata:
  name: mysql-secret
  namespace: mysql
  labels:
    app: mysql
type: Opaque
data:
  password: MTIzNDU2  # echo -n "123456" | base64

kubectl apply -f 05-mysql-secret.yaml

Services for Read‑Write Splitting

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

kubectl apply -f 06-mysql-services.yaml

StatefulSet for MySQL Master‑Slave Cluster

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
  namespace: mysql
  labels:
    app: mysql
spec:
  selector:
    matchLabels:
      app: mysql
  serviceName: mysql
  replicas: 2
  template:
    metadata:
      labels:
        app: mysql
    spec:
      initContainers:
      - name: init-mysql
        image: mysql:5.7
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: password
        command:
        - bash
        - -c
        - |
          set -ex
          [[ $(hostname) =~ -([0-9]+)$ ]] || exit 1
          ordinal=${BASH_REMATCH[1]}
          echo "[mysqld]" > /mnt/conf.d/server-id.cnf
          echo "server-id=$((100 + $ordinal))" >> /mnt/conf.d/server-id.cnf
          if [[ ${ordinal} -eq 0 ]]; then
            cp /mnt/config-map/master.cnf /mnt/conf.d
          else
            cp /mnt/config-map/slave.cnf /mnt/conf.d
          fi
        volumeMounts:
        - name: conf
          mountPath: /mnt/conf.d
        - name: config-map
          mountPath: /mnt/config-map
      - name: clone-mysql
        image: gcr.io/google-samples/xtrabackup:1.0
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: password
        command:
        - bash
        - -c
        - |
          set -ex
          [[ -d /var/lib/mysql/mysql ]] && exit
          [[ $(hostname) =~ -([0-9]+)$ ]] || exit 1
          ordinal=${BASH_REMATCH[1]}
          [[ $ordinal == 0 ]] && exit
          ncat --recv-only mysql-$((ordinal-1)).mysql 3307 | xbstream -x -C /var/lib/mysql
          xtrabackup --prepare --target-dir=/var/lib/mysql
        volumeMounts:
        - name: data
          mountPath: /var/lib/mysql
          subPath: mysql
        - name: conf
          mountPath: /etc/mysql/conf.d
      containers:
      - name: mysql
        image: mysql:5.7
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: password
        ports:
        - name: mysql
          containerPort: 3306
        volumeMounts:
        - name: data
          mountPath: /var/lib/mysql
          subPath: mysql
        - name: conf
          mountPath: /etc/mysql/conf.d
        resources:
          requests:
            cpu: 500m
            memory: 1Gi
        livenessProbe:
          exec:
            command: ["mysqladmin", "ping", "-uroot", "-p${MYSQL_ROOT_PASSWORD}"]
          initialDelaySeconds: 30
          periodSeconds: 10
          timeoutSeconds: 5
        readinessProbe:
          exec:
            command: ["mysqladmin", "ping", "-uroot", "-p${MYSQL_ROOT_PASSWORD}"]
          initialDelaySeconds: 5
          periodSeconds: 2
          timeoutSeconds: 1
      - name: xtrabackup
        image: gcr.io/google-samples/xtrabackup:1.0
        ports:
        - name: xtrabackup
          containerPort: 3307
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: password
        command:
        - bash
        - -c
        - |
          set -ex
          cd /var/lib/mysql
          if [[ -f xtrabackup_slave_info ]]; then
            mv xtrabackup_slave_info change_master_to.sql.in
            rm -f xtrabackup_binlog_info
          elif [[ -f xtrabackup_binlog_info ]]; then
            [[ $(cat xtrabackup_binlog_info) =~ ^(.*?)[[:space:]]+(.*?)$ ]] || exit 1
            rm xtrabackup_binlog_info
            echo "CHANGE MASTER TO MASTER_LOG_FILE='${BASH_REMATCH[1]}', MASTER_LOG_POS=${BASH_REMATCH[2]}" > change_master_to.sql.in
          fi
          if [[ -f change_master_to.sql.in ]]; then
            until mysql -h 127.0.0.1 -uroot -p${MYSQL_ROOT_PASSWORD} -e "SELECT 1"; do sleep 1; done
            mv change_master_to.sql.in change_master_to.sql.orig
            mysql -h 127.0.0.1 -uroot -p${MYSQL_ROOT_PASSWORD} <<EOF
            $(< change_master_to.sql.orig),
            MASTER_HOST='mysql-0.mysql.mysql',
            MASTER_USER='root',
            MASTER_PASSWORD='${MYSQL_ROOT_PASSWORD}',
            MASTER_CONNECT_RETRY=10;
            START SLAVE;
EOF
          fi
          exec ncat --listen --keep-open --send-only --max-conns=1 3307 -c "xtrabackup --backup --slave-info --stream=xbstream --host=127.0.0.1 --user=root --password=${MYSQL_ROOT_PASSWORD}"
        volumeMounts:
        - name: data
          mountPath: /var/lib/mysql
          subPath: mysql
        - name: conf
          mountPath: /etc/mysql/conf.d
  volumeClaimTemplates:
  - metadata:
      name: data
    spec:
      accessModes:
      - "ReadWriteOnce"
      storageClassName: local-storage
      resources:
        requests:
          storage: 3Gi

kubectl apply -f 07-mysql-statefulset.yaml

After applying, two Pods mysql-0 (master) and mysql-1 (slave) should be running. Verify replication with:

kubectl -n mysql exec mysql-1 -c mysql -- bash -c "mysql -uroot -p123456 -e 'show slave status \G'"

Create a test database and table on the master, insert a row, and confirm the slave has synchronized the data:

kubectl -n mysql exec mysql-0 -c mysql -- bash -c "mysql -uroot -p123456 -e 'create database test'"
kubectl -n mysql exec mysql-0 -c mysql -- bash -c "mysql -uroot -p123456 -e 'use test; create table counter(c int);'"
kubectl -n mysql exec mysql-0 -c mysql -- bash -c "mysql -uroot -p123456 -e 'use test; insert into counter values(123);'"
kubectl -n mysql exec mysql-1 -c mysql -- bash -c "mysql -uroot -p123456 -e 'use test; select * from counter;'"

Scale the StatefulSet to add more slaves:

kubectl -n mysql scale statefulset mysql --replicas=3
kubectl -n mysql get pods -n mysql

New pod mysql-2 will join the cluster and automatically synchronize data from the master, confirming that StatefulSet enables easy horizontal scaling of MySQL replicas.