MySQL中如何使用binlog時binlog格式的選擇

145次閱讀

共計 4689 個字符，預計需要花費 12 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

這篇文章主要介紹 MySQL 中如何使用 binlog 時 binlog 格式的選擇，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

一、binlog 的三種模式 1.statement level 模式

每一條會修改數據的 sql 都會記錄到 master 的 bin-log 中。slave 在復制的時候 sql 進程會解析成和原來 master 端執行過的相同的 sql 來再次執行。
優點：statement level 下的優點，首先就是解決了 row level 下的缺點，不需要記錄每一行數據的變化，減少 bin-log 日志量，節約 io，提高性能。因為他只需要記錄在 master 上所執行的語句的細節，以及執行語句時候的上下文的信息。
缺點：由于它是記錄的執行語句，所以為了讓這些語句在 slave 端也能正確執行，那么他還必須記錄每條語句在執行的時候的一些相關信息，也就是上下文信息，以保證所有語句在 slave 端被執行的時候能夠得到和在 master 端執行時候相同的結果。另外就是, 由于 mysql 現在發展比較快，很多的新功能加入，使 mysql 的復制遇到了不小的挑戰, 自然復制的時候涉及到越復雜的內容，bug 也就越容易出現。在 statement level 下，目前已經發現的就有不少情況會造成 mysql 的復制問題，主要是修改數據的時候使用了某些特定的函數或者功能的時候會出現，比如 sleep()在有些版本就不能正確復制。

2.rowlevel 模式

日志中會記錄成每一行數據被修改的形式，然后在 slave 端再對相同的數據進行修改
優點：bin-log 中可以不記錄執行的 sql 語句的上下文相關的信息，僅僅只需要記錄那一條記錄被修改了，修改成什么樣了。所以 row level 的日志的內容會非常清楚的記錄下每一行數據修改的細節。而且不會出現某些特定情況下的存儲過程，或 function, 以及 trigger 的調用和觸發無法被正確復制的問題。
缺點：row level 下，所有的執行的語句當記錄到日志中的時候，都將以每行記錄的修改記錄，這樣可能會產生大量的日志內容，比如有這樣一條 update 語句：update product set owner_member_id= d where owner_member_id= a , 執行之后，日志中記錄的不是這條 update 語句所對應的事件(mysql 是以事件的形式來記錄 bin-log 日志)，而是這條語句所更新的每一條記錄的變化情況，這樣就記錄成很多條記錄被更新的很多事件。自然，bin-log 日志的量會很大。

3.mixed 模式

實際上就是前兩種模式的結合，在 mixed 模式下，mysql 會根據執行的每一條具體的 sql 語句來區分對待記錄的日志形式，也就是在 statement 和 row 之間選一種。新版本中的 statement level 還是和以前一樣，僅僅記錄執行的語句。而新版本的 mysql 中對 row level 模式被做了優化，并不是所有的修改都會以 row level 來記錄，像遇到表結構變更的時候就會以 statement 模式來記錄，如果 sql 語句確實就是 update 或者 delete 等修改數據的語句，那么還是會記錄所有行的變更。

二、我們使用 binlog 時應該選擇什么格式呢

通過上面的介紹我們知道了 binlog_format 為 STATEMENT 在一些場景下能夠節省 IO、加快同步速度，但是對于 InnoDB 這種事務引擎，在 READ-COMMITTED、READ-UNCOMMITTED 隔離級別或者參數 innodb_locks_unsafe_for_binlog 為 ON 時，禁止 binlog_format=statement 下的寫入，同時對于 binlog_format=mixed 這種對于非事務引擎、其他隔離級別默認寫 statement 格式的模式也只會記錄 row 格式。

select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
 create table t(c1 int) engine=innodb;
 set binlog_format=statement;
 insert into t values(1);
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
 set binlog_format= mixed 
 show binlog events in mysql-bin.000004 \G
*************************** 3. row ***************************
 Log_name: mysql-bin.000002
 Pos: 287
 Event_type: Gtid
 Server_id: 3258621899
End_log_pos: 335
 Info: SET @@SESSION.GTID_NEXT= ed0eab2f-dfb0-11e7-8ad8-a0d3c1f20ae4:9375 
*************************** 4. row ***************************
 Log_name: mysql-bin.000002
 Pos: 335
 Event_type: Query
 Server_id: 3258621899
