詳細(xì)了解MySQL中的主備、主從和讀寫分離

本篇文章帶大家了解一下mysql中的主備、主從和讀寫分離，希望對(duì)大家有所幫助！

一、mysql主備的基本原理

在狀態(tài)1中，客戶端的讀寫都直接訪問(wèn)節(jié)點(diǎn)A，而節(jié)點(diǎn)B是A的備庫(kù)，只是將A的更新都同步過(guò)來(lái)，到本地執(zhí)行。這樣可以保持節(jié)點(diǎn)B和A的數(shù)據(jù)是相同的。當(dāng)需要切換的時(shí)候，就切成狀態(tài)2。這時(shí)候客戶端讀寫訪問(wèn)的都是節(jié)點(diǎn)B，而節(jié)點(diǎn)A是B的備庫(kù)。【相關(guān)推薦：mysql視頻教程】

在狀態(tài)1中，雖然節(jié)點(diǎn)B沒(méi)有被直接訪問(wèn)，但是建議把備庫(kù)節(jié)點(diǎn)B，設(shè)置成只讀模式。有以下幾個(gè)原因：

1.有時(shí)候一些運(yùn)營(yíng)類的查詢語(yǔ)句會(huì)被放到備庫(kù)上去查，設(shè)置為只讀可以防止誤操作

2.防止切換邏輯有bug

3.可以用readonly狀態(tài)，來(lái)判斷節(jié)點(diǎn)的角色

把備庫(kù)設(shè)置成只讀，還怎么跟主庫(kù)保持同步更新？

readonly設(shè)置對(duì)超級(jí)權(quán)限用戶是無(wú)效的，而用于同步更新的線程，就擁有超級(jí)權(quán)限

下圖是一個(gè)update語(yǔ)句在節(jié)點(diǎn)A執(zhí)行，然后同步到節(jié)點(diǎn)B的完整流程圖：

備庫(kù)B和主庫(kù)A之間維持了一個(gè)長(zhǎng)連接。主庫(kù)A內(nèi)部有一個(gè)線程，專門用于服務(wù)備庫(kù)B的這個(gè)長(zhǎng)連接。一個(gè)事務(wù)日志同步的完整過(guò)程如下：

1.在備庫(kù)B上通過(guò)change master命令，設(shè)置主庫(kù)A的IP、端口、用戶名、密碼，以及要從哪個(gè)位置開(kāi)始請(qǐng)求binlog，這個(gè)位置包含文件名和日志偏移量

2.在備庫(kù)B上執(zhí)行start slave命令，這時(shí)備庫(kù)會(huì)啟動(dòng)兩個(gè)線程，就是圖中的io_thread和sql_thread。其中io_thread負(fù)責(zé)與主庫(kù)建立連接

3.主庫(kù)A校驗(yàn)完用戶名、密碼后，開(kāi)始按照備庫(kù)B傳過(guò)來(lái)的位置，從本地讀取binlog，發(fā)給B

4.備庫(kù)B拿到binlog后，寫到本地文件，稱為中轉(zhuǎn)日志

5.sql_thread讀取中轉(zhuǎn)日志，解析出日志里的命令，并執(zhí)行

由于多線程復(fù)制方案的引入，sql_thread演化成了多個(gè)線程

二、循環(huán)復(fù)制問(wèn)題

雙M結(jié)構(gòu)：

節(jié)點(diǎn)A和節(jié)點(diǎn)B互為主備關(guān)系。這樣在切換的時(shí)候就不用再修改主備關(guān)系

雙M結(jié)構(gòu)有一個(gè)問(wèn)題要解決，業(yè)務(wù)邏輯在節(jié)點(diǎn)A上更新了一條語(yǔ)句，然后再把生成的binlog發(fā)給節(jié)點(diǎn)B，節(jié)點(diǎn)B執(zhí)行完這條更新語(yǔ)句后也會(huì)生成binlog。那么，如果節(jié)點(diǎn)A同時(shí)是節(jié)點(diǎn)B的備庫(kù)，相當(dāng)于又把節(jié)點(diǎn)B新生成的binlog拿過(guò)來(lái)執(zhí)行了一次，然后節(jié)點(diǎn)A和B間，會(huì)不斷地循環(huán)執(zhí)行這個(gè)更新語(yǔ)句，也就是循環(huán)復(fù)制

MySQL在binlog中記錄了這個(gè)命令第一次執(zhí)行時(shí)所在實(shí)例的server id。因此，可以用下面的邏輯，來(lái)解決兩個(gè)節(jié)點(diǎn)間的循環(huán)復(fù)制問(wèn)題：

1.規(guī)定兩個(gè)庫(kù)的server id必須不同，如果相同，則它們之間不能設(shè)定為主備關(guān)系

2.一個(gè)備庫(kù)接到binlog并在重放的過(guò)程中，生成與原binlog的server id相同的新的binlog

3.每個(gè)庫(kù)在收到從自己的主庫(kù)發(fā)過(guò)來(lái)的日志后，先判斷server id，如果跟自己的相同，表示這個(gè)日志是自己生成的，就直接丟棄這個(gè)日志

雙M結(jié)構(gòu)日志的執(zhí)行流如下：

1.從節(jié)點(diǎn)A更新的事務(wù)，binlog里面記的都是A的server id

2.傳到節(jié)點(diǎn)B執(zhí)行一次以后，節(jié)點(diǎn)B生成的binlog的server id也是A的server id

3.再傳回給節(jié)點(diǎn)A，A判斷這個(gè)server id與自己的相同，就不會(huì)再處理這個(gè)日志。所以，死循環(huán)在這里就斷掉了

三、主備延遲

1、什么是主備延遲？

