140
技術社區[雲棲]
一條更新操作引起的MySQL主從複製異常
作者介紹
康壯，大連樓蘭科技股份數據庫運維組負責人，主要負責集團內部MySQL、MongoDB、Redis數據庫運維管理工作。曾在第三方數據庫運維公司任職技術部經理，負責政府行業的Oracle數據庫運維工作，具有Oracle OCP、OCM、MySQL OCP等相關認證、對數據庫架構設計、故障處理、優化有深入的理解。
一、環境描述
生產環境異地機房主從數據庫，數據量過百G，數據庫版本社區版本5.6.25。
二、問題描述
同事根據開發提供的SQL在Master節點執行了一個大表的的全表更新操作，導致從節點Slave IO線程中斷。
三、問題分析
1)相關參數
my.cnf中有兩個參數設置：
expire_logs_days = 7 #binlog保留時間7天
max_binlog_size = 1G #binlog大小
2)表大小，執行SQL
Table: v_clda 5.8G
Sql: update v_clda set uploadtime ＝now(); 主庫執行成功
3)主庫，大事物產生的binlog
4)異地從庫報錯
Slave 已經無法同步數據。
一個事物隻能寫入一個binlog日誌中，默認情況下，binlog日誌達到設定值後(max_binlog_size)，會自動生成一個新的日誌文件，也會根據過期參數(expire_logs_days)設置自動刪除binlog日誌。如果生成了一個超大的binlog日誌，很可能是由於大事物引起的。
四、問題處理
嚐試從啟slave線程，多次嚐試後失敗。
嚐試跳過事物，具體方法如下：
從節點執行(基於GTID)
從節點執行更新操作，同步數據
五、解決方案
在執行大事物前關閉 set session sql_log_bin=0; （默認是開啟的），尤其是異地機房，網絡帶寬有限，而且VPN通道不是十分穩定的情況下。不允許它生成大量binlog日誌。
如果像本例中，已經執行了，而且生成了大量的binlog，最終導致複製異常，可以考慮使用跳過事物的方法來解決這個問題。
最笨的方法就是重新搭建主從，由於數據量比較大，還是異地不可取。
根本解決方法還是要拆分大事物，進行批量提交操作。賀春暘老師的MySQL管理之道一書中第四章4.4節有具體的解決方法。
參考改為用存儲過程，每刪除10000條事務就提交一次，循環操作直至刪除完畢。經過優化，行鎖的範圍變小了，性能也就變好了。相關代碼如下：
存儲過程上線後，觀察一段時間，同步複製時，Slave複製正常，問題解決。
原文發布時間為：2017-04-10
本文來自雲棲社區合作夥伴DBAplus
最後更新：2017-05-16 12:01:34