Kubernetes 完美部署MySQL主从集群

妖狐艹你老母 2023-03-01 09:13 57阅读 0赞

> **在Kubernetes中可以快速MySQL主从集群，但需要解决以下问题：**
> 
> 1.  主从节点关系自动建立；
> 2.  实现一主一从或一主多从，并且读写分离；
> 3.  从节点server-id自动生成，并且不能重复；
> 4.  自愈功能，主节点或从节点Pod重启后，主从复制状态自动恢复，且数据不丢失；
> 5.  数据库数据持久化存储。

### 1. 简化MySQL主从配置步骤 ###

在Kubernetes中部署MySQL主从集群，需要使用GTID代替传统复制技术classic，简化主从配置。

**全局事物标识：Global Transaction Identifieds。**

1.  GTID事物是全局唯一性的，且一个事务对应一个GTID。
2.  一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。
3.  GTID用来代替classic的复制方法，不在使用binlog+pos开启复制。而是使用master\_auto\_postion=1的方式自动匹配GTID断点进行复制。
4.  建议在MySQL 5.7或以上版本中使用。

**GTID比传统复制classic的优势：**

1.  更简单的实现failover，不用以前那样在需要找log\_file和log\_pos。
2.  更简单的搭建主从复制，比传统复制更加安全。
3.  Slave保障节点Pod重启后，主从复制状态自动恢复，且数据不丢失。

**开启GTID:**

开启GTID非常简单，在MySQL配置文件中增加：  gtid\_mode=on

### 2. MySQL主从节点关系自动建立 ###

MySQL Docker镜像中有一个文件夹 /docker-entrypoint-initdb.d/，该文件夹中的.sql .sh文件都会被自动执行，但是需要注意该文件夹中的.sql .sh文件只在MySQL容器第一次初始化数据库时才会自动执行。

因此我们可以将MySQL Master和Slave初始化主从关系的SQL语句放在 /docker-entrypoint-initdb.d/文件夹下。

**MySQL Master节点** \- 初始化主从命令

> \-- create repl user  
> CREATE USER 'xxxx'@'%' IDENTIFIED WITH mysql\_native\_password BY 'xxxx';
> 
> \-- grant repl  
> GRANT REPLICATION SLAVE ON \*.\* TO 'xxxx'@'%';

**MySQL Slave节点 -** 初始化主从命令

> CHANGE MASTER TO MASTER\_HOST='xxxx', MASTER\_USER='xxxx', MASTER\_PASSWORD='xxxx', master\_auto\_position=1;

### 3. 从节点server-id自动生成 ###

从节点需要生成唯一的server-id，可以通过InitContainer为Slave节点自动生成server-id

