Xtrabackup导致主从延时问题

背景问题

公司数据库备份采用Xtrabackup，备份期间会导致数据库实例产生主从延时，增加数据库告警数量。而且数据库主从延时会影响数据访问的准确性，延时期间如果主库发生故障，会有数据丢失的风险；延时也可能影响抽数等相关任务；

原因分析：Xtrabackup备份完idb数据文件后，执行FLUSH TABLES WITH READ LOCK（FTWRL），取得一致性位点，然后开始备份非InnoDB文件（如frm、MYD、MYI、CSV、opt、par等格式的文件），在拷贝非InnoDB文件的过程当中，数据库处于全局只读状态，直到非InnoDB文件备份完毕后释放；

参数说明

默认参数

FTWRL ，它有两个作用，

1：将打开的表关闭，并将内存中的脏数据刷到硬盘，从而保证数据的一致性；

2：获取全局读锁，阻塞其他线程的写操作，从而确保备份过程中数据不被修改；

--lock-ddl:

采用"LOCK TABLES FOR BACKUP"，参数的作用是在整个备份过程中获取全局的元数据锁（MDL），以阻止所有 DDL 操作。这意味着当使用此参数时，任何尝试对数据库进行 DDL 操作（如 ALTER TABLE、CREATE TABLE 等）的线程都将被阻塞，直到备份完成并释放 MDL 锁。

FTWRL 和LOCK TABLES FOR BACKUP 区别

1，范围：FTWRL锁定所有表，包括事务性和非事务性的；LOCK TABLES FOR BACKUP主要锁定非事务性的表。

2, 阻塞： FTWRL会阻塞所有表的更新操作，LOCK TABLES FOR BACKUP只阻塞非事务表的更新操作；

3, LOCK TABLES FOR BACKUP

--lock-ddl-per-table

参数的作用是为每个备份的表单独获取 MDL 锁。这意味着备份工具会针对每个非 InnoDB 表单独加锁，而不是在整个备份过程中加全局锁。这种方式可以减少对数据库操作的阻塞，因为不是所有的 DDL 操作都会被阻塞，只有那些针对正在备份的表的 DDL 操作才会被阻塞，从而降低对数据库延时的影响；

--lock-ddl-per-table备份过程日志如下，日志中可以看到，备份每张表的时候都单独添加MDL锁

240313 12:49:27 Locking MDL for mysql.plugin
240313 12:49:27 [01] Copying ./mysql/plugin.ibd to /export/data/dbbak/localbackup/mysql/plugin.ibd
240313 12:49:27 [01]        ...done
240313 12:49:27 Locking MDL for mysql.servers
240313 12:49:27 [01] Copying ./mysql/servers.ibd to /export/data/dbbak/localbackup/mysql/servers.ibd
240313 12:49:27 [01]        ...done
240313 12:49:27 Locking MDL for mysql.help_topic
240313 12:49:27 [01] Copying ./mysql/help_topic.ibd to /export/data/dbbak/localbackup/mysql/help_topic.ibd
240313 12:49:27 [01]        ...done
240313 12:49:27 Locking MDL for mysql.help_category

功能测试

下面测试对比xtrabackup默认备份方式和 --lock-ddl-per-table方式对备份延时，备份时长的影响；

备份方式	FTWRL	lock-ddl-per-table 第1次	lock-ddl-per-table 第2次	lock-ddl 第1次	lock-ddl 第2次
备份节点	xx,xx,xx,xx	xx,xx,xx,xx	xx,xx,xx,xx	xx,xx,xx,xx	xx,xx,xx,xx
节点配置	8c 24576M	8c 24576M	8c 24576M	8c 24576M	8c 24576M
落盘方式	云盘	云盘	云盘	云盘	云盘
备份数据量	295GB	295GB	295GB	295GB	295GB
开始时间	3/13 15:26	3/13 15:26	3/13 16:37	3/13 20:13	3/13 20:20
结束时间	3/13 16:06	3/13 16:08	3/13 17:20	3/13 21:04	3/13 21:10
备份时长	40min	42min	43min	51min	50min
备份产生延时	114s	0	0	115s	120s