複製錯誤案例分享(一)
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 statusG中查看到的信息,我們認為目前主從的複製是正常的,但是考慮實際的數據,主從的數據已經不一致了。
故障分析
看到主庫的更新操作沒有在從庫上應用,首先考慮,這個事務的binlog是否真的被從庫接收到。於是檢查從庫上的relay log,使用mysqlbinlog工具解析relay log
從relay log中可以看到,主庫上的更新操作在從庫上是接收到了的。接著根據 show slave statusG的信息,也可以確定該事務是被sql線程應用了的。再仔細一看這個 relay log 發現,這個 update 操作是被以STATEMENT的格式保存下來,並複製到從庫。所以在從庫上只是簡單的執行這個語句。並且因為從庫上int_b=1的記錄已經被修改為 int_b=2,從而在從庫上執行這個語句的時候,找不到符合相應條件的記錄需要修改。 這個更新操作是執行了的,只是沒有找到符合where條件的記錄。所以 show slave statusG 查看複製狀態也是正常。但是主從數據不一致了。 所以,在複製架構中一定要強調不要隨便在從庫上執行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資料庫運行機制,豐富的資料庫及複製架構故障診斷、性能調優、資料庫備份恢復及遷移經驗。
推薦閱讀: