為什麼要把MySQL的binlog格式修改為row

我們知道binlog有兩種常用的格式，一種是statement（默認），一種是row，很多人都說建議你修改為row格式，那麼是為什麼呢？

首先我們需要知道它們兩個之間有什麼不同？ statement格式記錄的我們寫的SQL語句，而row格式記錄的則是實際受影響的數據的變化前後值

這裡舉兩個例子說明一下：

刪除

statement記錄的是這個刪除的語句，例如：

delete from t where age>10 and modified_time<='2020-03-04'  limit 1

使用這個格式的binlog很可能出現下面這種問題：

  1. 在主庫執行這條SQL語句的時候，用的是索引age，而在備庫執行這條SQL語句的時候，卻使用了索引modified_time
  2. 主備同步本身就存在一部分延遲，limit語句很可能受延遲的影響

而row格式記錄的是實際受影響的數據是真實刪除行的主鍵id，例如：

delete from t where id=3 and age=12 and modified_time='2020-03-05'

這樣 binlog傳到備庫去的時候，就肯定會刪除id=3的行，不會存在主備刪除不同行的問題

修改

注意這個例子數據庫隔離級別為讀提交

statment格式記錄的binlog可能會是這樣：

會話二：  begin；  update t set d=5 where id=0;  commit;    會話一：  begin；  update t set d=100 where d=5;  commit;

通過上面解析出來的binlog執行就有問題了，最終結果是2行數據d變成了100，明顯與期望不一致。

如果是row格式，那麼偽日誌記錄如下：

會話二：  begin；  update t where id=0 and c=0 and d=0  set id=0,c=0,d=5  commit;    會話一：  begin；  update t where id=5 and c=5 andd=5  set id=5,c=5,d=100  commit;

顯然row格式記錄方式按照這個binlog執行明顯是正確的，也符合預期

注意：為什麼這個例子強調了數據庫隔離級別為讀提交呢？

可重複讀級別下會存在間隙鎖，會話2必須等會話1釋放鎖後才能執行，自然也不會出問題

數據恢復

除了避免主備不一致外，使用row格式的binlog對恢複數據也很友好

delete

row格式的binlog會把被刪掉的行的整行 信息保存起來。所以，如果你在執行完一條delete語句以後，發現刪錯數據了，可以直接把binlog中記錄的delete語句轉成insert

insert

row格式下，insert語句的binlog里會記錄所有的字段信息，這些信息可以用來精確定位剛剛被插入的那一行。這時，你直接把insert語句轉成delete語句，刪除掉這被誤插入的一行數據就可以了

update

row格式下，binlog裏面會記錄修改前整行的數據和修改後的整行數據。所 以，如果你誤執行了update語句的話，只需要把這個event前後的兩行信息對調一下，再去數據庫裏面執行，就能恢復這個更新操作了

VirMach 便宜 VPS

QNews

QNews