MySQL 5.7贴心参数之binlog_row_image=full
相信大家都了解mysql binlog的格式,那就是有三种,分别是STATEMENT,MiXED,ROW。各有优劣,具体的请大家自行查阅资料。在MySQL 5.7版本以前,虽然ROW格式有各种各样的好处。
1. 比如加快从库重放日志;ROW直接调用mysql的存储引擎接口(handler API) 来执行行的插入、删除和更新,完全跳过了mysql的优化器的处理逻辑。
2. 保证主从数据的一致性。记录的每一行的变更。
3. 可以通过对binlog的逆向解析实现闪回功能。
那么实际上还是有一个缺陷,那就是由于记录的是每一行的变更,会带来磁盘IO上的开销,同时由于binlog日志变大,网络开销也变大了。那么在MySQL 5.7以后binlog的格式默认就是ROW了,同时引入了新的参数binlog_row_image,这个参数默认值是FULL,其还有一个值是minimal。由于5.7版本的其他功能都有人提到了,这个没人提到,我也简单的介绍一下,哈哈。(后面发现5.6就有该参数了,关注度不够,该死。。)
FULL记录每一行的变更,minimal只记录影响后的行。下面简单的测试一下大家就明白了。
mysql> show variables like '%row_im%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | binlog_row_image | FULL | +------------------+-------+ 1 row in set (0.01 sec) mysql> update t2 set uid=99 where name='yayundeng'; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql>
解析binlog看看啥情况
[root@yayundeng 3306]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000010 | tail -n 20 #161210 11:02:32 server id 152 end_log_pos 2043 CRC32 0x3ce8a225 Update_rows: table id 110 flags: STMT_END_F ### UPDATE `test`.`t2` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='gz' /* STRING(20) meta=65044 nullable=1 is_null=0 */ ### @3='yayundeng' /* STRING(20) meta=65044 nullable=1 is_null=0 */ ### @4=1 /* INT meta=0 nullable=1 is_null=0 */ ### SET ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='gz' /* STRING(20) meta=65044 nullable=1 is_null=0 */ ### @3='yayundeng' /* STRING(20) meta=65044 nullable=1 is_null=0 */ ### @4=99 /* INT meta=0 nullable=1 is_null=0 */ # at 2043 #161210 11:02:32 server id 152 end_log_pos 2074 CRC32 0x93619126 Xid = 49 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@yayundeng 3306]#
下面设置一下binlog_row_image的值为minimal
mysql> set binlog_row_image ='minimal'; Query OK, 0 rows affected (0.00 sec) mysql> update t2 set uid=100 where name='yayundeng'; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql>
再解析binlog看看啥情况:
[root@yayundeng 3306]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000010 | tail -n 20 SET TIMESTAMP=1481339144/*!*/; BEGIN /*!*/; # at 2211 #161210 11:05:44 server id 152 end_log_pos 2263 CRC32 0x57948074 Table_map: `test`.`t2` mapped to number 110 # at 2263 #161210 11:05:44 server id 152 end_log_pos 2309 CRC32 0x72114b1f Update_rows: table id 110 flags: STMT_END_F ### UPDATE `test`.`t2` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### SET ### @4=100 /* INT meta=0 nullable=1 is_null=0 */ # at 2309 #161210 11:05:44 server id 152 end_log_pos 2340 CRC32 0x23e45a71 Xid = 52 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@yayundeng 3306]#
可以清楚的看到,当把binlog_row_image设置为minimal以后,binlog记录的就只是影响后的行,大赞。如此一来使用ROW格式一定是首选了,完全没必要使用STATEMENT,MiXED。不过现在也没有人使用STATEMENT了,所以准确的说就是完全没必要使用MiXED格式了。
总结:
MySQL 5.7是真正的一个里程碑的版本,比如可以在线调整bp pool,在线修改复制过滤,真正意义上多线程复制(5.6是基于库,5.7是基于表),支持bp pool预热,支持undo log回收,支持通用表空间,支持json,支持函数索引(通过虚拟列实现),支持只读事务等等等。
人家明明是列,你全文说行。。。搞的人一愣一愣的
(1)full: Log all columns in both the before image and the after image.
(2)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.
(3)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.