mysql主从复制延迟解决(mysql主从复制延迟问题)

导语：MySQL主从复制延迟的监测和缓解

MySQL的主从复制有多种原因可以导致延迟，这个是公认的了，下面我们谈谈怎样监测复制的延迟，以及怎样尽量的解决延迟的问题。

延迟的监测

Seconds_behind_master

在SLAVE上执行SHOW SLAVE STATUS，监控Seconds_behind_master列值，备库Seconds_Behind_Master值是通过将服务器当前的时间戳（这里其实有个主从服务器时间差的问题，但是实际上主从在连接上后会做一次主从时间差的对比并记录该偏移量）与二进制日志中的事件时间戳相对比得到的，如果在I/O线程没有延时的情况下，这个还是准的。

Master_Log_Pos

如果I/O有延迟，那么Seconds_behind_master列值就不准确了，这时应该在主库上SHOW MASTER STATUS，记录LogFile和Log Position值，然后再在从库上SHOW SLAVE STATUS，查看Read_Master_Log_Pos和Exec_Master_Log_Pos，看看BinLog在主从上各自的位置，可以知道是否延迟。

利用pt-heartbeat或mk-heartbeat监控工具

该工具可以计算出MySQL复制，它可以更新master或者监控复制，还可以从my.cnf 读取配置。它借助timestmp的比较实现的，首先需要保证主从服务器时间必须要保持一致，通过与相同的一个NTP server同步时钟。它需要在主库上创建一个heartbeat的表，里面的时间戳ts就是当前的时间戳 now()，该结构也会被复制到从库上。表建好以后，会在主库上以后台进程的模式去执行一行更新操作的命令，定期去向表中的插入数据，这 个周期默认为1 秒，同时从库也会在后台执行一个监控命令，与主库保持一致的周期+0.5S（默认0.5S延迟检查）去比较，复制过来记录的ts值与主库上的同一条ts值，差值为0表示无延时，差值越大表示 延时的秒数越多。

利用pt-table-checksum确定主备是否一致

复制延时或网络问题并总是会让主备数据不完全一致。主备一致应该是一种规范，而不是例外，也就是说，检查你的主备一致性应该是一个日常工作，特别是是当使用备库来做备份时尤为重要。pt-table-checksum能够用于确认主备库数据是否一致。

延迟的缓解（只能是缓解而无法彻底解决）

最简单的办法是配置InnoDB

使其不要那么频繁地刷新磁盘，这样事务提交的更快些。设置innodb_flush_log_at_trx_commit=2来实现。还可以在备库上禁止二进制日志记录，把innodb_locks_unsafe_for_binlog设置为1，并把MyISAM的delay_key_write设置为ALL。但是这些设置以牺牲安全换取速度。如果需要将备库提升为主库，记得把这些选项设置回安全值。

不要重要写操作中代价较高的部分

重构应用程序或者优化查询通常是最好的保持备库同步的办法。如果可以把工作转移备库，那么就只有一台备库需要执行，然后我们可以把写的结果回传到主库，例如，通过执行LOAD DATA INFILE。

限制主库过大的包

修改主库max_allowed_packet值，太大的包会使二进制事务变的复杂。

利用MySQL5.5以上的半同步

事实上半同步复制在某些场景下确实能够提供足够的灵活性以改善性能，在主库关闭sync_binlog的情况下保证更加安全。写入远程的内存（一台备库反馈）比写入本地的磁盘（写入并刷新）要更快。有人进行过测试，使用半同步复制相比在主库上进行强持久化的性能有两倍改善。在任何系统上都没有绝对的持久化，只有更高的持久化层次，并且看起来半同步复制应该是一种比其他替代方案开销更小的系统数据持久化方法。

在复制之外并行写入

所有写操作都应该从主库传递到备库？如果能确定一些写入可以轻易地在复制之外执行，就可以并行化这些操作以利用备库的写入容量。例如，一些归档数据，可以在主备上分别进行归档操作。

为复制线程预取缓存

通过程序实现，在SQL线程更新前提前读取中继日志并将其转化为SELECT语句执行。这会使服务器将数据从磁盘加载到内存中，这样SQL线程执行到相应语句时，就无需从磁盘读取数据。

这个办法目前已经有一种工具叫relayfetch，这个主意的想法是通过程序，让他比从服务器的sql线程稍微提前一点在中继日志中读取到查询语句，并将其作为select语句来执行，这导致服务器把一些数据从磁盘读取到内存，因此当从服务器的sql线程从中继日志中执行命令的时候，它就不需要等待从磁盘读取数据。Select并行处理从服务器必须串行处理的I/O。后面会有单独的对这个工作的介绍和使用。

多线程同步

这个办法目前也已经有一种工具叫MySQL-Transefer（下称Transfer），是一个基于MySQL+patch后得到的主从同步工具。该工具的原理是以多线程的方式来读取relaylog更新SlAVE的方式。后面会有单独的对这个工作的介绍和使用。

本文内容由小思整理编辑！