原理+案例，关于主从延迟，一篇文章给你讲明白！

前言

在生产环境中，为了满足安全性，高可用性以及高并发等方面的需求，基本上采用的MySQL数据库架构都是MHA、MGR等，最低也得是一主一从的架构，搭配自动切换脚本，实现故障自动切换。

上述架构都是通过集群主从复制（Master-Slave）的方式来同步数据。

MySQL集群简单架构图：

说到主从同步，离不开binlog这个东西，先从binlog说起。

binlog

binlog是什么？有什么作用？

binlog是一个二进制文件，主要记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的所有操作。

binlog 是 mysql 的逻辑日志，并且由 Server层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。在实际应用中， binlog 的主要使用场景有四个：

• 恢复（recovery）：某些数据的恢复需要二进制日志。例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复。
• 复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为slave或者standby）与一台MySQL数据库（一般称为master或者primary）进行实时同步。
• 审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。
• 用于数据备份，在数据库备份文件生成后，binlog保存了数据库备份后的详细信息，以便下一次备份能从备份点开始。

除了上面介绍的几个作用外，binlog对于事务存储引擎的崩溃恢复也有非常重要的作用。在开启binlog的情况下，为了保证binlog与redo的一致性，MySQL将采用事务的两阶段提交协议。

当MySQL系统发生崩溃时，事务在存储引擎内部的状态可能为prepared和commit两种。

对于prepared状态的事务，是进行提交操作还是进行回滚操作，这时需要参考binlog：如果事务在binlog中存在，那么将其提交；如果不在binlog中存在，那么将其回滚，这样就保证了数据在主库和从库之间的一致性。

日志格式

binlog 日志有三种格式，分别为 STATMENT 、 ROW 和 MIXED，日志格式通过 binlog-format 指定。详情如下:

• STATEMENTStatement 模式只记录执行的 SQL，不需要记录每一行数据的变化，因此极大的减少了 binlog 的日志量，避免了大量的 IO 操作，提升了系统的性能。但是，正是由于 Statement 模式只记录 SQL，而如果一些 SQL 中包含了函数，那么可能会出现执行结果不一致的情况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就获取到另外一个结果了。
• ROW从 MySQL5.1.5 版本开始，binlog 引入了 Row 格式，Row 格式不记录 SQL 语句上下文相关信息，仅仅只需要记录某一条记录被修改成什么样子了。Row 格式的日志内容会非常清楚的记录下每一行数据修改的细节，这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。不过 Row 格式也有一个很大的问题，那就是日志量太大了，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题。现在对于ROW格式的二进制日志基本是标配了，主要是因为它的优势远远大于缺点。并且由于ROW格式记录行数据，所以可以基于这种模式做一些DBA工具，比如数据恢复，不同数据库之间数据同步等。
• MIXED从 MySQL5.1.8 版开始，MySQL 又推出了 Mixed 格式，这种格式实际上就是 Statement 与 Row 的结合。在 Mixed 模式下，系统会自动判断该用 Statement 还是 Row：一般的语句修改使用 Statement 格式保存 binlog；对于一些 Statement 无法准确完成主从复制的操作，则采用 Row 格式保存 binlog。Mixed 模式中，MySQL 会根据执行的每一条具体的 SQL 语句来区别对待记录的日志格式，也就是在 Statement 和 Row 之间选择一种。

重要参数：sync_binlog

在MySQL 5.7之前版本默认情况下，二进制日志并不是在每次写的时候同步的磁盘（用户可以理解为缓冲写）。因此，当数据库所在的操作系统发生宕机时，可能会有最后一部分数据没有写入二进制文件中，这会给恢复和复制带来问题。参数sync_binlog=[N]中的N表示每提交多少个事务就进行binlog刷新到磁盘。如果将N设为1，即sync_binlog=1表示采用同步写磁盘的方式来写二进制日志，每次事务提交时就会刷新binlog到磁盘；sync_binlog为0表示刷新binlog时间点由操作系统自身来决定，操作系统自身会每隔一段时间就会刷新缓存数据到磁盘；

如果使用Innodb存储引擎进行复制，并且想得到最大的高可用性，需要将此值设置为1。不过该值为1时，确时会对数据库IO系统带来一定的开销。但是，即使将sync_binlog设为1，还是会有一种情况导致问题的发生。当使用InnoDB存储引擎时，在一个事务发出COMMIT动作之前，由于sync_binlog为1，因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志，但是提交还没有发生，并且此时发生了宕机，那么在MySQL数据库下次启动时，由于COMMIT操作并没有发生，这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，不能被回滚。

对于这个问题，MySQL使用了两阶段提交来解决的，简单说就是对于已经写入到binlog文件的事务一定会提交成功， 而没有写入到binlog文件的事务就会进行回滚，从而保证二进制日志和InnoDB存储引擎数据文件的一致性，保证主从复制的安全。

主从复制原理

mysql主从复制需要三个线程：master（binlog dump thread）、slave（I/O thread 、SQL thread）

