吃透3大Binlog模式，MySQL数据零丢失

用错 Binlog 模式，可能导致 MySQL 的数据不一致性、性能下降、存储空间浪费和数据恢复困难等问题。

在 MySQL 中，不管使用什么存储引擎，只要表数据更新，就会产生 Binlog（归档日志）。

而 MySQL 数据库的数据备份、主备、主主、主从等实现，也离不开 Binlog（归档日志）。

针对不同的应用场景，Binlog 推出了三种工作模式 Statement、Row、Mixed，以满足对数据库的需求。

大家好，我是宝妹儿。

今天我们就来深入 Binlog 的三种模式，包括它的原理、优缺点、适用场景、配置管理、切换方式等。

这也是 MySQL 的重要知识点及高频面试点，宝妹儿顺便将这个题目以及答案，整理到2023版《MySQL 大厂高频面试题大全》PDF了，方便系统学习、面试通关。

宝妹儿精编的2023版《MySQL 大厂高频面试题大全》，已收录100+道真题，近30000字，长期迭代、持续更新。吃透它，应付MySQL面试没问题。

公众号Java面试题宝，自取。

1. Binlog 概述

在 MySQL 中，Binlog 是一种二进制形式记录的日志文件，用于记录数据库的修改操作，包括插入、更新和删除等。

Binlog 的两个主要作用：

• 数据恢复：如果 MySQL 意外停止，可以通过该日志进行恢复、备份；
• 数据复制：master 把它的二进制日志传递给 slaves ，从而实现 master-slove 数据的一致性。

2. Binlog 的三种工作模式

Binlog 的三种工作模式分别是：Statement、Row、Mixed。

2.1 Statement 模式

Statement 模式的概念

Statement 是基于语句的复制模式。

Statement 模式将数据库中执行的修改操作记录为 SQL 语句，再从数据库上执行相同的 SQL 语句来实现数据同步。

Statement 模式的优缺点

Statement 模式的优点是简单明了，易于理解和实现。

但是，Statement 模式在执行涉及非确定性函数、触发器和存储过程等操作时，可能会导致不一致的结果。

Statement 模式的缺点：

1）不支持 RU、RC 隔离级别；

2）binglog 日志文件中，上一个事物的结束点是下一个事物的开始点；

3）DML、DDL 语句都会明文显示；

4）对一些系统函数不能准确复制或者不能复制；

5）主库执行 delete from t1 where c1=xxx limit 1，statement 模式下，从库也会这么执行，可能导致删除的不是同一行数据；

6）主库有 id=1 和 id=10 两行数据，从库有 id=1,2,3,10 这四行数据，主库执行 delete from t1 where id<10 命令，从库删除过多数据。

Statement 模式的应用场景

Statement 模式适用于大多数情况下的数据库复制需求。

例如：

1）一次更新大量数据，如二十万数据。反之，在复制时，从库可能会追得太慢，然后导致延时；

2）使用 pt-table-checksum 工具时。

示例一：

update这个事物的开始是insert这个事物结束的点at1581；
update结束的点是commit之后的点at1842;
[root@Darren2 logs]# MySQLBinlog --start-position=1581 --stop-position=1842 MySQL-bin.000022;
......
BEGIN
/*!*/;
# at 1729
#170408 14:40:49 server id 330622  end_log_pos 1841 CRC32 0xb443cf1e    Query   thread_id=55    exec_time=0     error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1491633649/*!*/;
update t10 set c2='bbb' where c1=1
/*!*/;
# at 1841
#170408 14:40:49 server id 330622  end_log_pos 1872 CRC32 0xd06c40f5    Xid = 1756
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by MySQLBinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;           

示例二：