> initContainers:  \# 用于生成slave server-id,确保多个slave server-id不重复  
> \- name: init-mysql  
>   image: mysql:latest  
>   imagePullPolicy: IfNotPresent  
>   command:  
>     - bash  
>     - "-c"  
>     - |  
>       set -ex
> 
>       \# 从config-map中复制配置文件到emptyDir，因为config-map目录为只读  
>       cp /etc/mysql-slave-conf/mysql-slave.cnf /etc/conf.d/mysql-slave.cnf
> 
>       \# 根据Pod序号生成server-id, =~ 判断字符串包含关系，BASH\_REMATCH变量存储匹配结果  
>       \[\[ \`hostname\` =~ -(\[0-9\]+)$ \]\] || exit 1  
>       pod\_number=$\{BASH\_REMATCH\[1\]\}
> 
>       \# 将生成的server-id写入mysql-slave.cnf  
>       echo -e "\\nserver-id=$((100 + pod\_number))" >> /etc/conf.d/mysql-slave.cnf

### 4. MySQL主从自愈 ###

MySQL数据库数据会存储在/var/lib/mysql文件夹下，因此只需要使用PVC对该文件夹做持久化存储即可。

MySQL Master节点在/var/lib/mysql做持久化存储后，重启或重建Pod都没有任何影响。

MySQL Slave节点在/var/lib/mysql做持久化存储后，重启或重建Pod都会丢失和Master节点的复制关系，需要重置复制关系。

可以在MySQL Slave节点在重启或重建Pod时执行以下Shell命令即可实现MySQL Slave节点自愈：

> \# wait mysqld running  
> until mysql -uroot -p$\{MYSQL\_ROOT\_PASSWORD\} -e "SELECT 1"; do sleep 2; done
> 
> \# start slave sql script  
> mysql -uroot -p$\{MYSQL\_ROOT\_PASSWORD\} <<EOF  
> stop slave;  
> reset slave;  
> CHANGE MASTER TO MASTER\_HOST='xxxx', MASTER\_USER='repl', MASTER\_PASSWORD='xxxx', master\_auto\_position=1;  
> start slave;  
> EOF

### 5. MySQL配置文件 ###

可以用ConfigMap存储MySQL Master和Slave节点需要用到的配置文件、SQL语句、Shell脚本。

>     ---
>     # MySQL Conf
>     apiVersion: v1
>     kind: ConfigMap
>     metadata:
>       name: mysql-conf
>     data:
>       mysql-master.cnf: |
>         [mysqld]
>         log-bin=mysql-bin
>         server-id=1
>         gtid_mode=on
>         enforce_gtid_consistency=1
>         skip-host-cache
>         skip-name-resolve
>     
>       mysql-slave.cnf: |
>         [mysqld]
>         log-bin=mysql-bin
>         # server-id=100 通过initContainer自动生成
>         gtid_mode=on
>         enforce_gtid_consistency=1
>         read_only=on
>         skip-host-cache
>         skip-name-resolve
>     
>       init-master.sql: |
>         CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
>         GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
>         flush privileges;
>     
>       init-slave.sql: |
>         CHANGE MASTER TO MASTER_HOST='mysql-master', MASTER_USER='repl', MASTER_PASSWORD='123456', master_auto_position=1;
>         start slave;
>     
>       reset-slave.sh: |
>         #!/bin/sh
>     
>         # wait mysqld startup
>         until mysql -uroot -p${MYSQL_ROOT_PASSWORD} -e "SELECT 1"; do sleep 1; done
>     
>         # reset slave
>         mysql -uroot -p${MYSQL_ROOT_PASSWORD} <<EOF
>         stop slave;
>         reset slave;
>         CHANGE MASTER TO MASTER_HOST='mysql-master', MASTER_USER='repl', MASTER_PASSWORD='123456', master_auto_position=1;
>         start slave;
>         EOF

### 6. MySQL StatefulSet ###

> 为了简化MySQL主从配置，将MySQL Master和MySQL Slave节点分离为两个单独的StatefulSet
> 
>  *  MySQL Master节点始终将replicas设置为1
>  *  MySQL Slave节点replicas可以设置为1或更多

**mysql-master.yaml**

---
    # MySQL Master Service
    apiVersion: v1
    kind: Service
    metadata:
      name: mysql-master
    spec:
      type: ClusterIP  # 可以提供MySQL读写服务
      ports:
      - name: mysql
        port: 3306
        protocol: TCP
        targetPort: 3306
      selector:
        app: mysql-master
    
    ---
    # MySQL Master StatefulSet
    apiVersion: apps/v1
    kind: StatefulSet
    metadata:
      name: mysql-master
    spec:
      replicas: 1  # 必须是1
      selector:
        matchLabels:
          app: mysql-master
      serviceName: mysql-master
      template:
        metadata:
          labels:
            app: mysql-master
        spec:
          containers:
            - name: mysql
              image: mysql:latest
              imagePullPolicy: IfNotPresent
              livenessProbe:
                exec:
                  command: ["mysqladmin", "-uroot", "-p$(MYSQL_ROOT_PASSWORD)", "ping"]
                initialDelaySeconds: 10
                periodSeconds: 10
                timeoutSeconds: 3
              readinessProbe:
                exec:
                  command: ["mysql", "-uroot", "-p$(MYSQL_ROOT_PASSWORD)", "-e", "SELECT 1"]
                initialDelaySeconds: 10
                periodSeconds: 10
                timeoutSeconds: 3
              env:
                - name: MYSQL_ROOT_PASSWORD
                  value: "root"
              ports:
                - containerPort: 3306
                  name: mysql
                  protocol: TCP
              volumeMounts:
                - name: mysql-master-data
                  mountPath: /var/lib/mysql  # 持久化数据库
                - name: mysql-conf
                  mountPath: /etc/mysql/conf.d/  # mysql启动配置文件
                - name: mysql-init
                  mountPath: /docker-entrypoint-initdb.d/  # master初始化sql语句
          volumes:
            - name: mysql-conf
              configMap:
                name: mysql-conf
                items:
                  - key: mysql-master.cnf
                    path: mysql-master.cnf
            - name: mysql-init
              configMap:
                name: mysql-conf
                items:
                  - key: init-master.sql
                    path: init.sql
     
      volumeClaimTemplates:  # NFS提供持久化存储
        - metadata:
            name: mysql-master-data
          spec:
            accessModes:
              - ReadWriteOnce
            resources:
              requests:
                storage: 10Gi
            storageClassName: NFS

**mysql-salve.yaml**

---
    # MySQL Slave Service
    apiVersion: v1
    kind: Service
    metadata:
      name: mysql-slave
    spec:
      type: ClusterIP   # 提供MySQL只读服务
      ports:
      - name: mysql
        port: 3306
        protocol: TCP
        targetPort: 3306
      selector:
        app: mysql-slave
    
    ---
    # MySQL StatefulSet
    apiVersion: apps/v1
    kind: StatefulSet
    metadata:
      name: mysql-slave
    spec:
      replicas: 3  # 可以是1个或更多
      selector:
        matchLabels:
          app: mysql-slave
      serviceName: mysql-slave  # 必须指定service
      template:
        metadata:
          labels:
            app: mysql-slave
        spec:
          initContainers:  # 用于生成slave server-id,确保多个slave server-id不重复
            - name: init-mysql
              image: mysql:latest
              imagePullPolicy: IfNotPresent
              command:
                - bash
                - "-c"
                - |
                  set -ex
    
                  # 从config-map中复制配置文件到emptyDir，因为config-map目录为只读
                  cp /etc/mysql-slave-conf/mysql-slave.cnf /etc/conf.d/mysql-slave.cnf
    
                  # 从Pod序号生成server-id, =~ 判断字符串包含关系，BASH_REMATCH变量存储匹配结果
                  [[ `hostname` =~ -([0-9]+)$ ]] || exit 1
                  pod_number=${BASH_REMATCH[1]}
                  echo -e "\nserver-id=$((100 + pod_number))" >> /etc/conf.d/mysql-slave.cnf
    
              volumeMounts:
                - name: server-conf
                  mountPath: /etc/conf.d/
                - name: mysql-slave-conf
                  mountPath: /etc/mysql-slave-conf/
          containers:
            - name: mysql
              image: mysql:latest
              imagePullPolicy: IfNotPresent
              env:
                - name: MYSQL_ROOT_PASSWORD
                  value: "root"
                - name: REPL_USERNAME
                  value: "repl"
                - name: REPL_PASSWORD
                  value: "123456"
                - name: mysql-slave-reset
                  mountPath: /reset-slave.sh
                  subPath: reset-slave.sh
              lifecycle:
                postStart:
                  exec:
                    command: ["/bin/sh","-c","if [ -f '/var/lib/mysql/mysql-bin.index' ]; then sh /reset-slave.sh; fi"]  # 通过判断mysql-bin.index文件是否存在，来确定Pod不是初次启动，从而执行reset-slave.sh脚本，否则不执行。
              livenessProbe:
                exec:
                  command: ["mysqladmin", "-uroot", "-p$(MYSQL_ROOT_PASSWORD)", "ping"]
                initialDelaySeconds: 10
                periodSeconds: 10
                timeoutSeconds: 3
              readinessProbe:
                exec:
                  command: ["mysql", "-uroot", "-p$(MYSQL_ROOT_PASSWORD)", "-e", "SELECT 1"]
                initialDelaySeconds: 10
                periodSeconds: 10
                timeoutSeconds: 3
              ports:
                - containerPort: 3306
                  name: mysql
                  protocol: TCP
              volumeMounts:
                - name: mysql-slave-data
                  mountPath: /var/lib/mysql
                - name: server-conf
                  mountPath: /etc/mysql/conf.d/
                - name: mysql-slave-init
                  mountPath: /docker-entrypoint-initdb.d/
                - name: mysql-slave-reset
                  mountPath: /reset-slave.sh
                  subPath: reset-slave.sh   # 以单个文件形式挂载
          volumes:
            - name: server-conf
              emptyDir: {}
            - name: mysql-slave-conf
              configMap:
                name: mysql-conf
                items:
                  - key: mysql-slave.cnf
                    path: mysql-slave.cnf
            - name: mysql-slave-init
              configMap:
                name: mysql-conf
                items:
                  - key: init-slave.sql
                    path: init-slave.sql
            - name: mysql-slave-reset
              configMap:
                name: mysql-conf
                defaultMode: 0777    # 默认权限为0644，需要增加执行权限
                items:
                  - key: reset-slave.sh
                    path: reset-slave.sh
    
      volumeClaimTemplates:
        - metadata:
            name: mysql-slave-data
          spec:
            accessModes:
              - ReadWriteOnce
            resources:
              requests:
                storage: 10Gi
            storageClassName: NFS