MySQL备库复制延迟的原因及解决办法
简介：MySQL备库复制延迟的原因及解决办法
MySQL作为一款免费的开源数据库语言，一直伴随着我们整个IT学习生涯。“SQL学得好，工作不愁找”。基本所有的IT公司都会使用MySQL语言，并且在面试时也要求手写SQL语句，通过MySQL去操作数据库等等。常见的专业都会涉及到MySQL的学习，比如Java、数据分析、大数据、云计算等等。
数据库的重要性不言而喻的，那么从今天开始，小课将不定时为大家分享一些MySQL相关的技术问题和解决方法.
今日的主题是MySQL主从复制延迟问题？
在讨论如何解决主从延迟之前，我们先了解下什么是主从延迟。
为了完成主从复制，从库需要通过 I/O 线程获取主库中 dump 线程读取的 binlog 内容并写入到自己的中继日志 relay log 中，从库的 SQL 线程再读取中继日志，重做中继日志中的日志，相当于再执行一遍 SQL，更新自己的数据库，以达到数据的一致性。
所谓主从延迟，就是同一个事务，从库执行完成的时间与主库执行完成的时间之差
可以在备库上执行【show slave status】命令，它的返回结果里面会显示【seconds_behind_master】，用于表示当前备库延迟了多少秒。 seconds_behind_master 的计算方法是这样的：
- 每个事务的 binlog 里面都有一个时间字段，用于记录主库上写入的时间；
- 备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到 seconds_behind_master。
由于主从延迟的存在，我们可能会发现，数据刚写入主库，结果在从库中却查不到，因为可能还未同步到从库。主从延迟越严重，该问题也愈加明显。
主从延迟的来源
1. 硬件性能差异，主库性能一般比从库更好，备份复制自然就慢了。
这个需要技术管理手段去规避，务必要保证主备库无论从硬件配置、操作系统配置、mysqld 配置都要一致，不会出现主库比备库性能好很多的情况。
2. 从库数量过多导致复制延迟
这种情况下只需要减少从库数量即可，酌情而定，一般3-5个可能会比较好
2. 主库的TPS太高
MySQL5.6 版本支持了并行复制，支持的粒度是按库并行。用于决定分发策略的 hash 表里，key 就是数据库名，同一个数据库需要在同一个worker中串行执行，这就避免了事务之间相互影响的问题。
可惜，在实际的工作中，我们大部分MySQL操作都是在同一个库中，一次这个版本的并行复制就有点鸡肋了。
MySQL 5.7.22 的并行复制策略
MySQL5.7.22 增加了一个新的并行复制策略，基于 WRITESET 的并行复制。相应地，新增了一个参数 binlog-transaction-dependency-tracking，用来控制是否启用这个新策略。这个参数的可选值有以下三种：
- WRITESET，表示的是对于事务涉及更新的每一行，计算出这一行的 hash 值，组成集合 writeset。如果两个事务没有操作相同的行，也就是说它们的 writeset 没有交集，就可以并行
- WRITESET_SESSION，是在 WRITESET 的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序
并行复制是有相关参数推荐的，请视情况调整：
skip_slave_start = 0
relay_log = /database/mysql/log/relaylog/3307/relay-bin
relay_log_recovery = 1
master_info_repository = table
relay_log_info_repository = table
slave_parallel_type = logical_clock
slave_parallel_workers = 4
loose-rpl_semi_sync_master_enabled = 1
loose-rpl_semi_sync_slave_enabled = 1
loose-rpl_semi_sync_master_timeout = 1000
slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_group_commit_sync_delay = 500
binlog_group_commit_sync_no_delay_count = 13
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH643. 备份导致写入阻塞
我们有一部分的 MySQL 备份计划是采用备库上每天凌晨执行物理全备的方法做备份。物理全备采用的是开源工具 xtrabackup，实际上无论使用 xtrabackup 或者 mysqldump，备份工具都需要执行 FLUSH TABLES WITH READ LOCK (FTWRL)命令，这个命令需要获取一些锁。
- 如果此时在备库上有大查询，就堵塞 FTWRL 操作，备库 show processlist 显示“Waiting for table flush”，这个 FTWRL 被阻塞会从而导致这张表上的事务无法提交，也就是影响扩大了，影响到表的写入了。这个一般不常见，因为如果这个从库定位为备库一般就规范使用会不作为读写分离的只读库使用，上面不应该有大查询
- 如果库里 MyISAM 表比较多，为了保证备份数据的一致性，FTWRL 操作持续时间会较长，直到所有 MyISAM 表拷贝完成，期间任何写入都会被阻塞，卡在 "Waiting for global read lock"，解决这个问题的办法就是不使用 MyISAM 存储引擎的表，请将他们修改为 InnoDB 存储引擎的表
这两种情况是备份导致的从库阻塞，情况 1 不常见，我建议的解决办法就是如果备份 FTWRL 操作 5 秒内获取不到锁就放弃备份，备份脚本/程序一小时后再尝试备份。情况 2 这种问题要技术管理方法来规避，不使用MyISAM，只用InnoDB。
如果能保证数据库只用 InnoDB 存储引擎，并且使用 MySQL8.0 最新版本，那么 FTWRL 不是问题了，因为最新的 xtrabackup8 备份 MySQL 在只有 InnoDB 存储引擎下是不会使用 FTWRL 的，只会使用轻量级的 backup lock(MySQL8.0 新特性)，那就可以避免因为 FTWRL 引发的相关问题。
另外例如 MHA 这种三机高可用架构，第一个从库可以作为优先切换从，也就是备库，第二个从库可以设置不参与切换的从库，用于备份专用库，那么情况1、2均不需要解决了
4. DDL导致（alter table,create index,optimize table,repair table等等）
DDL 基本就是大事务了，大事务操作会导致复制延迟，因为主库执行完提交后从库才可以回放，他运行时间一般较长，所以 DDL 务必在业务低峰期执行。如果您非常介意主库写锁或者从库复制延迟的话，DDL 是需要专业 DBA 评估和操作的，鉴定是否能采用原生 online DDL 或者可以通过开源工具 pt-osc 或 gh-ost 来执行以加快 DDL、避免长时间锁定或者降低复制延迟。
5. 运行大事务
任何数据库都不应该有大事务，无论 MySQL、PostgreSQL 或者 Oracle，而 MySQL 对大事务是非常敏感的，大事务会导致非常多的问题，不单只复制延迟。
同事提到无法拆分大事务，我觉得应该不存在无法拆分的大事务，前面提到大事务操作会导致复制延迟，因为主库执行完提交后从库才可以回放，如果一个事务主库执行 1 小时才能完成，那么从库就要 1 小时后才能开始执行，所以复制延迟就高达 1 小时！如果您使用的是 load data local infile 入库，这是安全上不建议的，安全给的规范是建议关闭 local_infile 参数，也就是禁止使用 load data local infile 操作，您可以修改为 insert，每 5000 条数据 insert 一次，做成 insert xxx into values(),()...() (5000 个 values) 这种一句式的 SQL，循环执行直到跑批完成，如果不冲突，您可以做成并行跑批。如果依然希望使用 load data local infile，毕竟他是最快的入库方式，那您可以拆分每次 load data 的数据量，使大事务变小事务，同样的如果没有冲突，您可以考虑并行 load data。每个 SQL 入库数据量建议不大于 10MB。
6. 对无主键的表进行删除或者更新
这条其实和 5 都是开发规范相关的，应该用技术管理手段——《MySQL开发规范》来限制开发人员，表结构必须要有主键。这个要么是上线 SQL 审核工具拒绝没有主键的表(例如：SQLE)，要么就编写《MySQL开发规范》，需要扯皮时甩开发同事脸上。因为无主键的SQL 容易造成复制延迟，而且这种复制延迟导致的结果是灾难性的，这个复制延迟可能高达一个月，甚至直到世界末日。
例如下面这样一条SQL：
delete * from XXX limt 5000;这种 delete + limit 的句子设定了一次只删 5000 行数据，可以有效防止大事务，非常好。
这个 sql 在主库执行很快。在 binlog_format=statement 格式下，从库也快，因为从库执行的也是 delete * from XXX limt 5000; 本身，复制延迟就是这个 SQL 执行的时间
如果 binlog_format=Mixed 设置下，区别于delete * from XXX(全表删除)，从库不会转化，binlog 走的 statement 格式，使用 limit 因从库无法确认具体删除的是具体哪一行数据，这个和数据的组织排序强相关，为了保证主从删除的数据是一致的，其 binlog 会转化为 row 格式。row 格式在复制时要求必须有主键的，这才是性能最高的，因为他是需要根据主键去定位每行数据的位置，然后逐条删除的。如果没有主键，每行都定位不到位置，需要对整张表的记录做一次全表扫描。从库每删除一行数据都要全表扫描一次，你只是删除 5000 行啊，他就要全表扫描 5000 次，此开销非常巨大，主从延迟会很严重。如果你表足够大，删的行数足够多，那你复制延迟永远追不平，此时唯一解决办法就是删除从库基于主库重新备份重做从库。
除了前面提到的 SQL 上线审核工具外，还可以考虑上 MySQL8.0.30，开启强制主键和自动创建隐式主键的功能参数，这样您的 MySQL 永远不会有没有主键的表。
sql_require_primary_key=on
sql_generate_invisible_primary_key=on有遗漏或者不对的可以在我的公众号留言哦