当查看commit之前的position点时，会看到rollback状态，说明这个截取的事物不完整：
[root@Darren2 logs]# MySQLBinlog --start-position=1581 --stop-position=1841 MySQL-bin.000022;
BEGIN
/*!*/;
# at 1729
#170408 14:40:49 server id 330622  end_log_pos 1841 CRC32 0xb443cf1e    Query   thread_id=55    exec_time=0     error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1491633649/*!*/;
update t10 set c2='bbb' where c1=1
/*!*/;
ROLLBACK /* added by MySQLBinlog */ /*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by MySQLBinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;           

2.2 Row 模式

Row 模式的概念

MySQL 5.7 默认的日志模式为 Row。

Row 模式是基于行的复制模式，它将数据库中实际修改的行记录写入 Binlog ，从数据库通过解析 Binlog 来逐行执行相应的修改操作。

相对 statement ，Row 模式更加精确、安全，能够确保数据的一致性。

Row 模式的优缺点

Row 模式的优点是能够准确复制修改的行记录，避免了语句复制模式下的不确定性问题。

Row 模式的缺点：

如果 Binlog 文件较大，传输成本就会很高，在某些情况下，可能会导致性能下降。

1）在表有主键的情况下复制更加快；

2）系统的特殊函数也能复制；

3）更少的锁，只有行锁；

4）Binlog 文件比较大，假设单语句更新 20 万行数据，可能要半小时，也有可能把主库跑挂；

5）MySQL 5.6 之前的版本，无法从 binog 看见用户执行的 SQL 语句；

6）DDL 语句明文显示，DML 语句加密显示；

7）DML 经过 base64 加密，需要使用参数 --base64-output=decode-rows --verbose；

8）update 修改的语句可以看到历史旧数据。

示例：

开启Binlog_rows_query_log_events参数,会显示执行的SQL语句，这个参数默认关闭，不显示执行的SQL
root@localhost [testdb]>set Binlog_rows_query_log_events=on;

[root@Darren2 logs]# MySQLBinlog -vv --base64-output=decode-rows MySQL-bin.000024
......
create table t10(c1 int,c2 varchar(50))

# insert into t10 values(1,now())
### INSERT INTO `testdb`.`t10`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='2017-04-08 15:26:09' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
# insert into t10 values(2,now())

### INSERT INTO `testdb`.`t10`
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='2017-04-08 15:26:09' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
# at 1033

# insert into t10 values(3,sysdate())

### INSERT INTO `testdb`.`t10`
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2='2017-04-08 15:26:09' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
# insert into t10 values(4,uuid())
### INSERT INTO `testdb`.`t10`
### SET
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2='a2b570b8-1c2c-11e7-bc58-000c29c1b8a9' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
# update t10 set c2='bbb' where c1=1
### UPDATE `testdb`.`t10`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='2017-04-08 15:26:09' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='bbb' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */           

Row 模式的应用场景

Row 模式适用于对数据一致性要求较高的场景，特别是涉及一些复杂的数据库操作和业务逻辑。

例如，涉及触发器、存储过程和函数等的数据库操作。

使用Row 模式时需注意，Row 模式可能导致 Binlog 文件较大，需要合理设置 Binlog 文件大小和保留时间。

3.3 Mixed 模式

Mixed 模式的概念

Mixed 模式（混合模式）是将语句复制模式和行复制模式结合起来使用。

大多数的修改操作，通常使用 Statement 模式记录对应的 SQL 语句。

一些特殊的操作，涉及非确定性函数和存储过程等，则使用 Row 模式记录修改的行记录。

Mixed 模式的优缺点

Mixed 模式综合了语句复制模式和行复制模式的优点，能够在大多数情况下高效地记录修改操作，并在需要时使用行复制模式确保数据的准确性。

但是 Mixed 模式对一些特殊操作的处理可能会很复杂，特别需要注意下配置和管理。

总结：

1）innodb 引擎，如果隔离级别是 RU、RC，则 Mixed 模式会转成 Row 模式存储。

示例：

