Mysql面试题分享二十：Mysql 主从集群同步延迟问题怎么解决？

一、前言

MySQL的主从同步是一个很成熟的架构，优点为：

①在从服务器可以执行查询工作(即我们常说的读功能)，降低主服务器压力;

②在从主服务器进行备份，避免备份期间影响主服务器服务;③当主服务器出现问题时，可以切换到从服务器。

相信大家对于这些好处已经非常了解了，在项目的部署中也采用这种方案。但是MySQL的主从同步一直有从库延迟的问题，那么为什么会有这种问题。这种问题如何解决呢？

二、MySQL数据库主从同步延迟原理

答：谈到MySQL数据库主从同步延迟原理，得从mysql的数据库主从复制原理说起，mysql的主从复制都是单线程的操作，主库对所有DDL和   DML产生binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率很比较高，下一步，   问题来了，slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的，不是顺  序的，成本高很多，还可能可slave上的其他查询产生lock争用，由于Slave_SQL_Running也是单线程的，所以一个DDL卡主了，需要 执行10分钟，那么所有之后的DDL会等待这个DDL执行完才会继续执行，这就导致了延时。有朋友会问：“主库上那个相同的DDL也需要执行10分，为什 么slave会延时？”，答案是master可以并发，Slave_SQL_Running线程却不可以。

三、MySQL数据库主从同步延迟是怎么产生的

答：当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。

四、MySQL数据库主从同步延迟解决方案

答：最简单的减少slave同步延时的方案就是在架构上做优化，尽量让主库的DDL快速执行。还有就是主库是写，对数据安全性较高，比如  sync_binlog=1，innodb_flush_log_at_trx_commit = 1  之类的设置，而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog也  可以设置为0来提高sql的执行效率。另外就是使用比主库更好的硬件设备作为slave。

mysql-5.6.3已经支持了多线程的主从复制。原理和丁奇的类似，丁奇的是以表做多线程，Oracle使用的是以数据库(schema)为单位做多线程，不同的库可以使用不同的复制线程。

基于局域网的master/slave机制在通常情况下已经可以满足'实时'备份的要求了。如果延迟比较大，就先确认以下几个因素：

1. 网络延迟

2. master负载

3. slave负载

一般的做法是，使用多台slave来分摊读请求，再从这些slave中取一台专用的服务器，只作为备份用，不进行其他任何操作，就能相对***限度地达到'实时'的要求了

slave_net_timeout单位为秒  默认设置为 3600秒 参数含义：当slave从主数据库读取log数据失败后，等待多久重新建立连接并获取数据  master-connect-retry单位为秒 默认设置为 60秒 参数含义：当重新建立主从连接时，如果连接建立失败，间隔多久后重试。

通常配置以上2个参数可以减少网络问题导致的主从数据同步延迟

1.Seconds_Behind_Master 

可以通过监控show slave statusG命令输出的Seconds_Behind_Master参数的值来判断，是否有发生主从延时。

其值有这么几种：

NULL - 表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No,而非Yes。0 - 该值为零，是我们极为渴望看到的情况，表示主从复制良好，可以认为lag不存在。正值 - 表示主从已经出现延时，数字越大表示从库落后主库越多。负值 - 几乎很少见，只是听一些资深的DBA说见过，其实，这是一个BUG值，该参数是不支持负值的，也就是不应该出现。

Seconds_Behind_Master是通过比较sql_thread执行的event的timestamp和io_thread复制好的   event的timestamp(简写为ts)进行比较，而得到的这么一个差值。我们都知道的relay-log和主库的bin-log里面的内容完全一   样，在记录sql语句的同时会被记录上当时的ts，所以比较参考的值来自于binlog，其实主从没有必要与NTP进行同步，也就是说无需保证主从时钟的   一致。

你也会发现，其实比较真正是发生在io_thread与sql_thread之间，而io_thread才真正与主库有关联，于是，问题就出来了，  当主库I/O负载很大或是网络阻塞，io_thread不能及时复制binlog（没有中断，也在复制），而sql_thread一直都能跟上  io_thread的脚本，这时Seconds_Behind_Master的值是0，也就是我们认为的无延时，但是，实际上不是，你懂得。

这也就是为什么大家要批判用这个参数来监控数据库是否发生延时不准的原因，但是这个值并不是总是不准，如果当io_thread与master网络很好的情况下，那么  该值也是很有价值的。之前，提到  Seconds_Behind_Master这个参数会有负值出现，我们已经知道该值是io_thread的最近跟新的ts与sql_thread执行到  的ts差值，前者始终是大于后者的，唯一的肯能就是某个event的ts发生了错误，比之前的小了，那么当这种情况发生时，负值出现就成为可能。

