MySQLSemisynchronousReplication introduction _ MySQL

Source: Internet
Author: User
This article mainly introduces MySQLSemisynchronousReplication. This article describes the definition and requirements of SemisynchronousReplication. For more information, see Preface

Before MySQL 5.5, the default Replication mode is Asynchronous. MySQL 5.5 provides the Semisynchronous Replication mode as plugins. Before introducing semi sync, we should first understand: semi-Synchronous Asynchronous and Synchronous.

Asynchronous replication mode

After the master database writes the committed transaction event to the binlog, it returns the success message to the app. In this mode, no committed transactions are transmitted to any slave and successfully applied.

Full synchronous replication mode.

When the master database commits a transaction event, the master database will wait until the transaction is passed to all slave, and all slave applay the transaction/event notifies the master database before returning a session, the transaction has been successful.

From the definition, we can see that the asynchronous mode does not guarantee data security because it does not wait for the transactions committed by the master database to be stored on the slave disk, the full synchronization mode will delay the transaction processing because it needs to wait for all slave to confirm that the committed transaction is successfully applied. Semi sync takes a relatively discounted approach to ensure that the committed transaction must exist on at least two machines (the master database and any slave database) and returns the transaction to the client immediately.

1. Semisynchronous Replication definition
In Semisynchronous Replication mode, when a transaction/event is submitted on the master database, it waits for at least one slave to notify the master database. slave has received the passed events and writes the relay log, return to the session layer for successful writing, or wait until the log transmission times out.

II. Advantages and disadvantages

Advantage: when the transaction returns to the client successfully, the transaction exists on at least two machines, enhancing data security. Compared with the asynchronous mode and full synchronization mode, this is a compromise.
Disadvantage: semi-sync does have a certain impact on database performance, because the commit of transactions must wait for slave feedback. Performance loss depends on the transmission time of the TCP/IP network, that is, the time when the transaction has been transferred and the slave feedback has been received.

III. features of MySQL semi-sync

1 when slave connects to the master database, it will tell the master database whether it is in semi sync mode.
2. if semi sync mode is enabled for the master database, and at least one slave also enables semi sync mode, a process that operates the transaction in the master database after the transaction is committed, before at least one slave notifies the master database to successfully receive all transactions, the process will be in the blocks wait state or wait until the timeout occurs.
3. if and only when the passed events are passed to slave, the relay log is written, and the refresh to the disk will notify the master database to complete.
4. Semisynchronous replication must be enabled at both ends of the master and slave nodes. Otherwise, the replication mode between the master and slave nodes is changed to the asynchronous replication mode.
5. when all slave instances are not returned to the master database for receiving the event within the (default value of rpl_semi_sync_master_timeout) time, the master and slave instances will return to the original asynchronous state.
MySQL 5.7 has been optimized for the second point. The ack Collector (Col) thread waits for the slave database to successfully receive the transaction notification, this will be detailed in the future-5.7 Semisync replication enhancement.

IV. Exception handling

When the backup database Crash, the master database will shut down the Semi-sync feature after a certain wait timeout and downgrade it to normal asynchronous replication. this situation is relatively simple.
MySQL 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 file, position 0.
140523 22:26:00 [Note] Semi-sync replication switched OFF.

Difficult to handle: when the host/master database crashes, some transactions may have been committed in the master database, but they have not yet been sent to any slave database, that is, these transactions are not returned to the client, so the client initiating the transaction does not know whether the transaction has been completed-"Wall transaction ". In this case, if the client does not perform a switchover, but continues to operate on the master database after the master database of Crash is restored, the client will find that all the preceding "wall transaction" has been completed, you can continue to perform subsequent business processing. In another case, if the client fails to the slave database, the client will find that the previous "wall transaction" was not successful, you need to re-execute these transactions and continue the subsequent business processing. In fact, the master and slave databases are inconsistent at this time. you need to check which database is correct through the master and slave data validation, then fix the problem.
V. Summary

In short, compared with the asynchronous replication mode semi sync earlier than MySQL 5.5, it has made great progress and enhanced data security. it is acceptable to change security to a certain degree of performance loss. We will introduce how to install and use semi sync later.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.