複製錯誤案例分享（一）

MySQL Replication是MySQL非常重要的特性。用好了，可以發揮很大的作用，做負載均衡，做讀寫分離，做備份等等，能在關鍵時刻救DBA一命；用不好，那就是給DBA自己找麻煩了，處理不盡的故障。所以我這邊給大家分享幾個關於複製的案例。

|  案例一：binlog_format=MIXED導致的主從資料不一致

環境資訊

• 作業系統 Red Hat 6.7

• 資料庫版本 MySQL5.6.36

• 主從IP 

            主庫：192.168.1.36 

            從庫：192.168.1.57

• 資料庫引數配置 sync_binlog=1 傳統複製，即非GTID複製

故障重現

• 將兩臺資料庫搭建成為主從架構（此處 省略 搭建步驟）

• 在主庫（192.168.1.36）上建立測試表格，並插入測試資料

• 在從庫（192.168.1.57）上檢查資料以及複製狀態

• 接著在從庫（192.168.1.57）上執行語句更新資料

• 在主庫（192.168.1.36）上執行語句更新資料

• 在從庫（192.168.1.57）上檢查資料和複製狀態，可以看到主庫的操作並沒有在從庫上生效，並且主從的複製狀態也是正常的。

現象

在測試步驟中我們可以看到，在從庫更新資料之後，主庫上的更新操作在從庫上沒有生效，但是檢視複製狀態一切正常。僅從show slave status\G中檢視到的資訊，我們認為目前主從的複製是正常的，但是考慮實際的資料，主從的資料已經不一致了。

故障分析

看到主庫的更新操作沒有在從庫上應用，首先考慮，這個事務的binlog是否真的被從庫接收到。於是檢查從庫上的relay log，使用mysqlbinlog工具解析relay log

從relay log中可以看到，主庫上的更新操作在從庫上是接收到了的。接著根據  show slave status\G 的資訊，也可以確定該事務是被sql執行緒應用了的。再仔細一看這個 relay log 發現，這個 update 操作是被以STATEMENT的格式儲存下來，並複製到從庫。所以在從庫上只是簡單的執行這個語句。並且因為從庫上int_b=1的記錄已經被修改為  int_b=2 ，從而在從庫上執行這個語句的時候，找不到符合相應條件的記錄需要修改。 這個更新操作是執行了的，只是沒有找到符合where條件的記錄。所以  show slave status\G  檢視複製狀態也是正常。但是主從資料不一致了。 所以，在複製架構中一定要強調不要隨便在從庫上執行insert、update、delete等操作，因為極有可能做了相應的操作之後，主從資料不一致，複製狀態正常。應用查詢資料出現異常，問題很難排查。

| 案例二：主從版本不一致導致的複製錯誤

環境資訊

• 作業系統 Red Hat 6.7

• 資料庫資訊 

            主庫IP：192.168.1.36 

            從庫IP：192.168.1.57 

            主庫資料庫版本：5.6.36 

            從庫資料庫版本：5.7.18

• 資料庫引數配置 sync_binlog=1 傳統複製，即非GTID複製

故障重現

• 主從搭建複製架構，搭建步驟此處省略

• 在主庫（192.168.1.36）上建立測試表

• 在從庫（192.168.1.57）上檢查資料以及複製狀態

• 在主庫（192.168.1.36）上將id欄位指定為允許為空

• 在從庫（192.168.1.57）上檢查複製狀態，發現SQL執行緒報了1171的複製錯誤。

現象

從以上測試步驟中可以看到，在複製正常的情況下，主庫上執行DDL提示沒有錯誤，在從庫上執行會有一個錯誤，提示說主鍵的欄位必須非空，如果你要在一個索引中使用NULL屬性，那應該使用唯一索引替代主鍵索引使用。

故障分析

因為主庫為5.6.36版本，從庫為5.7.18版本，所以很容易考慮說是不是因為主從資料庫版本不一致的原因。但是具體是因為5.6和5.7中什麼的不同導致的問題，需要接著分析。 可看到我們在主庫上執行DDL的語句的時候，執行成功了，但是檢視  show create table tt;  語句，可以看到這個DDL語句並沒有起作用，所以這個DDL語句在5.6版本中是被忽略了。 我們直接拿這個DDL語句在5.7的資料庫上執行，直接就報錯了

檢查主庫上的binlog日誌以及從庫上的relay log，都能看到DDL語句是被記錄了的

可以說明這句DDL語句是被正常複製的，但是該語句在5.6主庫上執行的時候，操作被忽略了。DDL語句被複制到5.7從庫上執行的時候，因為5.7不允許該操作，所以SQL執行緒在重放該操作的時候報錯，導致SQL執行緒中斷。

| 作者簡介

沈 剛·沃趣科技資料庫技術專家

熟悉MySQL資料庫執行機制，豐富的資料庫及複製架構故障診斷、效能調優、資料庫備份恢復及遷移經驗。