K8s集群中部署Mysql一主两从集群

'# K8s集群中部署Mysql一主两从集群

一、背景与问题

在Kubernetes集群中部署MySQL一主两从架构，是实现数据库高可用、读写分离和负载均衡的典型场景。这种架构通过主从复制机制，将读请求分发到从库，同时通过主库的写操作同步到从库，可有效提升系统吞吐量。

但实际部署中常遇到如下挑战：

网络拓扑导致主从同步延迟
状态管理复杂性
数据持久化与恢复机制
故障转移时的自动切换
配置错误导致的复制中断

二、基本原理

MySQL主从复制的核心原理是通过binlog日志实现数据同步。主库将事务日志记录到binlog中，从库通过I/O线程获取binlog，通过SQL线程重放日志实现数据同步。

在K8s中部署时需要考虑：

有序部署：使用StatefulSet保证Pod的有序创建和标识
网络策略：通过Headless Service实现主从通信
持久化存储：使用PersistentVolume保证数据持久化
初始化容器：在Pod启动时执行初始化脚本
复制配置：通过配置文件设置主从关系

三、环境准备

apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-config
data:
  my.cnf: |
    [mysqld]
    server-id=1
    log-bin=mysql-bin
    binlog-format=ROW
    sync-binlog=1
    innodb_flush_log_at_trx_commit=1
    # 主库配置
    # log-bin=mysql-bin
    # binlog-format=ROW
    # sync-binlog=1
    # innodb_flush_log_at_trx_commit=1

apiVersion: v1
kind: PersistentVolume
metadata:
  name: mysql-pv
spec:
  capacity:
    storage: 10Gi
  accessModes:
    - ReadWriteMany
  storageClassName: standard

四、核心实现

1. StatefulSet配置（主库）

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
spec:
  serviceName: mysql
  replicas: 3
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - name: mysql
        image: mysql:8.0
        ports:
        - containerPort: 3306
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: "rootpass"
        volumeMounts:
        - name: mysql-data
          mountPath: /var/lib/mysql
        lifecycle:
          postStart:
            exec:
              command: ["sh", "-c", "chown -R mysql:mysql /var/lib/mysql && /usr/bin/mysqld --initialize-insecure --user=mysql"]
      initContainers:
      - name: init-mysql
        image: mysql:8.0
        command:
        - sh
        - "-c"
        - |
          # 初始化主库
          if [ "$MYSQL_HOST" = "mysql-0.mysql" ]; then
            # 主库初始化
            mysql -u root -p"rootpass" -e "CREATE USER 'repl'@'%' IDENTIFIED BY 'replpass';"
            mysql -u root -p"rootpass" -e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'replpass';"
            mysql -u root -p"rootpass" -e "FLUSH PRIVILEGES;"
          elif [ "$MYSQL_HOST" = "mysql-1.mysql" ]; then
            # 从库初始化
            mysql -u root -p"rootpass" -e "CHANGE MASTER TO MASTER_HOST='mysql-0.mysql', MASTER_USER='repl', MASTER_PASSWORD='replpass', MASTER_AUTO_POSITION=1;"
            mysql -u root -p"rootpass" -e "START SLAVE;"
          fi
        env:
        - name: MYSQL_HOST
          valueFrom:
            fieldRef:
              fieldPath: metadata.name
        volumeMounts:
        - name: mysql-data
          mountPath: /var/lib/mysql
      volumes:
      - name: mysql-data
        persistentVolumeClaim:
          claimName: mysql-pv

2. Headless Service配置

apiVersion: v1
kind: Service
metadata:
  name: mysql
spec:
  clusterIP: None
  ports:
  - port: 3306
    protocol: TCP
  selector:
    app: mysql

3. 副本配置策略

apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-replication
data:
  replication: |
    # 主库配置
    [mysqld]
    server-id=1
    log-bin=mysql-bin
    binlog-format=ROW
    sync-binlog=1
    innodb_flush_log_at_trx_commit=1

    # 从库配置
    [mysqld]
    server-id=2
    relay-log=mysql-relay
    relay-log-index=mysql-relay.index
    read-only
    replicate-do-db=mydb
    replicate-ignore-db=information_schema
    replicate-ignore-db=performance_schema
    replicate-ignore-db=mysql

五、完整案例

以下是一个完整的部署案例，包含主库和两个从库的配置：

apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-config
data:
  my.cnf: |
    [mysqld]
    server-id=1
    log-bin=mysql-bin
    binlog-format=ROW
    sync-binlog=1
    innodb_flush_log_at_trx_commit=1
    # 主库配置
    # log-bin=mysql-bin
    # binlog-format=ROW
    # sync-binlog=1
    # innodb_flush_log_at_trx_commit=1
    # 从库配置
    # server-id=2
    # relay-log=mysql-relay
    # relay-log-index=mysql-relay.index
    # read-only
    # replicate-do-db=mydb
    # replicate-ignore-db=information_schema
    # replicate-ignore-db=performance_schema
    # replicate-ignore-db=mysql

---
apiVersion: v1
kind: PersistentVolume
metadata:
  name: mysql-pv
spec:
  capacity:
    storage: 10Gi
  accessModes:
    - ReadWriteMany
  storageClassName: standard

---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-pvc
spec:
  accessModes:
    - ReadWriteMany
  storageClassName: standard
  resources:
    requests:
      storage: 10Gi

