MySQL 叢集知識點整理

隨著專案架構的不斷擴大，單臺 MySQL 已經不能滿足需要了，所以需要搭建叢集將前來的請求進行分流處理。部落格主要根據丁奇老師的專欄<<MySQL實戰45講>>學習的總結。

架構

MySQL的叢集和 Redis 叢集類似，都是預設為master 庫，可以設定為從庫，主庫負責處理寫請求，從庫處理讀請求。一般將從庫設定為 read-only，也就是將這個引數設為 true。這樣既避免了在主從切換、測試時從庫的誤操作導致主從不一致，同時也可以通過這個屬性來判斷當前庫是什麼角色。

種類

1、按 master 數量來看，架構可以分為兩種。

第一種是明確的主備關係，這種使用的不多，因為在主備切換時會比較耗時。

第二種是有多個master，master 之間互為主備關係，只不過保留一個處理寫操作，其他的設定 readonly=true，只處理讀請求。這種架構在切換時比較方便、快捷。

2、按是否代理的角度來看，也可以分為兩種。

第一種是不使用代理。這種方式的好處是架構簡單、執行快、排錯快。缺點是在主備切換、庫遷移時需要改變後臺資料庫連線資訊。

 第二種是使用代理。這樣做的好處是後端不需要關注資料庫操作的細節，連線維護、後臺資訊維護都是由 proxy 完成的。但缺點也很明顯，這樣的架構比較複雜，設定、維護起來比較吃力，且 proxy 必須是高可用的。

搭建

1、設定master：

　　1）開啟組態檔（預設在/usr/my.cnf），主要設定的引數：

　　　　server-id：當前庫的 id

　　　　log-bin：binlog 儲存位置

　　　　read-only：是否唯讀。1代表唯讀，0代表讀寫。

　　　　binlog-ignore-db：不儲存操作到binlog 的資料庫，可以設為 mysql

　　然後重啟：service  mysql  restart ;

　　2）建立同步資料的賬戶，並授權：

　　　　grant  replication  slave  on  *.*  to  '使用者名稱'@''ip地址'  identified by  '密碼' ; 

　　　　flush  privilages;

　　3）檢視 master 狀態：show  master  status。得到 Position 值（紀錄檔最新寫入點）。

2、設定 slave：

　　1）開啟組態檔my.cnf，設定引數 server-id 、log-bin、read-only。然後重啟。

　　2）設定從庫與主庫的關聯：

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 四個引數，分別代表了主庫 A’的 IP、埠、使用者名稱和密碼。最後兩個引數 MASTER_LOG_FILE 和 MASTER_LOG_POS 表示，要從主庫的 master_log_name 檔案的 master_log_pos 這個位置的紀錄檔繼續同步。而這個值就是前面讀取的 Position 值。

上面這種是5.6之前通過位點來實現同步的，其存在很多不足，比如這個位點在主庫切換時需要重新去獲取，並且在新的主庫啟動後從庫可能會導致操作重複執行導致丟擲異常，所以在5.6引入了 GTID來替換位點，解決了這個問題。具體在下文會解釋。這裡先說一下使用 GTID 來完成主備連線。

　　1）首先要在主從庫上都設定引數：SET GLOBAL ENFORCE_GTID_CONSISTENCY = 'ON';　　SET GLOBAL GTID_MODE = 'ON';　　如果要永久有效，在組態檔 my.cnf 中設定 gtid-mode=ON　　enforce-gtid-consistency=1 。

　　2）設定從庫與主庫的關聯：

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

最後開啟同步：start  slave;

原理

內部執行圖：

1、在備庫 B 上通過 change master 命令，設定主庫 A 的 IP、埠、使用者名稱、密碼，以及要從哪個位置開始請求 binlog，這個位置包含檔名和紀錄檔偏移量。
2、在備庫 B 上執行 start slave 命令，這時候備庫會啟動兩個執行緒，就是圖中的 io_thread 和 sql_thread。其中 io_thread 負責與主庫建立連線。
3、主庫 A 校驗完使用者名稱、密碼後，開始按照備庫 B 傳過來的位置，從本地讀取 binlog（先從page cache中讀取，沒有再讀取磁碟），發給 B。
4、備庫 B 拿到 binlog 後，寫到本地檔案，稱為中轉紀錄檔（relay log）。
5、sql_thread 讀取中轉紀錄檔，解析出紀錄檔裡的命令，並執行。

