Xtrabackup备份如何保证数据一致性

环境信息

MySQL 5.7.32
xtrabackup version 2.4.22

全备命令

xtrabackup --defaults-file=/data/mysql/etc/6802/my.cnf ---host=127.0.0.1 --socket=/data/mysql/data/6802/mysqld.sock --port=6802 --user=admin --password='123456' --backup --no-timestamp  --target-dir=/tools/backup0310_full/mybackup_`date '+%Y%m%d%H%M%S'`

备份过程：

步骤	说明
1	version_check 版本检查之后进行连接
2	cd to /data/mysql/data/6802 进入数据目录并查看/data/mysql/log/redolog/6802 redo log位置; 打开undo 准备copy Opened 3 undo tablespaces(复制redo log 监听并持续复制)
3	Copying ./ibdata1 copying redo copying undo Copying *.idb copying redo 复制数据文件
4	Executing FLUSH TABLES WITH READ LOCK and Executing FLUSH NO_WRITE_TO_BINLOG TABLES and Starting to backup non-InnoDB tables and files 复制数据完成后，加全局读锁，为了备份非事务数据文件
5	Copying .MYI .MYD Copying .frm Copying .CSV Copying .CSM .TRG .TRN .opt
6	强制将commit log刷新到redo,保证事务是完整的 Finished backing up non-InnoDB tables and files 并写日志 Writing /tools/backup0310_full/mybackup_20220310180538/xtrabackup_binlog_info ；Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS
7	Stopping log copying thread ，Executing UNLOCK TABLES ，All tables unlocked Copying ib_buffer_pool 最后获取binlog 位点及元数据信息 MySQL binlog position:
8	Writing /tools/backup0310_full/mybackup_20220310180538/backup-my.cnf Writing /tools/backup0310_full/mybackup_20220310180538/xtrabackup_info completed OK!

Xtrabackup为什么不需要备份binlog文件？

Xtrabackup 的恢复模式属于 OS Crash recovery，服务异常重启会做以下事情：(binlog有记录就提交，无记录就回滚)
binlog有记录，redolog状态commit：正常完成的事务，不需要恢复；
binlog有记录，redolog状态prepare：在binlog写完提交事务之前的crash，恢复操作：提交事务。（因为之前没有提交）
binlog无记录，redolog状态prepare：在binlog写完之前的crash，恢复操作：回滚事务（因为crash时并没有成功写入数据库）


一般情况下 autocommit 参数为1，也就是自动提交。binlog 日志中会自动为事务执行begin;commit
 
二阶段提交：为了保证redo log和binlog 的逻辑一致性。前者是存储引擎层的，后者是OS层的。
 
binlog提交将提交分为了3个阶段，FLUSH阶段，SYNC阶段和COMMIT阶段。
FLUSH 阶段:将binlog buffer到I/O cache和通知dump线程dump binlog。伴随redo的操作是innodb的prepare将事务状态设为TRX_PREPARED，并将redo log刷磁盘。
SYNC阶段： 将一组binlog 落盘(sync动作，最耗时，假设sync_binlog为1)。存储引擎层prepare都执行成功之后将SQL语句写到binlog。(没有prepare成功的就回滚不记录binlog)
COMMIT阶段：逐一进行innodb commit。清除undo信息，刷redo日志，将事务设为TRX_NOT_STARTED状态。(binlog 已经在SYNC阶段落盘了，所以SYNC阶段之前的Crash recovery时需要回滚，之后的提交。而备份工具的FTWRL 锁如果是排他的，如果获取成功，就说明此刻COMMIT阶段阶段已经完成了)
 
redo日志是从备份开始一致持续复制，最后一次复制是在FTWRL 之后，此时由show master status获取binlog一致性位点。所以我们可以理解为获取FTWRL的时间点就是可恢复的截止时间点。
 

就像给数据库按了一下暂停键，我们copy 数据文件和 TRX_NOT_STARTED状态的redo 就行了。

知识点：如何判断binlog和redolog是否达成了一致?

当MySQL写完redolog并将它标记为prepare状态时，并且会在redolog中记录一个XID，它全局唯一的标识着这个事务。而当你设置sync_binlog=1时，做完了上面第一阶段写redolog后，mysql就会对应binlog并且会直接将其刷新到磁盘中。这是GTID 也会在事务开始的第一阶段生成。
磁盘上的row格式的binlog记录。binlog结束的位置上也有一个XID。
只要这个XID和redolog中记录的XID是一致的，MySQL就会认为binlog和redolog逻辑上一致。

Xtrabackup有关时间的参数

