生活中所受的苦，終會以一種形式回歸！

前言

在實際的生產(chǎn)環(huán)境中，由單臺MySQL作為獨立的數(shù)據(jù)庫是完全不能滿足實際需求的，無論是在安全性，高可用性以及高并發(fā)等各個方面

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

總結(jié)MySQL主從集群帶來的作用是：

提高數(shù)據(jù)庫負載能力，主庫執(zhí)行讀寫任務(wù)（增刪改），備庫僅做查詢。
提高系統(tǒng)讀寫性能、可擴展性和高可用性。
數(shù)據(jù)備份與容災(zāi)，備庫在異地，主庫不存在了，備庫可以立即接管，無須恢復(fù)時間。

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

biglog

binlog是什么？有什么作用？

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

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

在實際應(yīng)用中， binlog 的主要使用場景有兩個：

用于主從復(fù)制，在主從結(jié)構(gòu)中，binlog 作為操作記錄從 master 被發(fā)送到 slave，slave服務(wù)器從 master 接收到的日志保存到 relay log 中。
用于數(shù)據(jù)備份，在數(shù)據(jù)庫備份文件生成后，binlog保存了數(shù)據(jù)庫備份后的詳細信息，以便下一次備份能從備份點開始。

日志格式

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

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

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

STATMENT ：基于 SQL 語句的復(fù)制，每一條會修改數(shù)據(jù)的sql語句會記錄到 binlog 中
ROW ：基于行的復(fù)制
MIXED ：基于 STATMENT 和 ROW 兩種模式的混合復(fù)制，比如一般的數(shù)據(jù)操作使用 row 格式保存，有些表結(jié)構(gòu)的變更語句，使用 statement 來記錄

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

mysqlbinlog mysql-bin.00001 | more

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

主從復(fù)制原理

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

binlog dump線程： 主庫中有數(shù)據(jù)更新時，根據(jù)設(shè)置的binlog格式，將更新的事件類型寫入到主庫的binlog文件中，并創(chuàng)建log dump線程通知slave有數(shù)據(jù)更新。當I/O線程請求日志內(nèi)容時，將此時的binlog名稱和當前更新的位置同時傳給slave的I/O線程。
I/O線程： 該線程會連接到master，向log dump線程請求一份指定binlog文件位置的副本，并將請求回來的binlog存到本地的relay log中。
SQL線程： 該線程檢測到relay log有更新后，會讀取并在本地做redo操作，將發(fā)生在主庫的事件在本地重新執(zhí)行一遍，來保證主從數(shù)據(jù)同步。

基本過程總結(jié)

主庫寫入數(shù)據(jù)并且生成binlog文件。該過程中MySQL將事務(wù)串行的寫入二進制日志，即使事務(wù)中的語句都是交叉執(zhí)行的。
在事件寫入二進制日志完成后，master通知存儲引擎提交事務(wù)。
從庫服務(wù)器上的IO線程連接Master服務(wù)器，請求從執(zhí)行binlog日志文件中的指定位置開始讀取binlog至從庫。
主庫接收到從庫的IO線程請求后，其上復(fù)制的IO線程會根據(jù)Slave的請求信息分批讀取binlog文件然后返回給從庫的IO線程。
Slave服務(wù)器的IO線程獲取到Master服務(wù)器上IO線程發(fā)送的日志內(nèi)容、日志文件及位置點后，會將binlog日志內(nèi)容依次寫到Slave端自身的Relay Log（即中繼日志）文件的最末端，并將新的binlog文件名和位置記錄到master-info文件中，以便下一次讀取master端新binlog日志時能告訴Master服務(wù)器從新binlog日志的指定文件及位置開始讀取新的binlog日志內(nèi)容。
從庫服務(wù)器的SQL線程會實時監(jiān)測到本地Relay Log中新增了日志內(nèi)容，然后把RelayLog中的日志翻譯成SQL并且按照順序執(zhí)行SQL來更新從庫的數(shù)據(jù)。
從庫在relay-log.info中記錄當前應(yīng)用中繼日志的文件名和位置點以便下一次數(shù)據(jù)復(fù)制。

