MySQL主从数据库同步延迟问题解决

目录

一、mysql主从同步原理

1. MySQL数据库主从同步延迟原理。

2. MySQL数据库主从同步延迟是怎么产生的。

3. MySQL数据库主从同步延迟解决方案

判断主从延时，通常有两个方法：

二、原因详解

slave同步延迟的可能原因

如何查看同步延迟

三、减少同步延迟的操作方案

一、mysql主从同步原理

MySQL的主从同步是一个很成熟的架构，优点为：①在从服务器可以执行查询工作(即我们常说的读功能)，降低主服务器压力;②在从主服务器进行备份，避免备份期间影响主服务器服务;③当主服务器出现问题时，可以切换到从服务器。

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

1. MySQL数据库主从同步延迟原理。

2. MySQL数据库主从同步延迟是怎么产生的。

3. MySQL数据库主从同步延迟解决方案。

1. 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线程却不可以。

2. MySQL数据库主从同步延迟是怎么产生的。

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

3. 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  vs  2. mk-heartbeat，下面具体说下两者在实现功能的差别。

可以通过监控show slave status\G命令输出的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，Maatkit万能工具包中的一个工具，被认为可以准确判断复制延时的方法。

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

二、原因详解

slave同步延迟的可能原因

1--slave的I/O线程推迟读取日志中的事件信息；最常见原因是slave是在单线程中执行所有事务，而master有很多线程可以并行执行事务。

2--带来低效连接的长查询、磁盘读取的I/O限制、锁竞争和innodb线程同步启动等。

3--Master负载；Slave负载

4--网络延迟

5--机器配置（cpu、内存、硬盘）

（主从同步延迟怎么产生的？）总之，当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能处理的承受范围时，主从同步就会产生延时；或者当slave中有大型query语句产生了锁等待也会产生延时。

如何查看同步延迟

1--可以通过比对master、slave上的日志位置

2--通过"show slave status\G"查看Seconds_Behind_Master的值，这个值代表主从同步延迟的时间，值越大说明延迟越严重。值为0为正常情况，正值表示已经出现延迟，数字越大从库落后主库越多。

3--使用percona-toolkit的pt-hearbeat工具进行查看。

三、减少同步延迟的操作方案

1--减少锁竞争

    如果查询导致大量的表锁定，需要考虑重构查询语句，尽量避免过多的锁。

2--负载均衡

    搭建多少slave，并且使用lvs或nginx进行查询负载均衡，可以减少每个slave执行查询的次数和时间，从而将更多的时间用于去处理主从同步。

3--salve较高的机器配置

4--slave调整参数

    为了保障较高的数据安全性，配置sync_binlog=1，innodb_flush_log_at_trx_commit=1等设置。而Slave可以关闭binlog，innodb_flush_log_at_trx_commit也可以设置为0来提高sql的执行效率（这两个参数很管用）

5--并行复制

    即将单线程的复制改成多线程复制。

    从库有两个线程与复制相关：io_thread 负责从主库拿binlog并写到relaylog， sql_thread 负责读relaylog并执行。

多线程的思路就是把sql_thread 变成分发线程，然后由一组worker_thread来负责执行。

几乎所有的并行复制都是这个思路，有不同的，便是sql_thread 的分发策略。

MySQL5.7的真正并行复制enhanced multi-threaded slave（MTS）很好的解决了主从同步复制的延迟问题。

（2）slave同步状态中出现Slave_IO_Running: NO

报错：Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

原因1：清理数据导致主从库不同步（前提是主库的binlog日志没有被暴力删除或错误删除，即要确保正在使用的那个最新binlog文件在master主库机器上存在）。

解决办法：

1）先进入slave中执行:"slave stop;"来停止从库同步；

2）再去master中执行:"flush logs;"来清空日志；

3）然后在master中执行:"show master status;"查看下主库的状态，主要是日志的文件和position；

4）然后回到slave中，执行:"CHANGE MASTER TO ......执行同步指令

原因2：该错误发生在从库的io进程从主库拉取日志时，发现主库的mysql_bin.index文件中第一个文件不存在。出现此类报错可能是由于你的slave 由于某种原因停止了好长一段时间，当你重启slave 复制的时候，在主库上找不到相应的binlog ,会报此类错误。或者是由于某些设置主库上的binlog被删除了，导致从库获取不到对应的binglog file。

解决办法：

1）为了避免数据丢失，需要重新进行slave同步操作。

2）注意主库binlog的清理策略，选择基于时间过期的删除方式还是基于空间利用率的删除方式。

3）记住最好不要使用"rm -rf"命令删除binlog file，这样不会同步修改mysql_bin.index 记录的binlog 条目。在删除binlog的时候确保主库保留了从库"show slave status\G"的Relay_Master_Log_File对应的binlog file。任何时候都不能删除正在使用的那个最新binlog文件；最好把bin-log文件不要删除，最好给备份出来。