set  tx_isolation='read-committed';
set  Binlog_format='mixed';
flush logs;
create table t10(c1 int,c2 varchar(50));
insert into t10 values(1,now());
insert into t10 values(2,now());
insert into t10 values(3,sysdate());
insert into t10 values(4,uuid());
update t10 set c2='bbb' where c1=1;
[root@Darren2 logs]# MySQLBinlog -vv --base64-output=decode-rows MySQL-bin.000028
......
### UPDATE `testdb`.`t10`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='2017-04-08 18:34:08' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='bbb' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
......           

2）在以下几种情况下，Mixed 模式会自动将 Binlog 的模式 SBR 转化成 RBR 模式：

• 当更新一个 NDB 表时；
• 当函数包含 uuid() 函数时；
• 2个及以上包含 auto_increment 字段的表被更新时；
• 视图中必须要求使用 RBR 时。

示例：

set  tx_isolation='repeatable-read';
set  Binlog_format='mixed';
flush logs;
create table t10(c1 int,c2 varchar(50));
insert into t10 values(1,now());
insert into t10 values(2,now());
insert into t10 values(3,sysdate());
insert into t10 values(4,uuid());
update t10 set c2='bbb' where c1=1;
[root@Darren2 logs]# MySQLBinlog MySQL-bin.000029
......
update t10 set c2='bbb' where c1=1
......           

Mixed 模式的应用场景

Mixed 模式适用于大多数情况下的数据库复制需求，尤其适合需要兼顾效率和准确性的场景。

在使用 Mixed 模式时，需要注意对特殊操作进行测试和验证，确保数据的一致性和正确性。

4. 如何查看、配置 Binlog 模式

查看 Binlog 模式

MySQL> show global variables like "%Binlog_format%";  
+---------------+-----------+  
| Variable_name | Value    |  
+---------------+-----------+  
| Binlog_format | STATEMENT |  
+---------------+-----------+           

配置 Binlog 模式

vim my.cnf，在 MySQLd 模块中配置。

log-bin = /data/3306/MySQL-bin  
Binlog_format="STATEMENT"  
#Binlog_format="ROW"  
#Binlog_format="MIXED"           

不重启，使配置在 MySQL 中生效。

SET global Binlog_format='STATEMENT';           

4. Binlog 模式的切换方法

4.1 修改配置文件

可以通过修改 MySQL 的配置文件来切换 Binlog 模式。

编辑 MySQL 的配置文件（通常是 my.cnf 或 my.ini ），找到 Binlog_format 参数，并将其设置为所需的模式。

Binlog_format = ROW           

然后重新启动 MySQL 服务，使新的 Binlog 模式生效。

4.2 使用 SQL 语句切换

另一种切换 Binlog 模式的方法，是通过执行 SQL 语句来修改 Binlog_format 参数。

SET GLOBAL Binlog_format = 'ROW';           

这会立即将 Binlog 模式切换为指定的模式。

这种修改在 MySQL 服务重启后会失效，因此需要在重启前进行相应的配置修改。

4.3 动态切换

MySQL 5.6 版本及以上提供了动态切换 Binlog 模式的功能，可以在不重启 MySQL 服务的情况下进行切换。

SET SESSION Binlog_format = 'ROW';           

这将在当前会话中将 Binlog 模式切换为指定的模式

动态切换只对当前会话有效，不会影响其他会话或 MySQL 服务重启后的配置。

总结

通过本文，我们学习并掌握了 Binlog 三种模式 Statement、Row、Mixed，包括它们的特点、优缺点、应用场景、配置、切换等。

选型参考思路：

• 使用 MySQL 特殊功能较少，例如存储过程、触发器、函数等，用 Statement 模式。
• 使用 MySQL 特殊功能较多，用 Mixed 模式。
• 使用 MySQL 特殊功能较多，同时希望数据最大化一致，用 Row 模式。

在实际应用场景中，还是要结合具体情况来合理选择。

如果觉得有用，请顺手【点赞】支持下哦，这将是对宝妹儿的最大鼓励，谢谢~

最后

本文收录于宝妹儿精编的 2023版《MySQL 大厂高频面试题大全》PDF。

搞定这100道题，足以应对MySQL面试。