《MySQL实战45讲》实践篇 24-29 学习笔记 (主备篇)
圖片來自于極客時間，如有版權問題，請聯系我刪除。
24 | MySQL是怎么保證主備一致的？
主備切換流程如下:
建議把節點 B（也就是備庫）設置成只讀（readonly）模式。
1.有時候一些運營類的查詢語句會被放到備庫上去查，設置為只讀可以防止誤操作；
2.防止切換邏輯有 bug，比如切換過程中出現雙寫，造成主備不一致；
3.可以用 readonly 狀態，來判斷節點的角色
readonly 設置對超級 (super) 權限用戶是無效的，而用于同步更新數據的線程，就擁有超級權限。（所以一個readonly的從庫還是可以同步主庫的數據的）
主從同步的流程:
一個事務日志同步的完整過程是這樣的：
binlog 的三種格式對比
當 binlog_format=statement 時，binlog 里面記錄的就是 SQL 語句的原文
但是在statement，如果一個更新SQL使用了limit,在反復執行的時候可能實際用到的索引并不一樣，會導致結果可能出現不一致
mysql> delete from t /comment/ where a>=4 and t_modified<=‘2018-11-10’ limit 1;
mysql會產生一個warning
binlog_format=row, row 格式的 binlog 里沒有了 SQL 語句的原文，而是替換成了兩個 event：Table_map 和 Delete_rows(這里是針對delete語句）。
Table_map event，用于說明接下來要操作的表是 test 庫的表 t;
Delete_rows event，用于定義刪除的行為
當 binlog_format 使用 row 格式的時候，binlog 里面記錄了真實刪除行的主鍵 id，這樣 binlog 傳到備庫去的時候，就肯定會刪除 id=4 的行，不會有主備刪除不同行的問題
為什么會有 mixed 這種 binlog 格式的存在場景？
為了避免主從不同步的同時，節省存儲空間和IO資源浪費
mixed 格式的意思是，MySQL 自己會判斷這條 SQL 語句是否可能引起主備不一致，如果有可能，就用 row 格式，否則就用 statement 格式。
在越來越多的場景要求把 MySQL 的 binlog 格式設置成 row。這么做的理由有很多，我來給你舉一個可以直接看出來的好處：恢復數據 (statement的格式用來恢復數據比較困難，因為不會記錄到底刪除了什么數據)
有人 mysqlbinlog 解析出日志，然后把里面的 statement 語句直接拷貝出來執行 這是不提倡的
因為有些語句的執行結果是依賴于上下文命令的，直接執行的結果很可能是錯誤的。
比如:
insert into t values(10,10, now());
它用 SET TIMESTAMP命令約定了接下來的now()函數的返回時間,這樣如果這個語句在從庫上是什么時候執行的，其插入的數據和主庫都是一致的
所以，用 binlog 來恢復數據的標準做法是，用 mysqlbinlog 工具解析出來，然后把解析結果整個發給 MySQL 執行。
類似如下:
主庫 A 從本地讀取 binlog，發給從庫 B；
老師，請問這里的本地是指文件系統的 page cache還是disk呢？
作者回復: 好問題，
是這樣的，對于A的線程來說，就是“讀文件”，
這個行為是文件系統控制的，MySQL只是執行“讀文件”這個操作
雙主架構下的循環復制問題
節點 A 上更新了一條語句，然后再把生成的 binlog 發給節點 B，節點 B 執行完這條更新語句后也會生成 binlog。（我建議你把參數 log_slave_updates 設置為 on，表示備庫執行 relay log 后生成 binlog）。
那么，如果節點 A 同時是節點 B 的備庫，相當于節點A又把節點 B 新生成的 binlog 拿過來執行了一次，就會出現循環復制的問題了
解決思路是利用MySQL 在 binlog 中記錄了這個命令第一次執行時所在實例的 server id
具體流程:一個備庫接到 binlog 并在重放的過程中，生成與原 binlog 的 server id 相同的新的 binlog；而每個庫在收到從自己的主庫發過來的日志后，先判斷 server id，如果跟自己的相同，表示這個日志是自己生成的，就直接丟棄這個日志
課后問題:我們說 MySQL 通過判斷 server id 的方式，斷掉死循環。但是，這個機制其實并不完備，在某些場景下，還是有可能出現死循環。
一種場景是，在一個主庫更新事務后，用命令 set global server_id=x 修改了 server_id。等日志再傳回來的時候，發現 server_id 跟自己的 server_id 不同，就只能執行了。
另一種場景是，有三個節點的時候，trx1 是在節點 B 執行的，因此 binlog 上的 server_id 就是 B，binlog 傳給節點 A，然后 A 和 A’搭建了雙 M 結構，就會出現循環復制。
這種三節點復制的場景，做數據庫遷移的時候會出現。如果出現了循環復制，可以在 A 或者 A’上，執行如下命令：
stop slave；
CHANGE MASTER TO IGNORE_SERVER_IDS=(server_id_of_B);
start slave;
這樣這個節點收到日志后就不會再執行。過一段時間后，再執行下面的命令把這個值改回來。
stop slave；
CHANGE MASTER TO IGNORE_SERVER_IDS=();
start slave;
25 | MySQL是怎么保證高可用的？(主備延遲)
與數據同步有關的時間點主要包括以下三個：
1.主庫 A 執行完成一個事務，寫入 binlog，我們把這個時刻記為 T1;
2.之后傳給備庫 B，我們把備庫 B 接收完這個 binlog 的時刻記為 T2;
3.備庫 B 執行完成這個事務，我們把這個時刻記為 T3。
所謂主備延遲，就是同一個事務，在備庫執行完成的時間和主庫執行完成的時間之間的差值，也就是 T3-T1
在網絡正常的時候，日志從主庫傳給備庫所需的時間是很短的，即 T2-T1 的值是非常小的。也就是說，網絡正常情況下，主備延遲的主要來源是備庫接收完 binlog 和執行完這個事務之間的時間差。
所以說，主備延遲最直接的表現是，備庫消費中轉日志（relay log）的速度，比主庫生產 binlog 的速度要慢。
主備延遲的來源
首先，有些部署條件下，備庫所在機器的性能要比主庫所在的機器性能差
做這種部署時，一般都會將備庫設置為“非雙 1”的模式。
第二種常見的可能了，即備庫的壓力大
將大量查詢工作交給了備庫
我們一般可以這么處理：
這就是第三種可能了，即大事務
主庫上必須等事務執行完成才會寫入 binlog，再傳給備庫。所以，如果一個主庫上的語句執行 10 分鐘，那這個事務很可能就會導致從庫延遲 10 分鐘。
不要一次性地用 delete 語句刪除太多數據 其實，這就是一個典型的大事務場景
另一種典型的大事務場景，就是大表 DDL。這個場景，我在前面的文章中介紹過。處理方案就是，計劃內的 DDL，建議使用 gh-ost 方案（這里，你可以再回顧下第 13 篇文章《為什么表數據刪掉一半，表文件大小不變？》中的相關內容）。
*備庫的并行復制能力。
在主備切換的時候，有相應的不同策略*
可靠性優先策略
在雙 M 結構下，從狀態 1 到狀態 2 切換的詳細過程是這樣的：
1.判斷備庫 B 現在的 seconds_behind_master，如果小于某個值（比如 5 秒）繼續下一步，否則持續重試這一步；
2.把主庫 A 改成只讀狀態，即把 readonly 設置為 true；
3.判斷備庫 B 的 seconds_behind_master 的值，直到這個值變成 0 為止；4.把備庫 B 改成可讀寫狀態，也就是把 readonly 設置為 false；
5.把業務請求切到備庫 B
在切換過程中，從步驟2-5期間，MySQL集群都不可用的
可用性優先策略
如果強行把步驟 4、5 調整到最開始執行，也就是說不等主備數據同步，直接把連接切到備庫 B，并且讓備庫 B 可以讀寫，那么系統幾乎就沒有不可用時間了。
這個切換流程的代價，就是可能出現數據不一致的情況。
如果使用可用性優先策略，建議設置 binlog_format=row，情況又會怎樣呢？因為 row 格式在記錄 binlog 的時候，會記錄新插入的行的所有字段值，所以最后只會有一行不一致。而且，兩邊的主備同步的應用線程會報錯 duplicate key error 并停止。
也就是說，這種情況下，備庫 B 的 (5,4) 和主庫 A 的 (5,5) 這兩行數據，都不會被對方執行。
主備切換的可用性優先策略會導致數據不一致。因此，大多數情況下，我都建議你使用可靠性優先策略(如果一定要使用可用性優先策略，建議選擇binlog_format=row))。畢竟對數據服務來說的話，數據的可靠性一般還是要優于可用性的。
有沒有哪種情況數據的可用性優先級更高呢？
答案是，有的。
有一個庫的作用是記錄操作日志。這時候，如果數據不一致可以通過 binlog 來修補，而這個短暫的不一致也不會引發業務問題。同時，業務系統依賴于這個日志寫入邏輯，如果這個庫不可寫，會導致線上的業務操作無法執行。
改進措施就是，讓業務邏輯不要依賴于這類日志的寫入。也就是說，日志寫入這個邏輯模塊應該可以降級，比如寫到本地文件，或者寫到另外一個臨時庫里面。
按照可靠性優先的思路，異常切換會是什么效果?
假設，主庫 A 和備庫 B 間的主備延遲是 30 分鐘，這時候主庫 A 掉電了，HA 系統要切換 B 作為主庫。我們在主動切換的時候，可以等到主備延遲小于 5 秒的時候再啟動切換，但這時候已經別無選擇了。
采用可靠性優先策略的話，你就必須得等到備庫 B 的 seconds_behind_master=0 之后，才能切換。但現在系統處于完全不可用的狀態（主庫 A 掉電后，我們的連接還沒有切到備庫 B）
"我"認為這種情形下應該也只能使用可用性優先策略了,但是由于主備延遲，導致客戶端查詢可能看不到之前執行完成的事務，會認為有“數據丟失”。但是對于一些業務來說，查詢到“暫時丟失數據的狀態”也是不能被接受的。(比方說訂單的情形)
在滿足數據可靠性的前提下，MySQL 高可用系統的可用性，是依賴于主備延遲的。延遲的時間越小，在主庫故障的時候，服務恢復需要的時間就越短，可用性就越高。
在實際的應用中，我更建議使用可靠性優先的策略。畢竟保證數據準確，應該是數據庫服務的底線。在這個基礎上，通過減少主備延遲，提升系統的可用性。
課后問題:
一般現在的數據庫運維系統都有備庫延遲監控，其實就是在備庫上執行 show slave status，采集seconds_behind_master的值。
假設，現在你看到你維護的一個備庫，它的延遲監控的圖像類似圖6，是一個45°斜向上的線段，你覺得可能是什么原因導致呢？你又會怎么去確認這個原因呢？
備庫的同步在這段時間完全被堵住
生這種現象典型的場景主要包括兩種：
1.一種是大事務（包括大表 DDL、一個事務操作很多行）；
2.還有一種情況比較隱蔽，就是備庫起了一個長事務，比如
begin;
select * from t limit 1;
然后就不動了
這時候主庫對表 t 做了一個加字段操作，即使這個表很小，這個 DDL 在備庫應用的時候也會被堵住，也能看到這個現象
26 | 備庫為什么會延遲好幾個小時？
如果備庫執行日志的速度持續低于主庫生成日志的速度，那這個延遲就有可能不斷增加，然后變成了小時級別
在官方的 5.6 版本之前，MySQL 只支持單線程復制，由此在主庫并發高、TPS 高時就會出現嚴重的主備延遲問題。
多線程復制
1.事務能不能按照輪詢的方式分發給各個 worker?
不行的。因為，事務被分發給 worker 以后，不同的 worker 就獨立執行了。但是，由于 CPU 的調度策略，很可能第二個事務最終比第一個事務先執行。而如果這時候剛好這兩個事務更新的是同一行，也就意味著，同一行上的兩個事務，在主庫和備庫上的執行順序相反，會導致主備不一致的問題。
2.同一個事務的多個更新語句，能不能分給不同的 worker 來執行呢？
也不行。一個事務更新了表 t1 和表 t2 中的各一行，如果這兩條更新語句被分到不同 worker 的話，雖然最終的結果是主備一致的，但如果表 t1 執行完成的瞬間，備庫上有一個查詢，就會看到這個事務“更新了一半的結果”，破壞了事務邏輯的隔離性。
coordinator 在分發的時候，需要滿足以下這兩個基本要求：
1.不能造成更新覆蓋。這就要求更新同一行的兩個事務，必須被分發到同一個 worker 中。
2.同一個事務不能被拆開，必須放到同一個 worker 中。
MySQL 5.5 版本的并行復制策略
自己實現的兩個并行復制策略
按表分發策略
每個 worker 線程對應一個 hash 表，用于保存當前正在這個 worker 的“執行隊列”里的事務所涉及的表。hash 表的 key 是“庫名. 表名”，value 是一個數字，表示隊列中有多少個事務修改這個表。
新的事務T:
1.當前worker中都沒有操作事務T涉及到的表，那選擇最空閑的一個
2.有>1的worker在操作事務T涉及到的表，則等到只剩一個
3.只有一個worker在操作事務T涉及到的表，就分給該worker
缺點:如果碰到熱點表，就變成類似單線程復制了
按行分發策略
按行復制的核心思路是：如果兩個事務沒有更新相同的行，它們在備庫上可以并行執行。要求 binlog 格式必須是 row。且必須有主鍵，不能有外鍵(表如果有外鍵，級聯更新的行就不會記錄在binlog中)
每個 worker，分配一個 hash 表。只是要實現按行分發，這時候的 key，就必須是“庫名 + 表名 + 唯一鍵的值”。(這里的唯一鍵不單單指主鍵，還需要考慮唯一索引)， key 應該是“庫名 + 表名 + 索引 a 的名字 +a 的值”
eg.在表 t1 上執行 update t1 set a=1 where id=2 語句(id為主鍵，a為唯一索引)
這個事務的 hash 表就有三項:
1.key=hash_func(db1+t1+“PRIMARY”+2), value=2; 這里 value=2 是因為修改前后的行 id 值不變，出現了兩次。
2.key=hash_func(db1+t1+“a”+2), value=1，表示會影響到這個表 a=2 的行。(a=2為修改前的值)
3.key=hash_func(db1+t1+“a”+1), value=1，表示會影響到這個表 a=1 的行。
按行分發策略的并行度更高。 但對于操作很多行的大事務的話，按行分發的策略有兩個問題：
1.耗費內存。比如一個語句要刪除 100 萬行數據，這時候 hash 表就要記錄 100 萬個項。
2.耗費 CPU。解析 binlog，然后計算 hash 值，對于大事務，這個成本還是很高的
所以實現按行分發策略的時候會設置一個閾值，單個事務如果超過設置的行數閾值（比如超過 10 萬行），就暫時退化為單線程模式
1.coordinator 暫時先 hold 住這個事務；
2.等待所有 worker 都執行完成，變成空隊列；
3.coordinator 直接執行這個事務；
4.恢復并行模式
MySQL 5.6 版本的并行復制策略
官方 MySQL5.6 版本，支持了并行復制，只是支持的粒度是按庫并行
優點:
1.構造 hash 值快，只需要庫名；一個實例上 DB 數不多，不會出現需要構造 100 萬個項這種情況。
2.不要求 binlog 的格式
但是對于單庫或者DB熱點不同，其效率就不是很好了(當然可以做分庫)
MariaDB 的并行復制策略
redo log 組提交 (group commit) 優化， 而 MariaDB 的并行復制策略利用的就是這個特性：
1.能夠在同一組里提交的事務，一定不會修改同一行；
2.主庫上可以并行執行的事務，備庫上也一定是可以并行執行的。
流程:
1.在一組里面一起提交的事務，有一個相同的 commit_id(遞增就行)
2.commit_id 直接寫到 binlog 里面；
3.傳到備庫應用的時候，相同 commit_id 的事務分發到多個 worker 執行；
4.這一組全部執行完成后，coordinator 再去取下一批。
不足：
1.并沒有徹底實現“真正的模擬主庫并發度”這個目標。在主庫上，一組事務在 commit 的時候，下一組事務是同時處于“執行中”狀態的。而這里不行
2.如果有大事務，就會由于當前事務沒有commit導致下一個組事務還不能開始執行
開啟并行復制后，事務是按照組來提交的，從庫也是根據commit_id來回放，如果從庫也開啟binlog的話，那是不是存在主從的binlog event寫入順序不一致的情況呢？
作者回復: 是有可能binlog event寫入順序不同的，好問題
MySQL 5.7 的并行復制策略
參數 : slave-parallel-type 參考了MariaDB 的并行復制策略思想
1.配置為 DATABASE，表示使用 MySQL 5.6 版本的按庫并行策略；
2.配置為 LOGICAL_CLOCK，表示的就是類似 MariaDB 的策略。不過，MySQL 5.7 這個策略，針對并行度做了優化。
MariaDB 這個策略的核心，是“所有處于 commit”狀態的事務可以并行。事務處于 commit 狀態，表示已經通過了鎖沖突的檢驗了
但其實，不用等到 commit 階段，只要能夠到達 redo log prepare 階段，就表示事務已經通過鎖沖突的檢驗了。
MySQL 5.7 并行復制策略的思想是：
1.同時處于 prepare 狀態的事務，在備庫執行時是可以并行的；
2.處于 prepare 狀態的事務，與處于 commit 狀態的事務之間，在備庫執行時也是可以并行的。
這兩個參數是用于故意拉長 binlog 從 write 到 fsync 的時間，以此減少 binlog 的寫盤次數。在 MySQL 5.7 的并行復制策略里，它們可以用來制造更多的“同時處于 prepare 階段的事務”
這兩個參數，既可以“故意”讓主庫提交得慢些，又可以讓備庫執行得快些。在 MySQL 5.7 處理備庫延遲的時候，可以考慮調整這兩個參數值，來達到提升備庫復制并發度的目的。
MySQL 5.7.22 的并行復制策略
MySQL 5.7.22 版本里，MySQL 增加了一個新的并行復制策略，基于 WRITESET 的并行復制。
新增了一個參數 binlog-transaction-dependency-tracking，控制是否啟用這個新策略。
為了唯一標識，這個 hash 值是通過“庫名 + 表名 + 索引名 + 值”計算出來的。如果一個表上除了有主鍵索引外，還有其他唯一索引，那么對于每個唯一索引，insert 語句對應的 writeset 就要多增加一個 hash 值。
最大的優勢(比起按行分發策略):
writeset是在主庫生成后并直接寫入binlog的，對binlog的格式也沒有要求(因為備庫分發依賴于主庫生成的writeset而不是binlog內容)
對于“表上沒主鍵”和“外鍵約束”的場景，WRITESET 策略也是沒法并行的，也會暫時退化為單線程模型。
課后問題:
假設一個MySQL 5.7.22版本的主庫，單線程插入了很多數據，過了3個小時后，我們要給這個主庫搭建一個相同版本的備庫。
這時候，你為了更快地讓備庫追上主庫，要開并行復制。在binlog-transaction-dependency-tracking參數的COMMIT_ORDER、WRITESET和WRITE_SESSION這三個取值中，你會選擇哪一個呢？
你選擇的原因是什么？如果設置另外兩個參數，你認為會出現什么現象呢？
應該將這個參數設置為 WRITESET。
由于主庫是單線程壓力模式，所以每個事務的 commit_id 都不同，那么設置為 COMMIT_ORDER 模式的話，從庫也只能單線程執行。
同樣地，由于 WRITESET_SESSION 模式要求在備庫應用日志的時候，同一個線程的日志必須與主庫上執行的先后順序相同，也會導致主庫單線程壓力模式下退化成單線程復制。
所以，應該將 binlog-transaction-dependency-tracking 設置為 WRITESET。
27 | 主庫出問題了，從庫怎么辦？-- 主備切換
一主多從架構下，主庫發生故障后
A’會成為新的主庫，從庫B、C、D也要改接到A’。
基于位點的主備切換
切換主庫時，從庫需要執行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_posMASTER_LOG_FILE 和 MASTER_LOG_POS 表示，要從主庫的 master_log_name 文件的 master_log_pos 這個位置的日志繼續同步,也就是同步位點
一種取同步位點的方法是這樣的：
因為同步位點并不是精確的，可能會出現bin log重復執行的情形，出現主鍵沖突等錯誤。通常情況下，我們在切換任務的時候，要先主動跳過這些錯誤，有兩種常用的方法。
1.主動跳過一個事務。
set global sql_slave_skip_counter=1;
start slave;
sql_slave_skip_counter 跳過的是一個 event，由于 MySQL 總不能執行一半的事務，所以既然跳過了一個 event，就會跳到這個事務的末尾，因此 set global sql_slave_skip_counter=1;start slave 是可以跳過整個事務的。
2.通過設置 slave_skip_errors 參數，直接設置跳過指定的錯誤。
在執行主備切換時，有這么兩類錯誤，是經常會遇到的：
1.1062 錯誤是插入數據時唯一鍵沖突；
2.1032 錯誤是刪除數據時找不到行。
GTID
GTID 的全稱是 Global Transaction Identifier，也就是全局事務 ID，是一個事務在提交的時候生成的，是這個事務的唯一標識。
它由兩部分組成，GTID=server_uuid:gno
server_uuid 是一個實例第一次啟動時自動生成的，是一個全局唯一的值；gno 是一個整數，初始值是 1，每次提交事務的時候分配給這個事務(回滾的不會算入)，并加 1
GTID 模式的啟動: 啟動一個 MySQL 實例的時候，加上參數 gtid_mode=on 和 enforce_gtid_consistency=on
這個 GTID 有兩種生成方式，而使用哪種方式取決于 session 變量 gtid_next 的值。
1.如果 gtid_next=automatic，代表使用默認值。
這時，MySQL 就會把 server_uuid:gno 分配給這個事務。
a. 記錄 binlog 的時候，先記錄一行 SET @@SESSION.GTID_NEXT=‘server_uuid:gno’;
b. 把這個 GTID 加入本實例的 GTID 集合。
2.如果 gtid_next 是一個指定的 GTID 的值，比如通過 set gtid_next='current_gtid’指定為 current_gtid，那么就有兩種可能：
a. 如果 current_gtid 已經存在于實例的 GTID 集合中，接下來執行的這個事務會直接被系統忽略；
b. 如果 current_gtid 沒有存在于實例的 GTID 集合中，就將這個 current_gtid 分配給接下來要執行的事務，也就是說系統不需要給這個事務生成新的 GTID，因此 gno 也不用加 1。
一個 current_gtid 只能給一個事務使用。這個事務提交后，如果要執行下一個事務，就要執行 set 命令，把 gtid_next 設置成另外一個 gtid 或者 automatic
使用GTID的話，如果insert sql是重復的，可以通過把對應的GTID添加到從庫的GTID的集合中來避免出現重復key的異常
GTID 的主備切換
GTID模式下的切換命令
CHANGE MASTER TO MASTER_HOST=$host_name MASTER_PORT=$port MASTER_USER=$user_name MASTER_PASSWORD=$password master_auto_position=1實例 A’的 GTID 集合記為 set_a，實例 B 的 GTID 集合記為 set_b。
主備切換的時候，B先將set_b發送給A’,計算出set_a和set_b之間的差集 (因為A’和B都是原來A的從庫，bin log執行的速度可能不一樣，所以兩個實例的GTID集合不一定是一致的)
判斷 A’本地是否包含了這個差集需要的所有 binlog 事務。
a. 如果不包含，表示 A’已經把實例 B 需要的 binlog 給刪掉了，直接返回錯誤；
b. 如果確認全部包含，A’從自己的 binlog 文件里面，找出第一個不在 set_b 的事務，發給 B；
之后就從這個事務開始，往后讀文件，按順序取 binlog 發給 B 去執行。
隱含條件：在基于GTID的主備關系里，系統認為只要建立主備關系，就必須保證主庫發給備庫的日志是完整的。因此，如果實例B需要的日志已經不存在，A’就拒絕把日志發給B。
GTID 和在線 DDL
業務高峰期的慢查詢性能問題時，分析到如果是由于索引缺失引起的性能問題，我們可以通過在線加索引來解決。但是，考慮到要避免新增索引對主庫性能造成的影響，我們可以先在備庫加索引，然后再切換。
當時我說，在雙 M 結構下，備庫執行的 DDL 語句也會傳給主庫，為了避免傳回后對主庫造成影響，要通過 set sql_log_bin=off 關掉 binlog
評論區有位同學提出了一個問題：這樣操作的話，數據庫里面是加了索引，但是 binlog 并沒有記錄下這一個更新，是不是會導致數據和日志不一致？
假設，這兩個互為主備關系的庫還是實例 X 和實例 Y，且當前主庫是 X，并且都打開了 GTID 模式。
這時的主備切換流程可以變成下面這樣：
1.在實例 X 上執行 stop slave。
2.在實例 Y 上執行 DDL 語句。（不需要關閉 binlog）。執行完成后，查出這個 DDL 語句對應的 GTID，并記為 server_uuid_of_Y:gno。
3.到實例 X 上執行以下語句序列：
set GTID_NEXT=“server_uuid_of_Y:gno”;
begin;
commit;
set gtid_next=automatic;
start slave;
接下來，執行完主備切換，然后照著上述流程再執行一遍即可。
通過GTID,讓實例 Y 的更新有 binlog 記錄，同時也可以確保不會在實例 X (主庫)上執行這條更新。
課后問題: 在 GTID 模式下，如果一個新的從庫接上主庫，但是需要的 binlog 已經沒了，要怎么做？
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。
28 | 讀寫分離有哪些坑？
客戶端直連方案，
因為少了一層 proxy 轉發，所以查詢性能稍微好一點兒，并且整體架構簡單，排查問題更方便。但在出現主備切換、庫遷移等操作的時候，客戶端都會感知到，并且需要調整數據庫連接信息。一般采用這樣的架構，一定會伴隨一個負責管理后端的組件，比如 Zookeeper，盡量讓業務端只專注于業務邏輯開發。
帶 proxy 的架構，對客戶端比較友好
客戶端不需要關注后端細節，連接維護、后端信息維護等工作，都是由 proxy 完成的。但這樣的話，對后端維護團隊的要求會更高。而且，proxy 也需要有高可用架構。因此，帶 proxy 架構的整體就相對比較復雜
由于主從可能存在延遲，客戶端執行完一個更新事務后馬上發起查詢，如果查詢選擇的是從庫的話，就有可能讀到剛剛的事務更新之前的狀態。
這種“在從庫上會讀到系統的一個過期狀態”的現象，在這篇文章里，我們暫且稱之為“過期讀”
強制走主庫方案
將查詢請求做分類，需要實時讀取到最新的數據的走主庫，可以接受延遲的走從庫
但是對于要求所有查詢都不能是過期讀的，就沒法進行讀寫分離了
Sleep 方案
主庫更新后，讀從庫之前先 sleep 一下。具體的方案就是，類似于執行一條 select sleep(1) 命令。
這個方案存在的問題就是不精確。
這個不精確包含了兩層意思：
1.如果這個查詢請求本來 0.5 秒就可以在從庫上拿到正確結果，也會等 1 秒；
2.如果延遲超過 1 秒，還是會出現過期讀
判斷主備無延遲方案
show slave status 結果里的 seconds_behind_master 參數的值，可以用來衡量主備延遲時間的長短。
第一種確保主備無延遲的方法是，每次從庫執行查詢請求前，先判斷 seconds_behind_master 是否已經等于 0。如果還不等于 0 ，那就必須等到這個參數變為 0 才能執行查詢請求
show slave status結果
第二種方法，對比位點確保主備無延遲：
Master_Log_File 和 Read_Master_Log_Pos，表示的是讀到的主庫的最新位點；
Relay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是備庫執行的最新位點
這兩組值完全相同，就表示接收到的日志已經同步完成
第三種方法，對比 GTID 集合確保主備無延遲：
- Auto_Position=1 ，表示這對主備關系使用了 GTID 協議。
- Retrieved_Gtid_Set，是備庫收到的所有日志的 GTID 集合；
- Executed_Gtid_Set，是備庫所有已經執行完成的 GTID 集合。
如果這兩個集合相同，也表示備庫接收到的日志都已經同步完成
但還是有一個問題,上面判斷主備無延遲的邏輯，是“備庫收到的日志都執行完成了”。但是，從 binlog 在主備之間狀態的分析中，不難看出還有一部分日志，處于客戶端已經收到提交確認，而備庫還沒收到日志的狀態。也就是在從主庫向從庫傳遞binlog的過程中還會存在一部分延遲的數據
配合 semi-sync
半同步復制
semi-sync 做了這樣的設計：
semi-sync 配合前面關于位點的判斷，就能夠確定在從庫上執行的查詢請求，可以避免過期讀。
但是，semi-sync+ 位點判斷的方案，只對一主一備的場景是成立的。在一主多從場景中，主庫只要等到一個從庫的 ack，就開始給客戶端返回確認
判斷同步位點的方案還有另外一個潛在的問題，即：如果在業務更新的高峰期，主庫的位點或者 GTID 集合更新很快，那么上面的兩個位點等值判斷就會一直不成立，很可能出現從庫上遲遲無法響應查詢請求的情況
當發起一個查詢請求以后，我們要得到準確的結果，其實并不需要等到“主備完全同步”。 只需要當前查詢的結果在從庫已經同步就行了
semi-sync 配合判斷主備無延遲的方案，存在兩個問題：
1.一主多從的時候，在某些從庫執行查詢請求會存在過期讀的現象；
2.在持續延遲的情況下，可能出現過度等待的問題。
等主庫位點方案
select master_pos_wait(file, pos[, timeout]);
它是在從庫執行的；
參數 file 和 pos 指的是主庫上的文件名和位置；
timeout 可選，設置為正整數 N 表示這個函數最多等待 N 秒。
這個命令正常返回的結果是一個正整數 M，表示從命令開始執行，到應用完 file 和 pos 表示的 binlog 位置，執行了多少事務。
如果執行期間，備庫同步線程發生異常，則返回 NULL；
如果等待超過 N 秒，就返回 -1；
如果剛開始執行的時候，就發現已經執行過這個位置了，則返回 0
采用下面的流程就可以避免出現上述主備無延遲配合半同步復制情形下的問題：
等待GTID 方案
前提是開啟了GTID模式
select wait_for_executed_gtid_set(gtid_set, 1);
這條命令的邏輯是：等待，直到這個庫執行的事務中包含傳入的 gtid_set，返回 0；超時返回 1。
MySQL 5.7.6 版本開始，允許在執行完更新類事務后，把這個事務的 GTID 返回給客戶端（將參數 session_track_gtids 設置為 OWN_GTID，然后通過 API 接口 mysql_session_track_get_first 從返回包解析出 GTID 的值），這樣等 GTID 的方案就可以減少一次查詢。
https://dev.mysql.com/doc/refman/5.7/en/c-api-functions.html
具體流程和等主庫位點的類似
課后問題: 如果使用 GTID 等位點的方案做讀寫分離，在對大表做 DDL 的時候會怎么樣。
假設，這條語句在主庫上要執行 10 分鐘，提交后傳到備庫就要 10 分鐘（這里的10min就是指DDL在主庫的執行時間，典型的大事務）。那么，在主庫 DDL 之后再提交的事務的 GTID，去備庫查的時候，就會等 10 分鐘才出現。
這樣，這個讀寫分離機制在這 10 分鐘之內都會超時，然后走主庫。這種預期內的操作，應該在業務低峰期的時候，確保主庫能夠支持所有業務查詢，然后把讀請求都切到主庫，再在主庫上做 DDL。等備庫延遲追上以后，再把讀請求切回備庫。
使用gh-ost方案來解決這個問題也是不錯的選擇
我的疑問：
那10min是等DDL執行完，并寫完binlog可以發送到從庫，期間其他更新SQL在主庫不是也可以正常執行嗎，那相關的操作不是也能寫入binlog,為什么不能發送給從庫執行啊？？
今天的問題，大表做DDL的時候可能會出現主從延遲，導致等 GTID 的方案可能會導致這部分流量全打到主庫，或者全部超時。如果這部分流量太大的話，我會選擇上一篇文章介紹的兩種方法：1.在各個從庫先SET sql_log_bin = OFF，然后做DDL，所有從庫及備主全做完之后，做主從切換，最后在原來的主庫用同樣的方式做DDL。2.從庫上執行DDL；將從庫上執行DDL產生的GTID在主庫上利用生成一個空事務GTID的方式將這個GTID在主庫上生成出來。各個從庫做完之后再主從切換，然后再在原來的主庫上同樣做一次。需要注意的是如果有MM架構的情況下，承擔寫職責的主庫上的slave需要先停掉。
29 | 如何判斷一個數據庫是不是出問題了？
select 1 判斷
select 1 成功返回，只能說明這個庫的進程還在，并不能說明主庫沒問題
set global innodb_thread_concurrency=3;
innodb_thread_concurrency 參數的目的是，控制 InnoDB 的并發線程上限。也就是說，一旦并發線程數達到這個值，InnoDB 在接收到新請求的時候，就會進入等待狀態，直到有線程退出
通常情況下，我們建議把 innodb_thread_concurrency 設置為 64~128 之間的值–這主要是針對并發查詢(在線程進入鎖等待以后，并發線程的計數會減一，也就是說等行鎖（也包括間隙鎖）的線程是不算在并發線程數里面的。)
并發連接和并發查詢，并不是同一個概念。
你在 show processlist 的結果里，看到的幾千個連接，指的就是并發連接。而“當前正在執行”的語句，才是我們所說的并發查詢
查表判斷
一般的做法是，在系統庫（mysql 庫）里創建一個表，比如命名為 health_check，里面只放一行數據，然后定期執行
mysql> select * from mysql.health_check;
但是針對binlog空間滿了情形又不能檢測出來
更新事務要寫 binlog，而一旦 binlog 所在磁盤的空間占用率達到 100%，那么所有的更新語句和事務提交的 commit 語句就都會被堵住。但是，系統這時候還是可以正常讀數據的
更新判斷
mysql> update mysql.health_check set t_modified=now();
這樣會有新的問題:主庫要判斷庫是否可用，備庫也需要，那么就都需要執行上述更新語句,而雙M架構下主庫的binlog又會發送給備庫，更新同一行數據就可能出現行沖突，也就是可能會導致主備同步停止–需要多行(以server_id為主鍵)
但有可能，機器的I/O已經100%，但剛好健康檢查的sql拿到了資源，成功返回了 – 出現了誤判,判定延遲
內部統計
MySQL 5.6 版本以后提供的 performance_schema 庫，就在 file_summary_by_event_name 表里統計了每次 IO 請求的時間。
圖中這一行表示統計的是redo log的寫入時間，第一列EVENT_NAME 表示統計的類型。
剩下的三組分表是所有IO操作的統計，讀操作的統計以及寫操作的統計
SUM_NUMBER_OF_BYTES_READ統計的是，總共從redo log里讀了多少個字節。
最后的第四組數據，是對其他類型數據的統計。在redo log里，你可以認為它們就是對fsync的統計。
建議只打開自己需要的項進行統計。你可以通過下面的方法打開或者關閉某個具體項的統計。
打開redolog的時間監控
mysql> update setup_instruments set ENABLED=‘YES’, Timed=‘YES’ where name like ‘%wait/io/file/innodb/innodb_log_file%’;
可以通過 MAX_TIMER 的值來判斷數據庫是否出問題了。比如，設定閾值，單次 IO 請求時間超過 200 毫秒屬于異常
mysql> select event_name,MAX_TIMER_WAIT FROM performance_schema.file_summary_by_event_name where event_name in (‘wait/io/file/innodb/innodb_log_file’,‘wait/io/file/sql/binlog’) and MAX_TIMER_WAIT>200* 1000000000;
發現異常后，取到你需要的信息，再通過下面這條語句把之前的統計信息清空
mysql> truncate table performance_schema.file_summary_by_event_name;
我個人比較傾向的方案，是優先考慮 update 系統表，然后再配合增加檢測 performance_schema 的信息。
總結
以上是生活随笔為你收集整理的《MySQL实战45讲》实践篇 24-29 学习笔记 (主备篇)的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 《MySQL实战45讲》实践篇 9-15
- 下一篇： 让互联网更快的协议，QUIC在腾讯的实践