與數(shù)據(jù)同步有關(guān)的時(shí)間點(diǎn)主要包括以下三個(gè)：

1.主庫(kù)A執(zhí)行完成一個(gè)事務(wù)，寫入binlog，這個(gè)時(shí)刻記為T1

2.之后傳給備庫(kù)B，備庫(kù)B接收完這個(gè)binlog的時(shí)刻記為T2

3.備庫(kù)B執(zhí)行完這個(gè)事務(wù)，把這個(gè)時(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ì)算方法是這樣的：

1.每個(gè)事務(wù)的binlog里面都有一個(gè)時(shí)間字段，用于記錄主庫(kù)上寫入的時(shí)間

2.備庫(kù)取出當(dāng)前正在執(zhí)行的事務(wù)的時(shí)間字段的值，計(jì)算它與當(dāng)前系統(tǒng)時(shí)間的差值，得到seconds_behind_master

如果主備庫(kù)機(jī)器的系統(tǒng)時(shí)間設(shè)置不一致，不會(huì)導(dǎo)致主備延遲的值不準(zhǔn)。備庫(kù)連接到主庫(kù)的時(shí)候，會(huì)通過(guò)SELECTUNIX_TIMESTAMP()函數(shù)來(lái)獲得當(dāng)前主庫(kù)的系統(tǒng)時(shí)間。如果這時(shí)候發(fā)現(xiàn)主庫(kù)的系統(tǒng)時(shí)間與自己不一致，備庫(kù)在執(zhí)行seconds_behind_master計(jì)算的時(shí)候會(huì)自動(dòng)扣掉這個(gè)差值

網(wǎng)絡(luò)正常情況下，主備延遲的主要來(lái)源是備庫(kù)接收完binlog和執(zhí)行完這個(gè)事務(wù)之間的時(shí)間差

主備延遲最直接的表現(xiàn)是，備庫(kù)消費(fèi)中轉(zhuǎn)日志的速度，比主庫(kù)生產(chǎn)binlog的速度要慢

2、主備延遲的原來(lái)

1.有些部署條件下，備庫(kù)所在機(jī)器的性能要比主庫(kù)所在的機(jī)器性能差

2.備庫(kù)的壓力大。主庫(kù)提供寫能力，備庫(kù)提供一些讀能力。忽略了備庫(kù)的壓力控制，導(dǎo)致備庫(kù)上的查詢耗費(fèi)了大量的CPU資源，影響了同步速度，造成主備延遲

可以做以下處理：

• 一主多從。除了備庫(kù)外，可以多接幾個(gè)從庫(kù)，讓這些從庫(kù)來(lái)分擔(dān)讀的壓力
• 通過(guò)binlog輸出到外部系統(tǒng)，比如Hadoop這類系統(tǒng)，讓外部系統(tǒng)提供統(tǒng)計(jì)類查詢的能力

3.大事務(wù)。因?yàn)橹鲙?kù)上必須等事務(wù)執(zhí)行完才會(huì)寫入binlog，再傳給備庫(kù)。所以，如果一個(gè)主庫(kù)上的語(yǔ)句執(zhí)行10分鐘，那這個(gè)事務(wù)很可能會(huì)導(dǎo)致從庫(kù)延遲10分鐘

典型的大事務(wù)場(chǎng)景：一次性地用delete語(yǔ)句刪除太多數(shù)據(jù)和大表的DDL

四、主備切換策略

1、可靠性優(yōu)先策略

雙M結(jié)構(gòu)下，從狀態(tài)1到狀態(tài)2切換的詳細(xì)過(guò)程如下：

1.判斷備庫(kù)B現(xiàn)在的seconds_behind_master，如果小于某個(gè)值繼續(xù)下一步，否則持續(xù)重試這一步

2.把主庫(kù)A改成只讀狀態(tài)，即把readonly設(shè)置為true

3.判斷備庫(kù)B的seconds_behind_master的值，直到這個(gè)值變成0為止

4.把備庫(kù)B改成可讀寫狀態(tài)，也就是把readonly設(shè)置為false

5.把業(yè)務(wù)請(qǐng)求切到備庫(kù)B

這個(gè)切換流程中是有不可用的時(shí)間的。在步驟2之后，主庫(kù)A和備庫(kù)B都處于readonly狀態(tài)，也就是說(shuō)這時(shí)系統(tǒng)處于不可寫狀態(tài)，直到步驟5完成后才能恢復(fù)。在這個(gè)不可用狀態(tài)中，比較耗時(shí)的是步驟3，可能需要耗費(fèi)好幾秒的時(shí)間。也是為什么需要在步驟1先做判斷，確保seconds_behind_master的值足夠小

系統(tǒng)的不可用時(shí)間是由這個(gè)數(shù)據(jù)可靠性優(yōu)先的策略決定的

2、可用性優(yōu)先策略

可用性優(yōu)先策略：如果強(qiáng)行把可靠性優(yōu)先策略的步驟4、5調(diào)整到最開(kāi)始執(zhí)行，也就是說(shuō)不等主備數(shù)據(jù)同步，直接把連接切到備庫(kù)B，并且讓備庫(kù)B可以讀寫，那么系統(tǒng)幾乎沒(méi)有不可用時(shí)間。這個(gè)切換流程的代價(jià)，就是可能出現(xiàn)數(shù)據(jù)不一致的情況

mysql>?CREATE?TABLE?`t`?( ??`id`?int(11)?unsigned?NOT?NULL?AUTO_INCREMENT, ??`c`?int(11)?unsigned?DEFAULT?NULL, ??PRIMARY?KEY?(`id`))?ENGINE=InnoDB;insert?into?t(c)?values(1),(2),(3);

