binlog_row_image这个参数是MySQL5.6新增的参数,默认值是FULL,在5.7版本默认值也是FULL,但今天我看到有客户的 MySQL5.7版本参数模板采用的是MINIMAL而不是FULL,我对这个修改表示疑惑。

一般来说,对一个参数默认值作出修改,我们都应该考虑清楚影响范围,所以我准备做一次测试,并得出结论哪个参数值才是最佳设置。

术语解释

前提:binlog格式必须为row格式或者mixed格式,不可以是statement格式。名称解释:before image:前镜像,即数据库表中修改前的内容。after image:后镜像,即数据库表中修改后的内容。---

binlog_row_image三种设置及异同

binlog_row_image参数可以设置三个合法值: FULL、MINIMAL、NOBLOB三个不同值的作用如下:

FULL: Log all columns in both the before image and the after image.binlog日志记录所有前镜像和后镜像。MINIMAL: Log only those columns in the before image that are required to identify the row to be changed; log only those columns in the after image where a value was specified by the SQL statement, or generated by auto-increment.binlog日志的前镜像只记录唯一识别列(唯一索引列、主键列),后镜像只记录修改列。noblob: Log all columns (same as full), except for BLOB and TEXT columns that are not required to identify rows, or that have not changed.binlog记录所有的列,就像full格式一样。但对于BLOB或TEXT格式的列,如果他不是唯一识别列(唯一索引列、主键列),或者没有修改,那就不记录。---

For the before image, it is necessary only that the minimum set of columns required to uniquely identify rows is logged. If the table containing the row has a primary key, then only the primary key column or columns are written to the binary log. Otherwise, if the table has a unique key all of whose columns are NOT NULL, then only the columns in the unique key need be logged. (If the table has neither a primary key nor a unique key without any NULL columns, then all columns must be used in the before image, and logged.) In the after image, it is necessary to log only the columns which have actually changed.

官方提到:如果没有唯一识别列(唯一索引列、主键列),例如只有普通key,那么MINIMAL格式的前镜像也会记录所有所有列,但后镜像依然只记录修改列。

1. 这个参数如果设置成MINIMAL格式,可以节省不少磁盘空间,节省一定的io。但由于前镜像不记录修改列,只在后镜像记录修改列,如果数据出现误操作,必然不能通过flashback或binlog2SQL等快速闪回工具恢复数据,因为不能通过BinLog生成反向SQL了。

节省磁盘空间: 高数据安全性: 低

2. 这个参数如果设置成NOBLOB格式,在表中TEXT和BLOB等大字段如果不修改,就不记录前后镜像了,其他小字段的列的修改依然记录前后镜像,一般大字段消耗的磁盘空间是非常大的,可以节省不少磁盘空间。而如果表没有大字段,NOBLOB和FULL格式并没有区别,如果数据出现误操作,可以通过flashback或BinLog2SQL等快速闪回工具恢复数据。

节省磁盘空间: 中数据安全性: 中

3. 这个参数如果设置成FULL格式,这是MySQL5.6和MySQL5.7的默认设置,binlog记录所有数据的前后镜像,如果数据出现误操作,可以能通过flashback或binlog2sql等快速闪回工具恢复数据。在数据列比较大的情况下,在大量的update、delete操作时,binlog盘增长会很快,比较容易出现“binlog盘快满”的监控告警。

节省磁盘空间: 低数据安全性: 高

测试过程如下:

1 测试binlog_row_image=MINIMAL

1.1 测试没有主键没有唯一索引

看是否前镜像全保留

日志如下:

1.2 测试有主键

看是否前镜像只有主键列,后镜像只有修改列

日志如下:

确实前镜像只有主键列,后镜像只有修改列。就这个原因,导致不能闪回数据,安全性考虑不应该使用binlog_row_image=MINIMAL。

2 测试 binlog_row_image=noblob

2.1 测试没有主键没有唯一索引

由于没有主键没有唯一索引,所以前镜像是全保留,因为TEXT/blob是修改列,所以后镜像的TEXT/blob列也被保留了。整体和FULL格式一致。

日志如下:

2.2 测试有主键

有主键,修改列依然是TEXT/blob列,由于有主键了,所以前镜像不会强迫包含所有列,但前镜像的的TEXT列被忽略、不包含,后镜像的TEXT列由于是修改列,所以包含。

日志如下:

实验证明binlog_row_image=noblob这个格式,依然存在缺失前镜像的问题,导致某些场景无法闪回,所以也不推荐设置。

2.3 测试有主键,修改列也不是TEXT列

日志如下:

前镜像和后镜像包含除TEXT/BLOB列之外的所有列

大多数客户生产的安全性大于一切,在硬盘白菜价的今天,不提倡设置binlog_row_image=MINIMAL参数,应该继续使用默认值binlog_row_image=FULL格式。

爱可生(证券代码:)依托于融合、开放、创新的数据处理技术和服务能力,为大型行业用户的特定场景提供深度挖掘数据价值的解决方案。公司持续积累的核心关键技术,覆盖到分布式数据库集群、云数据库平台、数据库大体量运管平台、海量数据集成与存储、清洗与治理、人工智能分析挖掘、可视化展现、安全与隐私保护等多个领域。

公司已与多个行业内的专业公司建立了长期伙伴关系,不断促进新技术与行业知识相结合,为用户寻求新的数据驱动的价值增长点。公司已在金融、能源电力、电信、广电、政府等行业取得了多个大型用户典型成功案例,获得了市场的广泛认可和业务持续的增长。