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

前言

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

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

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

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

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

biglog

binlog是什么？有什么作用？

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

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

在實(shí)際應(yīng)用中， binlog 的主要使用場(chǎng)景有兩個(gè)：

• 用于主從復(fù)制，在主從結(jié)構(gòu)中，binlog 作為操作記錄從 master 被發(fā)送到 slave，slave服務(wù)器從 master 接收到的日志保存到 relay log 中。

• 用于數(shù)據(jù)備份，在數(shù)據(jù)庫(kù)備份文件生成后，binlog保存了數(shù)據(jù)庫(kù)備份后的詳細(xì)信息，以便下一次備份能從備份點(diǎn)開始。

日志格式

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

在 MySQL 5.7.7 之前，默認(rèn)的格式是 STATEMENT ， MySQL 5.7.7 之后，默認(rèn)值是 ROW

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

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

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

mysqlbinlog mysql-bin.00001 | more

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

主從復(fù)制原理

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

• binlog dump線程： 主庫(kù)中有數(shù)據(jù)更新時(shí)，根據(jù)設(shè)置的binlog格式，將更新的事件類型寫入到主庫(kù)的binlog文件中，并創(chuàng)建log dump線程通知slave有數(shù)據(jù)更新。當(dāng)I/O線程請(qǐng)求日志內(nèi)容時(shí)，將此時(shí)的binlog名稱和當(dāng)前更新的位置同時(shí)傳給slave的I/O線程。

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

• SQL線程： 該線程檢測(cè)到relay log有更新后，會(huì)讀取并在本地做redo操作，將發(fā)生在主庫(kù)的事件在本地重新執(zhí)行一遍，來保證主從數(shù)據(jù)同步。

基本過程總結(jié)

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

并行復(fù)制

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

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

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

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

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

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

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

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

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

按庫(kù)并行

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

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

組提交優(yōu)化

該特性如下：

1. 能夠同一組里提交的事務(wù)，定不會(huì)修改同一行；

2. 主庫(kù)上可以并行執(zhí)行的事務(wù)，從庫(kù)上也一定可以并行執(zhí)行。

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

1. 在同一組里面一起提交的事務(wù)，會(huì)有一個(gè)相同的commit_id，下一組為commit_id+1，該commit_id會(huì)直接寫到binlog中；

2. 在從庫(kù)使用時(shí)，相同commit_id的事務(wù)會(huì)被分發(fā)到多個(gè)worker并行執(zhí)行，直到這一組相同的commit_id執(zhí)行結(jié)束后，coordinator再取下一批。

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

下面開始介紹主從延時(shí)

主從延遲

主從延遲是怎么回事？

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

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

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

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

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

seconds_behind_master如何計(jì)算的？

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

主從延遲原因

為什么會(huì)主從延遲？

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

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

1、從庫(kù)的機(jī)器性能比主庫(kù)要差

比如將20臺(tái)主庫(kù)放在4臺(tái)機(jī)器，把從庫(kù)放在一臺(tái)機(jī)器。這個(gè)時(shí)候進(jìn)行更新操作，由于更新時(shí)會(huì)觸發(fā)大量讀操作，導(dǎo)致從庫(kù)機(jī)器上的多個(gè)從庫(kù)爭(zhēng)奪資源，導(dǎo)致主從延遲。

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

2、從庫(kù)的壓力大

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

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

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

一旦執(zhí)行大事務(wù)，那么主庫(kù)必須要等到事務(wù)完成之后才會(huì)寫入binlog。

比如主庫(kù)執(zhí)行了一條insert … select非常大的插入操作，該操作產(chǎn)生了近幾百G的binlog文件傳輸?shù)街蛔x節(jié)點(diǎn)，進(jìn)而導(dǎo)致了只讀節(jié)點(diǎn)出現(xiàn)應(yīng)用binlog延遲。

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

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

1、只讀節(jié)點(diǎn)與主庫(kù)的DDL同步是串行進(jìn)行，如果DDL操作在主庫(kù)執(zhí)行時(shí)間很長(zhǎng)，那么從庫(kù)也會(huì)消耗同樣的時(shí)間，比如在主庫(kù)對(duì)一張500W的表添加一個(gè)字段耗費(fèi)了10分鐘，那么從節(jié)點(diǎn)上也會(huì)耗費(fèi)10分鐘。

2、從節(jié)點(diǎn)上有一個(gè)執(zhí)行時(shí)間非常長(zhǎng)的的查詢正在執(zhí)行，那么這個(gè)查詢會(huì)堵塞來自主庫(kù)的DDL，表被鎖，直到查詢結(jié)束為止，進(jìn)而導(dǎo)致了從節(jié)點(diǎn)的數(shù)據(jù)延遲。

5、鎖沖突

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

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

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

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

因此，MySQL自5.7版本后就已經(jīng)支持并行復(fù)制了?？梢栽趶姆?wù)上設(shè)置 slave_parallel_workers為一個(gè)大于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        |
+------------------------+----------+

怎么減少主從延遲

主從同步問題永遠(yuǎn)都是一致性和性能的權(quán)衡，得看實(shí)際的應(yīng)用場(chǎng)景，若想要減少主從延遲的時(shí)間，可以采取下面的辦法：

1. 降低多線程大事務(wù)并發(fā)的概率，優(yōu)化業(yè)務(wù)邏輯
2. 優(yōu)化SQL，避免慢SQL，減少批量操作，建議寫腳本以u(píng)pdate-sleep這樣的形式完成。
3. 提高從庫(kù)機(jī)器的配置，減少主庫(kù)寫binlog和從庫(kù)讀binlog的效率差。
4. 盡量采用短的鏈路，也就是主庫(kù)和從庫(kù)服務(wù)器的距離盡量要短，提升端口帶寬，減少binlog傳輸?shù)木W(wǎng)絡(luò)延時(shí)。
5. 實(shí)時(shí)性要求的業(yè)務(wù)讀強(qiáng)制走主庫(kù)，從庫(kù)只做災(zāi)備，備份。