MySQL主从同步延迟的原因、排查和解决方案

主从同步延迟的现象：对主库做增删改操作后，查询主库生效了，但查询从库没及时生效，而是过一会儿才生效。

1、原因分析

常见原因：Master负载过高、Slave负载过高、网络延迟、机器性能太低、MySQL配置不合理、主从表结构不一致。
根本原因：从库relay log日志执行replay重放延迟，当主库的 TPS 并发较高，产生的 DDL 数量超过从库一个 SQL 线程所能承受的范围，那么延时就产生了，当然还有就是可能与从库的大型 query 语句产生了锁等待。

2、主从延时排查方法

命令： show slave status，查看的“Seconds_Behind_Master”的值来判断：
0：该值为零，表示主从复制良好；
正值：表示主从已经出现延时，数字越大表示从库延迟越严重

3、解决方案

3.1半同步复制

半同步复制介于异步复制和同步复制之间，主库在执行完事务后不立刻返回结果给客户端，需要等待至少一个从库接收到并写到relay log中才返回结果给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一个TCP/IP往返耗时的延迟。（牺牲性能，保证数据不延迟和安全性）
主库配置sync_binlog=1（从库配置0），innodb_flush_log_at_trx_commit=1。
sync_binlog的默认值是0，MySQL不会将binlog同步到磁盘，其值表示每写多少binlog同步一次磁盘。
innodb_flush_log_at_timeout 的设置只针对 innodb_flush_log_at_trx_commit为0或2时起作用，1时不起作用。

3.2一主多从，读写分离

分摊主库查询压力，但要避免复制的从节点数量过多。

3.3业务侧加缓存

使用Redis、MongoDB、Memcache、ES等降低MySQL的查询压力。

3.4根据业务分库

热门业务选择更好配置的服务器单独部署MySQL，并选择主从复制结构；业务量小的选择一般配置的服务器，可以不用主从复制结构。

3.5提升从库机器配置

可以和主库一样，甚至更好。

3.6避免大事务

如果一个事务执行就要 10 分钟，那么主库执行完后，给到从库执行，最后这个事务可能就会导致从库延迟 10 分钟啦。日常开发中，不要一次性 delete 太多 SQL，需要分批进行，另外大表的 DDL 语句，也会导致大事务。

3.7优化网络带宽

优化网络带宽，比如带宽 20M 升级到 100M。主从节点在同一个交换机下。

3.8选择高版本MySQL

低版本的 MySQL 只支持单线程复制，如果主库并发高，来不及传送到从库，就会导致延迟，可以换用更高版本的 MySQL，支持多线程复制。

3.9硬件方面

采用好服务器，比如4u比2u性能明显好，2u比1u性能明显好。
存储用ssd或者盘阵或者san，提升随机写的性能。
主从间保证处在同一个交换机下面，并且是万兆环境。
总结，硬件强劲，延迟自然会变小。一句话，缩小延迟的解决方案就是花钱和花时间。

3.10优化服务器文件系统

master端修改linux、Unix文件系统中文件的etime属性， 由于每当读文件时OS都会将读取操作发生的时间回写到磁盘上，对于读操作频繁的数据库文件来说这是没必要的，只会增加磁盘系统的负担影响I/O性能。可以通过设置文件系统的mount属性，组织操作系统写atime信息，在linux上的操作为：打开/etc/fstab，加上noatime参数/dev/sdb1 /data reiserfs noatime 1 2然后重新mount文件系统#mount -oremount /data