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

The main content of this article is "process Analysis of AFTER_SYNC/AFTER_COMMIT in MySQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the process Analysis of AFTER_SYNC/AFTER_COMMIT in MySQL.

MySQL 5.7enhances semi-synchronous replication, and rpl_semi_sync_master_wait_point increases the value of AFTER_SYNC. Two values of this parameter, AFTER_SYNC/AFTER_COMMIT, choose whether to enable enhanced semi-synchronization.

Mysql > SET rpl_semi_sync_master_wait_point= AFTER_SYNC; enables mysql 5.7 enhanced semi-synchronization. 5.7 is enabled by default.

Semi-synchronous mode of mysql > SET rpl_semi_sync_master_wait_point= AFTER_COMMIT; 5.6

When the semi-synchronous mode is AFTER_COMMIT:

The process analysis is as follows:

1 > session issues a commit request

2 > flush binlog and fsync binlog

3 > InnoDB engine layer commit

4 > send binlog to SLAVE and wait for slave to send ack confirmation

5 > slave accepts binlog to write to relay log, and sends ack confirmation packet to master after brushing.

6 > master returns commit ok information to session

In addition (master goes down while waiting for transaction A's ACK, and new transaction B starts before downtime):

1 > binlog was not sent to the slave library:

Transaction B gets the content committed by transaction A, and when the outage fails over to slave, the content obtained by transaction B is lost. Transaction A commit did not receive feedback (business judgment is required).

2 > binlog has been sent to the slave library:

Transaction B gets the content of transaction A commit, fails over to salve, B still gets A commit content, there is nothing wrong with it. Transaction A commit does not receive feedback, and if the transaction is re-executed, it is equivalent to executing the A transaction twice (business judgment is required).

When the semi-synchronous mode is AFTER_SYNC (recommended in version 5.7):

The process analysis is as follows:

1 > session issues a commit request

2 > flush binlog and fsync binlog

3 > send binlog to SLAVE and wait for slave to send ack confirmation

4 > slave accepts binlog to write to relay log, and sends ack confirmation packet to master after brushing.

5 > InnoDB engine layer commit

6 > master returns commit ok information to session

In addition (master goes down while waiting for transaction A's ACK, and new transaction B starts before downtime):

1 > transaction B cannot read the contents of transaction A because the engine layer of transaction A has not been committed (lossless replication)

Dump thread process Analysis:

Before the mysql5.6 version:

1 > master dump thread sends binlog events to IO thread of slave and waits for ack packet of slave

2 > slave accepts binlog events to write to redo log, and returns ack packet to master dump thread

3 > master dump thread receives the ack packet, returns commit ok to session, and then continues to send binlog to write a transaction.

Add ack thread after mysql5.7:

1 > master dump thread sends binlog events to slave's IO thread, opens the ack thread to wait for the ack packet of slave, and the dump thread continues to send the binlog of the next transaction to slaveIO thread.

2 > slave accepts binlog events to write to redo log, returns ack packet to master ack thread, and then returns commit ok to session.

Process summary:

Master does not Commit the transaction until it receives the reply from slave-after_sync (Master on 5.6wait for the reply from Slave after commit-after commit).

Therefore, concurrent transactions cannot see the data of the current transaction before confirming that the transaction is replicated to the Slave.

When Master fails, all committed transactions are copied to Slave.

By default, the reply waiting mechanism after_sync without data loss is adopted. Users can also choose to use the reply waiting mechanism after_commit of 5.6,

Setting method:

Mysql > SET rpl_semi_sync_master_wait_point= AFTER_SYNC

The commit transaction is not performed until Master receives N slave responses.

The user can set the number of reply Slave:

Mysql > SET GLOBAL rpl_semi_sync_master_wait_for_slave_count= N

At this point, I believe you have a deeper understanding of "process Analysis of AFTER_SYNC/AFTER_COMMIT in MySQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue 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.