Centos7 k8s 集群 - Mysql主从架构

项目地址

• MysqlCluster

configMap

mysql-configmap.yaml

apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql
  labels:
    app: mysql
data:
  primary.cnf: |
    # 主节点MySQL的配置文件
    [mysqld]
    # 使用二进制日志文件的方式进行主从复制，这是一个标准的设置
    log-bin
  replica.cnf: |
    # 从节点MySQL的配置文件
    [mysqld]
    # 代表的是从节点会拒绝除了主节点的数据同步操作之外的所有写操作，即：它对用户是只读的
    super-read-only

configmap资源

kubectl apply -f mysql-configmap.yaml

service

mysql-services.yaml

# Headless Service。类似绑定了mysql-0的IP地址
# 通过为 Pod 分配 DNS 记录来固定它的拓扑状态，比如“mysql-0.mysql”和“mysql-1.mysql”这样的 DNS 名字
# 其中，编号为 0 的节点就是我们的主节点
# 写请求，则必须直接以 DNS 记录的方式访问到 MySQL 的主节点
apiVersion: v1
kind: Service
metadata:
  name: mysql
  labels:
    app: mysql
spec:
  ports:
  - name: mysql
    port: 3306
  clusterIP: None
  selector:
    app: mysql
---
# 常规的 Service。用于负载均衡
# 所有用户的读请求，都必须访问第二个 Service 被自动分配的 DNS 记录
# 即：“mysql-read”（当然，也可以访问这个 Service 的 VIP）
# 读请求就可以被转发到任意一个 MySQL 的主节点或者从节点上
apiVersion: v1
kind: Service
metadata:
  name: mysql-read
  labels:
    app: mysql
spec:
  ports:
  - name: mysql
    port: 3306
  selector:
    app: mysql

创建service资源

kubectl apply -f mysql-services.yaml

StatefulSet

