k8s之xtrackup备份MySQL主从节点测试分析

xtrackup备份测试

实验环境

k8s环境参考: k8s-v1.20.10 二进制部署指导文档

测试原因

由于之前本人在网上查找k8s部署MySQL主从的时候,很多博客文章都在statefulset中有如下类型的逻辑判断,认为xtrackup备份出来的文件有xtrabackup_binlog_info而没有xtrabackup_slave_info,这种情况属于备份Master节点,既有xtrabackup_binlog_info又有xtrabackup_slave_info属于备份Slave节点,在后续测试中发现并不是如此,实际上是xtrabackup备份Master、Slave这二个文件都有,不同的是备份Slave文件时,如果加了--slave-info参数,该文件内容类似这样:CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;

 command:
        - bash
        - "-c"
        - |
          set -ex
          cd /var/lib/mysql  # 要先进到这个目录,否则xbk后续流式传输时会报错
          # xtrabackup备份后会生成一个文件,有两种情况,xtrabackup_slave_info和xtrabackup_binlog_info
          # 如果不是Master节点,xtrabackup_slave_info是记录了信息的,例如:CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=437;
          if [[ -f xtrabackup_slave_info ]];then
              # xtrabackup_slave_info存在则表示这个备份来自一个从节点,文件包含change master to sql语句,将这个文件改为change_master_to.sql.in
              cat xtrabackup_slave_info | sed -E 's/;$//g' > change_master_to.sql.in
              rm -f xtrabackup_slave_info
          elif [[ -f xtrabackup_binlog_info ]];then
              # 如果只存在xtrabackup_binlog_info文件则备份来自主节点,这个文件包含了bin-log文件名和position偏移量,需要我们自己解析成change master to sql
              # 使用正则解析获取binlog信息,并生成change master to sql
              [[ `cat xtrabackup_binlog_info` =~ ^(.*?)[[:space:]]+([0-9]+)$ ]] || exit 1
              echo "CHANGE MASTER TO MASTER_LOG_FILE='${BASH_REMATCH[1]}', MASTER_LOG_POS=${BASH_REMATCH[2]}" > change_master_to.sql.in
              # 删除xtrabackup_binlog_info,防止下一次没有经过备份时,重复生成change_master_to.sql.in
              rm -f xtrabackup_binlog_info
          fi
          # 判断initcontainer是否进行了备份,如果进行了备份会经过我们上面的逻辑生成change_master_to.sql.in,如果存在change_master_to.sql.in,则需要执行相应的sql
          if [[ -f change_master_to.sql.in ]];then
              # 等待mysql容器启动
              echo 'Waiting for mysqld to be ready (accept connections)'
              until mysql -h 127.0.0.1 -e 'select 1';do sleep 1;done
              sleep 5
              echo 'Initializing replication from clone position'
              # 执行change master sql
              sql="$(<change_master_to.sql.in), master_host='mysql-0.mysql', master_user='root', master_password='', master_connect_retry=10; start slave;"
              mysql -h 127.0.0.1 -e "$sql" || exit 1
              # 重命名change_master_to.sql.in文件,防止重复执行change master
              mv change_master_to.sql.in change_master_to.sql.in.orig
          fi
          # 使用ncat监听3307端口,在收到传输请求时会执行xtrabackup备份操作,然后传输数据给请求数据的节点
          # 使用exec将ncat作为常驻进程,决定这个容器的生命周期
          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@k8s-master-1 MySQL]# cat test.yaml 
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-test
data:
  mysql-1: |
    [mysqld]
    log_bin=mysql-bin #写文件名,默认是以/var/lib/mysql为路径
    server_id=1
  mysql-2: |
    [mysqld]
    log_bin=mysql-bin
    server_id=2
---
apiVersion: v1
kind: Pod
metadata:
  name: mysql-1
spec:
  containers:
  - name: mysql-1
    image: registry.cn-hangzhou.aliyuncs.com/jiayu-kubernetes/mysql:5.7.36
    imagePullPolicy: IfNotPresent
    ports:
    - containerPort: 3306 
    env:
    - name: MYSQL_ROOT_PASSWORD
      value: "password"
    volumeMounts:
    - name: config-volume
      mountPath: /etc/mysql/my.cnf
      subPath: mysql-1
  volumes:
  - name: config-volume
    configMap:
      name: mysql-test
---
apiVersion: v1
kind: Pod   
metadata:   
  name: mysql-2