表t定義了一個(gè)自增主鍵id，初始化數(shù)據(jù)后，主庫(kù)和備庫(kù)上都是3行數(shù)據(jù)。繼續(xù)在表t上執(zhí)行兩條插入語(yǔ)句的命令，依次是：

insert?into?t(c)?values(4);insert?into?t(c)?values(5);

假設(shè)，現(xiàn)在主庫(kù)上其他的數(shù)據(jù)表有大量的更新，導(dǎo)致主備延遲達(dá)到5秒。在插入一條c=4的語(yǔ)句后，發(fā)起了主備切換

下圖是可用性優(yōu)先策略，且binlog_format=mixed時(shí)的切換流程和數(shù)據(jù)結(jié)果

1.步驟2中，主庫(kù)A執(zhí)行完insert語(yǔ)句，插入了一行數(shù)據(jù)(4,4)，之后開(kāi)始進(jìn)行主備切換

2.步驟3中，由于主備之間有5秒的延遲，所以備庫(kù)B還沒(méi)來(lái)得及應(yīng)用插入c=4這個(gè)中轉(zhuǎn)日志，就開(kāi)始接收客戶端插入c=5的命令

3.步驟4中，備庫(kù)B插入了一行數(shù)據(jù)(4,5)，并且把這個(gè)binlog發(fā)給主庫(kù)A

4.步驟5中，備庫(kù)B執(zhí)行插入c=4這個(gè)中轉(zhuǎn)日志，插入了一行數(shù)據(jù)(5,4)。而直接在備庫(kù)B執(zhí)行的插入c=5這個(gè)語(yǔ)句，傳到主庫(kù)A，就插入了一行新數(shù)據(jù)(5,5)

最后的結(jié)果就是，主庫(kù)A和備庫(kù)B上出現(xiàn)了兩行不一致的數(shù)據(jù)

可用性優(yōu)先策略，設(shè)置binlog_format=row

因此row格式在記錄binlog的時(shí)候，會(huì)記錄新插入的行的所有字段值，所以最后只會(huì)有一行不一致。而且，兩邊的主備同步的應(yīng)用線程會(huì)報(bào)錯(cuò)duplicate key error并停止。也就是說(shuō)，這種情況下，備庫(kù)B的(5,4)和主庫(kù)A的(5,5)這兩行數(shù)據(jù)都不會(huì)被對(duì)方執(zhí)行

3、小結(jié)

1.使用row格式的binlog時(shí)，數(shù)據(jù)不一致問(wèn)題更容易被發(fā)現(xiàn)。而使用mixed或者statement格式的binlog時(shí)，可能過(guò)了很久才發(fā)現(xiàn)數(shù)據(jù)不一致的問(wèn)題

2.主備切換的可用性優(yōu)先策略會(huì)導(dǎo)致數(shù)據(jù)不一致。因此，大多數(shù)情況下，建議采用可靠性優(yōu)先策略

五、MySQL的并行復(fù)制策略

主備的并行復(fù)制能力，要關(guān)注的就是上圖中黑色的兩個(gè)箭頭。一個(gè)代表客戶端寫入主庫(kù)，另一個(gè)代表備庫(kù)上sql_thread執(zhí)行中轉(zhuǎn)日志

在MySQL5.6版本之前，MySQL只支持單線程復(fù)制，由此在主庫(kù)并發(fā)高、TPS高時(shí)就會(huì)出現(xiàn)嚴(yán)重的主備延遲問(wèn)題

多線程復(fù)制機(jī)制都是把只有一個(gè)線程的sql_thread拆成多個(gè)線程，都符合下面這個(gè)模型：

coordinator就是原來(lái)的sql_thread，不過(guò)現(xiàn)在它不再直接更新數(shù)據(jù)了，只負(fù)責(zé)讀取中轉(zhuǎn)日志和分發(fā)事務(wù)。真正更新日志的，變成了worker線程。而worker線程的個(gè)數(shù)就是由參數(shù)slave_parallel_workers決定的

coordinator在分發(fā)的時(shí)候，需要滿足以下兩個(gè)基本要求：

• 不能造成更新覆蓋。這就要求更新同一行的兩個(gè)事務(wù)，必須被分發(fā)到同一個(gè)worker中
• 同一個(gè)事務(wù)不能被拆開(kāi)，必須放到同一個(gè)worker中

1、MySQL5.6版本的并行復(fù)制策略

MySQL5.6版本支持了并行復(fù)制，只是支持的粒度是按庫(kù)并行。用于決定分發(fā)策略的hash表里，key是數(shù)據(jù)庫(kù)名

這個(gè)策略的并行效果取決于壓力模型。如果在主庫(kù)上有多個(gè)DB，并且各個(gè)DB的壓力均衡，使用這個(gè)策略的效果會(huì)很好

這個(gè)策略的兩個(gè)優(yōu)勢(shì)：

• 構(gòu)造hash值的時(shí)候很快，只需要庫(kù)名
• 不要求binlog的格式，因?yàn)閟tatement格式的binlog也可以很容易拿到庫(kù)名

可以創(chuàng)建不同的DB，把相同熱度的表均勻分到這些不同的DB中，強(qiáng)行使用這個(gè)策略

2、MariaDB的并行復(fù)制策略

redo log組提交優(yōu)化，而MariaDB的并行復(fù)制策略利用的就是這個(gè)特性：

• 能夠在同一個(gè)組里提交的事務(wù)，一定不會(huì)修改同一行
• 主庫(kù)上可以并行執(zhí)行的事務(wù)，備庫(kù)上也一定是可以并行執(zhí)行的

在實(shí)現(xiàn)上，MariaDB是這么做的：

1.在一組里面一起提交的事務(wù)，有一個(gè)相同的commit_id，下一組就是commit_id+1

2.commit_id直接寫到binlog里面