mysql-statefulset.yaml

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
spec:
  selector:
    # 这个 StatefulSet 要管理的 Pod 必须携带 app=mysql 标签
    # 它声明要使用的 Headless Service 的名字是：mysql
    matchLabels:
      app: mysql
      app.kubernetes.io/name: mysql
  serviceName: mysql
  # 这个 StatefulSet 的 replicas 值是 3，表示它定义的 MySQL 集群有三个节点
  # 一个 Master 节点，两个 Slave 节点
  replicas: 3
  template:
    metadata:
      labels:
        app: mysql
        app.kubernetes.io/name: mysql
    spec:
      # InitContainer 应该是排头兵，做了一系列的准备工作：确定M/S角色、拷贝配置文件、挂载存储卷
      # 当 InitContainer 复制完配置文件退出后，后面启动的 MySQL 容器只需要直接声明挂载这个名叫 conf 的 Volume
      # 它所需要的.cnf 配置文件已经出现在里面了
      initContainers:
      - name: init-mysql
        image: mysql:5.7
        command:
        - bash
        - "-c"
        - |
          set -ex
          # 从Pod的序号, 生成server-id
          # 通过这个序号，判断当前 Pod 到底是 Master 节点（即：序号为 0)还是 Slave 节点（即：序号不为 0)
          [[ $HOSTNAME =~ -([0-9]+)$ ]] || exit 1
          ordinal=${BASH_REMATCH[1]}
          echo [mysqld] > /mnt/conf.d/server-id.cnf
          # 由于server-id=0有特殊含义, 我们给ID加一个100来避开它
          echo server-id=$((100 + $ordinal)) >> /mnt/conf.d/server-id.cnf
          # 如果Pod序号是0, 说明它是Master节点, 从ConfigMap里把Master的配置文件拷贝到/mnt/conf.d/目录. 否则，拷贝Slave的配置文件
          if [[ $ordinal -eq 0 ]]; then
            cp /mnt/config-map/primary.cnf /mnt/conf.d/
          else
            cp /mnt/config-map/replica.cnf /mnt/conf.d/
          fi          
        volumeMounts:
          # 而文件拷贝的目标目录，即容器里的 /mnt/conf.d/ 目录
          # 对应的则是一个名叫 conf 的、emptyDir 类型的 Volume
        - name: conf
          mountPath: /mnt/conf.d
          # ConfigMap 里保存的内容，就会以文件的方式出现在它的 /mnt/config-map 目录
        - name: config-map
          mountPath: /mnt/config-map
      - name: clone-mysql
        # 在 Slave Pod 启动前，从 Master 或者其他 Slave Pod 里拷贝数据库数据到自己的目录下
        # 
        image: yizhiyong/xtrabackup
        command:
        - bash
        - "-c"
        - |
          set -ex
          # 拷贝操作只需要在第一次启动时进行，所以如果数据已经存在，跳过
          [[ -d /var/lib/mysql/mysql ]] && exit 0
          # Master节点(序号为0)不需要做这个操作
          [[ `hostname` =~ -([0-9]+)$ ]] || exit 1
          ordinal=${BASH_REMATCH[1]}
          [[ $ordinal -eq 0 ]] && exit 0
          # 使用ncat指令，远程地从前一个节点拷贝数据到本地.保证上一个pod是启动运行状态的
          # 3307 是一个特殊端口，运行着一个专门负责备份 MySQL 数据的辅助进程
          ncat --recv-only mysql-$(($ordinal-1)).mysql 3307 | xbstream -x -C /var/lib/mysql
          # 执行--prepare，这样拷贝来的数据就可以用作恢复了
          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_ALLOW_EMPTY_PASSWORD
          value: "1"
        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"]
          initialDelaySeconds: 30
          periodSeconds: 10
          timeoutSeconds: 5
        readinessProbe:
          exec:
            # Check we can execute queries over TCP (skip-networking is off).
            command: ["mysql", "-h", "127.0.0.1", "-uroot", "-e", "SELECT 1"]
          initialDelaySeconds: 5
          periodSeconds: 2
          timeoutSeconds: 1
      - name: xtrabackup
        # 如何在 Slave 节点的 MySQL 容器第一次启动之前，执行初始化 SQL
        image: yizhiyong/xtrabackup
        ports:
        - name: xtrabackup
          containerPort: 3307
        command:
        - bash
        - "-c"
        - |
          set -ex
          cd /var/lib/mysql

          # 从备份信息文件里读取MASTER_LOG_FILEM和MASTER_LOG_POS这两个字段的值，用来拼装集群初始化SQL
          if [[ -f xtrabackup_slave_info && "x$(<xtrabackup_slave_info)" != "x" ]]; then
            # 如果xtrabackup_slave_info文件存在，说明这个备份数据来自于另一个Slave节点
            # 这种情况下，XtraBackup工具在备份的时候，就已经在这个文件里自动生成了"CHANGE MASTER TO" SQL语句
            # 所以，我们只需要把这个文件重命名为change_master_to.sql.in，后面直接使用即可
            cat xtrabackup_slave_info | sed -E 's/;$//g' > change_master_to.sql.in
            # 所以，也就用不着xtrabackup_binlog_info了
            rm -f xtrabackup_slave_info xtrabackup_binlog_info
          elif [[ -f xtrabackup_binlog_info ]]; then
            # 如果只存在xtrabackup_binlog_inf文件，那说明备份来自于Master节点
            # 我们就需要解析这个备份信息文件，读取所需的两个字段的值
            [[ `cat xtrabackup_binlog_info` =~ ^(.*?)[[:space:]]+(.*?)$ ]] || exit 1
            rm -f xtrabackup_binlog_info xtrabackup_slave_info
            echo "CHANGE MASTER TO MASTER_LOG_FILE='${BASH_REMATCH[1]}',\
                  MASTER_LOG_POS=${BASH_REMATCH[2]}" > change_master_to.sql.in
          fi

          # 如果change_master_to.sql.in存在，就意味着需要做集群初始化工作
          if [[ -f change_master_to.sql.in ]]; then
            # 但一定要先等MySQL容器启动之后才能进行下一步连接MySQL的操作
            echo "Waiting for mysqld to be ready (accepting connections)"
            until mysql -h 127.0.0.1 -e "SELECT 1"; do sleep 1; done

            echo "Initializing replication from clone position"
            # 将文件change_master_to.sql.in改个名字，防止这个Container重启的时候
            # 因为又找到了change_master_to.sql.in，从而重复执行一遍这个初始化流程
            mysql -h 127.0.0.1 \
                  -e "$(<change_master_to.sql.in), \
                          MASTER_HOST='mysql-0.mysql', \
                          MASTER_USER='root', \
                          MASTER_PASSWORD='', \
                          MASTER_CONNECT_RETRY=10; \
                        START SLAVE;" || exit 1
            # 使用change_master_to.sql.orig的内容，也是就是前面拼装的SQL，组成一个完整的初始化和启动Slave的SQL语句
            mv change_master_to.sql.in change_master_to.sql.orig
          fi

          # 使用ncat监听3307端口。它的作用是，在收到传输请求的时候
          # 直接执行"xtrabackup --backup"命令，备份MySQL的数据并发送给请求者
          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"          
        volumeMounts:
        - name: data
          mountPath: /var/lib/mysql
          subPath: mysql
        - name: conf
          mountPath: /etc/mysql/conf.d
        resources:
          requests:
            cpu: 100m
            memory: 100Mi
      volumes:
      - name: conf
        emptyDir: {}
        # 一个名叫 conf 的、emptyDir 类型的 Volume
      - name: config-map
        configMap:
          name: mysql
  # PVC 模板。来为每个 Pod 定义 PVC
  # 这个 PVC 模板的 resources.requests.strorage 指定了存储的大小为 10 GiB
  volumeClaimTemplates:
  - metadata:
      name: data
    spec:
      storageClassName: rook-ceph-block
      # ReadWriteOnce 指定了该存储的属性为可读写，并且一个 PV 只允许挂载在一个宿主机上
      accessModes: ["ReadWriteOnce"]
      resources:
        requests:
          storage: 10Gi

