MySQL 複製 - 效能與擴充套件性的基石 3：常見問題及解決方案

北國丶風光發表於2019-04-08
MySql套件

主備複製過程中有很大可能會出現各種問題，接下來我們就討論一些比較普遍的問題，以及當遇到這些問題時，如何解決或者預防問題發生。

1 資料損壞或丟失

問題描述：伺服器崩潰、斷電、磁碟損壞、記憶體或網路錯誤等問題，導致資料損壞或丟失。 問題原因：非正常關機導致沒有把資料及時的寫入硬碟。

這種問題，一般可以分為幾種情況導致：

1.1 主庫意外關閉

問題未發生，避免方案：設定主庫的 sync_binlog 選項為 1。此選項表示 MySQL 是否控制 binlog 的重新整理。當設定為 1 時，表示每次事務提交，MySQL 都會把 binlog 刷下去，是最安全，效能損耗也最大的設定。 問題已發生，解決方案：指定備庫從下一個二進位制日誌的開頭重新讀日誌。但是一些日誌事件將永久性丟失。可以使用 Percona Toolkit 中的 pt-table-checksum 工具來檢查主備一致性，以便於修復。

1.2 備庫意外關閉

備庫意外關閉重啟時，會去讀 master.info 檔案以找到上次停止複製的位置。但是在意外關閉的情況下，這個檔案儲存的資訊可能是錯誤的。此外，備庫也可能會嘗試重新執行一些二進位制檔案，這可能會導致唯一索引錯誤。我們可以通過 Percona Toolkit 中的 pt-slave-restart 工具，幫助備庫重新執行日誌檔案。

如果使用的是 InnoDB 表，可以在重啟後觀察 MySQL 的錯誤日誌。InnoDB 在恢復過程中會列印出恢復點的二進位制日誌座標，可以使用這個值來決定備庫指向主庫的偏移量。

1.3 主庫二進位制日誌損壞

如果主庫上的二進位制日誌損壞，除了忽略損壞的位置外，別無選擇。在忽略存貨位置後，我們可以通過 FLUSH LOGS 命令在主庫開始一個新的日誌檔案，然後將備庫指向該檔案的開始位置。

1.4 備庫中繼日誌損壞

如果主庫上的日誌是完好的，有兩種解決方案： 1) 手工處理。找到 master binlog 日誌的 pos 點，然後重新同步。

2) 自動處理。mysql5.5 考慮到 slave 當機中繼日誌損壞這一問題，只要在 slave 的的配置檔案 my.cnf 裡增加一個引數 relay_log_recovery=1 即可。

1.5 二進位制日誌與 InnoDB 事務日誌不同步

由於各種各樣的原因，MySQL 的複製碰到伺服器崩潰、斷電、磁碟損壞、記憶體或網路錯誤時，很難恢復當時丟失的資料。幾乎都需要從某個點開始重啟複製。

2 未定義的伺服器 ID

如果沒有再 my.cnf 裡定義伺服器 ID，雖然可以通過 CHANGE MASTER TO 來設定備庫，但在啟動複製時會遇到：

mysql> START SLAVE;
ERROR 1200 (HY000): The server us bit configured as slave; fix in config file or with CHANGE MASTER TO
複製程式碼

這個報錯可能會讓人困惑。因為我們可能已經通過 CHANGE MASTER TO 設定了備庫，並且通過 SHOW MASTER STATUS 也確認了，為什麼還會有這樣的報錯呢？我們通過 SELECT @@server_id 可以獲得一個值，要注意的是，這個值只是預設值，我們必須為備庫顯式地設定伺服器 ID。也就是在 my.cnf 裡顯示的設定伺服器 ID。

3 對未複製資料的依賴性

如果在主庫上有備庫上不存在的資料庫或資料表，複製就很容易中斷，反之亦然。 對於前者，假設在主庫上有一個 single_master 表，備庫沒有。在主庫上對此表進行操作後，備庫在嘗試回放這些操作時就會出現問題，導致複製中斷。

對於後者，假裝置庫上有一個 single_slave 表，主庫沒有。在主庫上執行建立 single_slave 表的語句時，備庫在回放該建表語句時就會出現問題。

對於此問題，我們能做的就是做好預防：

  1. 主備切換時，儘量在切換後對比資料，查清楚是否有不一致的表或庫。
  2. 一定不要在備庫執行寫操作。

4 丟失的臨時表

臨時表和基於語句的複製方式不相容。如果備庫崩潰或者正常關閉，任何複製執行緒擁有的臨時表都會丟失。重啟備庫後，所有依賴於該臨時表的語句都會失敗。

複製時出現找不到臨時表的異常時，可以做：

  1. 直接跳過錯誤，或者手動地建立一個名字和結構相同的表來代替消失的的臨時表。

