【20180507】MySQL主從線上修改從庫binlog格式從STATEMENT更改成ROW格式

標籤：binlog format   主從複製   ROW   

需求

公司內部有幾十套基於傳統複製的MySQL主從執行個體，而且binlog的格式都是STATEMENT格式。在接手這些MySQL主從執行個體之後就有考慮過想將binlog格式更改成ROW格式。而這次則是因為我們elk上面一個第三方工具需要解析和監聽binlog資訊，並且只能解析ROW格式的binlog，藉此機會正好將公司部分MySQL主從複製執行個體的binlog格式更改成ROW格式。

ROW和STATEMENT比對

1. row格式
   • 優點：就是能夠完全保證主從資料的一致性，不會出現因為在SQL中使用MySQL內建的函數導致資料不一致的現象。例如：當使用now()函數的時候，可能因為從庫延時的問題導致時間的資料不一致。線上是有遇到過這個問題的。
   • 缺點：就是會消耗比較大的磁碟空間和磁碟IO；還有一個比較重要的問題就是因為ROW格式是基於每行進行修改的，若是在master執行一個update修改5000行資料，那麼slave就會執行5000次修改資料操作，那麼這就會帶來更嚴重的主從延遲。（因為我們線上使用的是MySQL5.6，是基於schema的並行複製，並且slave的硬體資源是比master差的）
2. statement格式
   • 優點：就是消耗較少的磁碟儲存和IO。
   • 缺點：可能會導致資料不一致，並且在做基於binlog恢複的時候可能會出現資料不一致的現象。

binlog格式變更的痛點

雖然binlog變更是可以進行線上修改的，但是由於MySQL的master上面存在許多的長連結，哪怕你動態修改之後，長連結的寫入和修改還是舊的binlog格式。在這裡最開始有提出過倆個方案：

• 重啟master。但是線上業務無法中斷，所以無法進行修改。
• kill掉所有的長連結。但是由於長連結太多，一個個去kill掉的話實在是耗費時間和經曆，所以也不被認可。

解決方案

最後還是迴歸到需求本身，關於我們的需求就是需要解析binlog的格式是ROW格式，所以我們最後的方案就是在slave上面修改binlog的格式為ROW格式。(log_slave_updates參數必須在slave上面開啟。否則master通過binlog傳遞到slave上面重放的SQL是不會在slave本地的binlog記錄的)

• 但是需要注意的是修改完畢之後要想在slave上面的需要重啟啟動複製。即stop slave，start slave。否則是不會生效的。
• 還有一個需要注意的是，當slave上面已經修改成了ROW格式的時候，這個時候在將slave的binlog格式修改成STATEMENT格式的話，複製是會報錯的，哪怕重新restart slave 也會報錯。

【20180507】MySQL主從線上修改從庫binlog格式從STATEMENT更改成ROW格式