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

一、前言

MySQL的主从同步是一个非常成熟的架构，具有以下优点：

① 可以在从服务器上进行查询工作（也就是我们常说的读功能），减轻主服务器的压力；

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

相信大家都非常熟悉这些好处，并且在项目部署中也采用了该方案。但是MySQL的主从同步一直存在从库延迟的问题，那么为什么会出现这个问题呢？如何解决这个问题呢？

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

答：在谈论MySQL数据库主从同步延迟的原理时，我们得先从mysql数据库主从复制的原理说起。 Mysql的主从复制是单线程操作。主数据库生成的所有DDL和DML都是顺序写入的，所以效率非常高。从线程去主库获取日志，效率很高。接下来，问题就出现了。从机线程在从机上实现主库的DDL和DML操作。 DML和DDL的IO操作是随机的，不是顺序的，成本要高很多。从属设备上的其他查询也可能导致锁争用。由于它们也是单线程的，如果掌握了一张DDL卡，执行起来需要10分钟。那么后续的所有DDL都会等待这个DDL执行完才继续，这就导致了延迟。有朋友会问：“主库上同样的DDL也需要执行10分钟，为什么从库会延迟呢？”答案是可以并发，但线程不行。

3、MySQL数据库主从同步延迟是如何出现的？

答：当主库TPS并发较高时，生成的DDL数量超过从库一个SQL线程可以承受的范围，就会出现延迟。当然，slave的大查询语句也可能存在锁等待的情况。

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

答：减少从同步延迟最简单的方案就是优化架构，尽量让主库的DDL执行得更快。并且主库是编写好的，数据安全性很高，比如=1、=1等设置。然而，从机不需要这么高的数据安全性。可以设置为0或者关闭，也可以设置为0来提高sql的执行效率。另一种是使用比主库更好的硬件设备作为从库。

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

基于局域网的/slave机制已经可以满足正常情况下“实时”备份的要求。如果延迟比较大，首先确认以下因素：

网络延迟

加载

从负载

一般的做法是使用多个slave来分配读请求，然后使用这些slave中的一个专用服务器仅用于备份，而不进行任何其他操作，这样可以最大程度地实现“实时”。已请求

单位是秒。默认设置为 3600 秒。参数含义：当slave从数据库读取日志数据失败时，等待多长时间重新建立连接并获取数据--retry。单位是秒。默认设置为 60 秒。参数含义：主从重新建立时。连接时，如果连接建立失败，需要多长时间才能重试？

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

1.r

通过监控show Slave命令输出的r参数值，可以判断是否出现主从延时。

有几种类型的值：

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

r是通过比较执行的事件和复制的事件（缩写为ts）获得的差值。我们都知道relay-log中的内容和主库的bin-log是一模一样的。在记录sql语句的时候，会记录当时的ts，所以比较的参考值就来自于。事实上，主从不需要与NTP交互。同步是指不需要保证主从时钟一致。

你还会发现，实际上发生在 和 之间的比较确实与主库有关。那么，问题就出现了。当主库I/O负载较重或网络阻塞时，无法及时（不间断）进行复制。 ，也是复制），而能一直跟上的脚本，那么r的值为0，这就是我们认为的没有延迟，但实际上并不是，你知道。

这也是为什么大家批评用这个参数来监控数据库延迟是否不准确，但是这个值并不总是不准确。如果网络很好的话，那么这个价值也是非常有价值的。 。之前提到过 r 参数将具有负值。我们已经知道这个值是最新的ts和已执行的ts之间的差值。前者总是大于后者。唯一的可能性就是某个事件的发生。 ts出现错误，比之前小了。当这种情况发生时，负值就成为可能。

2.mk工具包

一种被认为可以准确确定复制延迟的方法。

mk-的实现也是借助比较来实现的。首先需要保证主从服务器的时钟一致，时钟同步为同一个NTP。

它需要在主数据库上创建一个表，该表至少有两个字段：id和ts。 id是，ts是当前时间戳now()。该结构也将被复制到从数据库。建表后，会在主库上以后台进程方式执行行更新操作命令，定期向表中插入数据。该时间段默认为 1 秒。同时，从库也会在后台执行监控命令，与主库保持一致。将复制并记录的 ts 值与主数据库上相同的 ts 值进行比较。差值 0 表示没有延迟。差异越大，延迟的秒数就越多。我们都知道复制是异步的，TS不希望完全一致，所以这个工具允许有半秒的差异，在此范围内的任何差异都可以忽略，认为没有延迟。该工具使用真实副本和巧妙的借用来检查延迟。

3、避免频繁刷新磁盘

这样交易会提交得更快

--默认值1表示每次事务提交或者事务外的指令都需要将日志写入硬盘（几千1us），非常耗时。

--特别是使用电池供电的缓存（up 缓存）时。

-- 对于许多应用程序，尤其是从表中传输的应用程序，设置为 2。意思是不写入硬盘，而是写入系统缓存。

--日志仍会每秒刷新到磁盘，因此通常不会丢失超过 1-2 秒的更新

-- 设置为0会更快，但安全性较差。即使MySQL挂掉了，事务数据也可能会丢失。

--值2只会在整个操作系统挂起时才会丢失数据。

=2

--您还可以在备用数据库上禁用二进制日志记录，如下所示：但是，这些设置会牺牲安全获取速度。如果需要将备库提升为主库，记得将这些设置恢复到安全状态

锁定 =l

4.不要重复写操作中更昂贵的部分

重构应用程序或优化查询通常是保持备用数据库同步的最佳方法。主数据库上任何昂贵的写入操作都将在每个备用数据库上重播。如果可以将工作转移到备库，那么只需要执行一个备库，然后我们就可以将写入结果转移回主库，例如执行LOAD DATA

。举个栗子：

--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;

现在可以执行 INTO 然后 LOAD DATA 将结果集加载到主库中。如果有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 ','

我们还可以通过将结果分离并部分插入到主库中来将结果返回给应用程序。这种方法再次避免了在备用数据库上执行 GROUP BY 部门。

观点。将 和 分开意味着查询操作不会在每个备用数据库上重播。这节省了备用数据库上昂贵的写入操作。

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

5.复制之外的并行写入

避免备用数据库严重延迟的另一种方法是绕过复制。自己将数据复制到另一台服务器，而不是通过复制。特别回顾

瓶颈通常集中在少数表上。如果您可以将这些与复制分开处理，则可以显着加快复制速度。

6. 并行复制

.7可以称为真正的并行复制。造成这种情况的主要原因是服务器的播放与服务的播放一致。

如何在服务器上进行并行执行，那么如何在slve上进行并行播放。不再有基于库的并行复制限制，并且对二进制日志格式没有特殊要求（基于库的并行复制也没有

要求）。