臨時表的特性：

  1. 只對建立臨時表的連線可見。不會和其他擁有相同名字的臨時表的連線起衝突；
  2. 隨著連線關閉而消失，無須顯式的移除它們。

4.1 更好使用臨時表的方式

保留一個專用的資料庫，在其中建立持久表，把它們作為偽臨時表，以模擬臨時表特性。只需要通過 CONNETCTION_ID() 的返回值，給臨時表建立唯一的名字。

偽臨時表的優劣勢 優勢：

  1. 更容易除錯應用程式。可以通過別的連線來檢視應用正在維護的資料；

劣勢：

  1. 比臨時表多一些開銷。建立較慢偽臨時表會較慢，因為表的 .frm 檔案需要重新整理到磁碟。

5 InnoDB 加鎖讀導致主備資料不一致

使用共享鎖，序列化更新，保證備庫複製時資料一致。

某些情況下，加鎖讀可以防止混亂。假設有兩張表：tab1 沒有資料，tab2 只有一行資料，值為 99。此時，有兩個事務更新資料。事務 1 將 tab2 的資料插入到 tab1，事務 2 更新 tab2。

兩個事務更新資料，使用共享鎖序列化更新

  1. 事務 1 使用獲取 tab2 資料時，加入共享鎖，並插入 tab1；
  2. 同時，事務 2 更新 tab2 資料時，由於寫操作的排它鎖機制，無法獲取 tab2 的鎖，等待；
  3. 事務 1 插入資料後，刪除共享鎖，提交事務，寫入 binlog（此時 tab1 和 tab2 的記錄值 都是 99）；
  4. 事務 2 獲取到鎖，更新資料，提交事務，寫入 binlog（此時 tab1 的記錄值為 99，tab2 的記錄值為 100）。

上述過程中，第二步非常重要。事務 2 嘗試去更新 tab2 表，這需要在更新的行上加排他鎖（寫鎖）。排他鎖與其他鎖不相容，包括事務 1 在行記錄上加的共享鎖。因此事務 2 需要等待事務 1 完成。備庫在根據 binlog 進行復制時，會按同樣的順序先執行事務 1，再執行事務 2。主備資料一致。

同樣的過程，如果事務 1 在第一步時沒有加共享鎖，流程就變成：

兩個事務更新資料，未使用共享鎖序列化更新

  1. 事務 1 無鎖讀取 tab2 資料，並插入 tab1（此時 tab1 和 tab2 的記錄值 都是 99）；
  2. 同時，事務 2 更新 tab2 資料，先與事務 1 提交事務，寫入 binlog（此時 tab1 的記錄值為 99，tab2 的記錄值為 100）；
  3. 事務 1 提交事務，寫入 binlog（此時記錄值無變化）；

mysqldump --single-transaction --all-databases --master-data=1 --host=server1 | mysql --host=server2 要注意的是，上述過程中，事務 2 先提交，先寫入 binlog。在備庫複製時，同樣先執行事務 2，將 tab2 的記錄值更新為 100。然後執行事務 1，讀取 tab2 資料，插入 tab1，所以最終的結果是，tab1 的記錄值和 tab2 的記錄值都是 100。很明顯，資料和主庫有差異。

建議在大多數情況下將 innodb_unsafe_for_binlog 的值設定為 0。基於行的複製由於記錄了資料的變化而非語句，因此不會存在這個問題。

6 複製延遲過大

產生延遲的兩種方式

  1. 突然產生延遲，然後再跟上；
  2. 穩定的延遲增大

前者通常是由於一條執行時間過長的 SQL 導致，而後者即使在沒有慢語句也會出現。

對於前者，我們可以通過備庫上的慢查詢日誌來進行優化。在備庫上開啟 log_slow_slave_statement 選項，可以在慢查詢日誌中記錄複製執行緒執行的語句。

而對於後者，沒有針對性的解決方案，只能通過各種方式提高備庫的複製效率。而當我們想去對備庫做優化時，會發現，除了購買更快的磁碟和 CPU，並沒有太多的調優空間。只能通過 MySQL 選項禁止某些額外的工作以減少備庫的複製。可以通過下面幾種方式：

  1. 使用 InnoDB 引擎時，設定 innodb_flush_log_at_trx_commit 值為 2，來使備庫不要頻繁的重新整理磁碟，以提高事務提交效率。
  2. 禁止二進位制日誌記錄。把 innodb_locks_unsafe_for_binlog 設定為 1，並把 MyISAM 的 delay_key_write 設定為 ALL。要注意的是，這些設定是以安全換取速度，在將備庫提升為主庫時，記得把這些選項設定回安全的值。
  3. 拆分效率較低的複製 SQL，分離複雜語句中的 SELECT 和 UPDATE 語句，降低複製消耗，提高效率。

總結

  1. 複製問題要分清楚是 master 的問題，還是 slave 的問題。
  2. master 問題找 binlog，slave 問題找 relaylog。

相關文章