A brief talk about MySQL semi-synchronous replication

Introduction

MySQL achieves high availability of the storage system through replication. Currently, MySQL supports the following replication methods:

1. Asynchronous Replication: The principle is simplest and the performance is the best. However, there is a high probability that the data between the primary and backup servers will be inconsistent.
2. Semi-synchronous Replication: Compared with asynchronous replication, semi-synchronous replication sacrifices certain performance and improves the consistency of data between the primary and standby servers (in some cases, the primary and standby data will still be inconsistent).
3. Group Replication: Achieve strong consistency of distributed data replication based on the Paxos algorithm. As long as a majority of machines are alive, the system is guaranteed to be available. Compared with semi-synchronous replication, Group Replication has higher data consistency and system availability.
   

This article mainly discusses MySQL semisynchronous replication.

The basic process of semi-synchronous replication

The implementation of MySQL semisynchronous replication is based on MySQL asynchronous replication. MySQL supports two slightly different kinds of semisynchronous replication: AFTER_SYNC and AFTER_COMMIT (controlled by rpl_semi_sync_master_wait_wait_point).

When semi-synchronous replication is enabled, the Master will wait for the Slave's response or timeout before returning. When the slave times out, semisynchronous replication degenerates into asynchronous replication. This is also a problem with MySQL semi-synchronous replication. This article does not discuss the situation where Salve times out (does not discuss asynchronous replication).

Basic process of semi-synchronous replication AFTER_SYNC mode

The AFTER_SYNC mode is a semi-synchronous replication mode supported by MySQL 5.7, and is also the default semi-synchronous replication mode of MySQL 5.7:

• Prepare the transaction in the storage engine(s).
  
• Write the transaction to the binlog, flush the binlog to disk.
  
• Wait for at least one slave to acknowledge the reception for the binlog events for the transaction.
  
• Commit the transaction to the storage engine(s).
  

Basic process of semi-synchronous replication AFTER_COMMIT mode

MySQL 5.5 and 5.6 semisynchronous replication only supports AFTER_COMMIT:

• Prepare the transaction in the storage engine(s).
  
• Write the transaction to the binlog, flush the binlog to disk.
  
• Commit the transaction to the storage engine(s).
  
• Wait for at least one slave to acknowledge the reception for the binlog events for the transaction.
  

Summary of AFTER_SYNC and AFTER_COMMIT

AFTER_SYNC: After the log is copied to the Slave, the Master commits again.
All transactions committed on the master have been copied to the slave.
All transactions that have been copied to the slave may not be committed by the master (for example, the master crashes after copying the log to the slave but before committing)

AFTER_COMMIT: After the Master commits, the log is copied to the Slave.
Not all transactions committed on the master are necessarily copied to the slave. (For example, after the master commits, it crashes before it has time to copy the log to the slave)
All transactions that have been copied to the slave must be committed on the master.
Obviously, AFTER_COMMIT cannot guarantee data consistency when the master crashes (after the master commits, it crashes before it has time to copy the log to the slave). This article will only discuss the AFTER_SYNC mode.
MySQL 5.7.3 supports configuring the number of slave responses that semi-synchronous replication waits for: rpl_semi_sync_master_wait_slave_count.

Analysis of abnormal situations in AFTER_SYNC mode

Abnormal situation 1: After the master goes down, the master-slave switch occurs.

The master executes transaction T. Before flushing the binlog of transaction T to the hard disk, the master crashes. The slave is upgraded to the master. After the master is restarted, crash recovery will roll back transaction T. The primary and backup data are consistent.

The master executes transaction T. After flushing the binlog of transaction T to the hard disk and before receiving the ACK from the slave, the master crashes (there is a pending log). The slave is upgraded to the master.

2.1 The slave has not received the binlog of transaction T. After the master restarts, crash recovery will directly submit the pending log. The primary and backup data are inconsistent.

2.2 The slave has received the binlog of transaction T. The primary and backup data are consistent.

Abnormal situation 2: After the master crashes, the host is not switched. Just consider 2.1 in exception case 1.

After the master is restarted, the pendinglog is submitted directly. At this time, the master and slave data are inconsistent:

The slave connects to the master and obtains the binlog of transaction T through asynchronous replication. The primary and backup data are consistent.
The slave has not had time to copy the binlog of transaction T. If the master crashes again, the disk will be damaged. The master and slave data are inconsistent, and the data of transaction T is lost.
Abnormal situation handling

From the simple analysis of the above abnormal situation, we know that semi-synchronous replication needs to handle the special situation that the master crashes and restarts and there is a pending log (a binlog that the slave has not responded to).

For the situation where the master fails and the master-slave switch is not performed:

After crash recovery, the master waits for slave connections and replicates until at least one slave has replicated the binlog of all committed transactions. (SHOW MASTER STATUS on master and SELECT master_pos_wait() on slave).

For the situation where the master fails and the master-slave switch is performed:

After the old master is restarted, the pendinglog is rolled back during crash recovery. (Manually truncate the uncopied part of the master's binlog?)

think

Why after the master restarts, during the crash recovery process, does it directly commit pendinglog instead of retrying to request the slave's response?

MySQL's asynchronous replication and semi-synchronous replication are both triggered by the slave, and the slave actively connects to the master to synchronize binlog.

No master-slave switch occurs, and after the machine restarts, it is impossible to know which machine is the slave.
If a master-slave switch occurs, it is no longer the master and no slave will connect to it. If you continue to wait, it will not work properly.

Summarize

MySQL semi-synchronous replication has the following problems:

1. When the slave times out, it degenerates into asynchronous replication.
2. When the Master fails, data consistency cannot be guaranteed and requires manual processing.
3. Replication is serial.

Because MySQL has these problems in the consistency of master and standby data, which affects the 7*24 high-availability services of Internet businesses, major companies have launched their own "patches": Tencent's TDSQL, WeChat's PhxSQL, Alibaba's AliSQL, and NetEase's InnoSQL.

MySQL officially launched a new replication mode in MySQL 5.7 - MySQL Group Replication.

References

Discussion on Data Consistency of MySQL Semi-Synchronous Replication

MySQL High Availability Solutions

Loss-less Semi-Synchronous Replication on MySQL 5.7.2

Enhanced semisync replication