MySQL中的主備、主從和讀寫分離的原理

本篇內(nèi)容介紹了“MySQL中的主備、主從和讀寫分離的原理”的有關(guān)知識(shí)，在實(shí)際案例的操作過程中，不少人都會(huì)遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

一、MySQL主備的基本原理

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

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

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

2.防止切換邏輯有bug

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3.每個(gè)庫在收到從自己的主庫發(fā)過來的日志后，先判斷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.主庫A執(zhí)行完成一個(gè)事務(wù)，寫入binlog，這個(gè)時(shí)刻記為T1

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

3.備庫B執(zhí)行完這個(gè)事務(wù)，把這個(gè)時(shí)刻記為T3

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

可以在備庫上執(zhí)行show slave status命令，它的返回結(jié)果里面會(huì)顯示seconds_behind_master，用于表示當(dāng)前備庫延遲了多少秒

seconds_behind_master的計(jì)算方法是這樣的：

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

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

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

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

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

2、主備延遲的原來

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

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

可以做以下處理：

• 一主多從。除了備庫外，可以多接幾個(gè)從庫，讓這些從庫來分擔(dān)讀的壓力

• 通過binlog輸出到外部系統(tǒng)，比如Hadoop這類系統(tǒng)，讓外部系統(tǒng)提供統(tǒng)計(jì)類查詢的能力

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

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

四、主備切換策略

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

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

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

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

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

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

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

這個(gè)切換流程中是有不可用的時(shí)間的。在步驟2之后，主庫A和備庫B都處于readonly狀態(tài)，也就是說這時(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)整到最開始執(zhí)行，也就是說不等主備數(shù)據(jù)同步，直接把連接切到備庫B，并且讓備庫B可以讀寫，那么系統(tǒng)幾乎沒有不可用時(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ù)后，主庫和備庫上都是3行數(shù)據(jù)。繼續(xù)在表t上執(zhí)行兩條插入語句的命令，依次是：

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

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

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

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

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

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

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

最后的結(jié)果就是，主庫A和備庫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并停止。也就是說，這種情況下，備庫B的(5,4)和主庫A的(5,5)這兩行數(shù)據(jù)都不會(huì)被對(duì)方執(zhí)行

3、小結(jié)

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

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

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

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

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

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

coordinator就是原來的sql_thread，不過現(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ù)不能被拆開，必須放到同一個(gè)worker中

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

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

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

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

• 構(gòu)造hash值的時(shí)候很快，只需要庫名

• 不要求binlog的格式，因?yàn)閟tatement格式的binlog也可以很容易拿到庫名

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

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

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

• 能夠在同一個(gè)組里提交的事務(wù)，一定不會(huì)修改同一行

• 主庫上可以并行執(zhí)行的事務(wù)，備庫上也一定是可以并行執(zhí)行的

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

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

2.commit_id直接寫到binlog里面

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

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

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

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

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

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

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

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

• 配置為DATABASE，表示使用MySQL5.6版本的按庫并行策略

• 配置為LOGICAL_CLOCK，表示的就是類似MariaDB的策略。MySQL在此基礎(chǔ)上做了優(yōu)化

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

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

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

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

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

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

2.處于prepare狀態(tài)的事務(wù)，與處于commit狀態(tài)的事務(wù)之間，在備庫執(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ù)是用于故意拉長binlog從write到fsync的時(shí)間，以此減少binlog的寫盤次數(shù)。在MySQL5.7的并行復(fù)制策略里，它們可以用來制造更多的同時(shí)處于prepare階段的事務(wù)。這樣就增加了備庫復(fù)制的并行度。也就是說，這兩個(gè)參數(shù)既可以故意讓主庫提交得慢些，又可以讓備庫執(zhí)行得快些

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

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

• COMMIT_ORDER，根據(jù)同時(shí)進(jìn)入prepare和commit來判斷是否可以并行的策略

• WRITESET，表示的是對(duì)于事務(wù)涉及更新的每一行，計(jì)算出這一行的hash值，組成集合writeset。如果兩個(gè)事務(wù)沒有操作相同的行，也就是說它們的writeset沒有交集，就可以并行

