MySQL's semi-synchronous replication

MySQL's semi-synchronous replication Introduction

MySQL enables high availability of storage systems through replication (Replication). Currently, MySQL supports replication in the following ways:

• Asynchronous Replication (Asynchronous Replication): The principle is the simplest, the best performance. However, the probability of data inconsistency between the primary and standby is very high.

• semi-synchronous replication (semi-synchronous Replication): Compared to asynchronous replication, semi-synchronous replication sacrifices a certain amount of performance, increasing the consistency of data between the primary and standby (in some cases, inconsistent primary and standby data).

• Group Replication (Group Replication): Strong consistency of distributed data replication based on Paxos algorithm. As long as most machines survive, the system is guaranteed to be available. The Group replication has a higher level of data consistency and system availability than semi-synchronous replication.

MySQL5.5 introduces a pattern called semi-synchronous replication. This mode can be used to ensure that the slave database receives the Binlog log sent by the master database and writes it to its own trunk log, and then feeds back to the master database informing that it has been copied.

When this mode is turned on, the primary database automatically reverts to asynchronous replication mode when a timeout occurs until at least one binlog from the server to the primary database is received and fed back to the primary database. The primary database is then switched back to semi-synchronous replication mode.

Asynchronous replication is the master database that sends the Binlog log to the slave database, and then it's gone.

By default, MySQL replication is asynchronous, and after the main library finishes committing the commit operation, it can be successfully returned to the client after the main library has written binlog, without waiting for Binlog to pass from the library. When a transaction is written to the main library and committed successfully, and the library has not yet been pushed by the main library Binlog, the main library is down, resulting in a possible loss of the transaction from the library, resulting in inconsistencies between the master and slave libraries.

To solve this problem, MySQL introduced semi-synchronous replication. Semi-synchronous replication ensures that every binlog on the main library is reliably replicated to the slave library, and the main library does not return to the front-end user in time for each transaction submission, but waits for one of them to receive the Binlog from the library and successfully write to the relay log, which returns the commit operation to the client successfully. At this point, there are at least two log records, one on the binlog of the main library, and the other on at least one relay log from the library, thus guaranteeing the integrity of the data.

After the master's dumper thread notifies slave, an ACK (ACK as a token) is added, which is the successful receipt of the transaction T1 's flag code. That is dumper thread in addition to send T1 to Slave, also undertook to receive slave ack work. (This actually increases the extra overhead of the dumper thread) If an exception occurs and no ACK is received, it is automatically demoted to normal replication until the exception is fixed.
Simple understanding: On the basis of the master-slave replication, added a layer of insurance, when the semi-synchronous copy is turned on, then there will be rpl_semi_sync_master_timeout the default value of this parameter, manual can be modified, in this value set the time of the master side will always wait slave The end will pull the log file to the ACK value of relay log, and if it exceeds the set value, it will automatically become asynchronous replication.

5.7 Semi-synchronous features:
1, After_sync, strengthen the data consistency, and so on before the thing is submitted ACK signal
2, Rpl_semi_sync_master_wait_slave_count, can be used to control the main library to accept the number of slave write
Transaction success Feedback.
3, the Binlog lock is optimized, the old version in the main commit Binlog write session and dump thread read Binlog
Will add a mutex to the binlog, causing the read and write of the Binlog file to be serialized. 5.7 version of this in
The optimization is done and the parallelization is realized.

5.6 Serial

5.7 Parallel

Under the premise of master-slave replication, do the following experiments

Experiment: SEREVR2 Master

Server3 slave

On the master side:

Installing plugins

mysql> Install plugin rpl_semi_sync_master soname ' semisync_master.so ';
Mysql> SELECT * from Information_schema.plugins where plugin_name like '%semi% ' \g;

See if the half-sync plugin is active show variables like '%rpl% ';

rpl_semi_sync_master_enabled                         #设置开启或者关闭半同步复制,
rpl_semi_sync_master_timeout                         #超出此时间变成异步复制 (ms)
Rpl_semi_sync_master_trace_ Level                      #启用半同步复制的调制级别
Rpl_semi_sync_master_wait_for_slave_count    #可以有几个从进行半同步复制
Rpl_semi_ sync_master_wait_no_slave                  #是否允许 Master Every thing waits for the ack of slave

Rpl_semi_sync_master_wait_point #此参数在 mysql5.7 version, the default is After_sync, meaning: master waits for slave feedback to receive relay log ACK, and then commits the transaction and returns a commit OK result to the client. Even if the main library crash, all transactions that have been committed on the main library are guaranteed to be synchronized to the slave relaylog.

Rpl_stop_slave_timeout #这个参数是在执行重大的事物时, stop slave is required, but at this point the stop slave time is very long, and the secondary parameter is used to control Stop slave the time of the.

The parameters of the semi-synchronous copy are opened as follows

Mysql> set global rpl_semi_sync_master_enabled=on;

Slave End:

Installing plugins

mysql> Install plugin rpl_semi_sync_slave soname ' semisync_slave.so ';

#查看插件是否激活

Mysql> SELECT * from Information_schema.plugins where plugin_name like '%semi% ' \g;

#打开半同步复制从库的参数

Mysql> set global semi_sync_slave_enabled=on;

Test:

Analog network delay or failure from library, will close the IO thread from the library

mysql> stop slave io_thread;

Creating write data in the main library

The main library waits for an ACK on the slave side in rpl_semi_sync_master_timeout=10000 (milliseconds)
Signal

After the IO thread is opened, the copy has been completed from the library

Insert a record to view the status of the main library:

mysql> INSERT into USERTB values (' use ', ' 555 ');

Mysql> Show status like '%semi_sync% ';

Status value analysis:

Rpl_semi_sync_master_status: The value is on, indicating that the semi-synchronous is currently open;

RPL_SEMI_SYNC_MASTER_YES_TX: A value of 1 indicates that the main library is currently having a transaction that is copied to the slave library by a semi-synchronous;

RPL_SEMI_SYNC_MASTER_NO_TX: A value of 0 indicates that there is no immediate response from the library that is not in the semi-synchronous mode.

Analog network failure, semi-synchronous to asynchronous replication mode

1) Semi-synchronous replication waits 10 seconds in the main library timeout

2) analog from library failure

mysql> stop slave;

3) The main library executes a transaction and commits, and the commit operation of the main library is blocked for 10 seconds

A new window checks the current main library thread and discovers that the commit operation waits for a semi-synchronous operation from the library accordingly:

Mysql> show Processlist\g;

4) Wait 10 seconds timeout on the main library

Check the semi-sync status value:

Status value analysis:

Rpl_semi_sync_master_status: The value is off, which means the semi-synchronous shutdown, the current replication mode is asynchronous replication;

RPL_SEMI_SYNC_MASTER_YES_TX: The value is 1, indicating that the transaction was not completed by a semi-synchronous, not cumulative;

RPL_SEMI_SYNC_MASTER_NO_TX: A value of 1, which indicates that there is a 1 increase in the semi-synchronous mode of transactions that are not responding in a timely manner from the library.

analog asynchronous replication automatically switches to semi-synchronous mode

1) normal from library

2) Check the status from the library, then synchronize the data to the slave library

3) Check the Master library semi-synchronous replication status, which means auto switch to semi-synchronous mode

4) test the current mode in the main library is semi-synchronous mode

After committing a transaction, the RPL_SEMI_SYNC_MASTER_YES_TX is changed from 1 to 2, indicating that the operation was done in semi-synchronous mode.

Semi-synchronous replication of MySQL