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

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

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

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 表的語句時，備庫在回放該建表語句時就會出現問題。

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

主備切換時，盡量在切換後對比資料，查清楚是否有不一緻的表或庫。

一定不要在備庫執行寫操作。

4 丢失的臨時表

臨時表和基于語句的複制方式不相容。如果備庫崩潰或者正常關閉，任何複制線程擁有的臨時表都會丢失。重新開機備庫後，所有依賴于該臨時表的語句都會失敗。

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

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

臨時表的特性：

隻對建立臨時表的連接配接可見。不會和其他擁有相同名字的臨時表的連接配接起沖突；

随着連接配接關閉而消失，無須顯式的移除它們。

4.1 更好使用臨時表的方式

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

僞臨時表的優劣勢

優勢：

更容易調試應用程式。可以通過别的連接配接來檢視應用正在維護的資料；

劣勢：

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

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

使用共享鎖，串行化更新，保證備庫複制時資料一緻。

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

兩個事務更新資料，使用共享鎖串行化更新

事務 1 使用擷取 tab2 資料時，加入共享鎖，并插入 tab1；

同時，事務 2 更新 tab2 資料時，由于寫操作的排它鎖機制，無法擷取 tab2 的鎖，等待；

事務 1 插入資料後，删除共享鎖，送出事務，寫入 binlog（此時 tab1 和 tab2 的記錄值 都是 99）；

事務 2 擷取到鎖，更新資料，送出事務，寫入 binlog（此時 tab1 的記錄值為 99，tab2 的記錄值為 100）。

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

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

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

事務 1 無鎖讀取 tab2 資料，并插入 tab1（此時 tab1 和 tab2 的記錄值 都是 99）；

同時，事務 2 更新 tab2 資料，先與事務 1 送出事務，寫入 binlog（此時 tab1 的記錄值為 99，tab2 的記錄值為 100）；

事務 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 複制延遲過大

産生延遲的兩種方式

突然産生延遲，然後再跟上；

穩定的延遲增大

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

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

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

使用 InnoDB 引擎時，設定 innodb_flush_log_at_trx_commit 值為 2，來使備庫不要頻繁的重新整理磁盤，以提高事務送出效率。

禁止二進制日志記錄。把 innodb_locks_unsafe_for_binlog 設定為 1，并把 MyISAM 的 delay_key_write 設定為 ALL。要注意的是，這些設定是以安全換取速度，在将備庫提升為主庫時，記得把這些選項設定回安全的值。

拆分效率較低的複制 SQL，分離複雜語句中的 SELECT 和 UPDATE 語句，降低複制消耗，提高效率。

總結

複制問題要厘清楚是 master 的問題，還是 slave 的問題。

master 問題找 binlog，slave 問題找 relaylog。

原文位址

https://www.cnblogs.com/BeiGuo-FengGuang/p/10666693.html