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

博客园 - 尹正杰

Categraf使用指南 window系统部署Claude Code实战 N9E集群部署 Ubuntu 24.04 LTS部署Codex实战 aiops运维平台从0到1部署实战 aiops平台环境部署 TKE集群LoadBalancer映射案例 k8s集群为每个节点分配不通的ippool实战 ansible剧本实现一键升级K8S集群的NVIDIA GPU驱动 vmagent采集etcd数据并写入victoriametrics集群 VictoriaMetrics集群部署实战 Squid正向代理实战 kubespray生产故障案例之kubelet启动参数案例 filebeat指定名称空间采集pod数据 Calico自定义Ipool实战 ansible剧本实现kibana的部署和卸载 ansible剧本应用案例集合2 ansible剧本实现磁盘格式化 Ansible急速入门实战篇 ansible快速入门篇 k8s集群管理 kubesray实战 kubespray实战案例 kubespray管理k8s的worker集群扩缩容 kubespray快速部署k8s集群实战 Kubeasz使用吐槽博客专题 Kubeasz基于ezctl实现etcd集群的管理实战 Kubeasz基于ezctl实现k8s集群一键升级 Calico启用纯BGP模式+RR实战案例 Calico 底层原理及IPIP（依赖BGP协议）)和vxlan（不依赖BGP）工作模式切换

尹正杰 · 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.从库执行命令操作