--kill-long-queries-timeout：该选项表示从开始执行FLUSH TABLES WITH READ LOCK到kill掉阻塞它的这些查询之间等待的秒数。默认值为0，不会kill任何查询，使用这个选项xtrabackup需要有Process和super权限。
--kill-long-query-type：该选项表示kill的类型，默认是all，可选select。
--ftwrl-wait-query-type：该选项表示获得全局锁之前允许那种查询完成，默认是ALL，可选update。
--ftwrl-wait-threshold：该选项表示检测到长查询，单位是秒，表示长查询的阈值。若--ftwrl-wait-timeout=0此参数无效，默认值为60s。
--ftwrl-wait-timeout=#  此选项指定innobackupex在运行之前应等待阻止FTWRL的查询的时间（以秒为单位）。 如果超时到期时仍有此类查询，则innobackupex将终止并显示错误。 默认值为0，在这种情况下，innobackupex不会等待查询完成并立即启动FTWRL。

一般设置：–ftwrl-wait-timeout=120 --ftwrl-wait-threshold=120 --kill-long-queries-timeout=60 --kill-long-query-type=all --ftwrl-wait-query-type=all
表示备份时加FTWRL如果失败，会等待120s待查询完成,如果120s之后该查询还是没有完成，连接就会被kill掉。目的是为了备份任务能成功进行。

场景一

备份命令kill掉长查询，备份成功

xtrabackup --defaults-file=/data/mysql/etc/6802/my.cnf ---host=10.186.62.36 --socket=/data/mysql/data/6802/mysqld.sock --port=6802 --user=admin --password='123456' --backup --no-timestamp --ftwrl-wait-timeout=120 --ftwrl-wait-threshold=120 --kill-long-queries-timeout=60 --kill-long-query-type=all --ftwrl-wait-query-type=all --target-dir=/tools/backup0310_full/mybackup_`date '+%Y%m%d%H%M%S'`
 
我们设置kill-long-queries-timeout 为60s 手动在备份之前 执行select sleep(80),id from t1; (模拟备份命令 把 查询连接kill掉的场景，备份依旧会成功)
 
会话1
begin;
select sleep(80),id from t1;
ERROR 2013 (HY000): Lost connection to MySQL server during query # 可以报错是之后被kill时输出的提示
 
shell执行上面的备份命令：以下是部分输出信息
220315 14:45:45 Waiting 120 seconds for queries running longer than 120 seconds to finish  # --ftwrl-wait-timeout=120 --ftwrl-wait-threshold=120
220315 14:45:45 Executing FLUSH TABLES WITH READ LOCK...  # 申请全局读锁
220315 14:45:45 Kill query timeout 60 seconds.  # --kill-long-queries-timeout 为60s
220315 14:45:45 >> log scanned up to (5182371194)
----
220315 14:46:45 Connecting to MySQL server host: localhost, user: admin, password: set, port: 6802, socket: /data/mysql/data/6802/mysqld.sock
220315 14:46:45 Killing query 1001224 (duration 82 sec): select sleep(80),id from t1   # 会话1 语句被kill 掉了
220315 14:46:45 Kill query thread stopped
220315 14:46:45 Starting to backup non-InnoDB tables and files  # 开始copy 非事务表文件
220315 14:46:45 [01] Copying ./mysql/db.opt to /tools/backup0310_full/mybackup_20220315144531/mysql/db.opt
220315 14:46:45 [00]        ...done
xtrabackup: Transaction log of lsn (5182371185) to (5182371194) was copied.
220315 14:46:45 completed OK!

场景二

备份命令备长查询阻塞而超时，备份失败

xtrabackup --defaults-file=/data/mysql/etc/6802/my.cnf ---host=10.186.62.36 --socket=/data/mysql/data/6802/mysqld.sock --port=6802 --user=admin --password='123456' --backup --no-timestamp --ftwrl-wait-timeout=10 --ftwrl-wait-threshold=10 --kill-long-queries-timeout=60 --kill-long-query-type=all --ftwrl-wait-query-type=all --target-dir=/tools/backup0310_full/mybackup_`date '+%Y%m%d%H%M%S'`
 
注意将参数调整 --ftwrl-wait-timeout=10 --ftwrl-wait-threshold=10 目的是小于 --kill-long-queries-timeout=60 的值，否则长查询会被备份命令kill掉，回到了场景一。
 
会话1
begin;
select sleep(40),id from t1;
ERROR 2006 (HY000): MySQL server has gone away # 100S+ 之后连接重连之后执行成功
No connection. Trying to reconnect
 
