Can a MySQL Replica Omit sync_binlog=1? Experiment and Analysis

It is well known that to avoid losing Binlog data during power outages or crash recovery, the MySQL primary must have sync_binlog=1 . The question is whether a replica (slave) can be an exception.

At first glance the answer seems to be no, because if the primary can lose data the replica should behave the same. In fact, a replica that loses data can re‑synchronize from the primary as long as the replication position matches the replica’s own data.

1 Background Knowledge

InnoDB’s two‑phase commit writes Redo Log in the Prepare stage and Binlog in the Commit stage, guaranteeing that all committed transactions have their Binlog persisted while uncommitted transactions do not.

When a replica sets relay_log_info_repository = table , the table slave_relay_log_info (stored as an InnoDB table) records the relay‑log position in the same transaction as the SQL thread’s execution.

GTID is persisted in the Binlog; during certain conditions a replica starts replication from the Executed_Gtid_Set .

If a replica does not set sync_binlog to 1, the following situations may occur during crash recovery:

Transaction state: TRX_COMMITTED_IN_MEMORY, TRX_NOT_STARTED . If the Binlog is not flushed, the transaction is replayed, resulting in data that is ahead of the Binlog. The slave_relay_log_info entry will be ahead of Executed_Gtid_Set .

Transaction state: TRX_PREPARED . Because the Binlog is not flushed, the recovery rolls back the transaction, keeping data consistent with the Binlog; the slave_relay_log_info position equals the Executed_Gtid_Set .

When the replica powers on after a crash, if the start position is taken from slave_relay_log_info , the replica can continue replication and stay consistent with the primary, though GTID may jump. If the start position is taken from Executed_Gtid_Set , duplicate‑transaction errors occur and manual repair is required.

2 Experiment Process

1. Set replica parameters and induce a fault

The replica is configured as follows (the primary writes concurrently using mysqlslap ), then the replica is forcibly shut down with reboot -f :

sync_binlog = 1000
innodb_flush_log_at_trx_commit = 1
relay_log_info_repository = table  ## slave_relay_log_info table is InnoDB
relay_log_recovery = on
gtid_mode = on

2. Restart the replica

After the replica boots, MySQL is restarted and the following information is observed:

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

mysql> 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)

The output shows that the replica has replayed up to mysql-bin.000001:48159613 (GTID fb9b7d78-6eb5-11ec-985a-0242ac101704:167222 ), while the Binlog on the replica is missing transactions up to GTID ...:1-167216 .

... 
SET @@SESSION.GTID_NEXT= 'fb9b7d78-6eb5-11ec-985a-0242ac101704:167222'/*!*/;
...
# at 48159586
#220407 14:10:34 server id 123456  end_log_pos 48159613         Xid = 169239
COMMIT/*!*/;
# at 48159613
...

The replica already contains data for transaction 167222.

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

3. Start replication on the replica

The error log shows the start position matches slave_relay_log_info ( mysql-bin.000001:48159613 ), corresponding to GTID 167222+1. However, the SQL thread reports an earlier position mysql-bin.000001:48158146 (GTID 167217), indicating that replication starts from the GTID rather than the relay‑log info.

Slave I/O thread: Start asynchronous replication to master '[email protected]:3308' in log 'mysql-bin.000001' at position 48159613

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' ... end_log_pos 48158146 ...

This demonstrates that the replication start point is determined by the GTID when relay_log_recovery=on .

4. Repeat the test with master_auto_position=0

Before starting replication, executing change master to master_auto_position=0; prevents the error; replication begins from GTID 167223, but the replica’s GTID jumps.

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)

3 Conclusion

If a replica’s sync_binlog is not set to 1, a power loss will cause Binlog loss, and because GTID is persisted in the Binlog, GTID will also be lost. However, the data position stored in slave_relay_log_info remains consistent with the SQL thread’s replay position.

When master_auto_position=0 , the replica can restart replication from the correct position and stay data‑consistent with the primary, but GTID will have a jump.

When master_auto_position=1 , the replica restarts from the GTID position; missing GTID entries cause duplicate‑transaction errors.