如何优雅的备份MySQL数据

1 备份锁分析

XtraBackup 备份的时候出现锁问题，然后查文档有以下几个方案，每种方案都有优势与不足，根据需求选择，我们使用的是 kill 其他阻塞线程方案

1.1 死锁现象及原因:

1.2 Flush table with read lock

XtraBackup 可以实现 Innodb 表的无锁备份，但是一个数据库中，即使所有的业务表都是 innodb 表，但是还存在一些 MySQL 系统库下的 user 表等，均是 myisam 表（MySQL 8.0 均替换为 InnoDB），同时备份过程需要获取 Binlog 文件名和位置，也要保证表定义文件的一致性，所以从整个实例的角度，即使用 XtraBackup 还是有一段时间需要执行 Flush table with read lock 全局锁的，会对用户访问产生影响，同时由于 Flush table with read lock 的一些特殊性，如果稍不注意，可能会对用户访问数据库产生致命影响。

1.3 使用 MTS：

slave_preserve_commit_order=1 时，relay-log 中事务的提交顺序会严格按照在 relay-log 中出现的顺序提交。
所以，事务的执行和 flush tables with read lock 语句获得两个锁都不是原子的，并行复制时模式下按以下的顺序就会出现死锁。

• 事务 A、B 可以并行复制，relay-log 中 A 在前，slave_preserve_commit_order=1
• 从库回放时 B 事务执行较快，先执行到 commit，获得 commit 锁，并进入 waiting for preceding transaction to commit 的状态
• 执行 flush tables with read lock,进入 waiting for commit 的状态
• 事务 A 执行。事务 A 如果在 FTWRL 语句获得 global read lock 锁之后执行，那么事务 A 就进入 waiting for global read lock 的状态，即第一种死锁；如果事务 A 在 FTWRL 获得 global read lock 之前执行，同时 FTWRL 获得 global commit 锁之后应用 Xid_event 提交事务，则进入 waiting for the commit lock 的状态，即第二种死锁。

1.3.1 第一种死锁：

上图中：

• 162 线程是执行 innobackup 执行的 flush tables with read lock;
• 144 是 SQL 线程，并行复制中的 Coordinator 线程；
• 145/146 是并行复制的 worker 线程，145/146worker 线程队列中的事务可以并行执行。

144Coordinator 线程分发 relaylog 中事务时发现这个事务不能执行，要等待前面的事务完成提交，所以处于 waiting for dependent transaction to commit 的状态。145/146 线程和备份线程 162 形成死锁，145 线程等待 162 线程 global read lock 释放，162 线程占有 MDL::global read lock 全局读锁，申请全局 commit lock 的时候阻塞等待 146 线程，146 线程占有 MDL:: commit lock，因为从库设置 slave_preserve_commit_order=1，保证从库 binlog 提交顺序，而 146 线程执行事务对应的 binlog 靠后面，所以等待 145 的事务提交。最终形成了 145->162→146→145 的死循环，形成死锁。

1.3.2 第二种死锁：

上图中：

• 183 是备份程序执行的 flush tables with read lock;
• 165 是 SQL 线程，并行复制的 Coordinator 线程；
• 166/167 是并行复制的 worker 线程。

165Coordinator 线程分发的事务还不能执行，进入 waiting for dependent transaction to commit 的状态，183、166、167 三个线程形成死锁，183 占有全局读锁，获取全局 commit 锁的时候进入阻塞，等待 167 释放事务涉及到表的 commit 锁；166，167 的事务可以并行复制，167 占有表级 commit 锁，但是事务对应的 binlog 在后面，阻塞等待 166 先提交进入 waiting for preceding transaction to commit 的状态；166 线程事务执行时提交要获得表级 commit 锁，但已经被 183 占有，所以阻塞等待。这样形成了 183->167->166->183 的死锁

1.4 解决方法:

1.4.1 设置超时时间

XtraBackup 设置一个超时时间，避免无限期的等待。Xtrabackup 提供了一下参数实现该功能：

• –lock-wait-timeout=SECONDS ：一旦 Flush table with read lock 被阻塞超过预定时间，则 XtraBackup 出错返回退出，该值默认为 0，也就是说一旦阻塞，立即返回失败。

• –lock-wait-query-type=all|update ：语句执行之前必须等待什么类型的查询执行完成，默认值是 all

• –ftwrl-wait-timeout=SECONDS：如果超过指定时间之后仍然有查询在执行，则报错终止备份过程

• –ftwrl-wait-threshold=SECONDS：检测超过该选项指定的阀值的时间运行的查询

• –ftwrl-wait-query-type=all|update：语句执行之前必须等待什么类型的查询执行完成，默认值是 all

风险：当一条长查询刚开始执行，还没有到达 ftwrl-wait-threshold 指定的时间时，备份的 FTWRL 发送到了数据库，这时 xtrabackup 不会认为这条长查询是一条长查询。

1.4.2 kill 其他阻塞线程

Kill 掉所有阻塞 Flush table with read lock 的线程：