迴圈複製

這裡從庫可以將從主庫傳來的更新的資料也進行持久化記錄在原生的 binlog 中，通過將引數 log_slave_updates 設為 on 來開啟這個功能。但是這樣可能會發生迴圈複製。

更新操作的 binlog 的執行機制：某個庫處理了寫操作，那麼它就會將這個寫操作相關的 binlog 傳送給其他與其關聯的庫，這個 binlog 上會記錄當前庫的 server-id，其他庫收到 binlog 後會判斷 server-id 是否是當前庫的 server-id，如果不是就應用到庫中並記錄到 binlog（這裡記錄不會改變原有的接收到的資料，也就不是修改 server-id），然後再重複將寫操作的 binlog 傳送給其他庫。 

發生迴圈複製的場景：

1、主庫執行寫操作後，修改了當前庫的 server-id。因為修改了server-id，所以主庫在受到從庫傳送回來的紀錄檔後還會繼續執行，執行完後還會重複發給從庫，造成迴圈。

解決：這種只能提前規定庫的 server-id 在執行時不能修改。

2、三節點中某個節點執行寫操作後將寫操作傳給其他兩個節點後另外兩個節點發生了迴圈複製。

解決：先停止紀錄檔的傳送，一段時間後再改回來。

stop slave；
CHANGE MASTER TO IGNORE_SERVER_IDS=(server_id_of_B);
start slave; 

主從切換

主從切換指的就是主庫與備庫身份的切換。

切換策略

可靠性優先策略（優先）

可靠性優先策略是將資料的可靠性設為優先進行切換的策略。其核心就是先關閉主庫A對寫請求的處理，然後等待備庫的資料延遲變為0，也就是備庫B讀取完從主庫A傳來的所有操作紀錄檔且全部落盤。這時再將備庫B設為新的主庫B。

1、判斷備庫 B 現在的 seconds_behind_master（通過 show slave status 檢視），如果小於某個值（比如 5 秒）繼續下一步，否則持續重試這一步；
2、把主庫 A 改成唯讀狀態，即把 readonly 設定為 true；
3、判斷備庫 B 的 seconds_behind_master 的值，直到這個值變成 0 為止；（最耗時）
4、把備庫 B 改成可讀寫狀態，也就是把 readonly 設定為 false；
5、把業務請求切到備庫 B。

因為是可靠性優先，所以用這種方式切換後讀取的資料是可以保證準確性的。缺點是因為在開始需要等待備庫B與主庫A的延遲變為0後 B庫才可以設為主庫，而在這等待的過程中前來的寫請求是無法被處理的，全部會被阻塞，如果並行的寫操作很多，那麼就會很影響系統的響應效能。

可用性優先策略

可用性優先策略是將上面可靠性優先策略步驟裡的4,5提前到最開始執行，這樣就不會出現寫操作被阻塞的情況了，保證了可用性。但是這樣帶來的缺點是很嚴重的。可用性優先策略執行過程會根據 binlog 格式的不同有所不同。

1、mixed 格式

主鍵是自增的。執行過程：

1、步驟 2 中，主庫 A 執行完 insert 語句，插入了一行資料（4,4），之後開始進行主備切換。
2、步驟 3 中，由於主備之間有 5 秒的延遲，所以備庫 B 還沒來得及應用「插入 c=4」這個中轉紀錄檔，就開始接收使用者端「插入 c=5」的命令。
3、步驟 4 中，備庫 B 插入了一行資料（4,5），並且把這個 binlog 發給主庫 A。
4、步驟 5 中，備庫 B 執行「插入 c=4」這個中轉紀錄檔，插入了一行資料（5,4）。而直接在備庫 B 執行的「插入 c=5」這個語句，傳到主庫 A，就插入了一行新資料（5,5）。

