Build a MySQL Master‑Slave Cluster on Kubernetes with StatefulSet and Local PV

Introduction

Kubernetes can create multiple stateless Pods with ReplicaSet, but stateful Pods require a mechanism that preserves name, network identity, and data when they are rescheduled. StatefulSet provides this capability.

Problem Statement

In a container cluster, nodes are not 100% reliable and resources change dynamically. When a node fails or a pod is moved, local storage would lose data, causing data loss on restart.

Purpose of the Article

The article builds a MySQL master‑slave cluster to explore StatefulSet management. It uses local storage for simplicity (not recommended for production) and demonstrates the state management features of StatefulSet.

Environment Requirements

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 the slave nodes

Restrict all write operations to the master node

Permit read operations on both master and slave nodes

Synchronize data from master to slaves

Local Storage Principle

Local storage fixes the volume to a specific node, but Pods are scheduled freely. To bind a Pod to a specific PV, a StorageClass with no-provisioner and volumeBindingMode: WaitForFirstConsumer is used, enabling delayed binding for Local Persistent Volumes.

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

Step‑by‑Step Procedure

1. Pre‑create PersistentVolumes on the node (IP 172.31.170.51)

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 host paths.

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

2. Create 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

3. Create Namespace

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

kubectl apply -f 03-mysql-namespace.yaml

4. ConfigMap for Master/Slave MySQL configs

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

5. Secret for MySQL root password

apiVersion: v1
kind: Secret
metadata:
  name: mysql-secret
  namespace: mysql
  labels:
    app: mysql
type: Opaque
data:
  password: MTIzNDU2

kubectl apply -f 05-mysql-secret.yaml

6. Services for read/write separation

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

7. StatefulSet for MySQL master‑slave

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 0
          [[ $(hostname) =~ -([0-9]+)$ ]] || exit 1
          ordinal=${BASH_REMATCH[1]}
          [[ $ordinal == 0 ]] && exit 0
          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
        readinessProbe:
          exec:
            command: ["mysqladmin","ping","-uroot","-p${MYSQL_ROOT_PASSWORD}"]
          initialDelaySeconds: 5
          periodSeconds: 2
      - 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
            echo "Waiting for mysqld to be ready"
            until mysql -h 127.0.0.1 -uroot -p${MYSQL_ROOT_PASSWORD} -e "SELECT 1"; do sleep 1; done
            echo "Initializing replication"
            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
      volumes:
      - name: conf
        emptyDir: {}
      - name: config-map
        configMap:
          name: mysql
  volumeClaimTemplates:
  - metadata:
      name: data
    spec:
      accessModes: ["ReadWriteOnce"]
      storageClassName: local-storage
      resources:
        requests:
          storage: 3Gi

kubectl apply -f 07-mysql-statefulset.yaml

Verification

After the StatefulSet is created, two Pods (mysql-0 and mysql-1) run. Execute MySQL commands to check replication status, create a database, insert data on the master, and query the slave to confirm synchronization.

# Check slave status
kubectl -n mysql exec mysql-1 -c mysql -- bash -c "mysql -uroot -p123456 -e 'show slave status \G'"
# Create database and table on master
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)'"
# Verify data on slave
kubectl -n mysql exec mysql-1 -c mysql -- bash -c "mysql -uroot -p123456 -e 'use test;select * from counter'"

Scaling the Slave

StatefulSet allows easy scaling. Increase replicas to 3, and the new pod (mysql-2) automatically joins the replication set.

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

Query the new pod to confirm it has the replicated data.

kubectl -n mysql exec mysql-2 -c mysql -- bash -c "mysql -uroot -p123456 -e 'use test;select * from counter'"

Conclusion

The guide shows how StatefulSet, combined with local persistent volumes and delayed binding, can reliably run a MySQL master‑slave cluster, support read‑write separation, and allow horizontal scaling while preserving data consistency.