Can MySQL Secondary Set sync_binlog Non-1?​

Introduction​
It is well-known that to prevent data loss due to power outages and ensure data consistency during recovery, the MySQL Primary must set sync_binlog=1. But can the Secondary make an exception? The immediate reaction is no, as data loss on the Secondary would be the same as on the Primary. However, this is not necessarily the case. If the Secondary loses data, it can be replicated again from the Primary, as long as the replication position and the Secondary's data position are consistent. But can they be consistent? This article will discuss this issue.

​Background Knowledge​
To better explain this issue, let's review some related concepts:

• In InnoDB's two-phase commit, the Prepare phase writes to the Redo Log, and the Commit phase writes to the Binlog. During recovery, it ensures:
  • All committed transactions have their Binlog entries.
  • All uncommitted transactions do not have Binlog entries.
• When the Secondary sets relay_log_info_repository=table, the update of slave_relay_log_info (the Secondary's replication position) is committed in the same transaction as the SQL execution of the Relay Log.
• GTID is persisted in the Binlog. Under certain conditions, the Secondary starts replication from the Executed_Gtid_Set to replicate data from the Primary.

Based on these three points, if the Secondary sets sync_binlog to a value other than 1, the following scenarios may occur during recovery:

• ​Transaction State: TRX_COMMITTED_IN_MEMORY, TRX_NOT_STARTED​
  • If the Binlog is not flushed to disk, the transaction will be redone, resulting in more data than the Binlog. The slave_relay_log_info table's replication position will be ahead of the Executed_Gtid_Set.
• ​Transaction State: TRX_PREPARED​
  • Since the Binlog is not flushed to disk, the transaction will be rolled back during recovery, ensuring data consistency with the Binlog. The slave_relay_log_info table's replication position will match the Executed_Gtid_Set.

If the Secondary's replication position is determined by slave_relay_log_info after a power failure and restart, the data can still be replicated correctly and remain consistent with the Primary, although GTID may skip some numbers. Conversely, if the replication position is determined by Executed_Gtid_Set, the Secondary may encounter errors due to duplicate transaction replays, requiring repair.

Experiment Process​

1. ​Set Secondary Parameters and Simulate a Failure​
   • Secondary parameters

sync_binlog = 1000
innodb_flush_log_at_trx_commit = 1
relay_log_info_repository = table
relay_log_recovery = on
gtid_mode = on

‍

1. • Use mysqlslap to concurrently write data to the Primary, then force shutdown the Secondary (reboot -f).
2. ​Restart the Secondary​
   • After restarting the Secondary's server, check the following

show master status;

‍

the executed_gtid_ set is:
‍

fb9b7d78-6eb5-11ec-985a-0242ac101704:1-167216

‍

The Slave_relay_ log_ info table shows:

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. • The Secondary's data has been replayed to mysql-bin.000001:48159613, with GTID fb9b7d78-6eb5-11ec-985a-0242ac101704:167222, while the Binlog is missing GTID fb9b7d78-6eb5-11ec-985a-0242ac101704:1-167216.
2. ​Start Replication on the Secondary​
   • The error log shows the start position is the same as slave_relay_log_info, starting from mysql-bin.000001:48159613 with GTID 167222+1.
   • However, the SQL thread reports an error at mysql-bin.000001:48158146, which is earlier than the start position, with GTID 167217 (i.e., 167216+1).
3. ​Repeat the Test​
   • Before starting replication, execute

change master to master_auto_position=0;

‍

1. • This time, no error is reported, and replication starts from GTID 167223, with GTID skipping.

​Conclusion​
If the Secondary sets sync_binlog to a value other than 1, a power failure will cause Binlog loss, and since GTID is persisted in the Binlog, GTID will also be lost. However, the data and the SQL thread's replication position in the slave_relay_log_info table will remain consistent.

• If master_auto_position=0, the Secondary can start replication from the correct position, ensuring data consistency with the Primary, although GTID may skip some numbers.
• If master_auto_position=1, the Secondary will start replication from the GTID, and due to GTID loss, it will encounter errors due to duplicate transaction replays.

‍