最終導致主從資料不一致，同時在查詢相應資料時會返回錯誤的資料，並且還無法被發現，這是非常致命的。

2、Row 格式

在使用 row 格式的 binlog 進行紀錄檔記錄，那麼記錄的是完整的運算元據，所以不會出現上面出現的情況，並且在發生這種異常時會丟擲異常提示。

總結

因為可用性優先策略很容易造成資料不一致，所以 一般使用的都是可靠性優先策略，但是因為可靠性策略在等待備庫趕上主庫時寫操作會被阻塞，所以主從延遲就決定了 MySQL 的可用性。主從延遲越低，主庫在異常宕機後，從機就越快趕上主庫的資料，更快恢復。

主從同步方式

這裡以一主多從的架構為例說明。

假設原本 A 是主機，A' 和 A 互為主備關係，只不過 A' read-only 設為 true，B、C、D都是A的從庫。這時A突然宕機，那麼想要將 A' 設為新的主機，並且將 B、C、D 的從屬關係變為 A'。

通過紀錄檔的同步位點（傳統方式）

在上面搭建時在設定從庫與主庫的關聯時說到有兩種方式，第一種就是通過紀錄檔位點來確定從庫開始接收紀錄檔的起始點，這種方式在初始設定時可以直接檢視並賦值，但是如果中間主機宕機或者主動切換時就比較麻煩了。所以此時需要做的是：

1、等待新主庫 A’把中轉紀錄檔（relay log）全部同步完成；

2、在 A’上執行 show master status 命令，得到當前 A’上最新的 File 和 Position；

3、取原主庫 A 故障的時刻 T；

4、用 mysqlbinlog 工具解析 A’的 File，得到 T 時刻的位點。mysqlbinlog File --stop-datetime=T --start-datetime=T

得到 123，將這個值作為位點來設定B、C、D 的從屬關係。

位點不準確：通過上面方法得到的值並一定是準確的，如果主庫 A 在宕機前剛執行了一條insert事務，並且將此事務發給了 A‘、B，傳完後立刻宕機，那麼 A'、B 都已經同步了這一行 insert 事務，這時將 A' 作為主機啟動，然後在將 B 的從屬關係改成 A' 時把 Position 還寫成讀取到的 insert 事務那一行，那麼就會重複執行，如果格式是 row 的話就會丟擲異常（statement 就會造成主從不一致）。

如果是丟擲異常，就會斷開與主庫的連線，所以需要我們去處理，恢復連線。

解決方式：

1、丟擲異常後跳過。

跳過：set global sql_slave_skip_counter=1;

開啟從庫：start slave;

2、跳過指定的錯誤（不推薦）。

上面重複執行遇到的錯誤主要是兩種：1062，插入資料時唯一鍵不唯一；1032，刪除資料時找不到行。

所以可以將 slave_skip_errors 設為 "1032、1062"。

通過這種方式如果後端傳來的請求也會造成這樣的錯誤則也會被跳過，所以可能會引起後端無法收到反饋。這種方式只適用於無法確定同步點且確定到從庫恢復這段時間不會有 1032、1062 的事務，並且在一段時間後還需要將這個引數修改回去。

通過GTID（推薦）

是什麼：GTID 是 MySQL5.6 引入的概念，表示的是全域性事務ID，是一個事務的唯一標識，由兩部分組成，格式是：

官方檔案是：GTID=source_id:transaction_id
便於理解可以看作：GTID=server_uuid:gno
server_uuid 是一個範例第一次啟動時自動生成的，是一個全域性唯一的值；
gno 是一個整數，初始值是 1，每次提交事務的時候分配給這個事務，並加 1。
transaction_id 是事務id，但是事務回滾的話，這個值也會自增。

