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

In Kubernetes, ReplicaSet creates stateless pod replicas, but stateful pods require a mechanism to preserve identity, network ID, and data when they are rescheduled; StatefulSet provides this capability.

Experimental 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

Permit read operations on both master and slave nodes

Synchronize data from the master to the slaves

Local Storage Principle

To quickly set up the test environment, local storage is used so that Kubernetes can directly mount a directory on the host without relying on remote storage services. This approach is suitable only for experiments; production should use dynamic provisioning with GCE, NFS, Ceph, etc.

"We fix storage to a single node, but pods are scheduled across nodes; how can we bind a PVC to a specific PV?"

Adding a nodeAffinity rule can solve the scheduling issue, but it shifts the responsibility to developers. Instead, the tutorial adopts delayed binding via a StorageClass with no-provisioner and volumeBindingMode: WaitForFirstConsumer.

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

Experiment Steps

1. Create PersistentVolumes on the node (static provisioning for the 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 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 a Namespace for MySQL

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

kubectl apply -f 03-mysql-namespace.yaml

4. Create a ConfigMap with master and slave configuration 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

5. Create a Secret for the 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

6. Define 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. Deploy the MySQL StatefulSet

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
          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
      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

After deployment, two pods appear (mysql-0 as master, mysql-1 as slave). Verify replication with:

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

Create a database and table on the master, insert data, then query the slave to confirm synchronization:

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

Verify the new pod (mysql-2) can read the data, confirming successful replication to the newly added node.

Original link: https://lihaoquan.me/2020/3/6/mysql-master-slave-statefulset.html