复制基本过程（面试常考）：

1. 主库写入数据并且生成binlog文件。该过程中MySQL将事务串行的写入二进制日志，依赖binlog dump线程。
2. 在事件写入二进制日志完成后，master通知存储引擎提交事务。
3. 从库服务器上的IO线程连接Master服务器，请求从执行binlog日志文件中的指定位置开始读取binlog至从库。
4. 主库接收到从库的IO线程请求后，其上复制的IO线程会根据Slave的请求信息分批读取binlog文件然后返回给从库的IO线程。
5. Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容。
6. 从库服务器的SQL线程会实时监测到本地Relay Log中新增了日志内容，然后把RelayLog中的日志翻译成SQL并且按照顺序执行SQL来更新从库的数据。
7. 从库在relay-log.info中记录当前应用中继日志的文件名和位置点以便下一次数据复制。

并行复制

在MySQL 5.6版本之前，Slave服务器上有两个线程I/O线程和SQL线程。

I/O线程负责接收二进制日志，SQL线程进行回放二进制日志。如果在MySQL5.6版本开启并行复制功能，那么SQL线程就变为了coordinator线程，coordinator线程主要负责以上两部分的内容。

这意味着coordinator线程并不是仅将日志发送给worker线程，自己也可以回放日志，但是所有可以并行的操作交付由worker线程完成。

coordinator线程与worker是典型的生产者与消费者模型。

不过到MySQL 5.7才可称为真正的并行复制，这其中最为主要的原因就是slave服务器的回放与主机是一致的即master服务器上是怎么并行执行的slave上就怎样进行并行回放。不再有库的并行复制限制，对于二进制日志格式也无特殊的要求。

为了兼容MySQL 5.6基于库的并行复制，5.7引入了新的变量slave-parallel-type，其可以配置的值有：

• DATABASE：默认值，基于库的并行复制方式
• LOGICAL_CLOCK：基于组提交的并行复制方式

下面分别介绍下两种并行复制方式按库并行

每个 worker 线程对应一个 hash 表，用于保存当前正在这个worker的执行队列里的事务所涉及到的库。其中hash表里的key是数据库名，用于决定分发策略。该策略的优点是构建hash值快，只需要库名，同时对于binlog的格式没有要求。

但这个策略的效果，只有在主库上存在多个DB，且各个DB的压力均衡的情况下，这个策略效果好。因此，对于主库上的表都放在同一个DB或者不同DB的热点不同，则起不到多大效果

组提交优化

该特性如下：

• 能够同一组里提交的事务，定不会修改同一行；
• 主库上可以并行执行的事务，从库上也一定可以并行执行。

详细内容可以去官网看看：https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html

主从延迟

主从延迟是怎么回事？

根据前面主从复制的原理可以看出，两者之间是存在一定时间的数据不一致，也就是所谓的主从延迟。我们来看下导致主从延迟的时间点：

• 主库 A 执行完成一个事务，写入 binlog，该时刻记为T1.
• 传给从库B，从库接受完这个binlog的时刻记为T2.
• 从库B执行完这个事务，该时刻记为T3.

那么所谓主从延迟，就是同一个事务，从库执行完成的时间和主库执行完成的时间之间的差值，即T3-T1。我们也可以通过在从库执行show slave status，返回结果会显示seconds_behind_master，表示当前从库延迟了多少秒。seconds_behind_master如何计算的？

• 每一个事务的binlog都有一个时间字段，用于记录主库上写入的时间
• 从库取出当前正在执行的事务的时间字段，跟当前系统的时间进行相减，得到的就是seconds_behind_master，也就是前面所描述的T3-T1。

主从延迟原因

为什么会主从延迟？

正常情况下，如果网络不延迟，那么日志从主库传给从库的时间是相当短，所以T2-T1可以基本忽略。

最直接的影响就是从库消费中转日志（relaylog）的时间段，而造成原因一般是以下几种：

1、从库的机器性能比主库要差

比如将20台主库放在4台机器，把从库放在一台机器。这个时候进行更新操作，由于更新时会触发大量读操作，导致从库机器上的多个从库争夺资源，导致主从延迟。

不过，目前大部分部署都是采取主从使用相同规格的机器部署。

2、从库的压力大

按照正常的策略，读写分离，主库提供写能力，从库提供读能力。将进行大量查询放在从库上，结果导致从库上耗费了大量的CPU资源，进而影响了同步速度，造成主从延迟。

对于这种情况，可以通过一主多从，分担读压力；也可以采取binlog输出到外部系统，比如Hadoop，让外部系统提供查询能力。

3、大事务的执行

一旦执行大事务，那么主库必须要等到事务完成之后才会写入binlog。比如主库执行了一条insert … select非常大的插入操作，该操作产生了近几百G的binlog文件传输到只读节点，进而导致了只读节点出现应用binlog延迟。

因此，DBA经常会提醒开发，不要一次性地试用delete语句删除大量数据，尽可能控制数量，分批进行。