spec:       
  containers:
  - name: mysql-2
    image: registry.cn-hangzhou.aliyuncs.com/jiayu-kubernetes/mysql:5.7.36
    imagePullPolicy: IfNotPresent
    ports:
    - containerPort: 3306 
    env:
    - name: MYSQL_ROOT_PASSWORD
      value: "password"
    volumeMounts:
    - name: config-volume
      mountPath: /etc/mysql/my.cnf
      subPath: mysql-1
  volumes:
  - name: config-volume
    configMap:
      name: mysql-test
---
apiVersion: v1
kind: Pod   
metadata:   
  name: "xtrabackup"
spec:       
  containers:
  - name: "xtrabackup"
    image: registry.cn-hangzhou.aliyuncs.com/jiayu-kubernetes/xtrabackup:v2.4.24
    command: ["/bin/bash","-c","sleep 8640000"]
[root@k8s-master-1 MySQL]# kubectl get pods
NAME         READY   STATUS    RESTARTS   AGE
mysql-1      1/1     Running   0          2m30s
mysql-2      1/1     Running   0          2m30s
xtrabackup   1/1     Running   0          2m30s

搭建MySQL主从

# 查看pod ip
[root@k8s-master-1 MySQL]# kubectl get pods -o wide
NAME         READY   STATUS    RESTARTS   AGE   IP           NODE         NOMINATED NODE   READINESS GATES
mysql-1      1/1     Running   0          11m   10.70.2.25   k8s-node-1   <none>           <none>
mysql-2      1/1     Running   0          11m   10.70.2.26   k8s-node-1   <none>           <none>
xtrabackup   1/1     Running   0          11m   10.70.2.27   k8s-node-1   <none>           <none>

# 查看 mysql-1 binglog信息
[root@k8s-master-1 MySQL]# kubectl exec -it mysql-1 -- mysql -uroot -ppassword -e 'show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

# mysql-1 创建repl 同步账号
[root@k8s-master-1 MySQL]# kubectl exec -it mysql-1 -- mysql -uroot -ppassword -e "grant replication slave on *.* to repl@'%' identified by 'password';"
mysql: [Warning] Using a password on the command line interface can be insecure.


# mysql-2同步mysql-1数据,可以发现mysql-2已经同步mysql-1的数据了
[root@k8s-master-1 MySQL]# kubectl exec -it mysql-2 -- mysql -uroot -ppassword -e "CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=154,MASTER_HOST='10.70.2.25',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='password',MASTER_CONNECT_RETRY=10; start slave; show slave status\G"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 10.70.2.25
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-2-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 

xtrabackup备份

由于我自制xtrabackup镜像在远程备份的时候,报错了,后续我在mysql-1,mysql-2安装xtrabackup上测试的

# xtrabackup 备份mysql-1,由于xtrabackup不知道什么原因,有问题,这里只说下测试结果

root@mysql-1:/tmp/mysql# xtrabackup --backup --slave-info --host=127.0.0.1 --user=root --password=password --target-dir=/tmp/mysql

root@mysql-1:/tmp/mysql# ls
backup-my.cnf	ibdata1  performance_schema  xtrabackup_binlog_info  xtrabackup_info	 xtrabackup_slave_info
ib_buffer_pool	mysql	 sys		     xtrabackup_checkpoints  xtrabackup_logfile

# 可以发现xtrabackup_slave文件信息为空,和之前测试一样,所以statefulset中command判断逻辑需要改
root@mysql-1:/tmp/mysql# cat xtrabackup_slave_info 
root@mysql-1:/tmp/mysql# 
# xtrabackup 备份mysql-2
root@mysql-2:/tmp/mysql# xtrabackup --backup --slave-info --host=127.0.0.1 --user=root --password=password --target-dir=/tmp/mysql

root@mysql-2:/tmp/mysql# ls
backup-my.cnf	ibdata1  performance_schema  xtrabackup_binlog_info  xtrabackup_info	 xtrabackup_slave_info
ib_buffer_pool	mysql	 sys		     xtrabackup_checkpoints  xtrabackup_logfile

# 可以发现xtrabackup_slave_info中含有数据了,后续节点根据这里的信息去同步Master节点即可
root@mysql-2:/tmp/mysql# cat xtrabackup_slave_info 
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;
上一篇:CentOS7环境下MySQL的主从配置


下一篇:spring动态切换多数据源