引言

MySQL 中出现主从延迟是常见现象，表现为从库落后主库（从库过期读），本文介绍并复现了一个很反常的现象，表现为主库落后伪装从库（从库超前读）。文中通过分析事务的提交流程与半同步复制原理，发现在发送 binlog 与 redo log commit 的时间差之内，数据与 binlog 不一致，主库有可能落后从库。

现象

研发反馈了一个很反常的现象，表现为业务数据库下游在订阅 binlog 并消费到数据后反查数据库时发现查到了历史数据。

实际上该现象的反常体现为不满足事务的持久性，客户端发起了事务的提交，binlog 中也已经写入，但是当前查询却看不到数据。

持久性的定义为一旦事务提交成功，其对数据的修改是持久性的。但从该案例中可以发现，写入 binlog 并不代表事务提交，原因与事务的提交流程有关。

MySQL 中事务的提交流程基于两阶段提交协议实现，写入 binlog 是中间一步，并不代表事务提交成功。如果两阶段提交都完成，不会出现该问题。

该实例是主库，并开启了半同步复制。

分析

半同步复制

查看半同步复制状态，显示主库开启半同步复制。

mysql> show status like '%Rpl_semi_sync_master_status%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
1 row in set (0.01 sec)

查看半同步复制相关配置。

mysql> show variables like '%semi%master%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 1000       |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)

其中：

• rpl_semi_sync_master_timeout=1s
• rpl_semi_sync_master_wait_point=AFTER_SYNC

查看刷盘参数，事务的每次提交都会触发 binlog 与 redo log 的刷盘。

mysql> select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)

参考文章gh-ost 翻车！使用后导致数据丢失！，其中从原理层面分析了 ghost 导致数据丢失的原因，也表现为写入 binlog 但是数据丢失。

下面详细介绍。

ghost 导致数据丢失

作为两种常用的 DDL 执行工具，pt-osc 与 ghost 中同步增量数据的方式不同：

• pt-osc，通过触发器同步增量数据；
• ghost，通过 binlog 同步增量数据。

其中 pt-osc 创建触发器的开销更大，比如主从创建触发器都有可能导致锁表，又比如主库触发器相关表的每次写入都需要在分别写入原表与临时表后才提交，相当于写放大。

首先简单介绍下 ghost 的原理，历史数据拷贝到新表中，增量数据通过 binlog 同步，具体是创建 binlog streamer 添加 binlog 监听。

源码实现层面的关键函数步骤如下所示：

• 1）addDMLEventsListener：添加对于二进制日志的过滤采集（指定表的二进制日志过滤）
• 2）ReadMigrationRangeValues：获取对应表唯一索引的 max、min 值
• 3）onBeforeRowCopy：将捕获的二进制日志应用到表 *_gho
• 4）iterateChunks：根据 min、max 值，批量插入数据到表 *_gho
• 5）rename & drop 新旧表

注意其中仅获取一次 min、max 值，然后根据该值分批插入。

因此，理论上对于 after_sync 模式的半同步复制，在 addDMLEventsListener 后，对应的二进制日志有可能“丢失”。

如下所示时序图，在记录 5 所在事务提交之前启动 ghost，addDMLEventsListener 捕获记录 5 以后的二进制日志，而 ReadMigrationRangeValues 获取到的 min、max 值分别为 1、4，使用新表替换旧表后记录 5 就会丢失，而且是主库从库数据都丢失。

要解决该问题，可以将获取 min、max 边界值时的 select 从快照读改为一致性读，从而确保读取到最新数据。

SELECT MIN(UK),MAX(UK) FROM xxx
LOCK IN SHARE MODE;

技术分享 | 实测在 after_sync 中使用 gh-ost是如何丢数据的文章中实测证明 ghost 可能导致数据丢失，可见线上环境使用 ghost 有风险，好在 v1.1.5 版本中已修复该问题。

GA release v1.1.5

• fix: lost data in mysql two-phase commit #1141

根据事务的持久性（ACID 中的 D），一旦事务提交成功，其对数据的修改是持久性的。但是从该案例中可以发现，写入 binlog 并不代表事务提交，原因与事务的提交流程有关，下面进行分析。

持久性是指事务一旦提交，它对数据库的改变就应该是永久性的，接下来的其他操作或故障不应该对本次事务的修改有任何影响。

事务提交流程

