为什么要把MySQL的binlog格式修改为row

本文深入探讨MySQL中binlog的statement与row两种格式的区别,通过具体案例解析不同格式下的数据同步问题,强调row格式在主备一致性及数据恢复方面的优势。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

我们知道binlog有两种常用的格式,一种是statement(默认),一种是row,很多人都说建议你修改为row格式,那么是为什么呢?

首先我们需要知道它们两个之间有什么不同?
statement格式记录的我们写的SQL语句,而row格式记录的则是实际受影响的数据的变化前后值

这里举两个例子说明一下:

删除

statement记录的是这个删除的语句,例如:

delete from t where age>10 and modified_time<='2020-03-04'  limit 1

使用这个格式的binlog很可能出现下面这种问题:

  1. 在主库执行这条SQL语句的时候,用的是索引age,而在备库执行这条SQL语句的时候,却使用了索引modified_time
  2. 主备同步本身就存在一部分延迟,limit语句很可能受延迟的影响

而row格式记录的是实际受影响的数据是真实删除行的主键id,例如:

delete from t where id=3 and age=12 and modified_time='2020-03-05'

这样 binlog传到备库去的时候,就肯定会删除id=3的行,不会存在主备删除不同行的问题

修改

注意这个例子数据库隔离级别为读提交

file

statment格式记录的binlog可能会是这样:

会话二:
begin;
update t set d=5 where id=0;
commit;

会话一:
begin;
update t set d=100 where d=5;
commit;

通过上面解析出来的binlog执行就有问题了,最终结果是2行数据d变成了100,明显与期望不一致。

如果是row格式,那么伪日志记录如下:

会话二:
begin;
update t where id=0 and c=0 and d=0
set id=0,c=0,d=5
commit;

会话一:
begin;
update t where id=5 and c=5 andd=5
set id=5,c=5,d=100
commit;

显然row格式记录方式按照这个binlog执行明显是正确的,也符合预期

注意:为什么这个例子强调了数据库隔离级别为读提交呢?

可重复读级别下会存在间隙锁,会话2必须等会话1释放锁后才能执行,自然也不会出问题

数据恢复

除了避免主备不一致外,使用row格式的binlog对恢复数据也很友好

delete

row格式的binlog会把被删掉的行的整行 信息保存起来。所以,如果你在执行完一条delete语句以后,发现删错数据了,可以直接把binlog中记录的delete语句转成insert

insert

row格式下,insert语句的binlog里会记录所有的字段信息,这些信息可以用来精确定位刚刚被插入的那一行。这时,你直接把insert语句转成delete语句,删除掉这被误插入的一行数据就可以了

update

row格式下,binlog里面会记录修改前整行的数据和修改后的整行数据。所 以,如果你误执行了update语句的话,只需要把这个event前后的两行信息对调一下,再去数据库里面执行,就能恢复这个更新操作了

推荐阅读
  1. Spring源码解析系列文章汇总
  2. SpringBoot源码解析系列文章汇总
  3. Eureka源码解析系列文章汇总
  4. SpringCloud服务调用源码解析系列文章汇总

1

### MySQL Binlog Row Mode 下级联操作未被记录的原因 在 MySQL 中,当启用 `binlog_row_mode` 时,某些情况下可能会发生级联操作未被记录的现象。以下是对此现象的具体分析: #### 1. **Binlog 行模式的工作机制** 在行模式下 (`ROW`),MySQL 的二进制日志仅记录受影响的行数据变更,而不是执行的操作语句本身[^1]。这意味着对于每一行的变化,都会生成一条对应的事件记录到 binlog 文件中。 然而,在涉及外键约束的情况下(例如删除父表中的某条记录触发子表中关联记录的自动删除),这些由外键引发的级联操作可能不会显式地作为单独的日志事件被捕获并写入 binlog。这是因为 MySQL 默认认为这些变化是由内部逻辑驱动而非外部 SQL 请求引起的[^2]。 #### 2. **InnoDB 配置的影响** 尽管 InnoDB 存储引擎提供了多种事务控制参数以优化性能和一致性保障,比如通过设置 `innodb_flush_log_at_trx_commit=1` 和 `sync_binlog=1` 来增强持久性和可靠性[^3],但它们并不能直接影响 binlog 对于特定类型操作(如因外键而产生的隐式更改)的行为表现。 因此即使启用了上述严格的刷盘策略,也不能解决由于 binlog row mode 导致的部分级联动作丢失问题。 #### 3. **主从复制环境下的影响** 在一个典型的 Master-Slave 架构或者更复杂的 Multi-Tier Replication 场景里,如果 master 上发生了带有 CASCADE 动作的关系型更新,则 slave 可能收不到完整的同步信息[^4]。这不仅可能导致最终状态不一致,还使得后续排查变得困难重重。 另外值得注意的是,虽然可以通过调整全局变量或配置文件选项来改变默认行为,但由于存在一些局限性——例如无法在线修改以及需要重启服务等情况的存在[^5],实际应用过程中往往面临更多挑战。 综上所述,在采用 ROW 格式的 binary logging 方案时遇到此类问题是正常现象之一;它源于设计层面的选择权衡,并非单纯的 bug 或错误配置所致。 ```sql -- 示例代码展示如何查看当前系统的binlog模式 SHOW VARIABLES LIKE 'binlog_format'; ```
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值