K8S的StatefulSet控制器应用案例之MySQL主从同步实战

博客园 - 尹正杰

6 5 4 3 2 1 sts部署kafka sts部署MySQL主从同步 windows极速部署Openclaw实战篇 k8s底层基于不同运行时集成harbor企业级私有仓库实战二进制K8S集群附加组件部署及CNI网络插件切换实战二进制部署K8S 1.35.0+最新版实战案例 etcd高可用集群部署及K8S周期性备份数据实战基于Docker实现《若依》服务业务容器化实战篇 k8s集群基于Flannel网络插件部署凡人修仙传 k8s集群基于Calico网络插件部署凡人修仙传 ElasticSEearch 9.X环境部署 K8S Vertical Pod Autoscaler(VPA)实战案例 Prometheus监控自定义程序指标

尹正杰 · 2026-02-01 · via 博客园 - 尹正杰

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰

一.k8s基于sts实现MySQL主从方式
- 1.解法一: 基于启动命令行操作
- 2.解法二: 基于lifecycle的postStart机制
二.测试验证
- 1.主库执行命令操作
- 2.从库执行命令操作

一.k8s基于sts实现MySQL主从方式

1.解法一: 基于启动命令行操作

[root@k8s-cluster251 manifests]# cat 02-sts-mysql-master-slave-command.yaml 
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-conf
data:
  mysql.conf: |
    [mysqld]
    skip-host-cache
    skip-name-resolve
    datadir=/var/lib/mysql
    socket=/var/run/mysqld/mysqld.sock
    secure-file-priv=/var/lib/mysql-files
    user=mysql
    log-bin=mysql-bin
    server-id=101
    pid-file=/var/run/mysqld/mysqld.pid
    
    
    [client]
    socket=/var/run/mysqld/mysqld.sock
    
    !includedir /etc/mysql/conf.d/

  sync-data.sh: |
    #!/bin/bash
    id=`echo $HOSTNAME | awk -F '-' '{print $NF}'`
    sleep 20
    if [[ $id -eq 0 ]]
       then
          mysql -pyinzhengjie -e "CREATE USER 'copy'@'%' IDENTIFIED BY 'yinzhengjie';"
          mysql -pyinzhengjie -e "GRANT REPLICATION SLAVE ON *.* TO 'copy'@'%';"
          mysql -pyinzhengjie -e "FLUSH PRIVILEGES;"
       else
          mysqldump  -h sts-mysql-0.master-slave  -pyinzhengjie -A > /tmp/all.sql 
          mysql -pyinzhengjie < /tmp/all.sql 
          filename=`mysql -h sts-mysql-0.master-slave  -pyinzhengjie -e "SHOW MASTER STATUS;" | awk 'NR==2{print$1}'`
          position=`mysql -h sts-mysql-0.master-slave  -pyinzhengjie -e "SHOW MASTER STATUS;" | awk 'NR==2{print$2}'`
          cat > /tmp/test.sql <<EOF
    CHANGE MASTER TO MASTER_HOST='sts-mysql-0.master-slave',MASTER_USER='copy',MASTER_PASSWORD='yinzhengjie',MASTER_LOG_FILE='$filename',MASTER_LOG_POS=$position;
    EOF
          mysql -pyinzhengjie < /tmp/test.sql
          mysql -pyinzhengjie -e "START SLAVE;"
    fi

---

apiVersion: v1
kind: Service
metadata:
  name: master-slave
spec:
  ports:
  - port: 3306
    name: db
  clusterIP: None
  selector:
    apps: db

---

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: sts-mysql
spec:
  selector:
    matchLabels:
      apps: db
  serviceName: master-slave
  replicas: 3
  template:
    metadata:
      labels:
        apps: db
    spec:
      volumes:
      - name: my-cnf
        configMap:
          defaultMode: 0777
          name: mysql-conf
          items:
          - key: mysql.conf
            path: mysql.conf
          - key: sync-data.sh
            path: sync-data.sh
      - name: data
        emptyDir: {}
      initContainers:
      - name: i1
        image: registry.cn-hangzhou.aliyuncs.com/yinzhengjie-k8s/apps:v1
        volumeMounts:
        - name: my-cnf
          mountPath: /data
        - name: data
          mountPath: /dest
        env:
        - name: pod-name
          valueFrom:
            fieldRef:
              fieldPath: metadata.name
        command:
        - /bin/sh
        - -c 
        - |
          #!/bin/sh
          id=`echo $HOSTNAME | awk -F '-' '{print $NF}'`
          cp /data/mysql.conf /dest/
          if [[ $id -eq 0 ]]
             then
                exit
             else
                sed -i "/server-id/s#101#$id#" /dest/mysql.conf 
          fi
      containers:
      - name: c1
        ports:
        - containerPort: 3306
          name: db
        image: harbor250.yinzhengjie.com/yinzhengjie-db/mysql:8.0.36-oracle
        imagePullPolicy: Always
        volumeMounts:
        - name: data
          mountPath: /data
        command:
        - /bin/bash
        - -c 
        - |
          docker-entrypoint.sh --defaults-file=/data/mysql.conf \
              --character-set-server=utf8mb4 \
              --collation-server=utf8mb4_unicode_ci \
              --default-authentication-plugin=mysql_native_password &
          sleep 5
          /sync-data.sh
          tail -f /etc/hosts
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: yinzhengjie
        - name: MYSQL_DATABASE
          value: wordpress
        - name: MYSQL_USER
          value: linux101
        - name: MYSQL_PASSWORD
          value: "JasonYin"
        volumeMounts:
        - name: data
          mountPath: /data
        - name: my-cnf
          mountPath: /sync-data.sh
          subPath: sync-data.sh