3.傳到備庫(kù)應(yīng)用的時(shí)候，相同commit_id的事務(wù)分發(fā)到多個(gè)worker執(zhí)行

4.這一組全部執(zhí)行完成后，coordinator再去取下一批

下圖中假設(shè)三組事務(wù)在主庫(kù)的執(zhí)行情況，trx1、trx2和trx3提交的時(shí)候，trx4、trx5和trx6是在執(zhí)行的。這樣，在第一組事務(wù)提交完成的時(shí)候，下一組事務(wù)很快就會(huì)進(jìn)入commit狀態(tài)

按照MariaDB的并行復(fù)制策略，備庫(kù)上的執(zhí)行效果如下圖：

在備庫(kù)上執(zhí)行的時(shí)候，要等第一組事務(wù)完全執(zhí)行完成后，第二組事務(wù)才能開(kāi)始執(zhí)行，這樣系統(tǒng)的吞吐量就不夠

另外，這個(gè)方案容易被大事務(wù)拖后腿。假設(shè)trx2是一個(gè)超大事務(wù)，那么在備庫(kù)應(yīng)用的時(shí)候，trx1和trx3執(zhí)行完成后，下一組才能開(kāi)始執(zhí)行。只有一個(gè)worker線程在工作，是對(duì)資源的浪費(fèi)

3、MySQL5.7版本的并行復(fù)制策略

MySQL5.7版本由參數(shù)slave-parallel-type來(lái)控制并行復(fù)制策略：

• 配置為DATABASE，表示使用MySQL5.6版本的按庫(kù)并行策略
• 配置為L(zhǎng)OGICAL_CLOCK，表示的就是類似MariaDB的策略。MySQL在此基礎(chǔ)上做了優(yōu)化

同時(shí)處于執(zhí)行狀態(tài)的所有事務(wù)，是不是可以并行？

不可以，因?yàn)檫@里面可能有由于鎖沖突而處于鎖等待狀態(tài)的事務(wù)。如果這些事務(wù)在備庫(kù)上被分配到不同的worker，就會(huì)出現(xiàn)備庫(kù)跟主庫(kù)不一致的情況

而MariaDB這個(gè)策略的核心是所有處于commit狀態(tài)的事務(wù)可以并行。事務(wù)處于commit狀態(tài)表示已經(jīng)通過(guò)了鎖沖突的檢驗(yàn)了

其實(shí)只要能夠達(dá)到redo log prepare階段就表示事務(wù)已經(jīng)通過(guò)鎖沖突的檢驗(yàn)了

因此，MySQL5.7并行復(fù)制策略的思想是：

1.同時(shí)處于prepare狀態(tài)的事務(wù)，在備庫(kù)執(zhí)行時(shí)是可以并行的

2.處于prepare狀態(tài)的事務(wù)，與處于commit狀態(tài)的事務(wù)之間，在備庫(kù)執(zhí)行時(shí)也是可以并行的

binlog組提交的時(shí)候有兩個(gè)參數(shù)：

• binlog_group_commit_sync_delay參數(shù)表示延遲多少微妙后才調(diào)用fsync
• binlog_group_commit_sync_no_delay_count參數(shù)表示基類多少次以后才調(diào)用fsync

這兩個(gè)參數(shù)是用于故意拉長(zhǎng)binlog從write到fsync的時(shí)間，以此減少binlog的寫盤次數(shù)。在MySQL5.7的并行復(fù)制策略里，它們可以用來(lái)制造更多的同時(shí)處于prepare階段的事務(wù)。這樣就增加了備庫(kù)復(fù)制的并行度。也就是說(shuō)，這兩個(gè)參數(shù)既可以故意讓主庫(kù)提交得慢些，又可以讓備庫(kù)執(zhí)行得快些

4、MySQL5.7.22的并行復(fù)制策略

MySQL5.7.22增加了一個(gè)新的并行復(fù)制策略，基于WRITESET的并行復(fù)制，新增了一個(gè)參數(shù)binlog-transaction-dependency-tracking用來(lái)控制是否啟用這個(gè)新策略。這個(gè)參數(shù)的可選值有以下三種：

• COMMIT_ORDER，根據(jù)同時(shí)進(jìn)入prepare和commit來(lái)判斷是否可以并行的策略
• WRITESET，表示的是對(duì)于事務(wù)涉及更新的每一行，計(jì)算出這一行的hash值，組成集合writeset。如果兩個(gè)事務(wù)沒(méi)有操作相同的行，也就是說(shuō)它們的writeset沒(méi)有交集，就可以并行
• WRITESET_SESSION，是在WRITESET的基礎(chǔ)上多了一個(gè)約束，即在主庫(kù)上同一個(gè)線程先后執(zhí)行的兩個(gè)事務(wù)，在備庫(kù)執(zhí)行的時(shí)候，要保證相同的先后順序

為了唯一標(biāo)識(shí)，hash值是通過(guò)庫(kù)名+表名+索引名+值計(jì)算出來(lái)的。如果一個(gè)表上除了有主鍵索引外，還有其他唯一索引，那么對(duì)于每個(gè)唯一索引，insert語(yǔ)句對(duì)應(yīng)的writeset就要多增加一個(gè)hash值

1.writeset是在主庫(kù)生成后直接寫入到binlog里面的，這樣在備庫(kù)執(zhí)行的時(shí)候不需要解析binlog內(nèi)容

2.不需要把整個(gè)事務(wù)的binlog都掃一遍才能決定分發(fā)到哪個(gè)worker，更省內(nèi)存

3.由于備庫(kù)的分發(fā)策略不依賴于binlog內(nèi)容，索引binlog是statement格式也是可以的

