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

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 |
+------+------+复制代码

我們做如下操作：

1. session1在事務中做update，UPDATE t1 SET a=11 where b=2;滿足條件的有行(10,2)的一筆記錄，並未提交。
2. session2也做update操作，將行(20,1)更新為(20,2)並提交。
3. 然後前面的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教程(视频)

以上是MySQL中使用binlog時binlog格式的選擇的詳細內容。更多資訊請關注PHP中文網其他相關文章！