为实现事务的持久性，需要在崩溃恢复时保证数据不丢失，即 crash-safe，具体基于事务日志 redo log 实现。

为保证 redo log 和 binlog 数据的一致性，MySQL 中事务提交流程基于两阶段提交协议实现，具体是将 redo log 的写入拆分为两个步骤，可以将写入 redo log 与 binlog 看作一个事务（原子性）。

redo log 用于宕机后恢复数据，binlog 用于备份后恢复数据。两者若是不一致，会导致宕机重启和备份恢复的数据不一致。

因此，binlog 写入 与 redo log commit 都成功才表示事务提交成功，而在 binlog fsync 与 redo log commit 时间差之内，redo log 与 binlog 不一致，表现为不满足事务的持久性。

理论上在该时间段内使用 ghost，都会导致数据丢失，而 after_sync 模式的半同步复制由于在 commit 之前需要等待从库响应，因此放大了该问题。

当前案例中业务数据库下游在订阅 binlog 并消费到数据后反查数据库时发现查到了历史数据，也不满足事务的持久性，因此怀疑也与半同步复制有关。

下面通过复现该现象进行证明。

复现思路是在 after_sync 模式的半同步复制下主库等待从库响应过程中，对比主库与伪装从库的查询结果，其中伪装从库通过 mysqlbinlog 实现。

复现

准备工作

开启半同步

主库，开启半同步，并将超时时间设置为 10 秒。

mysql> set global rpl_semi_sync_master_enabled=1;

mysql> set global rpl_semi_sync_master_timeout=10000;

主库，查看是否开启半同步，显示已开启，rpl_semi_sync_master_wait_point=AFTER_SYNC。

mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | OFF        |
| rpl_semi_sync_slave_trace_level           | 32         |
+-------------------------------------------+------------+
8 rows in set (0.00 sec)

mysql> show status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_slave_status                 | OFF   |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)

注意查询结果中已删除部分无关参数。

从库，开启半同步。

mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

停止复制后重新开启，否则未生效。

mysql> stop slave io_thread;start slave io_thread;
Query OK, 0 rows affected (0.00 sec)

从库，查看是否开启半同步，显示已开启。

mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | ON         |
| rpl_semi_sync_slave_trace_level           | 32         |
+-------------------------------------------+------------+
8 rows in set (0.01 sec)

mysql> show status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_slave_status                 | ON    |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)

准备数据

mysql> select * from t4;
+---+------+
| a | b    |
+---+------+
| 1 | a    |
| 2 | b    |
| 3 | c    |
+---+------+
3 rows in set (0.00 sec)

刷新日志

刷新 binlog 便于后续分析 binlog。

mysql> flush logs;

查看 binlog 文件，其中当前写入的 binlog 文件名为 mysql-bin.000006。

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000005 |     53393 |
| mysql-bin.000006 |       194 |
+------------------+-----------+
2 rows in set (0.00 sec)

拉取 binlog

基于 mysqlbinlog 通过伪装从库的方式以 mysql-bin.000006 文件为起点不间断拉取 binlog，模拟数据库下游业务。

[root@test]# /export/servers/mysql/bin/mysqlbinlog -uadmin -p --read-from-remote-server mysql-bin.000006 --base64-output=decode-rows -v --stop-never --raw

执行show slave hosts命令时可以发现，伪装从库并没有被识别为真正的从库，因此半同步复制过程中不会等待伪装从库的响应。

mysql> show slave hosts;
+------------+------+------+------------+--------------------------------------+
| Server_id  | Host | Port | Master_id  | Slave_UUID                           |
+------------+------+------+------------+--------------------------------------+
| 1017021684 |      | 3358 | 1017021683 | 3789c439-9136-11eb-a44d-fa163ec57f47 |
+------------+------+------+------------+--------------------------------------+
1 row in set (0.00 sec)

查看文件，显示 mysql-bin.000006 文件已拉取到本地当前路径。

[root@test ~]# ll -h mysql-bin.000006
-rw-r----- 1 root root 2.2K 2月   3 12:54 mysql-bin.000006

分析 binlog

准备好命令，基于 mysqlbinlog 分析拉取到的 binlog 中记录的事务。

[root@test]# mysqlbinlog --base64-output=decode-row -vv mysql-bin.000006

其中指定参数：

• –base64-output=decode-row
• -vv

两个参数的作用后续将在单独一篇文章中讲解。