對(duì)于表上沒(méi)主鍵和外鍵約束的場(chǎng)景，WRITESET策略也是沒(méi)法并行的，會(huì)暫時(shí)退化為單線程模型

六、主庫(kù)出問(wèn)題了，從庫(kù)怎么辦？

下圖是一個(gè)基本的一主多從結(jié)構(gòu)

圖中，虛線箭頭表示的是主備關(guān)系，也就是A和A’互為主備，從庫(kù)B、C、D指向的是主庫(kù)A。一主多從的設(shè)置，一般用于讀寫分離，主庫(kù)負(fù)責(zé)所有的寫入和一部分讀，其他的讀請(qǐng)求則由從庫(kù)分擔(dān)

一主多從結(jié)構(gòu)在切換完成后，A’會(huì)成為新的主庫(kù)，從庫(kù)B、C、D也要改接到A’

1、基于位點(diǎn)的主備切換

當(dāng)我們把節(jié)點(diǎn)B設(shè)置成節(jié)點(diǎn)A’的從庫(kù)的時(shí)候，需要執(zhí)行一條change master命令：

CHANGE?MASTER?TO? MASTER_HOST=$host_name? MASTER_PORT=$port? MASTER_USER=$user_name? MASTER_PASSWORD=$password? MASTER_LOG_FILE=$master_log_name? MASTER_LOG_POS=$master_log_pos

• MASTER_HOST、MASTER_PORT、MASTER_USER和MASTER_PASSWORD四個(gè)參數(shù)，分別代表了主庫(kù)A’的IP、端口、用戶名和密碼
• 最后兩個(gè)參數(shù)MASTER_LOG_FILE和MASTER_LOG_POS表示，要從主庫(kù)的master_log_name文件的master_log_pos這個(gè)位置的日志繼續(xù)同步。而這個(gè)位置就是所說(shuō)的同步位點(diǎn)，也就是主庫(kù)對(duì)應(yīng)的文件名和日志偏移量

找同步位點(diǎn)很難精確取到，只能取一個(gè)大概位置。一種去同步位點(diǎn)的方法是這樣的：

1.等待新主庫(kù)A’把中轉(zhuǎn)日志全部同步完成

2.在A’上執(zhí)行show master status命令，得到當(dāng)前A’上最新的File和Position

3.取原主庫(kù)A故障的時(shí)刻T

4.用mysqlbinlog工具解析A’的File，得到T時(shí)刻的位點(diǎn)，這個(gè)值就可以作為$master_log_pos

這個(gè)值并不精確，有這么一種情況，假設(shè)在T這個(gè)時(shí)刻，主庫(kù)A已經(jīng)執(zhí)行完成了一個(gè)insert語(yǔ)句插入了一行數(shù)據(jù)R，并且已經(jīng)將binlog傳給了A’和B，然后在傳完的瞬間主庫(kù)A的主機(jī)就掉電了。那么，這時(shí)候系統(tǒng)的狀態(tài)是這樣的：

1.在從庫(kù)B上，由于同步了binlog，R這一行已經(jīng)存在

2.在新主庫(kù)A’上，R這一行也已經(jīng)存在，日志是寫在master_log_pos這個(gè)位置之后的

3.在從庫(kù)B上執(zhí)行change master命令，指向A’的File文件的master_log_pos位置，就會(huì)把插入R這一行數(shù)據(jù)的binlog又同步到從庫(kù)B去執(zhí)行，造成主鍵沖突，然后停止tongue

通常情況下，切換任務(wù)的時(shí)候，要先主動(dòng)跳過(guò)這些錯(cuò)誤，有兩種常用的方法

一種是，主動(dòng)跳過(guò)一個(gè)事務(wù)

set?global?sql_slave_skip_counter=1;start?slave;

另一種方式是，通過(guò)設(shè)置slave_skip_errors參數(shù)，直接設(shè)置跳過(guò)指定的錯(cuò)誤。這個(gè)背景是，我們很清楚在主備切換過(guò)程中，直接跳過(guò)這些錯(cuò)誤是無(wú)損的，所以才可以設(shè)置slave_skip_errors參數(shù)。等到主備間的同步關(guān)系建立完成，并穩(wěn)定執(zhí)行一段時(shí)間之后，還需要把這個(gè)參數(shù)設(shè)置為空，以免之后真的出現(xiàn)了主從數(shù)據(jù)不一致，也跳過(guò)了

2、GTID

MySQL5.6引入了GTID，是一個(gè)全局事務(wù)ID，是一個(gè)事務(wù)提交的時(shí)候生成的，是這個(gè)事務(wù)的唯一標(biāo)識(shí)。它的格式是：

GTID=source_id:transaction_id

• source_id是一個(gè)實(shí)例第一次啟動(dòng)時(shí)自動(dòng)生成的，是一個(gè)全局唯一的值
• transaction_id是一個(gè)整數(shù)，初始值是1，每次提交事務(wù)的時(shí)候分配給這個(gè)事務(wù)，并加1

GTID模式的啟動(dòng)只需要在啟動(dòng)一個(gè)MySQL實(shí)例的時(shí)候，加上參數(shù)gtid_mode=on和enforce_gtid_consistency=on就可以了

在GTID模式下，每個(gè)事務(wù)都會(huì)跟一個(gè)GTID一一對(duì)應(yīng)。這個(gè)GTID有兩種生成方式，而使用哪種方式取決于session變量gtid_next的值

1.如果gtid_next=automatic，代表使用默認(rèn)值。這時(shí)，MySQL就把GTID分配給這個(gè)事務(wù)。記錄binlog的時(shí)候，先記錄一行SET@@SESSION.GTID_NEXT=‘GTID’。把這個(gè)GTID加入本實(shí)例的GTID集合

