安全考虑，binlog_row_image建议尽量使用FULL
背景
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 测试没有主键没有唯一索引
看是否前镜像全保留
drop table actionsky;
create table actionsky
(
id int(11) NOT NULL ,
name varchar(20) DEFAULT NULL,
key (id)
);
insert into actionsky value (1,'aa'),(2,'bb'),(3,'cc'),(4,'dd'),(5,'ee');
update actionsky set name='xxx';
日志如下:
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='aa' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
### @2='xxx' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='bb' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
### @2='xxx' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='cc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
### @2='xxx' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='dd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
### @2='xxx' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2='ee' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
### @2='xxx' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
binlog_row_image=MINIMAL，没有主键没有唯一索引，确实前镜像全保留，后镜像只有修改列
1.2 测试有主键
看是否前镜像只有主键列，后镜像只有修改列
create table actionsky
(
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(20) DEFAULT NULL,
primary key (id)
);
insert into actionsky value (1,'aa'),(2,'bb'),(3,'cc'),(4,'dd'),(5,'ee');
update actionsky set name='xxx';
日志如下:
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @2='xxx' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @2='xxx' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @2='xxx' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @2='xxx' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @2='xxx' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
确实前镜像只有主键列，后镜像只有修改列。就这个原因，导致不能闪回数据，安全性考虑不应该使用binlog_row_image=MINIMAL。
2 测试 binlog_row_image=noblob
2.1 测试没有主键没有唯一索引
由于没有主键没有唯一索引，所以前镜像是全保留，因为TEXT/blob是修改列，所以后镜像的TEXT/blob列也被保留了。整体和FULL格式一致。
mysql> set global binlog_row_image='noblob';
Query OK, 0 rows affected (0.00 sec)
drop table actionsky;
create table actionsky
(
id int(11) NOT NULL ,
id2 int(11) NOT NULL,
name text DEFAULT NULL,
key (id)
);
insert into actionsky value (1,1,'aa'),(2,2,'bb'),(3,3,'cc'),(4,4,'dd'),(5,5,'ee');
update actionsky set name='xxx';
日志如下:
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=1 /* INT meta=0 nullable=0 is_null=0 */
### @3='aa' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=1 /* INT meta=0 nullable=0 is_null=0 */
### @3='xxx' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=0 is_null=0 */
### @3='bb' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=0 is_null=0 */
### @3='xxx' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2=3 /* INT meta=0 nullable=0 is_null=0 */
### @3='cc' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2=3 /* INT meta=0 nullable=0 is_null=0 */
### @3='xxx' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2=4 /* INT meta=0 nullable=0 is_null=0 */
### @3='dd' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2=4 /* INT meta=0 nullable=0 is_null=0 */
### @3='xxx' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=5 /* INT meta=0 nullable=0 is_null=0 */
### @3='ee' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### SET
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=5 /* INT meta=0 nullable=0 is_null=0 */
### @3='xxx' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
2.2测试有主键
有主键，修改列依然是TEXT/blob列，由于有主键了，所以前镜像不会强迫包含所有列，但前镜像的的TEXT列被忽略、不包含,后镜像的TEXT列由于是修改列，所以包含。
mysql> set global binlog_row_image='noblob';
Query OK, 0 rows affected (0.00 sec)
drop table actionsky;
create table actionsky
(
id int(11) NOT NULL ,
id2 int(11) NOT NULL,
name text DEFAULT NULL,
primary key (id)
);
insert into actionsky value (1,1,'aa'),(2,2,'bb'),(3,3,'cc'),(4,4,'dd'),(5,5,'ee');
update actionsky set name='xxx';
日志如下:
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=1 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=1 /* INT meta=0 nullable=0 is_null=0 */
### @3='xxx' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=0 is_null=0 */
### @3='xxx' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2=3 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2=3 /* INT meta=0 nullable=0 is_null=0 */
### @3='xxx' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2=4 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2=4 /* INT meta=0 nullable=0 is_null=0 */
### @3='xxx' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=5 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=5 /* INT meta=0 nullable=0 is_null=0 */
### @3='xxx' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
实验证明binlog_row_image=noblob这个格式，依然存在缺失前镜像的问题，导致某些场景无法闪回，所以也不推荐设置。
2.3测试有主键，修改列也不是TEXT列
drop table actionsky;
create table actionsky
(
id int(11) NOT NULL ,
id2 int(11) NOT NULL,
name text DEFAULT NULL,
primary key (id)
);
insert into actionsky value (1,1,'aa'),(2,2,'bb'),(3,3,'cc'),(4,4,'dd'),(5,5,'ee');
update actionsky set id2=2;
日志如下:
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=1 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2=3 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2=4 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `actionsky57`.`actionsky`
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=5 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=0 is_null=0 */
前镜像和后镜像包含除TEXT/BLOB列之外的所有列
结论
大多数客户生产的安全性大于一切，在硬盘白菜价的今天，不提倡设置binlog_row_image=MINIMAL参数，应该继续使用默认值binlog_row_image=FULL格式。
欢迎加QQ群120242978，一起讨论MySQL技术，ocp考试问题。
posted on 2018-07-12 11:33 fanderchan 阅读(606) 评论(0) 编辑 收藏 举报
