MySQL中使用binlog時(shí)格式該如何選擇

一、binlog的三種模式

1.statement level模式

每一條會(huì)修改數(shù)據(jù)的sql都會(huì)記錄到master的bin-log中。slave在復(fù)制的時(shí)候sql進(jìn)程會(huì)解析成和原來(lái)master端執(zhí)行過(guò)的相同的sql來(lái)再次執(zhí)行。

優(yōu)點(diǎn)：statement level下的優(yōu)點(diǎn)，首先就是解決了row level下的缺點(diǎn)，不需要記錄每一行數(shù)據(jù)的變化，減少bin-log日志量，節(jié)約io，提高性能。因?yàn)樗恍枰涗浽趍aster上所執(zhí)行的語(yǔ)句的細(xì)節(jié)，以及執(zhí)行語(yǔ)句時(shí)候的上下文的信息。

缺點(diǎn)：由于它是記錄的執(zhí)行語(yǔ)句，所以為了讓這些語(yǔ)句在slave端也能正確執(zhí)行，那么他還必須記錄每條語(yǔ)句在執(zhí)行的時(shí)候的一些相關(guān)信息，也就是上下文信息，以保證所有語(yǔ)句在slave端被執(zhí)行的時(shí)候能夠得到和在master端執(zhí)行時(shí)候相同的結(jié)果。另外就是,由于mysql現(xiàn)在發(fā)展比較快，很多的新功能加入，使mysql的復(fù)制遇到了不小的挑戰(zhàn),自然復(fù)制的時(shí)候涉及到越復(fù)雜的內(nèi)容，bug也就越容易出現(xiàn)。在statement level下，目前已經(jīng)發(fā)現(xiàn)的就有不少情況會(huì)造成mysql的復(fù)制問(wèn)題，主要是修改數(shù)據(jù)的時(shí)候使用了某些特定的函數(shù)或者功能的時(shí)候會(huì)出現(xiàn)，比如sleep()在有些版本就不能正確復(fù)制。

2.rowlevel模式

日志中會(huì)記錄成每一行數(shù)據(jù)被修改的形式，然后在slave端再對(duì)相同的數(shù)據(jù)進(jìn)行修改

優(yōu)點(diǎn)：bin-log中可以不記錄執(zhí)行的sql語(yǔ)句的上下文相關(guān)的信息，僅僅只需要記錄那一條記錄被修改了，修改成什么樣了。所以row level的日志的內(nèi)容會(huì)非常清楚的記錄下每一行數(shù)據(jù)修改的細(xì)節(jié)。而且不會(huì)出現(xiàn)某些特定情況下的存儲(chǔ)過(guò)程，或function,以及trigger的調(diào)用和觸發(fā)無(wú)法被正確復(fù)制的問(wèn)題。

缺點(diǎn)：row level下，所有的執(zhí)行的語(yǔ)句當(dāng)記錄到日志中的時(shí)候，都將以每行記錄的修改記錄，這樣可能會(huì)產(chǎn)生大量的日志內(nèi)容，比如有這樣一條update語(yǔ)句：update product set owner_member_id='d' where owner_member_id='a',執(zhí)行之后，日志中記錄的不是這條update語(yǔ)句所對(duì)應(yīng)的事件(mysql是以事件的形式來(lái)記錄bin-log日志)，而是這條語(yǔ)句所更新的每一條記錄的變化情況，這樣就記錄成很多條記錄被更新的很多事件。自然，bin-log日志的量會(huì)很大。

3.mixed模式

實(shí)際上就是前兩種模式的結(jié)合，在mixed模式下，mysql會(huì)根據(jù)執(zhí)行的每一條具體的sql語(yǔ)句來(lái)區(qū)分對(duì)待記錄的日志形式，也就是在statement和row之間選一種。新版本中的statement level還是和以前一樣，僅僅記錄執(zhí)行的語(yǔ)句。而新版本的mysql中對(duì)row level模式被做了優(yōu)化，并不是所有的修改都會(huì)以row level來(lái)記錄，像遇到表結(jié)構(gòu)變更的時(shí)候就會(huì)以statement模式來(lái)記錄，如果sql語(yǔ)句確實(shí)就是update或者delete 等修改數(shù)據(jù)的語(yǔ)句，那么還是會(huì)記錄所有行的變更。

二、我們使用binlog時(shí)應(yīng)該選擇什么格式呢

通過(guò)上面的介紹我們知道了binlog_format為STATEMENT在一些場(chǎng)景下能夠節(jié)省IO、加快同步速度，但是對(duì)于InnoDB這種事務(wù)引擎，在READ-COMMITTED、READ-UNCOMMITTED隔離級(jí)別或者參數(shù)innodb_locks_unsafe_for_binlog為ON時(shí)，禁止binlog_format=statement下的寫入，同時(shí)對(duì)于binlog_format=mixed這種對(duì)于非事務(wù)引擎、其他隔離級(jí)別默認(rèn)寫statement格式的模式也只會(huì)記錄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下無(wú)法使用statement格式binlog？這是因?yàn)檎Z(yǔ)句在事務(wù)中執(zhí)行時(shí)，能夠看到其他事務(wù)提交或者正在寫入的數(shù)據(jù)。事務(wù)提交后binlog寫入，然后在從庫(kù)回放，就會(huì)看到的數(shù)據(jù)會(huì)與主庫(kù)寫入時(shí)候不對(duì)應(yīng)。

例如：

有表：

+------+------+
| a    | b    |
+------+------+
|   10 |    2 |
|   20 |    1 |
+------+------+

我們做如下操作：

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

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

三、問(wèn)題分析

上面是通過(guò)一個(gè)具體的例子說(shuō)明。本質(zhì)原因是RC事務(wù)隔離級(jí)別并不滿足事務(wù)串行化執(zhí)行要求，沒有解決不可重復(fù)和幻象讀。

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

READ-COMMITTED、READ-UNCOMMITTED的binlog_format限制可以說(shuō)對(duì)于所有事務(wù)引擎都適用。

四、拓展內(nèi)容

對(duì)于InnoDB RR和Serializable隔離級(jí)別下就一定能保證binlog記錄Statement格式么？也不一定。在Innodb中存在參數(shù)innodb_locks_unsafe_for_binlog控制GAP Lock，該參數(shù)默認(rèn)為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級(jí)別及以上除了行鎖還會(huì)加GAP Lock。但如果該參數(shù)設(shè)置為ON，對(duì)于當(dāng)前讀就不會(huì)加GAP Lock，即在RR隔離級(jí)別下需要加Next-key lock的當(dāng)前讀蛻化為READ-COMMITTED。所以如果此參數(shù)設(shè)置為ON時(shí)即便使用的事務(wù)隔離級(jí)別為Repetable-Read也不能保證從庫(kù)數(shù)據(jù)的正確性。

五、總結(jié)

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

到此這篇關(guān)于MySQL中使用binlog時(shí)格式該如何選擇的文章就介紹到這了,更多相關(guān)MySQL使用binlog時(shí)格式選擇內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！