2.如果gtid_next是一個(gè)指定的GTID的值，比如通過(guò)set gtid_next=‘current_gtid’，那么就有兩種可能：

• 如果current_gtid已經(jīng)存在于實(shí)例的GTID集合中，接下里執(zhí)行的這個(gè)事務(wù)會(huì)直接被系統(tǒng)忽略
• 如果current_gtid沒(méi)有存在于實(shí)例的GTID集合中，就將這個(gè)current_gtid分配給接下來(lái)要執(zhí)行的事務(wù)，也就是說(shuō)系統(tǒng)不需要給這個(gè)事務(wù)生成新的GTID，因此transaction_id也不需要加1

一個(gè)current_gtid只能給一個(gè)事務(wù)使用。這個(gè)事務(wù)提交后，如果要執(zhí)行下一個(gè)事務(wù)，就要執(zhí)行set命令，把gtid_next設(shè)置成另外一個(gè)gtid或者automatic

這樣每個(gè)MySQL實(shí)例都維護(hù)了一個(gè)GTID集合，用來(lái)對(duì)應(yīng)這個(gè)實(shí)例執(zhí)行過(guò)的所有事務(wù)

3、基于GTID的主備切換

在GTID模式下，備庫(kù)B要設(shè)置為新主庫(kù)A’的從庫(kù)的語(yǔ)法如下：

CHANGE?MASTER?TO?MASTER_HOST=$host_name? MASTER_PORT=$port? MASTER_USER=$user_name? MASTER_PASSWORD=$password? master_auto_position=1

其中master_auto_position=1就表示這個(gè)主備關(guān)系使用的是GTID協(xié)議

實(shí)例A’的GTID集合記為set_a，實(shí)例B的GTID集合記為set_b。我們?cè)趯?shí)例B上執(zhí)行start slave命令，取binlog的邏輯是這樣的：

1.實(shí)例B指定主庫(kù)A’，基于主備協(xié)議建立連接

2.實(shí)例B把set_b發(fā)給主庫(kù)A’

3.實(shí)例A’算出set_a與set_b的差集，也就是所有存在于set_a，但是不存在于set_b的GTID的集合，判斷A’本地是否包含了這個(gè)差集需要的所有binlog事務(wù)

• 如果不包含，表示A’已經(jīng)把實(shí)例B需要的binlog給刪掉了，直接返回錯(cuò)誤
• 如果確認(rèn)全部包含，A’從自己的binlog文件里面，找出第一個(gè)不在set_b的事務(wù)，發(fā)給B

4.之后從這個(gè)事務(wù)開(kāi)始，往后讀文件，按順序取binlog發(fā)給B去執(zhí)行

4、GTID和在線DDL

如果是由于索引缺失引起的性能問(wèn)題，可以在線加索引來(lái)解決。但是，考慮到要避免新增索引對(duì)主庫(kù)性能造成的影響，可以先在備庫(kù)加索引，然后再切換，在雙M結(jié)構(gòu)下，備庫(kù)執(zhí)行的DDL語(yǔ)句也會(huì)傳給主庫(kù)，為了避免傳回后對(duì)主庫(kù)造成影響，要通過(guò)set sql_log_bin=off關(guān)掉binlog，但是操作可能會(huì)導(dǎo)致數(shù)據(jù)和日志不一致

兩個(gè)互為主備關(guān)系的庫(kù)實(shí)例X和實(shí)例Y，且當(dāng)前主庫(kù)是X，并且都打開(kāi)了GTID模式。這時(shí)的主備切換流程可以變成下面這樣：

• 在實(shí)例X上執(zhí)行stop slave
• 在實(shí)例Y上執(zhí)行DDL語(yǔ)句。這里不需要關(guān)閉binlog
• 執(zhí)行完成后，查出這個(gè)DDL語(yǔ)句對(duì)應(yīng)的GTID，記為source_id_of_Y:transaction_id
• 到實(shí)例X上執(zhí)行一下語(yǔ)句序列：

set?GTID_NEXT="source_id_of_Y:transaction_id";begin;commit;set?gtid_next=automatic;start?slave;

這樣做的目的在于，既可以讓實(shí)例Y的更新有binlog記錄，同時(shí)也可以確保不會(huì)在實(shí)例X上執(zhí)行這條更新

七、MySQL讀寫分離

讀寫分離的基本結(jié)構(gòu)如下圖：

讀寫分離的主要目的就是分?jǐn)傊鲙?kù)的壓力。上圖中的結(jié)構(gòu)是客戶端主動(dòng)做負(fù)載均衡，這種模式下一般會(huì)把數(shù)據(jù)庫(kù)的連接信息放在客戶端的連接層。由客戶端來(lái)選擇后端數(shù)據(jù)庫(kù)進(jìn)行查詢

還有一種架構(gòu)就是在MySQL和客戶端之間有一個(gè)中間代理層proxy，客戶端只連接proxy，由proxy根據(jù)請(qǐng)求類型和上下文決定請(qǐng)求的分發(fā)路由

1.客戶端直連方案，因此少了一層proxy轉(zhuǎn)發(fā)，所以查詢性能稍微好一點(diǎn)，并且整體架構(gòu)簡(jiǎn)單，排查問(wèn)題更方便。但是這種方案，由于要了解后端部署細(xì)節(jié)，所以在出現(xiàn)主備切換、庫(kù)遷移等操作的時(shí)候，客戶端都會(huì)感知到，并且需要調(diào)整數(shù)據(jù)庫(kù)連接信息。一般采用這樣的架構(gòu)，一定會(huì)伴隨一個(gè)負(fù)責(zé)管理后端的組件，比如Zookeeper，盡量讓業(yè)務(wù)端只專注于業(yè)務(wù)邏輯開(kāi)發(fā)