复现

流程

按照如下流程可以复现该问题。

其中：

• session B 命令返回结果之前，session C 中对于事务更新不可见；
• session B 命令返回结果之前，下游由于消费到 binlog 因此对于事务更新可见；
• session B 中再次执行命令没有阻塞，原因是半同步复制已退化为异步复制；
• session A 中由于 IO 线程停止，因此数据不更新，主从数据不一致，读到了过期数据。

查看 binlog 解析结果，显示在 session B 执行命令返回之前伪装从库已经接收到了生成的 binlog。

# at 1193
#230203 12:48:07 server id 1017021683  end_log_pos 1258 CRC32 0xb8dc81d3  GTID last_committed=3 sequence_number=4 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '2c1ea9cd-9136-11eb-afe6-fa163e19c3b7:2232'/*!*/;
# at 1258
#230203 12:48:07 server id 1017021683  end_log_pos 1332 CRC32 0x25b9293b  Query thread_id=68 exec_time=0 error_code=0
SET TIMESTAMP=1675399687/*!*/;
BEGIN
/*!*/;
# at 1332
#230203 12:48:07 server id 1017021683  end_log_pos 1385 CRC32 0xc1a7daf1  Rows_query
# update t4 set b='d' where a=1
# at 1385
#230203 12:48:07 server id 1017021683  end_log_pos 1435 CRC32 0x6e4c8a6d  Table_map: `cctest`.`t4` mapped to number 241
# at 1435
#230203 12:48:07 server id 1017021683  end_log_pos 1495 CRC32 0x474e8c77  Update_rows: table id 241 flags: STMT_END_F
### UPDATE `cctest`.`t4`
### WHERE
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='a' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='d' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
# at 1495
#230203 12:48:07 server id 1017021683  end_log_pos 1526 CRC32 0x7c3233fc  Xid = 1157
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

查看复制关系，显示半同步复制已退化为异步复制。

# master
mysql> show status like '%Rpl_semi_sync_master_status%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF   |
+-----------------------------+-------+
1 row in set (0.00 sec)

# slave
mysql> show status like '%Rpl_semi_sync_slave_status%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+
1 row in set (0.00 sec)

分析

步骤包括：

• 关闭从库 IO 线程，模拟半同步复制延迟
• 主库写入阻塞，等待半同步超时
• 主库写入 binlog 文件后并发送给下游
• 半同步超时退化后异步复制，事务最终提交

其中：

• 半同步复制 after_sync 模式下主库等待从库响应，比如网络波动，文中关闭复制 IO 线程是一种极端场景；
• 异步复制模式下主库不再等待从库响应直接提交，因此主从数据可能不一致。

update 阻塞过程中执行show processlist命令，显示主库已发送 binlog 给从库，执行中 SQL 阻塞的原因是Waiting for semi-sync ACK from slave。

mysql> SHOW PROCESSLIST;
+----+------------+---------------------+--------+-------------+-------+---------------------------------------------------------------+-------------------------------+
| Id | User       | Host                | db     | Command     | Time  | State                                                         | Info                          |
+----+------------+---------------------+--------+-------------+-------+---------------------------------------------------------------+-------------------------------+
| 68 | admin      | localhost           | cctest | Query       |     3 | Waiting for semi-sync ACK from slave                          | update t4 set b='d' where a=1 |
| 71 | admin      | localhost           | cctest | Query       |     0 | starting                                                      | SHOW PROCESSLIST              |
| 72 | admin      | localhost           | NULL   | Binlog Dump | 17334 | Master has sent all binlog to slave; waiting for more updates | NULL                          |
| 81 | replicater |    x.x.x.x:59203    | NULL   | Binlog Dump |    17 | Master has sent all binlog to slave; waiting for more updates | NULL                          |
+----+------------+---------------------+--------+-------------+-------+---------------------------------------------------------------+-------------------------------+
4 rows in set (0.00 sec)

执行show slave hosts命令，显示半同步超时之前检测到从库，超时之后检测不到从库。

mysql> show slave hosts;
+------------+------+------+------------+--------------------------------------+
| Server_id  | Host | Port | Master_id  | Slave_UUID                           |
+------------+------+------+------------+--------------------------------------+
| 1017021684 |      | 3358 | 1017021683 | 3789c439-9136-11eb-a44d-fa163ec57f47 |
+------------+------+------+------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> show slave hosts;
Empty set (0.00 sec)

