Shulou(Shulou.com)05/31 Report--

This article mainly introduces the MySQL 5.7semi-synchronous replication after sync and after commit example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

If semi-synchronous replication is enabled in your production library, data consistency will be required, but there is a risk of data inconsistency in MySQL5.5/5.6. In such a scenario, the client commits a transaction and master sends binlog to slave. During the sending period, the network fluctuates, and the sending of Binlog Dump thread will get stuck. You have to wait for slave to write binlog to the local relay-log, and then give master a feedback. The waiting time is subject to the rpl_semi_sync_master_timeout parameter. The default is 10 seconds. During the 10 seconds of waiting, you can see the transaction in other sessions. In case of master downtime, because binlog is not sent to slave, the front-end app cuts to slave to check, and you will find that the transaction that has just been committed is missing.

To solve this problem, MySQL5.7 improves the defect of semi-synchronous replication. It is controlled by the parameter rpl_semi_sync_master_wait_point. The default is AFTER_SYNC, which is officially recommended. It works as follows: master sends binlog to slave. Only when slave writes binlog to local relay-log, it is submitted to the storage engine layer, and then the request is returned to the client. Only then can the client see the transaction just submitted. If the slave is not saved to the local relay-log, the client cannot see the transaction, so that the above scenario will not occur. Another value is AFTER_COMMIT, which uses the old-fashioned MySQL5.5/5.6 semi-synchronous replication work.

AFTER_SYNC (the default): The master writes each transaction to its binary log and the slave, and syncs the binary log to disk. The master waits for slave acknowledgment of transaction receipt after the sync. Upon receiving acknowledgment, the master commits the transaction to the storage engine and returns a result to the client, which then can proceed.

The master library writes each transaction to the binary log, saves it to disk, and sends it to the slave library. The main library is waiting to write confirmation information from the library to its own relay-log. After receiving the confirmation message, the main database writes the transaction to the storage engine and feeds the corresponding results back to the client, which will process it at that time.

AFTER_COMMIT: The master writes each transaction to its binary log and the slave, syncs the binary log, and commits the transaction to the storage engine. The master waits for slave acknowledgment of transaction receipt after the commit. Upon receiving acknowledgment, the master returns a result to the client, which then can proceed.

The master library writes each transaction to the binary log and saves it to disk, sends it to the slave library, and writes the transaction to the storage engine. The main library is waiting to write confirmation information from the library to its own relay-log. After receiving the confirmation message, the main library gives the corresponding results back to the client, and the client will process it at that time.

The replication characteristics of these settings differ as follows:

The differences between the two parameters are:

With AFTER_SYNC, all clients see the committed transaction at the same time: After it has been acknowledged by the slave and committed to the storage engine on the master. . Thus, all clients see the same data on the master.

When set to the AFTER_SYNC parameter, all clients can see the committed data at the same time: after getting the confirmation message written from the library to their own relay-log, the transaction is written to the storage engine. In this way, all clients can see the same data on the main library.

In the event of master failure, all transactions committed on the master have been replicated to the slave (saved to its relay log). A crash of the master and failover to the slave is lossless because the slave is up to date.

The master library reported an error, and all transactions that have been written to the slave library have been saved to relay log. The crash of the master library and the HA switching to the slave library will not cause any loss because the relay-log data of the slave library is up-to-date.

With AFTER_COMMIT, the client issuing the transaction gets a return status only after the server commits to the storage engine and receives slave acknowledgment. After the commit and before slave acknowledgment, other clients can see the committed transaction before the committing client.

When set to the AFTER_COMMIT parameter, the client initiating the transaction returns status only after the server writes data to the storage engine and accepts an acknowledgement from the library. After writing the data and before getting confirmation from the library, other clients can see in this transaction.

If something goes wrong such that the slave does not process the transaction, then in the event of a master crash and failover to the slave, it is possible that such clients will see a loss of data relative to what they saw on the master.

If some error occurs, for example, the sql_thread thread of the slave library does not execute, then it is possible that the client will lose the information they have seen on the master library if the master library crashes and fails over to the slave server.

Thank you for reading this article carefully. I hope the article "sample Analysis of MySQL 5.7semi-synchronous replication after sync and after commit" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.