MySQL中的主備、主從和讀寫(xiě)分離的原理

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

一、MySQL主備的基本原理

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

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

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

2.防止切換邏輯有bug

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

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

readonly設置對超級權限用戶(hù)是無(wú)效的，而用于同步更新的線(xiàn)程，就擁有超級權限

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

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

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

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

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

4.備庫B拿到binlog后，寫(xiě)到本地文件，稱(chēng)為中轉日志

5.sql_thread讀取中轉日志，解析出日志里的命令，并執行

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

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

雙M結構：

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

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

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

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

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

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

雙M結構日志的執行流如下：

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

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

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

三、主備延遲

1、什么是主備延遲？

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

1.主庫A執行完成一個(gè)事務(wù)，寫(xiě)入binlog，這個(gè)時(shí)刻記為T(mén)1

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

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

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

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

seconds_behind_master的計算方法是這樣的：

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

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

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

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

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

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

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

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

可以做以下處理：

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

• 通過(guò)binlog輸出到外部系統，比如Hadoop這類(lèi)系統，讓外部系統提供統計類(lèi)查詢(xún)的能力

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

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

四、主備切換策略

1、可靠性?xún)?yōu)先策略

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

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

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

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

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

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

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

系統的不可用時(shí)間是由這個(gè)數據可靠性?xún)?yōu)先的策略決定的

2、可用性?xún)?yōu)先策略

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

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，初始化數據后，主庫和備庫上都是3行數據。繼續在表t上執行兩條插入語(yǔ)句的命令，依次是：

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

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

下圖是可用性?xún)?yōu)先策略，且binlog_format=mixed時(shí)的切換流程和數據結果

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

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

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

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

最后的結果就是，主庫A和備庫B上出現了兩行不一致的數據

可用性?xún)?yōu)先策略，設置binlog_format=row

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

3、小結

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

2.主備切換的可用性?xún)?yōu)先策略會(huì )導致數據不一致。因此，大多數情況下，建議采用可靠性?xún)?yōu)先策略

五、MySQL的并行復制策略

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

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

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

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

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

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

• 同一個(gè)事務(wù)不能被拆開(kāi)，必須放到同一個(gè)worker中

1、MySQL5.6版本的并行復制策略

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

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

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

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

• 不要求binlog的格式，因為statement格式的binlog也可以很容易拿到庫名

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

2、MariaDB的并行復制策略

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

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

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

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

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

2.commit_id直接寫(xiě)到binlog里面

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

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

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

按照MariaDB的并行復制策略，備庫上的執行效果如下圖：

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

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

3、MySQL5.7版本的并行復制策略

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

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

• 配置為L(cháng)OGICAL_CLOCK，表示的就是類(lèi)似MariaDB的策略。MySQL在此基礎上做了優(yōu)化

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

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

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

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

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

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

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

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

• binlog_group_commit_sync_delay參數表示延遲多少微妙后才調用fsync

• binlog_group_commit_sync_no_delay_count參數表示基類(lèi)多少次以后才調用fsync

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

4、MySQL5.7.22的并行復制策略

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

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

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

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

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

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

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

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

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

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

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

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

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

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

當我們把節點(diǎn)B設置成節點(diǎn)A’的從庫的時(shí)候，需要執行一條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è)參數，分別代表了主庫A’的IP、端口、用戶(hù)名和密碼

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

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

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

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

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

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

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

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

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

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

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

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

set global sql_slave_skip_counter=1;start slave;

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

2、GTID

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

GTID=source_id:transaction_id

• source_id是一個(gè)實(shí)例第一次啟動(dòng)時(shí)自動(dòng)生成的，是一個(gè)全局唯一的值

• transaction_id是一個(gè)整數，初始值是1，每次提交事務(wù)的時(shí)候分配給這個(gè)事務(wù)，并加1

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

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

1.如果gtid_next=automatic，代表使用默認值。這時(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集合中，接下里執行的這個(gè)事務(wù)會(huì )直接被系統忽略

• 如果current_gtid沒(méi)有存在于實(shí)例的GTID集合中，就將這個(gè)current_gtid分配給接下來(lái)要執行的事務(wù)，也就是說(shuō)系統不需要給這個(gè)事務(wù)生成新的GTID，因此transaction_id也不需要加1

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

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

3、基于GTID的主備切換

在GTID模式下，備庫B要設置為新主庫A’的從庫的語(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。我們在實(shí)例B上執行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給刪掉了，直接返回錯誤

• 如果確認全部包含，A’從自己的binlog文件里面，找出第一個(gè)不在set_b的事務(wù)，發(fā)給B

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

4、GTID和在線(xiàn)DDL

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

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

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

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

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

• 到實(shí)例X上執行一下語(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上執行這條更新

七、MySQL讀寫(xiě)分離

讀寫(xiě)分離的基本結構如下圖：

讀寫(xiě)分離的主要目的就是分攤主庫的壓力。上圖中的結構是客戶(hù)端主動(dòng)做，這種模式下一般會(huì )把數據庫的連接信息放在客戶(hù)端的連接層。由客戶(hù)端來(lái)選擇后端數據庫進(jìn)行查詢(xún)

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

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

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

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

1、強制走主庫方案

強制走主庫方案其實(shí)就是將查詢(xún)請求做分類(lèi)。通常情況下，可以分為這么兩類(lèi)：

1.對于必須要拿到最新結果的請求，強制將其發(fā)到主庫上

2.對于可以讀到舊數據的請求，才將其發(fā)到從庫上

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

2、Sleep方案

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

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

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

1.如果這個(gè)查詢(xún)請求本來(lái)0.5秒就可以在從庫上拿到正確結果，也會(huì )等1秒

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

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

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

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

show slave status結果的部分截圖如下：

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

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

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

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

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

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

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

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

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

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

1）主庫執行完成，寫(xiě)入binlog，并反饋給客戶(hù)端

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

3）在備庫執行binlog完成

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

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

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

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

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

4、配合semi-sync

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

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

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

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

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

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

1.如果查詢(xún)是落在這個(gè)響應了ack的從庫上，是能夠確保讀到最新數據

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

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

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

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

1.一主多從的時(shí)候，在某些從庫執行查詢(xún)請求會(huì )存在過(guò)期讀的現象

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

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

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

這條命令的邏輯如下：

1.它是在從庫執行的

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

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

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

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

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

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

對于上圖中先執行trx1，再執行一個(gè)查詢(xún)請求的邏輯，要保證能夠查到正確的數據，可以使用這個(gè)邏輯：

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

2.選定一個(gè)從庫執行查詢(xún)語(yǔ)句

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

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

5.否則，到主庫執行查詢(xún)語(yǔ)句

流程如下：

6、GTID方案

 select wait_for_executed_gtid_set(gtid_set, 1);

這條命令的邏輯如下：

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

2.超時(shí)返回1

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

等GTID的流程如下：

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

2.選定一個(gè)從庫執行查詢(xún)語(yǔ)句

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

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

5.否則，到主庫執行查詢(xún)語(yǔ)句