浅析MySQL主从复制技术（异步复制、同步复制、半同步复制）
Preface
As we all know,there're three kinds of replication in MySQL nowadays.Such as,asynchronous replication,(full)synchronous replication,semi-synchronous replication.What's the difference between them?First of all,let's see the intact architecture picture of MySQL replication:
What will client do?
1. Client sends dml operations to the master while the transaction starts.
2. Master executes these dml operations from client in transaction.
3. Generates some binary logs which contains the transaction information.
4. Master will return results to the client immediately after dump thread has sent these binary logs to slave.
5. Slave receives the binary logs by IO_Thread and apply the relay logs by SQL_Thread. In step 4,master won't judge whether slave has received the binary logs (which are sent by itself) or not.If the master crashs suddenly after it has sent the binary logs,but slave does not receive them at all on account of network delay.Only if the slave takes over the application at this time,the committed transactions will miss which means data loss.This is not commonly acceptable in most important product systems especially in the financial ones.
In this method,master performs a commit before it receives ACK signal from slave.Let's suppose a situation that once master crashs after it commits a transaction but it hasn't receive the ACK signal from slave.Meanwhile,failover makes slave become the new master.How does the slave deal with then?Will the transaction lose?It depends.There're two scenarios:
In the picture above,the t1 transaction shouldn't be lost because of the master merely commits to the storage engine after receive the ACK signal from slave.In spite of master may crash before receiving ACK signal,no transaction will lose as the master hasn't commit at all.Meanwhile,the t2 transaction also get consistent query here.
In order to improve the data consistency(since after_commit has avoidless deficiency),MySQL official enhances the semi-synchronous replication which can be called "loss-less semi-synchronous replication" in MySQL 5.7 by add after_sync mode in parameter "rpl_semi_sync_master_wait_point".
Caution,semi-synchronous replication may turn into asynchronous replication whenever the delay time of slave surpass the value which is specified in parameter "rpl_semi_sync_master_timeout"(default values is 10000 milliseconds).Why it is permitted?I'm afraid in order to consider the performance of master.Notwithstanding,you can also play a trick to prevent it from being converted over by set a infinite number in this parameter such as "10000000" or above.Especially in case that your product system is too important to not lose data.
Further more,to configure semi-sychronous replication,you should implement the optional plugin component "rpl_semi_sync_master",which can be check by using command "show plugins;"
Summary:
扫码关注我们
微信号：SRE实战
拒绝背锅 运筹帷幄
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。
What will client do?
- generates transactions
- commits transactions to master
- receives results from master
- executes transactions
- generates binary logs
- dump thread sends contents(binary logs) to slave
- returns results to client
- connects to master
- IO Thread asks for data(binary logs) and gets them
- generates relay logs
- SQL Thread applies data(relay logs)
- asynchronous replication
1. Client sends dml operations to the master while the transaction starts.
2. Master executes these dml operations from client in transaction.
3. Generates some binary logs which contains the transaction information.
4. Master will return results to the client immediately after dump thread has sent these binary logs to slave.5. Slave receives the binary logs by IO_Thread and apply the relay logs by SQL_Thread. In step 4,master won't judge whether slave has received the binary logs (which are sent by itself) or not.If the master crashs suddenly after it has sent the binary logs,but slave does not receive them at all on account of network delay.Only if the slave takes over the application at this time,the committed transactions will miss which means data loss.This is not commonly acceptable in most important product systems especially in the financial ones.
- synchronous replication
- semi-synchronous replication
In this method,master performs a commit before it receives ACK signal from slave.Let's suppose a situation that once master crashs after it commits a transaction but it hasn't receive the ACK signal from slave.Meanwhile,failover makes slave become the new master.How does the slave deal with then?Will the transaction lose?It depends.There're two scenarios:
- Slave has received the binary log,and then turns it into relay log and applys it.There's no transaction loss.
- Slave hasn't received the binary log,the transaction committed by master just now will lose,but the client won't fail(only inconsistent in replication).
In the picture above,the t1 transaction shouldn't be lost because of the master merely commits to the storage engine after receive the ACK signal from slave.In spite of master may crash before receiving ACK signal,no transaction will lose as the master hasn't commit at all.Meanwhile,the t2 transaction also get consistent query here.
In order to improve the data consistency(since after_commit has avoidless deficiency),MySQL official enhances the semi-synchronous replication which can be called "loss-less semi-synchronous replication" in MySQL 5.7 by add after_sync mode in parameter "rpl_semi_sync_master_wait_point".
Caution,semi-synchronous replication may turn into asynchronous replication whenever the delay time of slave surpass the value which is specified in parameter "rpl_semi_sync_master_timeout"(default values is 10000 milliseconds).Why it is permitted?I'm afraid in order to consider the performance of master.Notwithstanding,you can also play a trick to prevent it from being converted over by set a infinite number in this parameter such as "10000000" or above.Especially in case that your product system is too important to not lose data.
Further more,to configure semi-sychronous replication,you should implement the optional plugin component "rpl_semi_sync_master",which can be check by using command "show plugins;"
Summary:
- Commonly,semi-sync replication is strongly recommended when implements MySQL replication nowadays(with gtid).
- I utterly recommend to upgrade product system to MySQL 5.7 in order to use "after_sync" mode which can avoid data loss.
- Be careful of specify an inappropriate value in parameter "rpl_semi_sync_master_timeout" which will cause converting semi-sync to async replication.
更多精彩