并行復(fù)制

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

I/O線程負責接收二進制日志，SQL線程進行回放二進制日志。如果在MySQL 5.6版本開啟并行復(fù)制功能，那么SQL線程就變?yōu)榱薱oordinator線程，coordinator線程主要負責以前兩部分的內(nèi)容

上圖的紅色框框部分就是實現(xiàn)并行復(fù)制的關(guān)鍵所在

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

coordinator線程與worker是典型的生產(chǎn)者與消費者模型。

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

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

DATABASE：默認值，基于庫的并行復(fù)制方式
LOGICAL_CLOCK：基于組提交的并行復(fù)制方式

下面分別介紹下兩種并行復(fù)制方式

按庫并行

每個 worker 線程對應(yīng)一個 hash 表，用于保存當前正在這個worker的執(zhí)行隊列里的事務(wù)所涉及到的庫。其中hash表里的key是數(shù)據(jù)庫名，用于決定分發(fā)策略。該策略的優(yōu)點是構(gòu)建hash值快，只需要庫名，同時對于binlog的格式?jīng)]有要求。

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

組提交優(yōu)化

該特性如下：

能夠同一組里提交的事務(wù)，定不會修改同一行；
主庫上可以并行執(zhí)行的事務(wù)，從庫上也一定可以并行執(zhí)行。

具體是如何實現(xiàn)的：

在同一組里面一起提交的事務(wù)，會有一個相同的commit_id，下一組為commit_id+1，該commit_id會直接寫到binlog中；
在從庫使用時，相同commit_id的事務(wù)會被分發(fā)到多個worker并行執(zhí)行，直到這一組相同的commit_id執(zhí)行結(jié)束后，coordinator再取下一批。

更詳細內(nèi)容可以去官網(wǎng)看看：https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html

下面開始介紹主從延時

主從延遲

主從延遲是怎么回事？

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

我們來看下導(dǎo)致主從延遲的時間點：

主庫 A 執(zhí)行完成一個事務(wù)，寫入 binlog，該時刻記為T1.
傳給從庫B，從庫接受完這個binlog的時刻記為T2.
從庫B執(zhí)行完這個事務(wù)，該時刻記為T3.

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

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

seconds_behind_master如何計算的？

每一個事務(wù)的binlog都有一個時間字段，用于記錄主庫上寫入的時間
從庫取出當前正在執(zhí)行的事務(wù)的時間字段，跟當前系統(tǒng)的時間進行相減，得到的就是seconds_behind_master，也就是前面所描述的T3-T1。

主從延遲原因

為什么會主從延遲？

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

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

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

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

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

2、從庫的壓力大

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

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

3、大事務(wù)的執(zhí)行

一旦執(zhí)行大事務(wù)，那么主庫必須要等到事務(wù)完成之后才會寫入binlog。
比如主庫執(zhí)行了一條insert … select非常大的插入操作，該操作產(chǎn)生了近幾百G的binlog文件傳輸?shù)街蛔x節(jié)點，進而導(dǎo)致了只讀節(jié)點出現(xiàn)應(yīng)用binlog延遲。

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

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

1、只讀節(jié)點與主庫的DDL同步是串行進行，如果DDL操作在主庫執(zhí)行時間很長，那么從庫也會消耗同樣的時間，比如在主庫對一張500W的表添加一個字段耗費了10分鐘，那么從節(jié)點上也會耗費10分鐘。
2、從節(jié)點上有一個執(zhí)行時間非常長的的查詢正在執(zhí)行，那么這個查詢會堵塞來自主庫的DDL，表被鎖，直到查詢結(jié)束為止，進而導(dǎo)致了從節(jié)點的數(shù)據(jù)延遲。

5、鎖沖突

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

6、從庫的復(fù)制能力

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

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

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

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