MySQL 备库可以设置 sync_binlog 非 1 吗？_MsSqlserver

众所周知，防止断电丢失 binlog、故障恢复过程丢失数据，mysql 主库必须设置 sync_binlog=1。那么作为备库可以例外吗？

我们的第一反应当然是不行，既然主库会丢数据，备库自然一样。但其实不然，**备库丢了数据是可以重新从主库上复制的，只要这个复制的位置和备库本身数据的位置一致就 ok 了，它们能一致吗？**本文将对这个问题进行讨论。

背景知识

为了更好的说明这个问题，下面赘述一下相关的知识点：

innodb 的二阶段提交中，prepare 阶段写 redo log，commit 阶段写 binlog，故障恢复时保证：
- 所有已提交事务的 binlog 一定存在。
- 所有未提交事务一定不记录 binlog。
备库设置 relay_log_info_repository = table 时，slave_relay_log_info（即备库回放位置）的更新与 relay log 回放的 sql 在同一个事务中提交。
gtid 持久化在 binlog 中，备库在某些条件下启动复制时会从 executed_gtid_set 开始到主库复制数据。

根据以上 3 点，备库如果设置 sync_binlog 不为 1，在做故障恢复时的就会发生以下情况。

**事务状态：trx_committed_in_memory、trx_not_started。**如果 binlog 未落盘，事务会重做，数据将比 binlog 多，slave_relay_log_info 表记录的复制位置也将领先 executed_gtid_set。
事务状态：trx_prepared。 由于binlog 未刷盘，recovery 时会回滚事务，数据与 binlog 是一致的，slave_relay_log_info 表记录的复制位置等于 executed_gtid_set。

如果备库断电恢复后，启动复制时用的位置由 slave_relay_log_info 决定，则备库数据还是能正常复制数据，并且能与主库保持一致，只是 gtid 会出现跳号。

反之如果由 executed_gtid_set 决定，则备库复制会因为重复回放事务而报错，需要进行修复。下面设计一个实验来进行验证。

实验过程

1. 设置备库参数并制造“故障”

备库参数设置如下，主库用工具并发写入数据（这里用的 mysqlslap），然后备库强制关机（reboot -f）。

sync_binlog = 1000
innodb_flush_log_at_trx_commit = 1
relay_log_info_repository = table  ##slave_relay_log_info 表为 innodb 表
relay_log_recovery = on
gtid_mode = on

2. 重启备库

备库服务器开机后重启 mysql，查看的信息如下。

show master status 输出的 executed_gtid_set 如下：
fb9b7d78-6eb5-11ec-985a-0242ac101704:1-167216 

mysql&gt; select * from slave_relay_log_info\g
*************************** 1. row ***************************
  number_of_lines: 7
   relay_log_name: ./localhost-relay-bin.000004
    relay_log_pos: 4
  master_log_name: mysql-bin.000001
   master_log_pos: 48159613
        sql_delay: 0
number_of_workers: 0
               id: 1
     channel_name:
1 row in set (0.00 sec)

根据输出内容可知，从库的数据确实回放到了 mysql-bin.000001:48159613，对应的 gtid 为：fb9b7d78-6eb5-11ec-985a-0242ac101704:167222，只是从库的 binlog 有丢失，gtid 为：fb9b7d78-6eb5-11ec-985a-0242ac101704:1-167216。

...
set @@session.gtid_next= 'fb9b7d78-6eb5-11ec-985a-0242ac101704:167222'/*!*/;
...
### insert into `mysqlslap`.`t`
### set
###   @1=167216 /* int meta=0 nullable=0 is_null=0 */
# at 48159586
#220407 14:10:34 server id 123456  end_log_pos 48159613         xid = 169239
commit/*!*/;
# at 48159613
...

从库已经有 167222 事务对应的数据。

mysql&gt; select * from t where id=167216;
+--------+
| id     |
+--------+
| 167216 |
+--------+
1 row in set (0.00 sec)

3. 备库启动复制

error log 显示的起始位置和 slave_relay_log_info 内容一样，从主库的 mysql-bin.000001:48159613 开始，对应 gtid 为 167222+1。

>slave i/o thread: start asynchronous replication to master 'repl@10.186.61.32:3308' in log 'mysql-bin.000001' at position 48159613

但接下来 sql 线程报错位置却是 mysql-bin.000001:48158146，比开始位置还靠前，这个位置对应的 gtid 为 167217（即167216+1）：

>2022-04-07t06:33:18.611181-00:00 4 [error] slave sql for channel '': could not execute write_rows event on table mysqlslap.t; duplicate entry '167212' for key 'primary', error_code: 1062; handler error ha_err_found_dupp_key; the event's master log mysql-bin.000001, end_log_pos 48158146, error_code: 1062

而且解析从库 relay log（因为设置了 relay_log_recovery = on，启动复制时会丢弃旧的未 relay log 重新到主库取 binlog），第一个事务也是 set @@session.gtid_next= 'fb9b7d78-6eb5-11ec-985a-0242ac101704:167217'/*!*/;，而不是 167223。这说明了启动复制的位置并不是 slave_relay_log_info 记录的位置，而是从库的 gtid。

4. 重复以上测试

在启动从库复制前执行 change master to master_auto_position=0; 这回不报错，是从 167223 这个 gtid 开始复制数据，从库 gtid 会出现跳号。

mysql&gt; show master status;
+------------------+----------+--------------+------------------+-------------------------------------------------------------+
| file             | position | binlog_do_db | binlog_ignore_db | executed_gtid_set                                           |
+------------------+----------+--------------+------------------+-------------------------------------------------------------+
| mysql-bin.000006 |  9976340 |              |                  | fb9b7d78-6eb5-11ec-985a-0242ac101704:1-167216:167223-200670 |
+------------------+----------+--------------+------------------+-------------------------------------------------------------+
1 row in set (0.01 sec)