k8s之xtrackup备份MySQL主从节点测试分析
2021/10/29 2:12:03
本文主要是介绍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;
这篇关于k8s之xtrackup备份MySQL主从节点测试分析的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-06-25MySQL报错Duplicate entry '0' for key 'PRIMARY'
- 2024-05-29阿里 Canal 实时同步 MySQL 增量数据至 ClickHouse 数据库
- 2024-05-24在Linux下管理MySQL的大小写敏感性
- 2024-04-26MySQL查出时间比实际晚8小时的解决方案
- 2024-04-01JPA不识别MySQL的枚举类型
- 2024-03-30mysql数据库表卡死解决方法
- 2024-03-15MySQL多数据源笔记5-ShardingJDBC实战
- 2024-03-11natural join mysql
- 2024-03-11关于VS2017，VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案
- 2024-02-26mysql 阿里云xb后缀备份文件恢复-icode9专业技术文章分享