创建mysql资源

kubectl apply -f mysql-statefulset.yaml

容器启动查询

init-mysql

[root@master ~]# kubectl logs mysql-0 init-mysql
+ [[ mysql-0 =~ -([0-9]+)$ ]]
+ ordinal=0
+ echo '[mysqld]'
+ echo server-id=100
+ [[ 0 -eq 0 ]]
+ cp /mnt/config-map/primary.cnf /mnt/conf.d/

mysql

[root@master ~]# kubectl logs mysql-0 init-mysql
+ [[ mysql-0 =~ -([0-9]+)$ ]]
+ ordinal=0
+ echo '[mysqld]'
+ echo server-id=100
+ [[ 0 -eq 0 ]]
+ cp /mnt/config-map/primary.cnf /mnt/conf.d/
[root@master ~]#  kubectl logs mysql-0 mysql
2023-03-24 07:09:15+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.36-1debian10 started.
2023-03-24 07:09:15+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2023-03-24 07:09:15+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.36-1debian10 started.
2023-03-24T07:09:15.578039Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-03-24T07:09:15.579656Z 0 [Note] mysqld (mysqld 5.7.36-log) starting as process 1 ...
2023-03-24T07:09:15.580982Z 0 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=mysql-0-bin' to avoid this problem.
2023-03-24T07:09:15.607203Z 0 [Note] InnoDB: PUNCH HOLE support available
2023-03-24T07:09:15.607219Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2023-03-24T07:09:15.607222Z 0 [Note] InnoDB: Uses event mutexes
2023-03-24T07:09:15.607224Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2023-03-24T07:09:15.607226Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2023-03-24T07:09:15.607229Z 0 [Note] InnoDB: Using Linux native AIO
2023-03-24T07:09:15.607506Z 0 [Note] InnoDB: Number of pools: 1
2023-03-24T07:09:15.607671Z 0 [Note] InnoDB: Using CPU crc32 instructions
2023-03-24T07:09:15.609449Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2023-03-24T07:09:15.617162Z 0 [Note] InnoDB: Completed initialization of buffer pool
2023-03-24T07:09:15.620370Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2023-03-24T07:09:15.644357Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2023-03-24T07:09:16.057153Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2023-03-24T07:09:16.057222Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2023-03-24T07:09:19.387580Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2023-03-24T07:09:19.388076Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2023-03-24T07:09:19.388095Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2023-03-24T07:09:19.388889Z 0 [Note] InnoDB: Waiting for purge to start
2023-03-24T07:09:19.446231Z 0 [Note] InnoDB: 5.7.36 started; log sequence number 12666149
2023-03-24T07:09:19.447072Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2023-03-24T07:09:19.447166Z 0 [Note] Plugin 'FEDERATED' is disabled.
2023-03-24T07:09:19.694456Z 0 [Note] InnoDB: Buffer pool(s) load completed at 230324  7:09:19
2023-03-24T07:09:19.787835Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2023-03-24T07:09:19.787892Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2023-03-24T07:09:19.787897Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2023-03-24T07:09:19.787898Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-03-24T07:09:19.790876Z 0 [Warning] CA certificate ca.pem is self signed.
2023-03-24T07:09:19.790926Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2023-03-24T07:09:19.794145Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2023-03-24T07:09:19.794362Z 0 [Note] IPv6 is available.
2023-03-24T07:09:19.794440Z 0 [Note]   - '::' resolves to '::';
2023-03-24T07:09:19.794469Z 0 [Note] Server socket created on IP: '::'.
2023-03-24T07:09:19.795754Z 0 [Warning] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2023-03-24T07:09:20.029315Z 0 [Note] Failed to start slave threads for channel ''
2023-03-24T07:09:20.567496Z 0 [Note] Event Scheduler: Loaded 0 events
2023-03-24T07:09:20.568119Z 0 [Note] mysqld: ready for connections.
Version: '5.7.36-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server (GPL)
2023-03-24T07:09:46.837520Z 9 [Warning] IP address '10.244.4.62' could not be resolved: Name or service not known
2023-03-24T07:09:46.844496Z 9 [Note] Start binlog_dump to master_thread_id(9) slave_server(101), pos(mysql-0-bin.000006, 756)
2023-03-24T07:10:40.115743Z 43 [Warning] IP address '10.244.3.116' could not be resolved: Name or service not known
2023-03-24T07:10:40.173087Z 43 [Note] Start binlog_dump to master_thread_id(43) slave_server(102), pos(mysql-0-bin.000006, 756)

xtrabackup

[root@master ~]# kubectl logs mysql-0 xtrabackup
+ cd /var/lib/mysql
+ [[ -f xtrabackup_slave_info ]]
+ [[ -f xtrabackup_binlog_info ]]
+ [[ -f change_master_to.sql.in ]]
+ 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'

案例验证

POD 查询

[root@master ~]# kubectl get pod -l app=mysql
NAME      READY   STATUS    RESTARTS   AGE
mysql-0   2/2     Running   0          4m5s
mysql-1   2/2     Running   0          3m10s
mysql-2   2/2     Running   0          2m45s

表创建

kubectl run mysql-client --image=mysql:5.7 -i --rm --restart=Never --\
  mysql -h mysql-0.mysql -uroot -e'
CREATE DATABASE test;
CREATE TABLE test.messages (message VARCHAR(250));
INSERT INTO test.messages VALUES ('hello');
'

表查询

kubectl run mysql-client --image=mysql:5.7 -i --rm --restart=Never -- mysql -h mysql-read -uroot -e'SELECT * FROM test.messages'

If you don't see a command prompt, try pressing enter.
message
hello
pod "mysql-client" deleted