MySQL semisynchronous Replication Introduction

This article mainly introduced the MySQL semisynchronous Replication Introduction, this article explained the semisynchronous Replication definition,, needs the friend may refer to under

Objective

The default replication is asynchronous (asynchronous) mode prior to MySQL version 5.5, and MySQL 5.5 provides the semisynchronous Replication pattern in plugins mode. Before introducing semi sync, let's take a look at the semi-synchronous asynchronous and sync synchronous.

Asynchronous replication Mode

When the main library writes the transaction event that has been committed to the Binlog, it returns to the app, which does not guarantee that any committed transactions will be passed to any slave and successfully applied.

Full-sync replication mode.

When the main library submits a transaction event, the main library waits for the transaction to be passed on to all slave, and all slave applay the transaction/event notify the main library before returning to the transaction, which has succeeded.

It can be seen from the definition that the asynchronous pattern does not guarantee the security of the data because it does not wait for the transaction to be committed by the main library to drop on the slave, and the full synchronization mode will result in a delay in transaction processing because it waits for all slave to confirm that the committed transaction was successfully applied. Semi sync takes a more eclectic approach, ensuring that committed transactions must exist at least two machines (primary and any) to return to the client transaction successfully.

I. Definition of semisynchronous Replication

In semisynchronous replication mode, a transaction/event is committed on the main library, which waits for at least one slave to notify the main library, Slave has received the events passed over and written to the relay log before returning to the reply-layer write success. or until the transfer log has timed out.

Ii. Advantages and Disadvantages

Advantage: When a transaction returns successfully to the client, the transaction exists at least two machines, enhancing data security. Compared to asynchronous mode and full sync mode, it is a compromise.

Disadvantage: Semi-sync does have a certain impact on database performance because the commit of a transaction must wait for slave feedback. The performance loss depends on the TCP/IP network transmission time, which is the time that the transport has committed transactions and waits for the slave feedback to have received the transaction.

Third, MySQL semi-synchronous characteristics

1 when slave connects to the main library, it tells the main library whether it is semi sync mode.

2 If the main library has semi sync mode enabled, and at least one slave has semi sync mode enabled, a process that operates a transaction in the main library after the transaction commits, and at least one slave notifies the main library that it has successfully received all transactions, is in the blocks Wait for the state or until the timeout occurs.

3 when and only when the events passed over to slave, are written to relay log, refresh to disk will notify the main library complete.

4 semisynchronous replication must be enabled at both the primary and standby sides, otherwise the copy mode between the primary and standby will be converted to asynchronous replication mode.

5 when all slave are not returned to the main library to receive the event successfully within the (rpl_semi_sync_master_timeout default) time, the primary standby is changed back to the original asynchronous state.

One of the 2nd MySQL 5.7 has done optimization, by the ACK Collector (Col) thread waiting for a successful reception of the transaction notification, this follow-up will do a detailed introduction-"5.7 semisync replication enhanced."

Iv. Exception Handling

When the standby is crash, it is simpler for the main library to turn off the Semi-sync feature and demote it to normal asynchronous replication after a wait timeout.

MySQL's error.log will prompt:

The code is as follows:

140523 22:26:00 [Warning] Timeout waiting for reply of Binlog (file:mysql-bin.000002, pos:465893519), semi-sync up to fi Le, position 0.

140523 22:26:00 [note] semi-sync replication switched off.

A more difficult situation to handle is when the host/main library is crash, there may be transactions that have been committed in the main library, but have not yet come and passed to any of the repositories, that is, those transactions are not returned to the client, so the client initiating the transaction does not know whether the transaction has been completed-"wall transactions." At this point, if the client does not switch, just wait for the main library of crash to continue to operate in the main library, the client will find that the previous "wall transaction" has been completed, you can continue the subsequent business processing; Alternatively, if the client failover to the standby, the client will find the preceding " A wall transaction "is not successful, you need to redo these transactions, and then continue the subsequent business processing, in fact, at this time the main standby is inconsistent, the need for the primary data validation to check which library is correct, and then fix.

V. Summary

In short, compared to the MySQL 5.5 version of the asynchronous replication mode semi sync has made great progress, enhance the security of data, in order to secure a certain performance loss is acceptable. The follow-up will describe how to install and use semi sync.