---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
spec:
  serviceName: mysql
  replicas: 3
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - name: mysql
        image: mysql:8.0
        ports:
        - containerPort: 3306
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: "rootpass"
        volumeMounts:
        - name: mysql-data
          mountPath: /var/lib/mysql
        lifecycle:
          postStart:
            exec:
              command: ["sh", "-c", "chown -R mysql:mysql /var/lib/mysql && /usr/bin/mysqld --initialize-insecure --user=mysql"]
      initContainers:
      - name: init-mysql
        image: mysql:8.0
        command:
        - sh
        - "-c"
        - |
          # 初始化主库
          if [ "$MYSQL_HOST" = "mysql-0.mysql" ]; then
            # 主库初始化
            mysql -u root -p"rootpass" -e "CREATE USER 'repl'@'%' IDENTIFIED BY 'replpass';"
            mysql -u root -p"rootpass" -e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'replpass';"
            mysql -u root -p"rootpass" -e "FLUSH PRIVILEGES;"
          elif [ "$MYSQL_HOST" = "mysql-1.mysql" ]; then
            # 从库初始化
            mysql -u root -p"rootpass" -e "CHANGE MASTER TO MASTER_HOST='mysql-0.mysql', MASTER_USER='repl', MASTER_PASSWORD='replpass', MASTER_AUTO_POSITION=1;"
            mysql -u root -p"rootpass" -e "START SLAVE;"
          fi
        env:
        - name: MYSQL_HOST
          valueFrom:
            fieldRef:
              fieldPath: metadata.name
        volumeMounts:
        - name: mysql-data
          mountPath: /var/lib/mysql
      volumes:
      - name: mysql-data
        persistentVolumeClaim:
          claimName: mysql-pvc

六、源码解析

1. StatefulSet配置分析

serviceName: mysql 指定Headless Service名称
replicas: 3 表示一个主库+两个从库
initContainers 用于初始化主从关系
- 主库初始化：创建复制用户、授权
- 从库初始化：设置主库信息、启动复制

2. Headless Service的作用

clusterIP: None 保证DNS解析返回Pod的IP地址
selector 确保服务指向正确的Pod
通过mysql-0.mysql等DNS名称实现主从通信

3. 环境变量配置

MYSQL_HOST 用于区分主库和从库
MYSQL_ROOT_PASSWORD 设置root密码
replpass 设置复制用户密码

七、进阶使用

1. 健康检查配置

livenessProbe:
  exec:
    command:
    - sh
    - "-c"
    - "mysqladmin -u root -p'rootpass' ping"
  initialDelaySeconds: 30
  periodSeconds: 10
readinessProbe:
  exec:
    command:
    - sh
    - "-c"
    - "mysqladmin -u root -p'rootpass' ping"
  initialDelaySeconds: 5
  periodSeconds: 5

2. 自动故障转移

apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-failover
data:
  failover: |
    # 自动故障转移脚本
    # 检测主库状态
    if [ "$MYSQL_HOST" = "mysql-0.mysql" ]; then
      # 主库检测
      if ! mysql -u root -p"rootpass" -e "SHOW STATUS LIKE 'Slave_IO_Running'" | grep -q 'Yes'; then
        # 主库故障，提升从库为新主库
        mysql -u root -p"rootpass" -e "STOP SLAVE;"
        mysql -u root -p"rootpass" -e "RESET SLAVE;"
        mysql -u root -p"rootpass" -e "CHANGE MASTER TO MASTER_HOST='mysql-1.mysql', MASTER_USER='repl', MASTER_PASSWORD='replpass', MASTER_AUTO_POSITION=1;"
        mysql -u root -p"rootpass" -e "START SLAVE;"
      fi
    fi

3. 性能调优

data:
  my.cnf: |
    [mysqld]
    server-id=1
    log-bin=mysql-bin
    binlog-format=ROW
    sync-binlog=1
    innodb_flush_log_at_trx_commit=1
    innodb_buffer_pool_size=1G
    innodb_log_file_size=128M
    innodb_log_files_in_group=4
    query_cache_type=OFF
    query_cache_size=0

八、性能与工程实践

1. 性能优化策略

调整innodb_buffer_pool_size：根据可用内存设置，建议设置为物理内存的70%-80%
优化binlog格式：使用ROW格式可提高复制可靠性
调整同步方式：使用sync-binlog=1保证数据一致性
启用压缩：在复制过程中启用压缩减少网络传输
定期清理binlog：使用PURGE BINARY LOGS清理旧日志

2. 安全实践

最小权限原则：复制用户仅需REPLICATION权限
SSL加密：配置SSL证书进行通信加密
定期备份：使用mysqldump或xtrabackup进行定期备份
访问控制：通过K8s的NetworkPolicy限制访问
审计日志：开启general_log和slow_log进行监控

3. 异常处理

复制延迟监控：
```
SHOW SLAVE STATUS\G
```
关注Seconds_Behind_Master指标

主从同步异常处理：

# 停止从库复制
mysql -u root -p"rootpass" -e "STOP SLAVE;"

# 重置从库
mysql -u root -p"rootpass" -e "RESET SLAVE;"

# 重新配置主库
mysql -u root -p"rootpass" -e "CHANGE MASTER TO MASTER_HOST='mysql-0.mysql', MASTER_USER='repl', MASTER_PASSWORD='replpass', MASTER_AUTO_POSITION=1;"

# 启动复制
mysql -u root -p"rootpass" -e "START SLAVE;"

九、常见问题与踩坑

1. 主从同步延迟问题

现象：Seconds_Behind_Master持续增大
原因：

网络带宽不足
主库写入压力过大
从库配置不当

解决：

检查网络QoS策略
调整innodb_flush_log_at_trx_commit为2
增加从库资源

2. 配置错误导致复制失败

错误示例：

# 错误的配置
spec:
  replicas: 2
  selector:
    matchLabels:
      app: mysql

问题：未指定serviceName导致无法发现Pod

改进：

spec:
  serviceName: mysql
  replicas: 3

3. 状态管理问题