shell执行上面的备份命令：以下是部分输出信息
220315 18:30:10 Waiting 10 seconds for queries running longer than 10 seconds to finish # --ftwrl-wait-timeout=10 --ftwrl-wait-threshold=10  等待时间超过10s,自动退出备份
220315 18:30:10 Waiting for query 1018739 (duration 16 sec): select sleep(40),id from t1220315 18:30:10 >> log scanned up to (5182371194)
220315 18:30:11 Waiting for query 1018739 (duration 17 sec): select sleep(40),id from t1220315 18:30:11 >> log scanned up to (5182371194)
220315 18:30:12 Waiting for query 1018739 (duration 18 sec): select sleep(40),id from t1220315 18:30:12 >> log scanned up to (5182371194)
220315 18:30:13 Waiting for query 1018739 (duration 19 sec): select sleep(40),id from t1220315 18:30:13 >> log scanned up to (5182371194)
220315 18:30:14 Waiting for query 1018739 (duration 20 sec): select sleep(40),id from t1220315 18:30:14 >> log scanned up to (5182371194)
220315 18:30:15 Waiting for query 1018739 (duration 21 sec): select sleep(40),id from t1220315 18:30:15 >> log scanned up to (5182371194)
220315 18:30:16 Waiting for query 1018739 (duration 22 sec): select sleep(40),id from t1220315 18:30:16 >> log scanned up to (5182371194)
220315 18:30:17 Waiting for query 1018739 (duration 23 sec): select sleep(40),id from t1220315 18:30:17 >> log scanned up to (5182371194)
220315 18:30:18 Waiting for query 1018739 (duration 24 sec): select sleep(40),id from t1220315 18:30:18 >> log scanned up to (5182371194)
220315 18:30:19 Waiting for query 1018739 (duration 25 sec): select sleep(40),id from t1220315 18:30:19 >> log scanned up to (5182371194)
220315 18:30:20 Waiting for query 1018739 (duration 26 sec): select sleep(40),id from t1220315 18:30:20 >> log scanned up to (5182371194)
220315 18:30:21 Unable to obtain lock. Please try again later.
 
以上等待时间均是备份命令开始申请FTWRL时开始计算，如上18:30:10 Waiting 10 seconds  至 18:30:21 Unable to obtain lock. Please try again later.

参数说明：

参数	说明
kill-long-queries-timeout=60	申请FTWRL时，会kill 查询时间超过60s的连接，适用业务场景 大部分为读请求时，此值可以适当调大或是默认
–ftwrl-wait-timeout=120 --ftwrl-wait-threshold=120	申请FTWRL之前，业务允许超时重连的最小时间，一般要大于kill-long-queries-timeout，kill是万不得已的事

恢复参数 --apply-log --redo-only 介绍

--apply-log：该选项表示同xtrabackup的--prepare参数,一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据 文件仍处理不一致状态。--apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。
--redo-only：这个选项在prepare base full backup，往其中merge增量备份（但不包括最后一个）时候使用。
 
此场景适用于增量恢复：具体参数如下
--prepare --apply-log-only  全量prepare,只做redo,不做undo  # 用于全量和增量
--prepare  增量prepare,只有最后一份增量redo和undo都prepare  # # 用于最后一份增量
 
下面我们来验证一下，redo-only如果用在最后一个备份的恢复过程中, 会有什么问题？
 
进行全量和4次增量备份：
--target-dir=全备目录
--incremental-dir=增备目录
 
xtrabackup --defaults-file=/data/mysql/etc/6802/my.cnf ---host=10.186.62.36 --socket=/data/mysql/data/6802/mysqld.sock --port=6802 --user=admin --password='123456' --backup --no-timestamp --ftwrl-wait-timeout=50 --ftwrl-wait-threshold=50 --kill-long-queries-timeout=60 --kill-long-query-type=all --ftwrl-wait-query-type=all --target-dir=/tools/backup0310_full/mybackup_`date '+%Y%m%d%H%M%S'`  # 生成 全备 /tools/backup0310_full/mybackup_20220315182750

xtrabackup --defaults-file=/data/mysql/etc/6802/my.cnf ---host=10.186.62.36 --socket=/data/mysql/data/6802/mysqld.sock --port=6802 --user=admin --password='123456' --backup --no-timestamp --incremental --target-dir=/tools/backup0316_inc/incbackup_`date '+%Y%m%d%H%M%S'` --incremental-basedir=/tools/backup0310_full/mybackup_20220315182750 # 基于上面的全备/tools/backup0310_full/mybackup_20220315182750生成下面的增备/tools/backup0316_inc/incbackup_20220316102918