4、主库的DDL(alter、drop、create)

只读节点与主库的DDL同步是串行进行，如果DDL操作在主库执行时间很长，那么从库也会消耗同样的时间，比如在主库对一张500W的表添加一个字段耗费了10分钟，那么从节点上也会耗费10分钟。

从节点上有一个执行时间非常长的的查询正在执行，那么这个查询会堵塞来自主库的DDL，表被锁，直到查询结束为止，进而导致了从节点的数据延迟。

5、锁冲突

锁冲突问题也可能导致从节点的SQL线程执行慢，比如从机上有一些select …. for update的SQL，或者使用了MyISAM引擎等。

6、从库的复制能力

一般场景中，因偶然情况导致从库延迟了几分钟，都会在从库恢复之后追上主库。但若是从库执行速度低于主库，且主库持续具有压力，就会导致长时间主从延迟，很有可能就是从库复制能力的问题。

从库上的执行，即sql_thread更新逻辑，在5.6版本之前，是只支持单线程，那么在主库并发高、TPS高时，就会出现较大的主从延迟。

因此，MySQL自5.7版本后就已经支持并行复制了。可以在从服务上设置 slave_parallel_workers为一个大于0的数，然后把slave_parallel_type参数设置为LOGICAL_CLOCK，这就可以了。

怎么减少主从延迟

主从同步问题永远都是一致性和性能的权衡，得看实际的应用场景，若想要减少主从延迟的时间，可以采取下面的办法：

• 降低多线程大事务并发的概率，优化业务逻辑
• 优化SQL，避免慢SQL，减少批量操作，建议写脚本以update-sleep这样的形式完成。
• 提高从库机器的配置，减少主库写binlog和从库读binlog的效率差。
• 尽量采用短的链路，也就是主库和从库服务器的距离尽量要短，提升端口带宽，减少binlog传输的网络延时。
• 实时性要求的业务读强制走主库，从库只做灾备，备份。

主从延迟案例分析

问题背景：

有一套主从每到凌晨就出现延迟现象，需要排查原因。首先查看master库每天凌晨有什么操作：分析binlog日志

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000204 > mysql-bin.000204.sql

日志在凌晨有大量的delete操作

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

#191126  0:08:35 server id 1  end_log_pos 1073744234 CRC32 0x11bf4f5d  Table_map: `user`.`table_name` mapped to number 4012691 # at 1073744234 #191126  0:08:35 server id 1  end_log_pos 1073744393 CRC32 0xa3229214  Delete_rows: table id 4012691 flags: STMT_END_F ### DELETE FROM `user`.`table_name` ### WHERE ###   @1=259121 ###   @2='2019-11-25' ###   @3=1 ###   @4=1 ###   @5=0 ###   @6='2019-11-25' ###   @7='08:30' ###   @8='2019-11-25' ###   @9='17:30' ###   @10=540 ###   @11='' ###   @12='' ###   @13=NULL ###   @14='' ###   @15='' ###   @16=NULL ###   @17=0 ###   @18=0 ###   @19=0 ###   @20=0 ###   @21=0 ###   @22=540 ###   @23=0 ###   @24=0 ###   @25=0 ###   @26=0 ###   @27=0 ###   @28='{}' # at 1073744393 #191126  0:08:35 server id 1  end_log_pos 1073744424 CRC32 0x5a03e7aa  Xid = 25909247548

　　

判断为大量的delete操作产出大量的binlog日志，slave应用不过来。

一般而言，slave相对master延迟较大，其根本原因就是slave上的复制线程没办法真正做到并发。简单说就是在master上是并发模式(以InnoDB引擎为主)完成事务提交的，而在slave上，复制线程只有一个sql thread用于binlog的apply，所以slave在高并发时会远落后master。

查看slave复制方式:

1 2 3 4 5 6 7 8

mysql> show variables like"%slave_parallel%"; +-------------------------------------------+----------------------+ | Variable_name                             | Value                | +-------------------------------------------+----------------------+ | slave_parallel_type                       | DATABASE             | | slave_parallel_workers                    | 0                    | +-------------------------------------------+----------------------+ 2 rows in set (0.00 sec)

当前的复制类型是 DATABASE，也就是统一数据库下只有一个线程进行复制，不能并行复制。当前并行工作的进程数是 0.配置从服务器上的多线程并行复制的参数（此处为实现多线程复制的重要参数）在数据库配置文件 my.cnf中设置

1 2 3 4 5 6

slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=16          #16为设置的并发线程个数，之后根据项目对数据传输的具体要求再更改 #一个schema下，slave_parallel_workers中的worker线程并发执行relay log中主库提交的事务 master_info_repository=TABLE relay_log_info_repository=TABLE relay_log_recovery=ON

　　

注：变量slave-parallel-type可以有两个值DATABASE 为默认值，意为基于库的并行复制方式；LOGICAL_CLOCK：基于组提交的并行复制方式