2. mk-heartbeat工具包

被认为可以准确判断复制延时的方法。

mk-heartbeat的实现也是借助timestmp的比较实现的，它首先需要保证主从服务器必须要保持一致，通过与相同的一个NTP   server同步时钟。

它需要在主库上创建一个heartbeat的表，里面至少有id与ts两个字段，id为server_id，ts就是当前的时间戳  now()，该结构也会被复制到从库上，表建好以后，会在主库上以后台进程的模式去执行一行更新操作的命令，定期去向表中的插入数据，这个周期默认为1   秒，同时从库也会在后台执行一个监控命令，与主库保持一致的周期去比较，复制过来记录的ts值与主库上的同一条ts值，差值为0表示无延时，差值越大表示  延时的秒数越多。我们都知道复制是异步的ts不肯完全一致，所以该工具允许半秒的差距，在这之内的差异都可忽略认为无延时。这个工具就是通过实打实的复  制，巧妙的借用timestamp来检查延时。

3.使其不要频繁地刷新磁盘

这样事务会提交得更快些

--默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（千1us)硬盘，这是很费时的。

--特别是使用电池供电缓存(Battery backed up cache)时。

--设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。

--日志仍然会每秒f1ush到硬盘，所以你一般不会丢失超过1-2秒的更新

--设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。

--而值2只会在整个操作系统挂了时才可能丢数据。
        innodb_flush_log_at_try_commit=2
--还可以在备库上禁止二进制日志记录，如下：但这些设置都是牺牲安全获取速度。如果需要将备库提升为主库，记得将这些设置还原成安全
        innodb locks unsafe for binlog=l

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

重构应用程序或者优化查询通常是最好的保持备库同步的方法。任何主库上昂贵的写操作都会在每一个备库上重放。如果可以把工作转移到备库，那么就只有一个备库需要执行，然后我们可以把写的结果回传到主库，例如，通过执行LOAD DATA
INFILE。举个栗子：
 

--replace into主要作用类似insert插入操作。
--主要的区别是replace会根据主键或者唯一索引检查数据是否存在，如果存在就先删除在更新。
REPLACE INTO table_min(coll,col2)
SELECT col1,SUM(col2)
FROM table_max
GROUP BY col1;

如上在主库上执行查询，每个备库将同样需要执行庞大的GROUP BY查询。当进行太多这样操作时，备库将被拉开差距。如果将查询转移到一个备库上也许会有帮组。在备库上创建一个特别保留的数据库，用于避免和从主库上复制的数据产生冲突。可以执行如下操作：

REPLACE INTO back.People(col1,col2)
SELECT col1,SUM(col2)
FROM main.People
GROUP BY col1;

现在可以执行SELECT INTO OUTFILE,然后执行LOAD DATA INFILE将结果集加载到主库中。如果有N个备库，就节约了N-1次庞大的GROUP BY操作。该策略的问题是备库中的数据和写入主库的数据很难保持一致。
 

SELECT INTO OUTFILE "/data/mysql/e.sql"FROM e;

load DATA需要有处理文件的权限，GRANT FILE ON米，*TO USER@host;

因为我们前面指定的分隔符是‘，'，LOAD DATA时也要指定分隔符，否则也会报错：

LOAD DATA INFILE "/data/mysql/e.sql"INTO TABLE e FIELDS TERMINATED BY ','

我们还可以通过分离REPLACE和SELECT部分，把结果返回给应用程序，然后将其插入到主库中。这种方法再次避免了在备库上执行GROUP BY部
分。将SELECT与REPLACE分离后意味着查询的SELECT操作不会在每一个备库上重放。节约了备库上昂贵的写入操作部分。

--先获取需要插入的数据集
SELECT col1,SUM(col2)FROM main.table_max GROUP BY col1;
--在插入数据
REPLACE INTO main.table_min(col1,col2)VALUES(7,?)

5.在复制之外并行写入

另一种避免备库严重延迟的办法是绕过复制。自己复制数据到另外一台服务器，而不是通过复制。特别是复核
的瓶颈通常集中在一些小部分表上。如果能在复制之外单独处理这些，就能够显著地加快复制。

6.并行复制

MySQL5.7才可称为真正的并行复制，这其中最为主要的原因就是slavel服务器的回放与master是一致的，即master)服务
器上是怎么并行执行的，那么slve上就怎样进行并行回放。不再有库的并行复制限制，对于二进制日志格式也无特殊的要求（基于库的并行复制也没有
要求)。