2.帶proxy的架構(gòu)，對(duì)客戶端比較友好。客戶端不需要關(guān)注后端細(xì)節(jié)，連接維護(hù)、后端信息維護(hù)等工作，都是由proxy完成的。但這樣的話，對(duì)后端維護(hù)團(tuán)隊(duì)的要求會(huì)更高，而且proxy也需要有高可用架構(gòu)

在從庫(kù)上會(huì)讀到系統(tǒng)的一個(gè)過(guò)期狀態(tài)的現(xiàn)象稱為過(guò)期讀

1、強(qiáng)制走主庫(kù)方案

強(qiáng)制走主庫(kù)方案其實(shí)就是將查詢請(qǐng)求做分類。通常情況下，可以分為這么兩類：

1.對(duì)于必須要拿到最新結(jié)果的請(qǐng)求，強(qiáng)制將其發(fā)到主庫(kù)上

2.對(duì)于可以讀到舊數(shù)據(jù)的請(qǐng)求，才將其發(fā)到從庫(kù)上

這個(gè)方案最大的問(wèn)題在于，有時(shí)候可能會(huì)遇到所有查詢都不能是過(guò)期讀的需求，比如一些金融類的業(yè)務(wù)。這樣的話，就需要放棄讀寫分離，所有讀寫壓力都在主庫(kù)，等同于放棄了擴(kuò)展性

2、Sleep方案

主庫(kù)更新后，讀從庫(kù)之前先sleep一下。具體的方案就是，類似于執(zhí)行一條select sleep(1)命令。這個(gè)方案的假設(shè)是，大多數(shù)情況下主備延遲在1秒之內(nèi)，做一個(gè)sleep可以很大概率拿到最新的數(shù)據(jù)

以買家發(fā)布商品為例，商品發(fā)布后，用Ajax直接把客戶端輸入的內(nèi)容作為最新商品顯示在頁(yè)面上，而不是真正地去數(shù)據(jù)庫(kù)做查詢。這樣，賣家就可以通過(guò)這個(gè)顯示，來(lái)確認(rèn)產(chǎn)品已經(jīng)發(fā)布成功了。等到賣家再刷新頁(yè)面，去查看商品的時(shí)候，其實(shí)已經(jīng)過(guò)了一段時(shí)間，也就達(dá)到了sleep的目的，進(jìn)而也就解決了過(guò)期讀的問(wèn)題

但這個(gè)方案并不精確：

1.如果這個(gè)查詢請(qǐng)求本來(lái)0.5秒就可以在從庫(kù)上拿到正確結(jié)果，也會(huì)等1秒

2.如果延遲超過(guò)1秒，還是會(huì)出現(xiàn)過(guò)期讀

3、判斷主備無(wú)延遲方案

show slave status結(jié)果里的seconds_behind_master參數(shù)的值，可以用來(lái)衡量主備延遲時(shí)間的長(zhǎng)短

1.第一種確保主備無(wú)延遲的方法是，每次從庫(kù)執(zhí)行查詢請(qǐng)求前，先判斷seconds_behind_master是否已經(jīng)等于0。如果還不等于0，那就必須等到這個(gè)參數(shù)變?yōu)?才能執(zhí)行查詢請(qǐng)求

show slave status結(jié)果的部分截圖如下：

2.第二種方法，對(duì)比位點(diǎn)確保主備無(wú)延遲：

• Master_Log_File和Read_Master_Log_Pos表示的是讀到的主庫(kù)的最新位點(diǎn)
• Relay_Master_Log_File和Exec_Master_Log_Pos表示的是備庫(kù)執(zhí)行的最新位點(diǎn)

如果Master_Log_File和Read_Master_Log_Pos和Relay_Master_Log_File和Exec_Master_Log_Pos這兩組值完全相同，就表示接收到的日志已經(jīng)同步完成

3.第三種方法，對(duì)比GTID集合確保主備無(wú)延遲：

• Auto_Position=1表示這堆主備關(guān)系使用了GTID協(xié)議
• Retrieved_Gitid_Set是備庫(kù)收到的所有日志的GTID集合
• Executed_Gitid_Set是備庫(kù)所有已經(jīng)執(zhí)行完成的GTID集合

如果這兩個(gè)集合相同，也表示備庫(kù)接收到的日志都已經(jīng)同步完成

4.一個(gè)事務(wù)的binlog在主備庫(kù)之間的狀態(tài)：

1）主庫(kù)執(zhí)行完成，寫入binlog，并反饋給客戶端

2）binlog被從主庫(kù)發(fā)送給備庫(kù)，備庫(kù)收到

3）在備庫(kù)執(zhí)行binlog完成

上面判斷主備無(wú)延遲的邏輯是備庫(kù)收到的日志都執(zhí)行完成了。但是，從binlog在主備之間狀態(tài)的分析中，有一部分日志，處于客戶端已經(jīng)收到提交確認(rèn)，而備庫(kù)還沒(méi)收到日志的狀態(tài)

這時(shí)，主庫(kù)上執(zhí)行完成了三個(gè)事務(wù)trx1、trx2和trx3，其中：

• trx1和trx2已經(jīng)傳到從庫(kù)，并且已經(jīng)執(zhí)行完成了
• trx3在主庫(kù)執(zhí)行完成，并且已經(jīng)回復(fù)給客戶端，但是還沒(méi)有傳到從庫(kù)中

如果這時(shí)候在從庫(kù)B上執(zhí)行查詢請(qǐng)求，按照上面的邏輯，從庫(kù)認(rèn)為已經(jīng)沒(méi)有同步延遲，但還是查不到trx3的

4、配合semi-sync

