[資料庫]MYSQL主從同步

關於mysql主從同步，相信大家都不陌生，隨著系統應用訪問量逐漸增大，單臺資料庫讀寫訪問壓力也隨之增大，當讀寫訪問達到一定瓶頸時，將資料庫的讀寫效率驟然下降，甚至不可用；為了解決此類問題，通常會採用mysql叢集，當主庫當機後，叢集會自動將一個從庫升級為主庫，繼續對外提供服務；那麼主庫和從庫之間的資料是如何同步的呢？本文針對MySQL 5.7版本進行下面的分析，下面隨筆者一起探究一下mysql主從是如何同步的。

MySQL主從複製原理

為了減輕主庫的壓力，應該在系統應用層面做讀寫分離，寫操作走主庫，讀操作走從庫，下圖為MySQL官網給出的主從複製的原理圖，從圖中可以簡單的瞭解讀寫分離及主從同步的過程，分散了資料庫的訪問壓力，提升整個系統的效能和可用性，降低了大訪問量引發資料庫當機的故障率。

binlog簡介

MySQL主從同步是基於binlog檔案主從複製實現，為了更好的理解主從同步過程，這裡簡單介紹一下binlog日誌檔案。

binlog日誌用於記錄所有更新了資料或者已經潛在更新了資料（例如，沒有匹配任何行的一個DELETE）的所有語句。語句以“事件”的形式儲存，它描述資料更改，它是以二進位制的形式儲存在磁碟中。我們可以通過mysql提供的檢視工具mysqlbinlog檢視檔案中的內容，例如 mysqlbinlog mysql-bin.00001 | more，這裡注意一下binlog檔案的字尾名00001，binlog檔案大小和個數會不斷的增加，當MySQL停止或重啟時，會產生一個新的binlog檔案，字尾名會按序號遞增，例如mysql-bin.00002、mysql-bin.00003，並且當binlog檔案大小超過 max_binlog_size系統變數配置時也會產生新的binlog檔案。

1. binlog日誌格式

（1）statement ： 記錄每一條更改資料的sql

• 優點：binlog檔案較小，節約I/O，效能較高。
• 缺點：不是所有的資料更改都會寫入binlog檔案中，尤其是使用MySQL中的一些特殊函式（如LOAD_FILE()、UUID()等）和一些不確定的語句操作，從而導致主從資料無法複製的問題。

（2）row ： 不記錄sql，只記錄每行資料的更改細節

• 優點：詳細的記錄了每一行資料的更改細節，這也意味著不會由於使用一些特殊函式或其他情況導致不能複製的問題。
• 缺點：由於row格式記錄了每一行資料的更改細節，會產生大量的binlog日誌內容，效能不佳，並且會增大主從同步延遲出現的機率。

（3）mixed：一般的語句修改使用statment格式儲存binlog，如一些函式，statement無法完成主從複製的操作，則採用row格式儲存binlog，MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日誌形式，也就是在Statement和Row之間選擇一種。

2. binlog日誌內容

mysqlbinlog命令檢視的內容如下：

根據事件型別檢視的binlog內容：

3. binlog事件型別

MySQL binlog記錄的所有操作實際上都有對應的事件型別的，譬如STATEMENT格式中的DML操作對應的是QUERY_EVENT型別，ROW格式下的DML操作對應的是ROWS_EVENT型別，如果想了解更多請參考官方文件，有關binlog日誌內容不在這裡過多贅述，簡單介紹一下是為了更好的理解主從複製的細節，下面我們進入正題。

MySQL主從複製原理

mysql主從複製需要三個執行緒，master（binlog dump thread）、slave（I/O thread 、SQL thread）。

master

（1）binlog dump執行緒：當主庫中有資料更新時，那麼主庫就會根據按照設定的binlog格式，將此次更新的事件型別寫入到主庫的binlog檔案中，此時主庫會建立log dump執行緒通知slave有資料更新，當I/O執行緒請求日誌內容時，會將此時的binlog名稱和當前更新的位置同時傳給slave的I/O執行緒。

slave

（2）I/O執行緒：該執行緒會連線到master，向log dump執行緒請求一份指定binlog檔案位置的副本，並將請求回來的binlog存到本地的relay log中，relay log和binlog日誌一樣也是記錄了資料更新的事件，它也是按照遞增字尾名的方式，產生多個relay log（ host_name-relay-bin.000001）檔案，slave會使用一個index檔案（ host_name-relay-bin.index）來追蹤當前正在使用的relay log檔案。

（3）SQL執行緒：該執行緒檢測到relay log有更新後，會讀取並在本地做redo操作，將發生在主庫的事件在本地重新執行一遍，來保證主從資料同步。此外，如果一個relay log檔案中的全部事件都執行完畢，那麼SQL執行緒會自動將該relay log 檔案刪除掉。

下面是整個複製過程的原理圖：

主從同步延遲

mysql的主從複製都是單執行緒的操作，主庫對所有DDL和DML產生binlog，binlog是順序寫，所以效率很高，slave的I/O執行緒到主庫取日誌，效率也比較高，但是，slave的SQL執行緒將主庫的DDL和DML操作在slave實施。DML和DDL的IO操作是隨即的，不是順序的，成本高很多，還可能存在slave上的其他查詢產生lock爭用的情況，由於SQL也是單執行緒的，所以一個DDL卡住了，需要執行很長一段事件，後續的DDL執行緒會等待這個DDL執行完畢之後才執行，這就導致了延時。當主庫的TPS併發較高時，產生的DDL數量超過slave一個sql執行緒所能承受的範圍，延時就產生了，除此之外，還有可能與slave的大型query語句產生了鎖等待導致。

由於主從同步延遲是客觀存在的，我們只能從我們自己的架構上進行設計， 儘量讓主庫的DDL快速執行。下面列出幾種常見的解決方案：

• 業務的持久化層的實現採用分庫架構，mysql服務可平行擴充套件，分散壓力；
• 服務的基礎架構在業務和mysql之間加入memcache或者Redis的cache層。降低mysql的讀壓力；
• 使用比主庫更好的硬體裝置作為slave；
• sync_binlog在slave端設定為0；
• –logs-slave-updates 從伺服器從主伺服器接收到的更新不記入它的二進位制日誌；
• 禁用slave的binlog。

參考資料

• https://dev.mysql.com/doc/refman/5.7/en/replication.html
• http://www.linuxidc.com/Linux/2014-05/101450.htm
• http://blog.csdn.net/xiongping_/article/details/49907095
• http://www.cnblogs.com/martinzhang/p/3454358.html