• WRITESET_SESSION，是在WRITESET的基礎(chǔ)上多了一個(gè)約束，即在主庫上同一個(gè)線程先后執(zhí)行的兩個(gè)事務(wù)，在備庫執(zhí)行的時(shí)候，要保證相同的先后順序

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

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

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

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

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

六、主庫出問題了，從庫怎么辦？

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

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

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

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

當(dāng)我們把節(jié)點(diǎn)B設(shè)置成節(jié)點(diǎn)A’的從庫的時(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ù)，分別代表了主庫A’的IP、端口、用戶名和密碼

• 最后兩個(gè)參數(shù)MASTER_LOG_FILE和MASTER_LOG_POS表示，要從主庫的master_log_name文件的master_log_pos這個(gè)位置的日志繼續(xù)同步。而這個(gè)位置就是所說的同步位點(diǎn)，也就是主庫對(duì)應(yīng)的文件名和日志偏移量

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

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

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

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

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

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

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

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

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

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

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

set global sql_slave_skip_counter=1;start slave;

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

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的值，比如通過set gtid_next=‘current_gtid’，那么就有兩種可能：

• 如果current_gtid已經(jīng)存在于實(shí)例的GTID集合中，接下里執(zhí)行的這個(gè)事務(wù)會(huì)直接被系統(tǒng)忽略

• 如果current_gtid沒有存在于實(shí)例的GTID集合中，就將這個(gè)current_gtid分配給接下來要執(zhí)行的事務(wù)，也就是說系統(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集合，用來對(duì)應(yīng)這個(gè)實(shí)例執(zhí)行過的所有事務(wù)

3、基于GTID的主備切換

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

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指定主庫A’，基于主備協(xié)議建立連接

2.實(shí)例B把set_b發(fā)給主庫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ù)開始，往后讀文件，按順序取binlog發(fā)給B去執(zhí)行

4、GTID和在線DDL

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

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

• 在實(shí)例X上執(zhí)行stop slave

• 在實(shí)例Y上執(zhí)行DDL語句。這里不需要關(guān)閉binlog

• 執(zhí)行完成后，查出這個(gè)DDL語句對(duì)應(yīng)的GTID，記為source_id_of_Y:transaction_id

• 到實(shí)例X上執(zhí)行一下語句序列：

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)傊鲙斓膲毫?。上圖中的結(jié)構(gòu)是客戶端主動(dòng)做負(fù)載均衡，這種模式下一般會(huì)把數(shù)據(jù)庫的連接信息放在客戶端的連接層。由客戶端來選擇后端數(shù)據(jù)庫進(jìn)行查詢

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

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

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

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

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

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

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

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

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

2、Sleep方案

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

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

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

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

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

3、判斷主備無延遲方案

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

1.第一種確保主備無延遲的方法是，每次從庫執(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)確保主備無延遲：

• Master_Log_File和Read_Master_Log_Pos表示的是讀到的主庫的最新位點(diǎn)

• Relay_Master_Log_File和Exec_Master_Log_Pos表示的是備庫執(zhí)行的最新位點(diǎn)

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

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

• Auto_Position=1表示這堆主備關(guān)系使用了GTID協(xié)議

• Retrieved_Gitid_Set是備庫收到的所有日志的GTID集合

• Executed_Gitid_Set是備庫所有已經(jīng)執(zhí)行完成的GTID集合

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

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

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

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

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

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

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

• trx1和trx2已經(jīng)傳到從庫，并且已經(jīng)執(zhí)行完成了

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

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

4、配合semi-sync

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

這條命令的邏輯如下：

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

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

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

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

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

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

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

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

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

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

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

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

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

流程如下：

6、GTID方案

 select wait_for_executed_gtid_set(gtid_set, 1);

這條命令的邏輯如下：

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

2.超時(shí)返回1

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

等GTID的流程如下：

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

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

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

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

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

“MySQL中的主備、主從和讀寫分離的原理”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！