MySQL默認Replication如何搭建異步復制

本文主要給大家介紹MySQL默認Replication如何搭建異步復制，文章內容都是筆者用心摘選和編輯的，具有一定的針對性，對大家的參考意義還是比較大的，下面跟筆者一起了解下MySQL默認Replication如何搭建異步復制吧。  

1.背景知識

Asynchronous replication 

the master writes events to its binary log and slaves request them when they are ready. There is no guarantee that any event will ever reach any slave.

--主庫只管把events寫入binlog中，不管從庫有沒有收到。

Fully synchronous replication 

when a master commits a transaction, all slaves also will have committed the transaction before the master returns to the session that performed the transaction. The drawback of this is that there might be a lot of delay to complete a transaction.

--主庫提交一個事物，需要等待所有從庫先提交才能返回結果，執行這個事物。這樣會造成一個事物延時。

Semisynchronous replication 

falls between asynchronous and fully synchronous replication. The master waits only until at least one slave has received and logged the events. It does not wait for all slaves to acknowledge receipt, and it requires only receipt, not that the events have been fully executed and committed on the slave side.

--介于異步復制和全復制之間，主庫僅僅只要等待至少一個從庫收到和記錄events。它不需要等待所有的從庫告訴它收到events,也不需要從庫執行和提交事物，從庫只是收到events就會告訴主庫，這樣主庫就可以提前提交事物了。

此外，半同步也分兩種，有參數rpl_semi_sync_master_wait_point控制，這里我就不多做解釋了，我們使用默認設置after_sync,這種數據零丟失

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.

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.

2.測試環境

一個master,slave1和slave2都是master的直接從庫。

分兩種情況測試：

（1）當slave1和slave2都是異步復制的時候

（2）當slave1是半同步復制，slave2是異步復制的時候

3.壓力測試

使用TPCC壓力測試軟件，比較TPS判斷異步和半同步復制的性能差異到底有多大。

具體如何測試，可以參考之前的博客：MySQL壓測--TPCC安裝，測試

• 通過TPCC測試獲得的數據庫TpmC數據

• 繪制折線圖像

4.總結

從本測試結果可以看出：

• 無論是半同步復制還是異步復制，數據庫的TpmC剛開始隨著并發線程的增加而增加，到達200個左右，TpmC開始逐漸下降；

• 當線程數在16-1024左右之間，異步復制的TpmC明顯比半同步的大很多，相差大概1000-2000左右，所以半同步相比異步性能是要下降很多；
  

• 如果你的數據庫并發并不是很多，又對數據安全要求比較高，可以優先考慮使用半同步復制；

看完以上關于MySQL默認Replication如何搭建異步復制，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業知識信息 ，可以持續關注我們的行業資訊欄目的。