[root@k8s-cluster251 manifests]#

2.解法二: 基于lifecycle的postStart机制

[root@k8s-cluster251 manifests]# cat 03-sts-mysql-master-slave-lifecycle.yaml 
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-conf
data:
  mysql.conf: |
    [mysqld]
    skip-host-cache
    skip-name-resolve
    datadir=/var/lib/mysql
    socket=/var/run/mysqld/mysqld.sock
    secure-file-priv=/var/lib/mysql-files
    user=mysql
    log-bin=mysql-bin
    server-id=101
    pid-file=/var/run/mysqld/mysqld.pid
    
    
    [client]
    socket=/var/run/mysqld/mysqld.sock
    
    !includedir /etc/mysql/conf.d/

  sync-data.sh: |
    #!/bin/bash
    id=`echo $HOSTNAME | awk -F '-' '{print $NF}'`
    sleep 20
    if [[ $id -eq 0 ]]
       then
          mysql -pyinzhengjie -e "CREATE USER 'copy'@'%' IDENTIFIED BY 'yinzhengjie';"
          mysql -pyinzhengjie -e "GRANT REPLICATION SLAVE ON *.* TO 'copy'@'%';"
          mysql -pyinzhengjie -e "FLUSH PRIVILEGES;"
       else
          mysqldump  -h sts-mysql-0.master-slave  -pyinzhengjie -A > /tmp/all.sql 
          mysql -pyinzhengjie < /tmp/all.sql 
          filename=`mysql -h sts-mysql-0.master-slave  -pyinzhengjie -e "SHOW MASTER STATUS;" | awk 'NR==2{print$1}'`
          position=`mysql -h sts-mysql-0.master-slave  -pyinzhengjie -e "SHOW MASTER STATUS;" | awk 'NR==2{print$2}'`
          cat > /tmp/test.sql <<EOF
    CHANGE MASTER TO MASTER_HOST='sts-mysql-0.master-slave',MASTER_USER='copy',MASTER_PASSWORD='yinzhengjie',MASTER_LOG_FILE='$filename',MASTER_LOG_POS=$position;
    EOF
          mysql -pyinzhengjie < /tmp/test.sql
          mysql -pyinzhengjie -e "START SLAVE;"
    fi

---

apiVersion: v1
kind: Service
metadata:
  name: master-slave
spec:
  ports:
  - port: 3306
    name: db
  clusterIP: None
  selector:
    apps: db

---

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: sts-mysql
spec:
  selector:
    matchLabels:
      apps: db
  serviceName: master-slave
  replicas: 3
  template:
    metadata:
      labels:
        apps: db
    spec:
      volumes:
      - name: my-cnf
        configMap:
          defaultMode: 0777
          name: mysql-conf
          items:
          - key: mysql.conf
            path: mysql.conf
          - key: sync-data.sh
            path: sync-data.sh
      - name: data
        emptyDir: {}
      initContainers:
      - name: i1
        image: registry.cn-hangzhou.aliyuncs.com/yinzhengjie-k8s/apps:v1
        volumeMounts:
        - name: my-cnf
          mountPath: /data
        - name: data
          mountPath: /dest
        env:
        - name: pod-name
          valueFrom:
            fieldRef:
              fieldPath: metadata.name
        command:
        - /bin/sh
        - -c 
        - |
          #!/bin/sh
          id=`echo $HOSTNAME | awk -F '-' '{print $NF}'`
          cp /data/mysql.conf /dest/
          if [[ $id -eq 0 ]]
             then
                exit
             else
                sed -i "/server-id/s#101#$id#" /dest/mysql.conf 
          fi
      containers:
      - name: c1
        ports:
        - containerPort: 3306
          name: db
        image: harbor250.yinzhengjie.com/yinzhengjie-db/mysql:8.0.36-oracle
        imagePullPolicy: Always
        lifecycle:
          postStart:
            exec:
              command:
              - /bin/bash
              - -c
              - /sync-data.sh
        volumeMounts:
        - name: data
          mountPath: /data
        args:
        - --defaults-file=/data/mysql.conf
        - --character-set-server=utf8mb4
        - --collation-server=utf8mb4_unicode_ci
        - --default-authentication-plugin=mysql_native_password
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: yinzhengjie
        - name: MYSQL_DATABASE
          value: wordpress
        - name: MYSQL_USER
          value: linux101
        - name: MYSQL_PASSWORD
          value: "JasonYin"
        volumeMounts:
        - name: data
          mountPath: /data
        - name: my-cnf
          mountPath: /sync-data.sh
          subPath: sync-data.sh
