MySQL binlog_row_image参数详解
2,570 total views, 1 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。