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

由于历史原因，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. 主从同步的延迟的原因

我们知道， 一个服务器开放Ｎ个链接给客户端来连接的，　这样有会有大并发的更新操作, 但是从服务器的里面读取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 - 该值为零，是我们极为渴望看到的情况，表示主从复制状态正常

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

总结