[root@k8s-cluster251 manifests]#

二.测试验证

1.主库执行命令操作

[root@k8s-cluster251 manifests]# kubectl apply -f  02-sts-mysql-master-slave-command.yaml 
configmap/mysql-conf created
service/master-slave created
statefulset.apps/sts-mysql created
[root@k8s-cluster251 manifests]# 
[root@k8s-cluster251 manifests]# kubectl get pods -o wide
NAME          READY   STATUS     RESTARTS   AGE   IP               NODE             NOMINATED NODE   READINESS GATES
sts-mysql-0   1/1     Running    0          5s    10.100.230.163   k8s-cluster251   <none>           <none>
sts-mysql-1   1/1     Running    0          3s    10.100.105.30    k8s-cluster253   <none>           <none>
sts-mysql-2   0/1     Init:0/1   0          0s    <none>           k8s-cluster252   <none>           <none>
[root@k8s-cluster251 manifests]# 
[root@k8s-cluster251 manifests]# kubectl get pods -o wide
NAME          READY   STATUS    RESTARTS   AGE   IP               NODE             NOMINATED NODE   READINESS GATES
sts-mysql-0   1/1     Running   0          8s    10.100.230.163   k8s-cluster251   <none>           <none>
sts-mysql-1   1/1     Running   0          6s    10.100.105.30    k8s-cluster253   <none>           <none>
sts-mysql-2   1/1     Running   0          3s    10.100.201.152   k8s-cluster252   <none>           <none>
[root@k8s-cluster251 manifests]# 
[root@k8s-cluster251 manifests]# kubectl exec -it sts-mysql-0 -- mysql -pyinzhengjie
Defaulted container "c1" out of: c1, i1 (init)
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wordpress          |
+--------------------+
5 rows in set (0.01 sec)

mysql> USE wordpress
Database changed
mysql> 
mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> 
mysql> CREATE TABLE t1(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, hobby VARCHAR(255) NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> INSERT INTO t1(name,hobby) VALUES ('SunWuKong','ZiXiaXianZi');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1;
+----+-----------+-------------+
| id | name      | hobby       |
+----+-----------+-------------+
|  1 | SunWuKong | ZiXiaXianZi |
+----+-----------+-------------+
1 row in set (0.00 sec)

mysql>

2.从库执行命令操作

[root@k8s-cluster251 manifests]# kubectl exec -it sts-mysql-1 -c c1 -- mysql -pyinzhengjie -e "SELECT * FROM wordpress.t1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-----------+-------------+
| id | name      | hobby       |
+----+-----------+-------------+
|  1 | SunWuKong | ZiXiaXianZi |
+----+-----------+-------------+
[root@k8s-cluster251 manifests]# 
[root@k8s-cluster251 manifests]# kubectl exec -it sts-mysql-2 -c c1 -- mysql -pyinzhengjie -e "SELECT * FROM wordpress.t1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-----------+-------------+
| id | name      | hobby       |
+----+-----------+-------------+
|  1 | SunWuKong | ZiXiaXianZi |
+----+-----------+-------------+
[root@k8s-cluster251 manifests]# 


[root@k8s-cluster251 manifests]# kubectl exec -it sts-mysql-1 -c c1 -- mysql -pyinzhengjie -e "SHOW SLAVE STATUS\G" 
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: sts-mysql-0.master-slave
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1427
               Relay_Log_File: sts-mysql-1-relay-bin.000002
                Relay_Log_Pos: 926
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
			...
			
			
[root@k8s-cluster251 manifests]# kubectl exec -it sts-mysql-2 -c c1 -- mysql -pyinzhengjie -e "SHOW SLAVE STATUS\G" 
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: sts-mysql-0.master-slave
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1427
               Relay_Log_File: sts-mysql-2-relay-bin.000002
                Relay_Log_Pos: 926
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
			  ...

此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。原文来自 — 版权归原作者所有。

推荐订阅源

博客园 - 尹正杰

一.k8s基于sts实现MySQL主从方式

1.解法一: 基于启动命令行操作

2.解法二: 基于lifecycle的postStart机制

二.测试验证

1.主库执行命令操作

2.从库执行命令操作