原因2的情况下，使用原因1的处理方案显然是解决不了的！此时的解决方案是：

在从库上执行：

mysql> stop slave;

mysql> reset slave;

mysql> start slave;

mysql> show slave status \G;

（3）slave同步状态中出现Slave_IO_Running: Connecting

导致这个错误的原因一般是：

1--网络不通

2--权限问题（连接master的用户名和密码跟master授权不一致）

3--连接时用的log file和pos节点跟"show master status"的结果不一致

（4）slave同步状态中出现Slave_SQL_Running: No ，即slave不同步！

解决办法：

第一种方法：忽略错误后，继续同步。

该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况（下面均为在slave机器上的操作）

mysql> stop slave;

mysql> set global sql_slave_skip_counter =1;  //表示跳过一步错误，后面的数字可变；或者在my.cnf里添加slave-skip-errors = all（上面已在配置中添加）

mysql> start slave;

mysql> show slave status\G      //查看

第二种方法：重新做主从，完全同步

该方法适用于主从库数据相差较大，或者要求数据完全统一的情况

1--master主库上操作

mysql> flush tables with read lock;  //进行锁表，防止数据写入。注意该处是锁定为只读状态，语句不区分大小写

#mysqldump --lock-all-tables --all-databases --flush-logs --master-data=2 > /root/allsql.sql   //主库完全备份(如果是指定库同步，就备份指定库)，注意数据库备份一定要定期进行，确保数据万无一失

mysql> show master status;      //查看master状态，注意log file和pos节点，slave同步会用到

# scp mysql.bak.sql [email protected]:/tmp/   //把备份文件传到slave从库机器，进行数据恢复

2--slave从库操作

mysql> stop slave;

mysql> source /tmp/mysql.bak.sql

mysql> change master to master_host = '192.168.1.101', master_user = 'slave', master_port=3306.......；

mysql> start slave;

mysql> show slave status\G 

.......

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

此种方法中最为关键主要有两步：

①主服务器上锁表做完全备份，并滚动日志；

②从服务器上进行半道恢复.

（5）slave中继日志relay-log损坏？

什么是中继日志？

relay-log存放在从服务器上，从服务器将主服务器的二进制日志文件拷贝到自己的主机上放在中继日志中，然后调用SQL线程按照拷中继日志文件中的二进制日志文件执行以便就可达到数据的同步 。

如何避免中继日志损坏：

mysql 5.6版本后，在my.cnf文件中开启relay_log_recover=1即可避免。

（6）slave连接超时且重新连接频繁

若有多个slave，且没有设置server_id或两个slave设置相同的server_id，将有可能会出现服务器的ID冲突。这种情况下，其中一台slave可能会频繁超时或丢失后重新连接序列。

所以一定要确保每台slave及master在my.cnf中都要设置不一样的server_id。

（7）主库与从库使用不同的存储引擎造成不同步

（8）从库同步时，提示表不存在

错误：Last_Error: Error executing row event: 'Table 'test.t1' doesn't exist'

解决方法：在从库重建这张表。

（9）max_allowed_packet设置过小导致slave报错

max_allowed_packet默认是16M，主从库的max_allowed_packet值和备库上的不匹配。

在这情况下，主库可能会记录一个备库认为过大的包。当备库获取到该二进制日志事件时，可能会碰到各种问题，如无限报错和重试、中继日志损坏等。

具体表现：

从库的Slave_IO_Thread死掉了，查看后，出现以下错误提示：

Got a packet bigger than 'max_allowed_packet' bytes

很明显是由于max_allowed_packet的设置太小导致的，然后查检主从库上的设置，主库的设置大于从库，因为max_allowed_packet是动态参数，先调整从库上的max_allowed_packet 与主库相同，重新单独启动I/O线程就正常了。

原理说明：binlog的事件以RBR格式记录，且当前的事件长度大于了从库的max_allowed_packet, 导致无法Slave IO不能正常读取master binlog event.

（10）在master上删除一条记录时出现的故障

在master上删除一条记录后，slave上因找不到这条记录而报错。

解决方法：

由于主库上已经对这条语句进行了删除操作，故可以跳过。

在这种情况下，说明主从同步可能数据会有不一致的情况发生，所以需要使用pt-table-checksum进行数据库一致性比对。

（11）在master更新一条记录，而slave却找不到。

主从数据不致时，master有某条记录，但在salve上没有这条记录，若在master上进行更新这条记录，则在slave中可能报错。

解决方法：

1--根据从库发生异常的位置，查主库上的二进制日志。

2--根据主库二进制日志信息，找到更新后的整条记录。

3--在从库上执行在主库上找到的记录信息，进行insert操作。

4--跳过这条语句，再同步slave。

5--使用pt-table-checksum查看主从库表数据否一致。