MySQL binlog_row_image参数详解
2,695 total views, 3 views today
基于行复制（row-base replication，RBR）的情况下， binlog_row_image 参数决定了行的镜像（image）是如何写入到binlog日志，改参数从MySQL 5.6引入。
binlog_row_image 是动态参数，可以直接修改，无需重启MySQL实例。
在RBR 中，每个行的变化都会含有两个镜像，前镜像（before image）和后镜像（after image）。
前镜像（before image）：修改前的数据。
后镜像（after image）：修改后的数据。
如果是insert操作，那么只记录后镜像； 如果是delete，只记录前镜像。
binlog_row_image 共有三个值：full（默认）、minimal、noblob。
1.三种类型的区别：
full
在安装MySQL后，binlog_row_image 默认是full，前、后镜像记录所有列的数据。但是，记录所有列的改变是非必须的，记录所有列改变的数据，意味着占用更多的磁盘、带宽和内存。
minimal
将 binlog_row_image 设置为 minimal，可以减少binlog量（也有例外，下面提到）。 可以减少网络带宽、磁盘空间、内存的占用，以及减少IO的产生。
各种情况下，前、后镜像记录的列。
| 场景 | 前镜像记录 | 后镜像记录 |
| 有主键 | 主键列 | 修改的列 |
| 有唯一键且not null | 唯一键列 | 修改的列 |
| 无主键、无唯一键 | 所有列 | 所有列 |
noblob
binlog记录所有列的数据，就像full一样。BLOB和TEXT列如果没有改变，或者没有作为主键、唯一键，那么不会记录到binlog。 BLOB和TEXT类型的列一般占用空间较多，通常不会作为主键和唯一键，因此设置为noblob可以减少binlog量。
如果表中有BLOB、TEXT类型的字段，修改BLOB、TEXT类型的字段会记录到后镜像（前镜像不记录），不修改那么BLOB、TEXT类型的字段不会记录到前后镜像中。
2.安全性
minimal
如果将binlog_row_image 设置为minimal，此时前镜像不记录修改列的数据，后记录记录修改列的数据。那么，如果出现误操作更新数据，则无法通过flashback或者binlog2SQL等恢复工具恢复数据。
minimal最大程度节省空间，但是安全性低。
noblob
如果表中没有BLOB、TEXT类型的字段，那么noblob和full没有区别，前、后镜像记录所有列的数据。如果表中有BLOB、TEXT类型的字段，修改BLOB、TEXT类型的字段会记录到后镜像（前镜像不记录），不修改那么BLOB、TEXT类型的字段不会记录到前后镜像中。
noblob可节省空间，安全性中等。
full
这个不用多说，安全性最高，占空间最多。
3.实际测试
3.1 full
|
1 2 3 4 5 6 7 |
root@ 10:57: [sbtest]> show variables like '%binlog_row_image%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | binlog_row_image | FULL | +------------------+-------+ 1 row in set (0.01 sec) |
insert操作：后镜像（set部分）记录所有列数据，因为insert之前并没有对应数据，所以前镜像无记录。
delete操作：前镜像（where部分）记录所有列数据，后镜像不记录。
update操作：前镜像（where部分）记录所有列数据，后镜像（set部分）记录所有列数据。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
root@ 11:02: [sbtest]> update t1 set id=3 where id=2; ### UPDATE `sbtest`.`t1` ### WHERE ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ ### @2='2020-07-15 09:56:50' /* DATETIME(0) meta=0 nullable=0 is_null=0 */ ### SET ### @1=3 /* INT meta=0 nullable=1 is_null=0 */ ### @2='2020-07-15 09:56:50' /* DATETIME(0) meta=0 nullable=0 is_null=0 */ # at 914 #200717 11:03:01 server id 2230 end_log_pos 941 Xid = 322114344 COMMIT/*!*/; |
3.2 minimal
生产中，InnoDB表必须要有主键，因此测试场景不考虑没有主键的情况。
创建表并插入数据
insert操作：前镜像（where部分）不记录，后镜像（set部分）记录有列数据。
|
1 2 3 4 5 6 7 8 9 10 |
root@ 11:35: [sbtest]> update t2 set name='Jack' where id=1; ### UPDATE `sbtest`.`t2` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### SET ### @2='Jack' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */ # at 2333 #200717 11:35:36 server id 2230 end_log_pos 2360 Xid = 322114362 COMMIT/*!*/; |
delete操作：前镜像（where部分）记录主键列的值，无后镜像。所以，minimal的情况下误删除数据无法恢复。
|
1 2 3 4 5 6 |
### DELETE FROM `sbtest`.`t2` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ # at 2575 #200717 11:38:25 server id 2230 end_log_pos 2602 Xid = 322114364 COMMIT/*!*/; |
3.3 noblob
创建表
insert操作： 后镜像（set部分）记录所有插入列的操作；后镜像无记录。
update操作：text/blob 字段不更新，那么前、后镜像都不记录txt/blob字段的值；更新text/blob 字段，前镜像记录除了 text/blob 字段之外其他字段的值，后镜像记录所有列的值。
4.总结：
在生产中， innodb_row_image 应该设置为full，这样最安全，失误更新数据数据之后，根据binlog中记录的前镜像将原有值找回。
如果生产中，遇到网络带宽不足、磁盘IO出现瓶颈等造成主从延迟，以及磁盘空间紧张等场景，我们可以临时将innodb_row_image修改为 minimal，减少日志的产生量，临时缓解问题。但，不是长久之计，应该采取升级网络、升级硬件，或者优化程序等手段，从根本上解决问题，然后将innodb_row_image调回full。