在實際的生產環境中，由單臺MySQL作為獨立的數據庫是完全不能滿足實際需求的，無論是在安全性，高可用性以及高并發等各個方面

因此，一般來說都是通過集群主從復制（Master-Slave）的方式來同步數據，再通過讀寫分離（MySQL-Proxy）來提升數據庫的并發負載能力進行部署與實施

總結MySQL主從集群帶來的作用是：

• 提高數據庫負載能力，主庫執行讀寫任務（增刪改），備庫僅做查詢。

• 提高系統讀寫性能、可擴展性和高可用性。

• 數據備份與容災，備庫在異地，主庫不存在了，備庫可以立即接管，無須恢復時間。

說到主從同步，離不開binlog這個東西，先介紹下binlog吧

biglog

binlog是什么？有什么作用？

用于記錄數據庫執行的寫入性操作(不包括查詢)信息，以二進制的形式保存在磁盤中。可以簡單理解為記錄的就是sql語句

binlog 是 mysql 的邏輯日志，并且由?Server層進行記錄，使用任何存儲引擎的 mysql 數據庫都會記錄 binlog 日志

在實際應用中， binlog 的主要使用場景有兩個：

• 用于主從復制，在主從結構中，binlog 作為操作記錄從 master 被發送到 slave，slave服務器從 master 接收到的日志保存到 relay log 中。

• 用于數據備份，在數據庫備份文件生成后，binlog保存了數據庫備份后的詳細信息，以便下一次備份能從備份點開始。

日志格式

binlog 日志有三種格式，分別為 STATMENT 、 ROW 和 MIXED

在 MySQL 5.7.7 之前，默認的格式是 STATEMENT ， MySQL 5.7.7 之后，默認值是 ROW

日志格式通過?binlog-format?指定。

• STATMENT ：基于 SQL 語句的復制，每一條會修改數據的sql語句會記錄到 binlog 中

• ROW ：基于行的復制

• MIXED ：基于 STATMENT 和 ROW 兩種模式的混合復制，比如一般的數據操作使用 row 格式保存，有些表結構的變更語句，使用 statement 來記錄

我們還可以通過mysql提供的查看工具mysqlbinlog查看文件中的內容，例如

mysqlbinlog?mysql-bin.00001?|?more

binlog文件大小和個數會不斷的增加，后綴名會按序號遞增，例如mysql-bin.00002等。

主從復制原理

可以看到mysql主從復制需要三個線程：master（binlog dump thread）、slave（I/O thread 、SQL thread）

• binlog dump線程：?主庫中有數據更新時，根據設置的binlog格式，將更新的事件類型寫入到主庫的binlog文件中，并創建log dump線程通知slave有數據更新。當I/O線程請求日志內容時，將此時的binlog名稱和當前更新的位置同時傳給slave的I/O線程。

• I/O線程：?該線程會連接到master，向log dump線程請求一份指定binlog文件位置的副本，并將請求回來的binlog存到本地的relay log中。

• SQL線程：?該線程檢測到relay log有更新后，會讀取并在本地做redo操作，將發生在主庫的事件在本地重新執行一遍，來保證主從數據同步。

基本過程總結

1. 主庫寫入數據并且生成binlog文件。該過程中MySQL將事務串行的寫入二進制日志，即使事務中的語句都是交叉執行的。

2. 在事件寫入二進制日志完成后，master通知存儲引擎提交事務。

3. 從庫服務器上的IO線程連接Master服務器，請求從執行binlog日志文件中的指定位置開始讀取binlog至從庫。

4. 主庫接收到從庫的IO線程請求后，其上復制的IO線程會根據Slave的請求信息分批讀取binlog文件然后返回給從庫的IO線程。

5. Slave服務器的IO線程獲取到Master服務器上IO線程發送的日志內容、日志文件及位置點后，會將binlog日志內容依次寫到Slave端自身的Relay Log（即中繼日志）文件的最末端，并將新的binlog文件名和位置記錄到master-info文件中，以便下一次讀取master端新binlog日志時能告訴Master服務器從新binlog日志的指定文件及位置開始讀取新的binlog日志內容。

6. 從庫服務器的SQL線程會實時監測到本地Relay Log中新增了日志內容，然后把RelayLog中的日志翻譯成SQL并且按照順序執行SQL來更新從庫的數據。

7. 從庫在relay-log.info中記錄當前應用中繼日志的文件名和位置點以便下一次數據復制。

并行復制

在MySQL 5.6版本之前，Slave服務器上有兩個線程I/O線程和SQL線程。

I/O線程負責接收二進制日志，SQL線程進行回放二進制日志。如果在MySQL 5.6版本開啟并行復制功能，那么SQL線程就變為了coordinator線程，coordinator線程主要負責以前兩部分的內容

上圖的紅色框框部分就是實現并行復制的關鍵所在

這意味著coordinator線程并不是僅將日志發送給worker線程，自己也可以回放日志，但是所有可以并行的操作交付由worker線程完成。

coordinator線程與worker是典型的生產者與消費者模型。

不過到MySQL 5.7才可稱為真正的并行復制，這其中最為主要的原因就是slave服務器的回放與主機是一致的即master服務器上是怎么并行執行的slave上就怎樣進行并行回放。不再有庫的并行復制限制，對于二進制日志格式也無特殊的要求。

為了兼容MySQL 5.6基于庫的并行復制，5.7引入了新的變量slave-parallel-type，其可以配置的值有：

• DATABASE：默認值，基于庫的并行復制方式

• LOGICAL_CLOCK：基于組提交的并行復制方式

下面分別介紹下兩種并行復制方式

按庫并行