使用：資料庫在啟動時加上引數 gtid_mode=on 和 enforce_gtid_consistency=on 來開啟 GTID，如果要永久有效，在組態檔 my.cnf 中設定 gtid-mode=ON　　enforce-gtid-consistency=1 。

在主機A 宕機後，改變從庫 B、C、D 的 slave 關係時使用

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

切換原理：

1、將範例A'的 GTID 集合記為 set_a，範例B 的 GTID 集合記為 set_b。那麼執行邏輯如下：
2、範例 B 指定主庫 A’，基於主備協定建立連線。
3、範例 B 把 set_b 發給主庫 A’。
4、範例 A’算出 set_a 與 set_b 的差集，也就是所有存在於 set_a，但是不存在於 set_b 的 GTID 的集合，判斷 A’本地是否包含了這個差集需要的所有 binlog 事務。
　　a. 如果不包含，表示 A’已經把範例 B 需要的 binlog 給刪掉了，直接返回錯誤；
　　b. 如果確認全部包含，A’從自己的 binlog 檔案裡面，找出第一個不在 set_b 的事務，發給 B；
5、之後就從這個事務開始，往後讀檔案，按順序取 binlog 發給 B 去執行。

GTID生成方式：

1、gtid_next=automatic。

使用預設值，會使用預設分配的 server_uuid:gno 分配給這個事務。
在記錄 binlog 時會自動先記錄一行 SET @@SESSION.GTID_NEXT=‘server_uuid:gno’;
來表示將這個事務的 GTID加入集合，從庫執行時也會先檢查

2、gtid_next ='指定的值'

通過 set gtid_next = '指定的值'，然後就會檢查GTID集合是否已存在這個值，如果存在下一個事務就會跳過。

例子：

庫X

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

insert into t values(1,1);

記錄操作在binlog相關的 GTID如下：

可以看到 insert 操作對應的 GTID 是 '00000000-1111-0000-1111-000000000000:2'。此時將其設為Y的從庫，在主庫Y上執行 insert into t values(1,1)，這條語句在範例 Y 上的 GTID 是 「aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10」;那麼如何避免從庫X發生異常？
答：從庫（應該）執行下面的操作

set gtid_next='aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10';
begin;
commit;
set gtid_next=automatic;
start slave;

這裡是執行一個空事務，作用是向從庫的 GTID集合中新增主庫這條記錄的GTID，從庫在和主庫比較時就會自動跳過這條操作的事務

優點：將原本通過紀錄檔位點來匹配的方式簡化了，對使用人員非常友好。

問題：如果一個新的從庫接上主庫，但是需要的 binlog 已經沒了，要怎麼做？（對應上面切換原理4.a 的情況）

答：1、如果業務允許主從不一致的情況，那麼可以在主庫上先執行 show global variables like ‘gtid_purged’，得到主庫已經刪除的 GTID 集合，假設是 gtid_purged1；然後先在從庫上執行 reset master，再執行 set global gtid_purged =‘gtid_purged1’；最後執行 start slave，就會從主庫現存的 binlog 開始同步。binlog 缺失的那一部分，資料在從庫上就可能會有丟失，造成主從不一致。

2、如果需要主從資料一致的話，最好還是通過重新搭建從庫來做。

3、如果有其他的從庫保留有全量的 binlog 的話，可以把新的從庫先接到這個保留了全量 binlog 的從庫，追上紀錄檔以後，如果有需要，再接回主庫。

4、如果 binlog 有備份的情況，可以先在從庫上應用缺失的 binlog，然後再執行 start slave。

主備延遲（MySQL的可用性）

在上面的我們說到一般專案使用的都是可靠性優先策略，所以 MySQL 的可用性就決定於主從延遲。

主備延遲的來源

1、備庫所在的機器效能比主庫所在的機器效能差。也就是從庫 binlog 落盤的速度比主庫 binlog 寫操作慢，主從延遲越來越高。

2、備庫壓力大。解決：1）使用一主多從架構 　　2）通過 binlog 輸出到外部系統，比如 Hadoop 這類系統，讓外部系統提供統計類查詢的能力。

