MySQL主从同步延迟的原因及解决办法 - 行业资讯 - 肥雀云

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

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

　　<李>复制架构下,大事务日志提交速度快,李　　<李>复制架构下,主从数据延迟小;李　　　　

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

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

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

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

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

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

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

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

继电器就是传递,接力赛就是接力赛的意思

<强> 1。主从同步的延迟的原因

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

<强> 2。主从同步延迟的解决办法

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