最终从现象上看，对于 after_sync 模式的半同步复制，数据在同步过程中，数据是不可见的。

下面从原理层面解释 after_sync 模式半同步复制导致反查不到数据的原因。

原理

半同步复制

MySQL 5.5 中引入半同步复制，用于解决数据一致性问题。

在此之前，MySQL 仅支持异步复制，因此如果在从库接收到 binlog 之前主库宕机将导致数据丢失。

半同步复制的原理简单说就是主库在事务提交之前需要接收到从库响应，从库会在将 binlog 写入 relay-log 后发送 ACK 给主库，然后主库才会将 commit OK 结果反馈给客户端。

异步复制与半同步复制的对比见下图。

相比于异步复制，半同步复制提高了数据一致性，但是降低了写入性能，至少导致一个 TCP/IP 往返的延迟，可以理解为写放大。

实际上，半同步复制的演进也经历了两个阶段：

• MySQL 5.6 开始支持半同步复制，仅支持 AFTER_COMMIT 模式；
• MySQL 5.7.2 为增强半同步，引入 AFTER_SYNC 模式。

关于半同步复制，介绍三点经验：

• 半同步复制可能影响到业务 SQL 的写入性能，比如发现 insert 慢、update 慢时可以查看错误日志判断是否与半同步复制有关；
• 错误日志的局限是仅记录超时日志，因此对于未超时的半同步复制，可以通过开启 trace 日志查看每次的响应用时，缺点是日志量大，因此适用于短期内调试；
• 影响半同步复制的主要因素是网络，其次是从库的 IO 能力，原因是从库是在将接收到的 binlog 写入 relay log 以后才返回主库 ACK。

rpl_semi_sync_master_wait_point

MySQL 5.7.2 中新增 rpl_semi_sync_master_wait_point 参数用于控制半同步模式下主库在返回给客户端事务成功之前提交事务的方式。

rpl_semi_sync_master_wait_point

This variable controls the point at which a semisynchronous source waits for replica acknowledgment of transaction receipt before returning a status to the client that committed the transaction.

简单对比 AFTER_COMMIT 与 AFTER_SYNC 之间的区别。

AFTER_SYNC 也称为无损半同步，可见，AFTER_COMMIT 就是有损半同步了，那么什么是有损呢？

AFTER_COMMIT 模式下，主库当前连接在收到从库响应之前，主库其他连接中就已经可以看到“已提交”的事务。如果此时主库宕机而从库未执行该事务，主从切换后就会导致数据丢失，表现为在主从切换后，之前可以“看到”的数据看不到了。

为解决高可用切换后的“幻读”问题，引入 AFTER_SYNC 模式，主库当前连接在收到从库响应之前，主库其他连接中无法看到“已提交”的事务。

因此，MySQL 5.7 中默认使用 AFTER_SYNC，建议统一使用 AFTER_SYNC，特别是对于数据一致性要求极其严格的核心金融系统。

实际上，AFTER_COMMIT 与 AFTER_SYNC 两种模式在性能上也有差异。

P8级面试难题，after_sync vs after_commit，哪个性能更好？文章中详细对比了两种模式的性能，结论是 AFTER_SYNC 的组提交比例远远高于 AFTER_COMMIT，原因是 AFTER_SYNC 要等日志传送到远程，事务才提交，每次组提交中组员更多，因此可以有效降低 IO 消耗。

事务提交流程

结合事务提交流程可以更明确的看到 AFTER_COMMIT 与 AFTER_SYNC 之间的区别。

异步复制与半同步复制的相同点是都使用了事务的两阶段提交，不同点在于主库等待从库响应的时间，其中对于异步复制，主库不等待从库，具体如下图所示。

对于不同类型的复制，有三点相同，包括客户端发起事务提交、主库发送 binlog 给从库、数据库响应客户端。此外虚线括号内为两阶段提交，可以看到 AFTER_COMMIT 与 AFTER_SYNC 两种模式中主库等待从库响应与 redo log commit 的顺序不同。

其中：

• 在binlog 写入后通知 DUMP 线程发送 EVENT 给从库，半同步复制的两种模式的区别是主库等待从库响应的时间，而不是发送 binlog 的时间；
• redo log commit 用于进行 InnoDB 引擎层事务的提交，完成以后事务可见。AFTER_COMMIT 模式下先提交后等待，AFTER_SYNC 模式下先等待后提交。

