共计 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 网 – 提供最优质的资源集合！
