丁奇老師《MySQL實戰45講》的學習小結
第一篇：基礎概念
第二篇：運維管理
之前對資料庫主要是使用，運維管理做得很少，主要是備份、備份、備份 ^_^
通過這門課，在運維管理這方面學到的知識最多，學習内容的整理也最吃力。
照例從問題入手。
要了解MySQL在運維方面的機制，首先要了解資料庫運維面對的問題：
- 資料庫服務崩潰，但存儲器正常，如何恢複？
- 伺服器損毀，伺服器上資料損壞或丢失，如何恢複資料和服務？
- 誤删資料，如何恢複？
- 系統壓力超過單個資料庫伺服器的能力，如何分布到多個伺服器？
- 資料庫性能急劇下降，怎麼辦？
在處理這些問題之前，先了解MySQL的幾個運作機制，再來梳理如何應對這些問題。
第一部分：運作機制
1. 日志和主備同步（第24講，28講）
主庫和從庫之間的資料同步過程，如下圖所示：
從庫上有兩個線程。
io_thread負責向主庫發起請求，建立長連接配接，然後接收主庫發送的binlog，寫入relay log。從庫在發起請求時，可指定從哪個位置開始擷取binlog。
sql_thread負責讀取relay log，解析出日志裡的指令，并執行。
從一個事務在主庫上完成，到這個事務在從庫上完成，時間差即為同步延遲。
如果主庫掉電，事務已完成，但binlog尚未發送給備庫，會導緻資料丢失。
為了解決這個問題，可采用 semi-sync 模式。在主庫上，事務需等待至少一個從庫确認已收到日志，才能完成事務，向用戶端發送response。
主庫上有個dump_thread，負責監聽來自從庫的請求，并随時把binlog發送給從庫。
一個主庫可能要監聽多個從庫的請求，多個從庫請求的binlog起始位置也可能不同。
是以一個主庫不要帶很多個從庫，以免binlog的同步消耗太多資源。
2. 主備切換（第24講，25講，27講）
主備伺服器的配備，目前多采用對稱結構，即主備伺服器的硬體配置一緻，各一台，都提供讀寫服務，這樣一旦主庫出現問題，可以快速切換到備庫，對應用的影響最小。備庫雖然可讀寫，但正常情況下不做寫操作。以下的内容均以此為前提。
由于存在同步延遲，是以在主備切換時，從可靠性優先角度，常見做法是先把主庫設為隻讀，待同步延遲歸0後，再切換到備庫。通常情況下同步延遲小于1秒，這期間出現短暫的全庫隻讀，對應用的影響不大。（需要HA工具支援）
但如果遇到異常，導緻同步延遲很大，或者主庫掉電無法通路等情況，隻能選擇可用性優先政策。這種情況下，可能會出現資料錯誤，需要人工介入。
資料庫伺服器的配備，一主多從是目前使用最多的結構。
一主多從結構下，備庫是從庫的一種，它可能被切換為主庫，而其他從庫不會。
一主多從結構下的主備切換，如下圖所示：
《MySQL實戰54講》第27講 一主多從基本結構 -- 主備切換
在一主多從結構下，主備切換包括了從庫切換master的過程。
除了主備的資料同步延遲之外，還需要考慮各從庫的同步延遲，且每個從庫的同步延遲不盡相同。
位點：
5.6版本之前，MySQL隻能通過找位點的方式。
從庫跟主庫建立聯系時，可指定從哪個binlog檔案、哪個偏移量開始同步。這個檔案+偏移量就是位點。但對于同一個事務（sql），在主庫和備庫上，位點是不同的。可通過切換時間定位到一個近似值，但并不精确。需要人工介入。
GTID：
MySQL 5.6版本引入了GTID以解決這個問題。
GTID = server_uuid:gno
gno是在每個MySQL執行個體上，單調遞增的數值，在事務送出時配置設定。server_uuid + gno 的組合可以保證全局唯一。GTID被包含在binlog中，發給從庫。
每個MySQL執行個體都維護了一個GTID集合，記錄“這個執行個體執行過的所有事務”。
在主備切換時，從庫會發送本執行個體的GTID集合，例如 set B 給新的主庫 A'。新的主庫 A' 對比自己的和從庫B的GTID集合，先檢查set B在本執行個體上全部存在，再發送 set A' - set B 這些事務的binlog給從庫B，然後就回到正常的binlog同步。
GTID的全部集合很大，MySQL應該會定期移除曆史，不斷提高水位。這個教程中未涉及，是猜測。
GTID除了解決主備切換時的同步問題，還可以用來在同步時跳過特定的事務。
3. 緩存和落盤（第12講，23講）
為了提升性能，MySQL對資料的讀寫都優先在記憶體上進行，并選擇合适的時機将資料讀入記憶體、将修改結果同步到磁盤。不僅對資料如此，對日志也使用這個政策。
為了保證資料可靠性，需要控制redo log和binlog盡快寫入磁盤。
MySQL提供了兩個參數來控制：
- innodb_flush_log_at_trx_commit = 1 每次事務送出時都将 redo log 直接持久化到磁盤
- sync_binlog = 1 每次送出事務都會執行 fsync
redo log的刷盤有兩點說明：
- 根據上一篇redo log + binlog 實作crash-safe的機制，有binlog的情況下，有prepare狀态的redo log即可恢複資料。是以，一個事務隻需要等待redo log prepare刷盤，不用等redo log commit刷盤。
- redo log cache為共用，在一個事務送出時，把該事務最後一條日志及在此之前的所有redo log刷盤，實作group commit，提升IO效率。
在這樣的機制下，我們可以認為MySQL的資料（及log）不會丢失。
後續關于運維問題的小結，均在此基礎上進行。
第二部分：運維場景
1. 資料庫服務崩潰，但存儲器正常，如何恢複（第2講，23講）
先明确恢複的目标。
站在應用的角度，送出了事務，資料庫服務傳回response，即認為資料已更新。這類資料需要恢複。如果事務未送出，資料庫服務崩潰，則資料不應更新，資料庫恢複後的狀态，同僚務復原。
如果送出了事務，但未收到response伺服器連接配接就斷開了，事務的狀态依賴于資料庫的log來判斷。
詳見如下流程圖：
幾點說明：
- 用戶端發出“送出事務”指令後，伺服器成功傳回，則用戶端認為事務完成、資料已更新。但是，如果 2、3 這兩步都是異步請求的話，在伺服器崩潰時，binlog和redo log有可能都未被持久化。是以教程中建議采用“雙1”配置，保證在一個事務完成前，binlog和prepare狀态的redo log被持久化，以保證能恢複資料。
- 用戶端發出送出“送出事務”指令後，伺服器崩潰，用戶端沒有收到response，這種情況下，隻要binlog已持久化，即視為事務已完成，需要用相應的redo log恢複資料。
- 資料的持久化，即“flush髒頁”，必然是異步的。
2. 伺服器損毀，伺服器上資料損壞或丢失，如何恢複資料和服務
這個問題分兩種情況，一是主庫損毀，二是從庫損毀。兩種情況都要利用備份。
2.1 主庫損毀：
如果做了熱備份，就可以将備庫切換為新的主庫。見上文的“主備切換”。
如果原主庫上的binlog尚未被發送到備庫，會發生資料損失。
binlog被發送到備庫，轉存為relay log後再被執行。這裡有個時間差。隻要業務上能接受，應在relog log都被執行後再開放服務（至少把寫操作放在這個時間點後）。
如果沒有熱備，那就隻能用 全量備份 + 全量備份時間點之後的binlog備份 來恢複。資料損失大。
2.2 從庫損毀
從庫損毀，隻要主庫正常，資料不會丢失。重建從庫即可。
先把從庫恢複到某個時間點的資料版本，再執行這個時間點之後的binlog。前一步通過實體拷貝恢複，後一步為邏輯恢複。完成這個操作後，再從主庫擷取後續的binlog（用位點或GTID找齊）。
從庫損毀，主要考慮的是應用的影響，需要盡快發現問題，把原來指向從庫的請求重定向到其他庫，并通知運維人員，盡快重建從庫。
盡快發現資料庫出現異常，這很重要，早發現早處理，減少對業務造成的影響。這也是HA的基礎。
判斷資料庫是否出現問題，有很多種方式，見第29講，這裡不再贅述。
3. 誤删資料，如何恢複（第31講）
誤删行：
根據binlog，flashback（binlog_format=row，binlog_row_image=FULL）
恢複操作不要直接在主庫上執行。在别的庫上執行，并确認後，再将其恢複回主庫。
誤删庫/表：
全量備份，加增量日志恢複。重放增量日志時，跳過誤操作的那個語句。最好使用GTID模式。
要求線上有定期的全量備份，并且實時備份binlog。
因為全量備份可能是數天前的，重放增量日志很慢，要着重考慮如何加速恢複速度。
rm删除資料
即上一條，伺服器損毀、資料損壞的情況。
建立有高可用機制的MySQL叢集應對這種情況，并做好備份。
強調兩點：
- 做好預案，盡量把恢複過程固化為工具，定期演練。
- 預防資料誤删，例如：隻配置設定必要的權限，删除前先做标記（更新、重命名）。
4. 系統壓力超過單個資料庫伺服器的能力，如何分布到多個伺服器（第28講）
一主多從結構，主庫可讀寫，從庫隻讀，這是典型的讀寫分離結構，以分攤主庫的壓力。
在這種結構下，用戶端通路資料庫服務，有兩種方案
- 用戶端自行制定通路哪個庫
- 通過一個proxy層，由proxy來選擇通路哪個庫
兩種方案，都會遇到“過期讀”的問題。即由于存在同步延遲，在從庫上讀到的不是最新資料。
存在幾種不同的處理方案：
- 強制走主庫，由用戶端判斷業務上是否必須讀取最新的資料，是則指定通路主庫。
- sleep，多用在寫完之後的讀，例如發帖後的檢視，控制延遲1秒再發檢視請求。看起來很笨拙，但很實用。主從延遲，正常情況下不超過1s。
-
等待從庫擷取最新資料，這又可細分3種方法
- 等待 seconds_behind_master = 0
- 先查已接收的最新位點，等待從庫執行的最新位點跟前者一緻
- 先查備庫已收到的所有日志的GTID集合，等待這些事務在從庫上都已經被執行
後兩種方法，隻能保證已接收的日志都已經被執行，無法處理主庫執行後，從庫還未接收日志的情況。為了解決這個問題，需要結合 semi-sync 機制。
5. 資料庫性能急劇下降，怎麼辦（第22講）
首先分析原因，按照原因，對症下藥。必要時，犧牲一定的可靠性，來提升可用性。
可能的原因：
連接配接風暴、慢查詢、QPS突增、長事務、online DDL、死鎖檢測。
應對政策
- 白名單、賬号管理，可用于屏蔽來自特定伺服器、特定應用的請求，避免局部問題拖垮整體。
- sql動态重寫，可臨時屏蔽大量占用資源的問題sql，或強制其使用正确的索引。
- online DDL是風險比較高的操作，最好在從庫上執行，然後做主從切換，再在原主庫上執行。
- 長事務往往會帶來并發問題，應進行監控，發現問題，及早解決。
這裡要再次強調下：
- “消滅問題”優于“解決問題”。設立規範、流程，減少人為操作失誤。良醫治未病。
- 建立監控體系，定期監測長事務、慢SQL等問題，盡早發現，不讓小問題堆積成大問題。
- 問題應對方法流程化、工具化，定期演練，遇到問題時才能快速響應。功夫在平時。
本文内容為丁奇老師《MySQL實戰45講》的學習筆記，隻是一個提綱。
這門課程的内容群組織方式，每一講的思考題、大家的留言、老師的點評，都非常棒。
繼續推薦。
黃鶴
2019-12-03