3、大事務在從庫執行的時間比較長，導致資料更新的延遲變長。 解決：1）如果長事務的操作比較多，嘗試將事務拆分成多個事務　　2）如果是大表 DDL 語句，可以使用開源專案 gh-ost 進行調節。

4、備庫的並行複製能力比較差。MySQL 的並行複製能力是比較重要的。

MySQL的並行複製

回顧一下操作執行在主備庫中的流程圖：

黑箭頭代表主備的並行複製能力，主庫上處理的是寫操作，因為 InnoDB 支援行鎖，再搭配資料庫的多執行緒，吞吐量很高。而備庫中讀取傳來解析紀錄檔的 sql_thread 是單執行緒，而在備庫中落盤的過程也是經歷了從單執行緒到多執行緒的過程。在5.6之前，備庫將主庫傳來的紀錄檔落盤都是單執行緒，所以就是圖中的細黑箭頭。

在5.6開始，備庫的落盤就變成了多執行緒，執行圖就變成下面這種：

coordinator 就是原來的 sql_thread, 不過現在它不再直接更新資料了，只負責讀取中轉紀錄檔和分發事務。真正更新紀錄檔的，變成了 worker 執行緒。

work 執行緒數設定：slave_parallel_workers。這個值設定為 8~16 之間最好（32 核物理機的情況），畢竟備庫還有可能要提供讀查詢，不能把 CPU 都吃光了。

coordinator 分發滿足的基本條件：

1、更新同一行的事務必須分發給同一個worker執行緒

2、同一個事務的所有操作必須分給同一個worker執行緒

coordinator 分發規則：

1、如果跟所有 worker 都不衝突，coordinator 執行緒就會把這個事務分配給最空閒的 woker;

2、如果跟多於一個 worker 衝突，coordinator 執行緒就進入等待狀態，直到和這個事務存在衝突關係的 worker 只剩下 1 個；

3、如果只跟一個 worker 衝突，coordinator 執行緒就會把這個事務分配給這個存在衝突關係的 worker。

1、5.6 按庫並行複製

思路：每個 worker 執行緒內部維護一個 hash 表，key 是 "庫名"，value 表示這個執行緒內有多少事務即將或正在執行這個庫。某個事務在被分配時，會根據 coordinator 分發規則來進行。

例子：假設有一個事務T，會操作庫 db1、db3，那麼 coordinator 會先判斷 worker_1 執行緒，發現存在操作 db1 的事務，所以換下一個；
緊接著發現 worker_2 也存在操作 db3 的事務，所以就會進入阻塞等待。如果後面 worker_2 中操作 db3的事務執行完畢，value變為0，那麼 coordinator 就會識別到，只剩 worker_1 與事務 T 有衝突，那麼就將 T 分配給 worker_1。

優點：1）在分解構造 hash 時非常快　　　　2）對 binlog 的格式沒有要求（不會發生主從不一致）

缺點：並行度不高，如果操作都集中在某一個庫，那麼執行就和使用單執行緒一樣。　　

2、mariaDB 並行複製策略

雖然不是 MySQL 的策略，但是因為它的思想對 MySQL 後續版本有啟迪作用，所以這裡也說一下。
思路：因為有 " 組提交 " 機制（不知道組提交可以檢視 組提交），位於一組提交的事務是可以並行複製的。一組提交的事務一定不會存在操作同一行記錄的操作，這是因為主庫執行寫操作時，如果存在處理同一行記錄的兩個事務，那麼其中一個一定會被行鎖所阻塞，" 被阻塞 " 的事務也就無法與 " 阻塞 " 事務一同提交了。執行過程就如下：

1、在一組裡面一起提交的事務，有一個相同的 commit_id，下一組就是 commit_id+1；
2、commit_id 直接寫到 binlog 裡面；
3、傳到備庫應用的時候，相同 commit_id 的事務分發到多個 worker 執行；
4、這一組全部執行完成後，coordinator 再去取下一批。

缺點：1）雖然是隨著主庫執行順序來執行的，但是效率並沒有主庫高。

