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


insert操作:后镜像(set部分)记录所有列数据,因为insert之前并没有对应数据,所以前镜像无记录。

delete操作:前镜像(where部分)记录所有列数据,后镜像不记录。
update操作:前镜像(where部分)记录所有列数据,后镜像(set部分)记录所有列数据。

3.2 minimal

生产中,InnoDB表必须要有主键,因此测试场景不考虑没有主键的情况。

创建表并插入数据

insert操作:前镜像(where部分)不记录,后镜像(set部分)记录有列数据。


update操作:前镜像(where部分)只记录主键列的值,后镜像(set部分)只记录修改列的值。其他列不记录。

delete操作:前镜像(where部分)记录主键列的值,无后镜像。所以,minimal的情况下误删除数据无法恢复。

3.3 noblob

创建表
insert操作: 后镜像(set部分)记录所有插入列的操作;后镜像无记录。

update操作:text/blob 字段不更新,那么前、后镜像都不记录txt/blob字段的值;更新text/blob 字段,前镜像记录除了 text/blob 字段之外其他字段的值,后镜像记录所有列的值。


delete操作:前镜像记录不记录TEXT、BLOB类型的列值,记录其他所有列的值。

4.总结:

在生产中, innodb_row_image 应该设置为full,这样最安全,失误更新数据数据之后,根据binlog中记录的前镜像将原有值找回。
如果生产中,遇到网络带宽不足、磁盘IO出现瓶颈等造成主从延迟,以及磁盘空间紧张等场景,我们可以临时将innodb_row_image修改为 minimal,减少日志的产生量,临时缓解问题。但,不是长久之计,应该采取升级网络、升级硬件,或者优化程序等手段,从根本上解决问题,然后将innodb_row_image调回full。

发表评论

必填项已用*标注