xtrabackup --defaults-file=/data/mysql/etc/6802/my.cnf ---host=10.186.62.36 --socket=/data/mysql/data/6802/mysqld.sock --port=6802 --user=admin --password='123456' --backup --no-timestamp --incremental --target-dir=/tools/backup0316_inc/incbackup_`date '+%Y%m%d%H%M%S'` --incremental-basedir=/tools/backup0316_inc/incbackup_20220316102918 # 再基于上面的增备/tools/backup0316_inc/incbackup_20220316102918生成新在增备/tools/backup0316_inc/incbackup_20220316103006

xtrabackup --defaults-file=/data/mysql/etc/6802/my.cnf ---host=10.186.62.36 --socket=/data/mysql/data/6802/mysqld.sock --port=6802 --user=admin --password='123456' --backup --no-timestamp --incremental --target-dir=/tools/backup0316_inc/incbackup_`date '+%Y%m%d%H%M%S'` --incremental-basedir=/tools/backup0316_inc/incbackup_20220316103006 # 在基于上面的增备/tools/backup0316_inc/incbackup_20220316103006生成新的增备/tools/backup0316_inc/incbackup_20220316103050

xtrabackup --defaults-file=/data/mysql/etc/6802/my.cnf ---host=10.186.62.36 --socket=/data/mysql/data/6802/mysqld.sock --port=6802 --user=admin --password='123456' --backup --no-timestamp --incremental --target-dir=/tools/backup0316_inc/incbackup_`date '+%Y%m%d%H%M%S'` --incremental-basedir=/tools/backup0316_inc/incbackup_20220316103050 # 同上 生成的目录 incbackup_20220316103125
 
恢复命令：目录按照备份顺序合并到全备目录 /tools/backup0310_full/mybackup_20220315182750
xtrabackup --prepare --apply-log-only --target-dir=/tools/backup0310_full/mybackup_20220315182750 --incremental-dir=/tools/backup0316_inc/incbackup_20220316102918
xtrabackup --prepare --apply-log-only --target-dir=/tools/backup0310_full/mybackup_20220315182750 --incremental-dir=/tools/backup0316_inc/incbackup_20220316103006
xtrabackup --prepare --apply-log-only --target-dir=/tools/backup0310_full/mybackup_20220315182750 --incremental-dir=/tools/backup0316_inc/incbackup_20220316103050
xtrabackup --prepare --apply-log-only --target-dir=/tools/backup0310_full/mybackup_20220315182750 --incremental-dir=/tools/backup0316_inc/incbackup_20220316103125 # 最后一次增备生成的目录 incbackup_20220316103125 注意：这个步骤在正常的恢复过程中参数应该只有--prepare，我们是为了测试才添加了--apply-log-only
最后将全备目录复制到其他主机进行恢复：
xtrabackup --defaults-file=/data/mysql/etc/6802/my.cnf --copy-back --target-dir=/tools/mybackup_20220315182750
 
数据目录：cp -rp U_MYSQL_DATA_INSTALLED /tmp ;重命名数据目录和redo目录
chown -R actiontech-mysql. 6802 修改数据目录权限及redo 目录权限
cp -rp /tmp/U_MYSQL_DATA_INSTALLED  到数据目录
启动数据库 (通过对比show master status\G 在源库是静止的情况下的备份  源库和目标库数据是一致的)
 
最终结论是 虽然我们在合并最后一个增备时使用了 --apply-log-only ，数据仍然是一致的。原因是服务重启时做了回滚操作。在以下的错误日志中有输出：
mysql-error.log
 
2022-03-16T13:04:31.228751+08:00 0 [Note] InnoDB: Database was not shutdown normally!
2022-03-16T13:04:31.228758+08:00 0 [Note] InnoDB: Starting crash recovery.
2022-03-16T13:04:31.520801+08:00 0 [ERROR] InnoDB: Page [page id: space=0, page number=5] log sequence number 5234093161 is in the future! Current system l
og sequence number 5182371367.


2022-03-16T13:04:38.964949+08:00 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 1002022-03-16T13:04:40.060835+08:00 0 [Note] InnoDB: Rollback of trx with id 15281329 completed
2022-03-16T13:04:40.060880+08:00 0 [Note] InnoDB: Rollback of non-prepared transactions completed

最后一次增量合并不应该使用apply-log-only

即使–apply-log-only在最后一步使用了，备份仍然是一致的，但在这种情况下，服务器将执行回滚阶段。并且会输出大量forcing recovery 信息。