End_log_pos: 407
 Info: BEGIN
*************************** 5. row ***************************
 Log_name: mysql-bin.000002
 Pos: 407
 Event_type: Table_map
 Server_id: 3258621899
End_log_pos: 452
 Info: table_id: 124 (test.t)
*************************** 6. row ***************************
 Log_name: mysql-bin.000002
 Pos: 452
 Event_type: Write_rows_v1
 Server_id: 3258621899
End_log_pos: 498
 Info: table_id: 124 flags: STMT_END_F
*************************** 7. row ***************************
 Log_name: mysql-bin.000002
 Pos: 498
 Event_type: Xid
 Server_id: 3258621899
End_log_pos: 529
 Info: COMMIT /* xid=18422 */ 復制代碼

為什么 READ-COMMITTED(RC)、READ-UNCOMMITTED 下無法使用 statement 格式 binlog？這是因為語句在事務中執行時，能夠看到其他事務提交或者正在寫入的數據。事務提交后 binlog 寫入，然后在從庫回放，就會看到的數據會與主庫寫入時候不對應。
有表：

+------+------+
| a | b |
+------+------+
| 10 | 2 |
| 20 | 1 |
+------+------+ 復制代碼

我們做如下操作：

session1 在事務中做 update，UPDATE t1 SET a=11 where b=2; 滿足條件的有行 (10,2) 的一條記錄，并未提交。session2 也做 update 操作，將行 (20,1) 更新為 (20,2) 并提交。然后前面的 sesssion1 提交對行 (10,2) 的更新。

如果 binlog 中使用 Statement 格式記錄，在 slave 回放的時候，session2 中的更新由于先提交會先回放，將行 (20,1) 更新為（20,2)。隨后回放 session1 的語句 UPDATE t1 SET a=11 where b=2; 語句就會將更新 (10,2) 和(20,2)兩行為(11,2)。這就導致主庫行為(11, 2), (20,2)，slave 端為(11,2), (11, 2)。

三、問題分析

上面是通過一個具體的例子說明。本質原因是 RC 事務隔離級別并不滿足事務串行化執行要求，沒有解決不可重復和幻象讀。

對于 Repetable-Read 和 Serializable 隔離級別就沒關系，Statement 格式記錄。這是因為對于 RR 和 Serializable，會保證可重復讀，在執行更新時候除了鎖定對應行還會在可能插入滿足條件行的時候加 GAP Lock。上述 case 更新時，session1 更新 b = 2 的行時，會把所有行和范圍都鎖住，這樣 session2 在更新的時候就需要等待。從隔離級別的角度看 Serializable 滿足事務的串行化，因此 binlog 串行記錄事務 statement 格式是可以的。同時 InnoDB 的 RR 隔離級別實際已經解決了不可重復讀和幻象讀，滿足了 ANSI SQL 標準的事務隔離性要求。

READ-COMMITTED、READ-UNCOMMITTED 的 binlog_format 限制可以說對于所有事務引擎都適用。

四、拓展內容

對于 InnoDB RR 和 Serializable 隔離級別下就一定能保證 binlog 記錄 Statement 格式么？也不一定。在 Innodb 中存在參數 innodb_locks_unsafe_for_binlog 控制 GAP Lock，該參數默認為 OFF：

mysql show variables like innodb_locks_unsafe_for_binlog 
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF |
+--------------------------------+-------+
1 row in set (0.01 sec)復制代碼

即 RR 級別及以上除了行鎖還會加 GAP Lock。但如果該參數設置為 ON，對于當前讀就不會加 GAP Lock，即在 RR 隔離級別下需要加 Next-key lock 的當前讀蛻化為 READ-COMMITTED。所以如果此參數設置為 ON 時即便使用的事務隔離級別為 Repetable-Read 也不能保證從庫數據的正確性。

五、總結

對于線上業務，如果使用 InnoDB 等事務引擎，除非保證 RR 及以上隔離級別的寫入，一定不要設置為 binlog_format 為 STATEMENT，否則業務就無法寫入了。而對于 binlog_format 為 Mixed 模式，RR 隔離級別以下這些事務引擎也一定寫入的是 ROW event。

以上是 MySQL 中如何使用 binlog 時 binlog 格式的選擇的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注丸趣 TV 行業資訊頻道！

向 AI 問一下細節

丸趣 TV 網 – 提供最優質的資源集合！

正文完