要解決上面的問(wèn)題，就要引入半同步復(fù)制。semi-sync做了這樣的設(shè)計(jì)：

1.事務(wù)提交的時(shí)候，主庫(kù)把binlog發(fā)送給從庫(kù)

2.從庫(kù)收到binlog以后，發(fā)回給主庫(kù)一個(gè)ack，表示收到了

3.主庫(kù)收到這個(gè)ack以后，才能給客戶端返回事務(wù)完成的確認(rèn)

如果啟用了semi-sync，就表示所有給客戶端發(fā)送過(guò)確認(rèn)的事務(wù)，都確保了備庫(kù)已經(jīng)收到了這個(gè)日志

semi-sync+位點(diǎn)判斷的方案，只對(duì)一主一備的場(chǎng)景是成立的。在一主多從場(chǎng)景中，主庫(kù)只要等到一個(gè)從庫(kù)的ack，就開(kāi)始給客戶端返回確認(rèn)。這時(shí)，在從庫(kù)上執(zhí)行查詢請(qǐng)求，就有兩種情況：

1.如果查詢是落在這個(gè)響應(yīng)了ack的從庫(kù)上，是能夠確保讀到最新數(shù)據(jù)

2.但如果查詢落到其他從庫(kù)上，它們可能還沒(méi)有收到最新的日志，就會(huì)產(chǎn)生過(guò)期讀的問(wèn)題

判斷同步位點(diǎn)的方案還有另外一個(gè)潛在的問(wèn)題，即：如果在業(yè)務(wù)更新的高峰期，主庫(kù)的位點(diǎn)或者GTID集合更新很快，那么上面的兩個(gè)位點(diǎn)等值判斷就會(huì)一直不成立，很有可能出現(xiàn)從庫(kù)上遲遲無(wú)法響應(yīng)查詢請(qǐng)求的情況

上圖從狀態(tài)1到狀態(tài)4，一直處于延遲一個(gè)事務(wù)的狀態(tài)。但是，其實(shí)客戶端是在發(fā)完trx1更新后發(fā)起的select語(yǔ)句，我們只需要確保trx1已經(jīng)執(zhí)行完成就可以執(zhí)行select語(yǔ)句了。也就是說(shuō)，如果在狀態(tài)3執(zhí)行查詢請(qǐng)求，得到的就是預(yù)期結(jié)果了

semi-sync配合主備無(wú)延遲的方案，存在兩個(gè)問(wèn)題：

1.一主多從的時(shí)候，在某些從庫(kù)執(zhí)行查詢請(qǐng)求會(huì)存在過(guò)期讀的現(xiàn)象

2.在持續(xù)延遲的情況下，可能出現(xiàn)過(guò)度等待的問(wèn)題

5、等主庫(kù)位點(diǎn)方案

select?master_pos_wait(file,?pos[,?timeout]);

這條命令的邏輯如下：

1.它是在從庫(kù)執(zhí)行的

2.參數(shù)file和pos指的是主庫(kù)上的文件名和位置

3.timeout可選，設(shè)置為正整數(shù)N表示這個(gè)函數(shù)最多等待N秒

這個(gè)命令正常返回的結(jié)果是一個(gè)正整數(shù)M，表示從命令開(kāi)始執(zhí)行，到應(yīng)用完file和pos表示的binlog位置，執(zhí)行了多少事務(wù)

1.如果執(zhí)行期間，備庫(kù)同步線程發(fā)生異常，則返回NULL

2.如果等待超過(guò)N秒，就返回-1

3.如果剛開(kāi)始執(zhí)行的時(shí)候，就發(fā)現(xiàn)已經(jīng)執(zhí)行過(guò)這個(gè)位置了，則返回0

對(duì)于上圖中先執(zhí)行trx1，再執(zhí)行一個(gè)查詢請(qǐng)求的邏輯，要保證能夠查到正確的數(shù)據(jù)，可以使用這個(gè)邏輯：

1.trx1事務(wù)更新完成后，馬上執(zhí)行show master status得到當(dāng)前主庫(kù)執(zhí)行到的File和Position

2.選定一個(gè)從庫(kù)執(zhí)行查詢語(yǔ)句

3.在從庫(kù)上執(zhí)行select master_pos_wait(file, pos, 1)

4.如果返回值是>=0的正整數(shù)，則在這個(gè)從庫(kù)執(zhí)行查詢語(yǔ)句

5.否則，到主庫(kù)執(zhí)行查詢語(yǔ)句

流程如下：

6、GTID方案

 select wait_for_executed_gtid_set(gtid_set, 1);

這條命令的邏輯如下：

1.等待，直到這個(gè)庫(kù)執(zhí)行的事務(wù)中包含傳入的gtid_set，返回0

2.超時(shí)返回1

等主庫(kù)位點(diǎn)方案中，執(zhí)行完事務(wù)后，還要主動(dòng)去主庫(kù)執(zhí)行show master status。而MySQL5.7.6版本開(kāi)始，允許在執(zhí)行完更新類事務(wù)后，把這個(gè)事務(wù)的GTID返回給客戶端，這樣等GTID的方案可以減少一次查詢

等GTID的流程如下：

1.trx1事務(wù)更新完成后，從返回包直接獲取這個(gè)事務(wù)的GTID，記為gtid1

2.選定一個(gè)從庫(kù)執(zhí)行查詢語(yǔ)句

3.在從庫(kù)上執(zhí)行 select wait_for_executed_gtid_set(gtid1, 1);

4.如果返回值是0，則在這個(gè)從庫(kù)執(zhí)行查詢語(yǔ)句

5.否則，到主庫(kù)執(zhí)行查詢語(yǔ)句

更多編程相關(guān)知識(shí)，請(qǐng)?jiān)L問(wèn)：mysql視頻教程！！