• –kill-long-queries-timeout=SECONDS ：参数允许用户指定了超过该阈值时间的查询会被 Kill，同时也允许用户指定 Kill SQL 语句的类型。
• –kill-long-query-type=all|select ：默认值为 ALL，如果选择 Select，只有 Select 语句会被 Kill，如果 Flush table with read lock 是被 Update 语句阻塞，则 XtraBackup 不会处理。

风险：这种方式对堵塞 SQL 影响很大，会强制 kill 掉，对业务会有一定的影响

1.4.3 暂停复制进程来防止阻塞

• –safe-slave-backup-timeout=SECONDS：等待状态变量 Slave_open_temp_tables 转变为 0 值的等待时间，默认值为 300 秒，如果超过指定的时间状态变量 Slave_open_temp_tables 还未转变为 0 值，则启动复制并报错终止备份

风险：这种方式通过关闭 sql thread 的方式避免死锁的发生，但是会加大主从的延迟

另外：可以设置 slave_preserve_commit_order=0 关闭从库 binlog 的顺序提交，关闭这个参数只是影响并行复制的事务在从库的提交顺序，对最终的数据一致性并无影响，所以如果无特别要求从库的 binlog 顺序必须与主库保持一致，可以设置 slave_preserve_commit_order=0 避免这个死锁的出现。

2 备份

2.1 安装xtrabackup

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install percona-xtrabackup-24 qpress -y

2.2 准备备份账号

• RELOAD开始复制文件之前,使用备份锁时需要此权限。
• REPLICATION CLIENT为了获得二进制日志位置。
• CREATE TABLESPACE以导入表（请参见还原单个表）。
• PROCESS查看服务器上运行的所有线程 SHOW ENGINE INNODB STATUS
• SUPER为了在复制环境中启动/停止副本线程，使用 XtraDB 更改页跟踪进行增量备份和改进的 FLUSH 表以及读锁定处理。
• CREATE创建PERCONA_SCHEMA.xtrabackup_history 数据库和表。
• ALTER升级PERCONA_SCHEMA.xtrabackup_history 数据库和表。
• INSERT历史记录添加到PERCONA_SCHEMA.xtrabackup_history 表中。
• SELECT查找 innobackupex –incremental-history-name 或 innobackupex –incremental-history-uuid

mysql> CREATE USER 'bkp'@'localhost' IDENTIFIED BY 'passwd';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkp'@'localhost';
mysql> FLUSH PRIVILEGES;

2.3 备份

• 备份 master 的话可能对 master 性能产生影响，但恢复的时候不会有任何问题
• 备份 slave 节点的话不会对 master 性能产生影响，但是恢复的话如果可以与 master 通信会自动加入到主从，数据可能会有问题，不过可以在备份的时候先暂停下主从同步

2.3.1 备份 master 节点

innobackupex --host=10.1.1.1 --port=3306 --user=bkp --password=passwd --kill-long-queries-timeout=20 --stream=xbstream tmp --compress >bak_compress.xb

2.3.2 要备份 slave 节点

需要添加–slave-info 并 暂时停止主从复制

mysql -h 10.1.1.2 -uroot -pqweasd -e "STOP SLAVE;"

innobackupex --host=10.1.1.2 --port=3306 --user=bkp --password=passwd --kill-long-queries-timeout=20 --stream=xbstream --slave-info tmp --compress >bak_compress.xb

mysql -h 10.1.1.2 -uroot -pqweasd -e "START SLAVE;"

ll -h bak_compress.xb

2.4 查看备份信息

查看备份节点的 binlog 位置

cat xtrabackup_binlog_info
mysql-bin.000009 79303734

备份详情

cat xtrabackup_info

uuid = 4dd186c4-9eae-11ec-8f57-fa163eeed232
name =
tool_name = innobackupex
tool_command = --host=10.1.1.2 --port=3306 --user=bkp --password=... --kill-long-queries-timeout=20 --stream=xbstream tmp --compress
tool_version = 2.4.21
ibbackup_version = 2.4.21
server_version = 5.6.51-log
start_time = 2022-03-08 15:06:38
end_time = 2022-03-08 15:06:40
lock_time = 0
binlog_pos = filename 'mysql-bin.000009', position '79303734'
innodb_from_lsn = 0
innodb_to_lsn = 3328605080
partial = N
incremental = N
format = xbstream
compact = N
compressed = compressed
encrypted = N

备份 slave 节点的话查看当时同步 master 节点 binlog 位置

cat xtrabackup_slave_info
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=55452573;

3 上传备份

参数	备注
–s3-access-key	minio 用户名
–s3-secret-key	minio 密码
–s3-bucket	minio 存储桶名称
–s3-区域	minio 区域。默认值为 us-east-1
–s3-endpoint	minio 访问地址
–parallel	最大并发上载/下载请求数

xbcloud put --storage=s3 --s3-endpoint='10.1.1.1:9000' --s3-access-key='miniouser' --s3-secret-key='miniopasswd' --s3-bucket='mysqlbackups' --parallel=10 $(date -I)-full_backup

3.1 远程下载

xbcloud get s3://mysqlbackups/$(date -I)-full_backup --s3-access-key='miniouser' --s3-secret-key='miniopasswd' --s3-bucket='mysqlbackups' --parallel=10 $(date -I)-full_backup 2>download.log | xbstream -x -C restore --parallel=8