每個 worker 線程對應一個 hash 表，用于保存當前正在這個worker的執行隊列里的事務所涉及到的庫。其中hash表里的key是數據庫名，用于決定分發策略。該策略的優點是構建hash值快，只需要庫名，同時對于binlog的格式沒有要求。

但這個策略的效果，只有在主庫上存在多個DB，且各個DB的壓力均衡的情況下，這個策略效果好。因此，對于主庫上的表都放在同一個DB或者不同DB的熱點不同，則起不到多大效果

組提交優化

該特性如下：

1. 能夠同一組里提交的事務，定不會修改同一行；

2. 主庫上可以并行執行的事務，從庫上也一定可以并行執行。

具體是如何實現的：

1. 在同一組里面一起提交的事務，會有一個相同的commit_id，下一組為commit_id+1，該commit_id會直接寫到binlog中；

2. 在從庫使用時，相同commit_id的事務會被分發到多個worker并行執行，直到這一組相同的commit_id執行結束后，coordinator再取下一批。

更詳細內容可以去官網看看：https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html

?

下面開始介紹主從延時

主從延遲

主從延遲是怎么回事？

根據前面主從復制的原理可以看出，兩者之間是存在一定時間的數據不一致，也就是所謂的主從延遲。

我們來看下導致主從延遲的時間點：

• 主庫 A 執行完成一個事務，寫入 binlog，該時刻記為T1.

• 傳給從庫B，從庫接受完這個binlog的時刻記為T2.

• 從庫B執行完這個事務，該時刻記為T3.

那么所謂主從延遲，就是同一個事務，從庫執行完成的時間和主庫執行完成的時間之間的差值，即T3-T1。

我們也可以通過在從庫執行show slave status，返回結果會顯示seconds_behind_master，表示當前從庫延遲了多少秒。

seconds_behind_master如何計算的？

• 每一個事務的binlog都有一個時間字段，用于記錄主庫上寫入的時間

• 從庫取出當前正在執行的事務的時間字段，跟當前系統的時間進行相減，得到的就是seconds_behind_master，也就是前面所描述的T3-T1。

?

主從延遲原因

為什么會主從延遲？

正常情況下，如果網絡不延遲，那么日志從主庫傳給從庫的時間是相當短，所以T2-T1可以基本忽略。

最直接的影響就是從庫消費中轉日志（relaylog）的時間段，而造成原因一般是以下幾種：

1、從庫的機器性能比主庫要差

比如將20臺主庫放在4臺機器，把從庫放在一臺機器。這個時候進行更新操作，由于更新時會觸發大量讀操作，導致從庫機器上的多個從庫爭奪資源，導致主從延遲。

不過，目前大部分部署都是采取主從使用相同規格的機器部署。

2、從庫的壓力大

按照正常的策略，讀寫分離，主庫提供寫能力，從庫提供讀能力。將進行大量查詢放在從庫上，結果導致從庫上耗費了大量的CPU資源，進而影響了同步速度，造成主從延遲。

對于這種情況，可以通過一主多從，分擔讀壓力；也可以采取binlog輸出到外部系統，比如Hadoop，讓外部系統提供查詢能力。

3、大事務的執行

一旦執行大事務，那么主庫必須要等到事務完成之后才會寫入binlog。

比如主庫執行了一條insert … select非常大的插入操作，該操作產生了近幾百G的binlog文件傳輸到只讀節點，進而導致了只讀節點出現應用binlog延遲。

因此，DBA經常會提醒開發，不要一次性地試用delete語句刪除大量數據，盡可能控制數量，分批進行。

4、主庫的DDL(alter、drop、create)

1、只讀節點與主庫的DDL同步是串行進行，如果DDL操作在主庫執行時間很長，那么從庫也會消耗同樣的時間，比如在主庫對一張500W的表添加一個字段耗費了10分鐘，那么從節點上也會耗費10分鐘。

2、從節點上有一個執行時間非常長的的查詢正在執行，那么這個查詢會堵塞來自主庫的DDL，表被鎖，直到查詢結束為止，進而導致了從節點的數據延遲。

?

5、鎖沖突

鎖沖突問題也可能導致從節點的SQL線程執行慢，比如從機上有一些select .... for update的SQL，或者使用了MyISAM引擎等。

6、從庫的復制能力

一般場景中，因偶然情況導致從庫延遲了幾分鐘，都會在從庫恢復之后追上主庫。但若是從庫執行速度低于主庫，且主庫持續具有壓力，就會導致長時間主從延遲，很有可能就是從庫復制能力的問題。

從庫上的執行，即sql_thread更新邏輯，在5.6版本之前，是只支持單線程，那么在主庫并發高、TPS高時，就會出現較大的主從延遲。

因此，MySQL自5.7版本后就已經支持并行復制了。可以在從服務上設置?slave_parallel_workers為一個大于0的數，然后把slave_parallel_type參數設置為LOGICAL_CLOCK，這就可以了

mysql>?show?variables?like?'slave_parallel%';
+------------------------+----------+
|?Variable_name??????????|?Value????|
+------------------------+----------+
|?slave_parallel_type????|?DATABASE?|
|?slave_parallel_workers?|?0????????|
+------------------------+----------+

怎么減少主從延遲

主從同步問題永遠都是一致性和性能的權衡，得看實際的應用場景，若想要減少主從延遲的時間，可以采取下面的辦法：

1. 降低多線程大事務并發的概率，優化業務邏輯

2. 優化SQL，避免慢SQL，減少批量操作，建議寫腳本以update-sleep這樣的形式完成。

3. 提高從庫機器的配置，減少主庫寫binlog和從庫讀binlog的效率差。

4. 盡量采用短的鏈路，也就是主庫和從庫服務器的距離盡量要短，提升端口帶寬，減少binlog傳輸的網絡延時。

5. 實時性要求的業務讀強制走主庫，從庫只做災備，備份。