主庫在前一組事務提交寫盤的同時下一組事務已經在執行了，而備庫上需要上一組事務完全落盤後才可以開始下一組事務的執行。

主庫：

備庫：

2）如果一組中的一個事務是大事務，那麼其他兩個事務執行完還需要等待這個事務執行完才能開始下一組事務的開始

4、5.7 結合了mariaDB 的思想

MySQL 在5.7 版本融合了 mariaDB 的思想並進行了一些改進。

思想：模式分成兩種，通過引數 slave-parallel-type 設定。

1）設定為 DATABASE。保持5.6 的策略，按庫執行

2）設定為 LOGICAL_CLOCK。和 mariaDB 的組提交來執行，只不過進行一些變化。在 mariaDB 中是在上一組事務完全 commit 後，也就是在三步提交中這一組事務全部到達 redo log commit 階段後才能進行下一組事務的執行；而在這個版本中則將下一組事務執行的時間提前到 redo log prepare ，只要全部到達 redo log prepare 階段就可以執行下一組事務了。這個原因也很簡單，到達 redo log prepare 階段說明事務已經執行完成。

引數設定：binlog_group_commit_sync_delay、 binlog_group_commit_sync_no_delay_count 。這兩個引數作用的都是在 binlog 完成 write 後到 fsync 的過程。分別表示 binlog 延遲多少微秒後才調 fsync、binlog 累積多少次以後才呼叫 fsync。通過這兩個引數可以將更多的事務新增到一個組中，使備庫的並行度更高。

5、5.7.22 提出了按行並行複製

在這個版本中拋棄了按庫並行的思想。並且引入了按行並行。

思想：分為三種。通過引數 binlog-transaction-dependency-tracking 設定。

1）COMMIT_ORDER。還是上個版本中的按事務組來執行。

2）WRITESET（重要）。按行並行複製。對於事務涉及更新的每一行，計算這一行的 hash 值，組成集合 writeset。如果兩個事務沒有操作相同的行，那麼他們就可以並行執行。 hash 值的計算是" 庫名+表名+索引名+值 "，這裡的索引指的是所有的唯一索引，值表示索引對應的值。

優勢：

　　Ⅰ、writeset 是在主庫生成後直接寫入到 binlog 裡面的，這樣在備庫執行的時候，不需要解析 binlog 內容（event 裡的行資料），節省了很多計算量；

　　Ⅱ、不需要把整個事務的 binlog 都掃一遍才能決定分發到哪個 worker，更省記憶體；

　　Ⅲ、由於備庫的分發策略不依賴於 binlog 內容，所以 binlog 是 statement 格式也是可以的。

不足：對於「表上沒主鍵」和「外來鍵約束」的場景，WRITESET 策略也是沒法並行的，也會暫時退化為單執行緒模型。

3）WRITESET_SESSION。在按行並行的基礎上，對主庫同一個執行緒先後事務的執行順序，在備庫執行時也需要保證。

問題：單執行緒新增很多記錄，在從庫追主庫的過程中，binlog-transaction-dependency-tracking 應該選用什麼引數？

答：由於主庫是單執行緒壓力模式，所以每個事務的 commit_id 都不同，那麼設定為 COMMIT_ORDER 模式的話，從庫也只能單執行緒執行。同樣地，由於 WRITESET_SESSION 模式要求在備庫應用紀錄檔的時候，同一個執行緒的紀錄檔必須與主庫上執行的先後順序相同，也會導致主庫單執行緒壓力模式下退化成單執行緒複製。所以，應該將 binlog-transaction-dependency-tracking 設定為 WRITESET。

主備延遲問題

原因：因為 seconds_behind_master 是備庫以當前系統時間減去執行事務開始寫入時間得出的。

1、主庫執行了大事務（大表DDL、一個事務操作很多行）
2、備庫起了一個長事務，如

begin; 
select * from t limit 1;

然後就不動了，這時候主庫執行了一個DDL操作，新增一個欄位，那麼就會被堵住。

