MySQL中使用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也不能保证从库数据的正确性。