MySQL主从同步延迟的原因及解决办法

共计 2165 个字符，预计需要花费 6 分钟才能阅读完成。

自动写代码机器人，免费开通

由于历史原因，MySQL 复制基于逻辑的二进制日志，而非重做日志。多次被问到何时 MySQL 能支持基于物理的复制，其实这就看 MySQL 各位大佬的想法。上次和赖老师脑暴，倏地说道：MySQL 会不会来个基于 Paxos 的 redo 复制？

物理复制的真正好处不在于正确性，因为基于 ROW 格式的日志复制也已能完全保证复制的正确性。由于物理日志的写入是在事务执行过程中就不断写入，而二进制日志的写入仅仅在事务提交时。因此物理日志的优势如下所示：

复制架构下，大事务日志提交速度快；
复制架构下，主从数据延迟小；

假设执行了 1 个小时的某大事务，在最后提交时，只需写入最后提交部分的重做日志（redo log 可视为物理日志）。虽然此大事务重做日志写入的总量可能有 1G，然而在提交时，数据主从复制仅需将最后一部分日志传输到远程从机，因为之前的重做日志已经在执行的 1 个小时内不断地同步到从机。

对于二进制日志，由于其写入时间发生在事务提交时，因此假设产生了 1G 的二进制日志，则需要事务提交时间会包含这 1G 日志的写入时间。在 Oracle 中有一种说法，事务的提交速度都是平的，不论事务的大小。这在 MySQL 数据库中是不成立的。即，MySQL 的提交速度取决于事务产生的二进制日志的大小，事务提交的速度不是平的。

更为糟糕的是，MySQL 主从复制在大事务下的延迟。同样假设 1 个大事务在主服务器上执行了 1 个小时，则需要在最后的提交时间传送到从服务器。主从延迟的时间至少为 1 个小时，若从服务器执行还需 1 个小时，则主从复制延迟的最坏情况可能是 2 个小时。物理复制则不存在这样的限制，原因还是如前所述，事务提交过程中，日志已经在传输和回放。

物理复制虽好，但是也有自己的缺陷，就我自己的实际体验来看：

物理复制下，主机坏块会导致主从服务器都无法启动；相信遇到过此问题的同学不在少数；
此外，做 ETL 是有困难的，比如怎么将物理日志同步到 Hadoop 大数据平台呢？

一言以蔽之，对于 MySQL 数据库来说，任何时刻不允许有大事务执行。若要执行，则将大事务拆成一个个小的子事务来执行。这是最基本心法口诀，但却又和 Oracle 有着很大不同。总之，气宗、剑宗，本无好坏，学会理解其中的差异，融会贯通方可达风清扬般的致臻境界。

mysql 用主从同步的方法进行读写分离，减轻主服务器的压力的做法现在在业内做的非常普遍。主从同步基本上能做到实时同步。我从别的网站借用了主从同步的原理图。

在配置好了，主从同步以后，主服务器会把更新语句写入 binlog,   从服务器的 IO 线程 (这里要注意，5.6.3 之前的 IO 线程仅有一个，5.6.3 之后的有多线程去读了，速度自然也就加快了) 回去读取主服务器的 binlog 并且写到从服务器的 Relay log 里面，然后从服务器的 的 SQL thread 会一个一个执行 relay log 里面的 sql，进行数据恢复。

relay 就是 传递, relay race 就是接力赛的意思

1. 主从同步的延迟的原因

我们知道，一个服务器开放N个链接给客户端来连接的，这样有会有大并发的更新操作, 但是从服务器的里面读取 binlog 的线程仅有一个，当某个 SQL 在从服务器上执行的时间稍长 或者由于某个 SQL 要进行锁表就会导致，主服务器的 SQL 大量积压，未被同步到从服务器里。这就导致了主从不一致，也就是主从延迟。

2. 主从同步延迟的解决办法

实际上主从同步延迟根本没有什么一招制敌的办法，因为所有的 SQL 必须都要在从服务器里面执行一遍，但是主服务器如果不断的有更新操作源源不断的写入，那么一旦有延迟产生，那么延迟加重的可能性就会原来越大。当然我们可以做一些缓解的措施。

a. 我们知道因为主服务器要负责更新操作，他对安全性的要求比从服务器高，所有有些设置可以修改，比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而 slave 则不需要这么高的数据安全，完全可以讲 sync_binlog 设置为 0 或者关闭 binlog，innodb_flushlog，innodb_flush_log_at_trx_commit  也 可以设置为 0 来提高 sql 的执行效率 这个能很大程度上提高效率。另外就是使用比主库更好的硬件设备作为 slave。
b. 就是把，一台从服务器当度作为备份使用，而不提供查询，那边他的负载下来了，执行 relay log 里面的 SQL 效率自然就高了。
c. 增加从服务器喽，这个目的还是分散读的压力，从而降低服务器负载。

3. 判断主从延迟的方法

MySQL 提供了从服务器状态命令，可以通过 show slave status 进行查看，  比如可以看看 Seconds_Behind_Master 参数的值来判断，是否有发生主从延时。

其值有这么几种：

NULL – 表示 io_thread 或是 sql_thread 有任何一个发生故障，也就是该线程的 Running 状态是 No, 而非 Yes.
0 – 该值为零，是我们极为渴望看到的情况，表示主从复制状态正常

其它的方法我也没试过，暂时不做评论

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对丸趣 TV 的支持。如果你想了解更多相关内容请查看下面相关链接

向 AI 问一下细节

丸趣 TV 网 – 提供最优质的资源集合！