解決過期讀

" 過期讀 " 指的是在主庫操作某行記錄後，立刻進行查詢，那麼查詢會被從庫所執行，這樣主庫上的 binlog 還未更新到從庫上，所以讀請求返回的結果還是修改前的記錄。下面是幾種解決 " 過期讀 " 的方式。

1、強制走主庫（使用最多，準確）

思想：對於必須要拿到最新值的請求，強制傳送給主庫執行；對於可以讀到舊資料的請求，傳送給從庫執行。

總結：實現簡單，對於必須拿最新值的請求的數量不多的場景可以使用。但是在數量多的場景使用這種方式會使主庫的壓力變大，演變成單庫模式。

2、Sleep（不準確）

思想：在從庫執行前，先 sleep 一下，類似執行一條 sleep(1) 的命令。類似的業務實現比如在淘寶購買成功後並不是直接跳轉到訂單頁面，而是跳轉到 " 購買成功 " 的提示頁面，如果想檢視這個訂單，需要點選檢視訂單，這就給了從庫來同步主庫的時間。

不足：1）因為總會延遲，如果主從延遲只有0.5秒也需要經過這個時間才能得到資料。　　2）如果是大事務，應用到從庫所要的時間比較長，那麼還是會發生 " 過期讀 "。

3、寫請求在主從無延遲後再返回（不太準確）

思想

實現主備無延遲主要有四種方案。

1）檢視 seconds_behind_master 是否已經等於0。seconds_behind_master 單位是秒，精度不夠，所以用於檢驗準確性不是很高。

2）對比位點，比較 binlog 紀錄檔讀取的位點。相比於第一種準確一些。通過 show slave status 檢視當前從庫的相關引數；通過 show master status 檢視主機的相關引數。

　　Master_Log_File 和 Read_Master_Log_Pos，表示的是讀到的主庫的最新位點；
　　Relay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是備庫執行的最新位點。
　　如果 Master_Log_File 和 Relay_Master_Log_File、Read_Master_Log_Pos 和 Exec_Master_Log_Pos 這兩組值完全相同，就表示接收到的紀錄檔已經同步完成。

3）對比當前備庫和所有備庫已經執行的 GTID 集合。相比於第一種準確一些。

　　Auto_Position=1 ，表示這對主備關係使用了 GTID 協定。
　　Retrieved_Gtid_Set，是備庫收到的所有紀錄檔的 GTID 集合；
　　Executed_Gtid_Set，是備庫所有已經執行完成的 GTID 集合。
　　如果這兩個集合相同，也表示備庫接收到的紀錄檔都已經同步完成。

問題：2）、3）為什麼會不準確？

答：一個事務的執行順序如下：　　

　　Ⅰ、主庫執行完成，寫入 binlog，並反饋給使用者端；　　
　　Ⅱ、binlog 被從主庫傳送給備庫，備庫收到；
　　Ⅲ、在備庫執行 binlog 完成。

如果在1、2之間判斷就會以為是最新的，沒有問題，直接進行操作，導致 " 過期讀 "。

4）使用 semi-sync（搭配2、3）。semi-sync 是半同步複製（emi-sync replication），而2、3 是非同步複製，也就是在主庫寫完 binlog 後就會傳送訊息給使用者端。而執行過程如下：

　　Ⅰ、事務提交的時候，主庫把 binlog 發給從庫；
　　Ⅱ、從庫收到 binlog 以後，發回給主庫一個 ack，表示收到了；
　　Ⅲ、主庫收到這個 ack 以後，才能給使用者端返回「事務完成」的確認。

通過 semi-sync 可以解決2、3出現的問題，同時在主庫異常斷電時也可以避免主庫在寫完 binlog 還未將 binlog 傳送給從庫就宕機，導致從庫未收到 binlog 導致主庫重啟後主從不一致。

缺點