参考《深入理解MySQL主从原理》，实际上，sync_binlog 参数有两个作用，一方面控制 binlog 的刷盘策略，另一方面控制主库给从库发送 binlog 的时间。具体如下所示：

• sync_binlog=0：binary log 不 sync 刷盘，依赖于 OS 刷盘机制。同时会在 flush 阶段后通知 DUMP 线程发送Event。

• sync_binlog=1：binary log 每次 sync 队列形成后都进行sync刷盘，约等于每次 group commit 进行刷盘。同时会在 sync 阶段后通知 DUMP 线程发送 Event。

• sync_binlog>1：binary log 将在指定次 sync 队列形成后进行 sync 刷盘，约等于指定次 group commit 后刷盘。同时会在 flush 阶段后通知 DUMP 线程发送 Event。

因此：

• sync_binlog 非 1 的设置可能导致从库比主库多事务，本质原因是从库收到了 binlog 但是主库事务未提交；
• 由于发送 binlog 始终在 redo log commit 之前，因此在该时间差之内主库的数据与 binlog 不一致，与 sync_binlog 的取值无关。

事务提交流程详见下图，可以看到整个过程主要分为三个阶段，包括 flush、sync、commit。

处理

业务反查主库的原因是保证读到的是最新数据，防止由于主从延迟导致从库读到过期数据。

但是由于数据在同步过程中，数据是不可见的，反而导致主库读到过期数据。

提出两组解决方案；

• 1、binlake消费的时候，进行延迟消费；
• 2、收到消息后反查数据的时候，判断下数据的版本号，如果版本号是旧的，进行重试。版本号可以通过在每个表中创建 version 字段，或直接使用 ts 时间戳。如果发现消息体里面的 version > 反查的 version，则进行重试。

为保证读到最新数据，最终将两组方案结合使用。

结论

业务数据库下游在订阅 binlog 并消费到数据后为防止从库读到过期数据因此反查主库，反而发现主库读到过期数据。

该现象看起来比较反常，体现为不满足事务的持久性，客户端发起了事务的提交，binlog 中也已经写入，但是当前查询却看不到数据。

实际上，写入 binlog 并不代表事务提交，原因是 MySQL 中事务的提交流程基于两阶段提交协议实现。由于发送 binlog 始终在 redo log commit 之前，因此在该时间差之内主库的数据与 binlog 不一致。因此，理论上该现象并不反常。

半同步复制支持两种模式，包括 AFTER_COMMIT 与 AFTER_SYNC，两者的区别是主库等待从库响应的时间，而不是发送 binlog 的时间。其中 AFTER_COMMIT 模式下先提交后等待，AFTER_SYNC 模式下先等待后提交。 因此导致该现象的原因不是半同步复制，只是 AFTER_SYNC 模式的半同步复制放大了该现象。

根据两阶段提交原理，redo log commit 用于进行 InnoDB 引擎层事务的提交，完成以后事务可见。因此 AFTER_COMMIT 模式的半同步复制可以避免该现象，原因是当前连接在收到从库响应之前，主库其他连接中就已经可以看到“已提交”的事务。

但是，不建议使用 AFTER_COMMIT 模式。主要有两方面原因：

• 如果从库执行事务之前主库宕机，主从切换后就会导致数据丢失；
• AFTER_COMMIT 模式性能也不如 AFTER_SYNC。

此外，介绍了 ghost 导致数据丢失的原理，如果在事务提交之前开启 ghost，快照读获取全表的 min、max 值时对于新写入的数据不可见，执行的最后一步从新表替换旧表以后，就会导致主从数据都丢失。

待办

• 事务提交流程
• ghost 原理

参考教程

• MySQL 5.7 Reference Manual  /  Replication Source Options and Variables
• gh-ost 翻车！使用后导致数据丢失！
• 如何正确地关闭 MySQL 数据库？99%的 DBA 都是错的！
• P8级面试难题，after_sync vs after_commit，哪个性能更好？
• 技术分享 | 客户说 insert 慢，我该怎么办
• 社区投稿 | MySQL 层事务提交流程简析

• 技术分享 | 实测在 after_sync 中使用 gh-ost是如何丢数据的
• 技术分享 | 聊聊 MySQL 关机的故事
• 技术分享 | 无损半同步复制下，主从高可用切换后数据一致吗？