1）只適用於一主一從。因為這個模式的執行過程是主庫收到 ACK 確認後立刻就會反饋給請求傳送方資訊，如果是一主多從，那麼任何一臺從機返回的 ACK 都會使主庫立刻返回反饋資訊給請求，這樣如果再次傳送讀該資料的請求，並且請求被分配到還未同步的從庫，那麼又會發生 "過期讀"。

2）在持續延遲的情況下，可能出現過度等待的問題。

 如果按2,3判斷，直到狀態4都無法執行讀操作，而實際上在狀態2就可以讀了。

4、先在一段時間內嘗試在從庫上執行，如果沒有同步再在主庫上執行（準確）

這種方式有兩種實現方案。

1）等主庫位點方案

原理：

實現主要通過命令：select master_pos_wait(file, pos, timeout);  特點如下：

Ⅰ、它是在從庫執行的；

Ⅱ、引數 file 和 pos 指的是主庫上的檔名和位置；

Ⅲ、timeout 可選，設定為正整數 N 表示這個函數最多等待 N 秒。

Ⅳ：返回值：一、正常返回的結果是一個正整數 M，表示從命令開始執行，到應用完 file 和 pos 表示的 binlog 位置，執行了多少事務。

　　　　　　二、如果執行期間，備庫同步執行緒發生異常，則返回 NULL；
　　　　　　三、如果等待超過 N 秒，就返回 -1；
　　　　　　四、如果剛開始執行的時候，就發現已經執行過這個位置了，則返回 0。

執行邏輯:

假設使用者端接收的延遲為1秒，那麼執行邏輯就如下：

Ⅰ、trx1 事務更新完成後，馬上執行 show master status 得到當前主庫執行到的 File 和 Position；
Ⅱ、選定一個從庫執行查詢語句；
Ⅲ、在從庫上執行 select master_pos_wait(File, Position, 1)；
Ⅳ、如果返回值是 >=0 的正整數，則在這個從庫執行查詢語句；
Ⅴ、否則，到主庫執行查詢語句。

2）GTID 方案

原理：

主要通過命令： select wait_for_executed_gtid_set(gtid_set, timeout); 

語句作用：等待，直到這個庫執行的事務中包含傳入的 gtid_set，返回0；超時返回1。

執行邏輯:

還是假設使用者端接收的延遲為1秒，執行邏輯如下：

Ⅰ、trx1 事務更新完成後，從返回包直接獲取這個事務的 GTID，記為 gtid1；
Ⅱ、選定一個從庫執行查詢語句；
Ⅲ、在從庫上執行 select wait_for_executed_gtid_set(gtid1, 1)；
Ⅳ、如果返回值是 0，則在這個從庫執行查詢語句；
Ⅴ、否則，到主庫執行查詢語句。

在上面的第Ⅰ步中，trx1 事務更新完成後，從返回包直接獲取這個事務的 GTID。我們只需要將引數 session_track_gtids 設定為 OWN_GTID，然後通過 API 介面 mysql_session_track_get_first 從返回包解析出 GTID 的值即可。

問題：假設你的系統採用了我們文中介紹的最後一個方案，也就是等 GTID 的方案，現在你要對主庫的一張大表做 DDL，可能會出現什麼情況呢？為了避免這種情況，你會怎麼做呢？

答：假設，這條語句在主庫上要執行 10 分鐘，提交後傳到備庫就要 10 分鐘（典型的大事務）。那麼，在主庫 DDL 之後再提交的事務的 GTID，去備庫查的時候，就會等 10 分鐘才出現。這樣，這個讀寫分離機制在這 10 分鐘之內都會超時，然後走主庫。
這種預期內的操作，應該在業務低峰期的時候，確保主庫能夠支援所有業務查詢，然後把讀請求都切到主庫，再在主庫上做 DDL。等備庫延遲追上以後，再把讀請求切回備庫。
這個思考題主要是想關注大事務對等位點方案的影響。當然了，使用 gh-ost 方案來解決這個問題也是不錯的選擇。

優點：相比於使用位點的方案，減少了一次在主庫上的查詢操作。

缺點：使用前提是開啟了 GTID，使用場景沒有使用位點廣泛。