MySQL二进制日志binlog_row_image参数说明

相信大家都了解mysql binlog的格式,那就是有三种,分别是`STATEMENT`,`MiXED`,`ROW`。各有优劣,具体的请大家自行查阅资料。在MySQL 5.7版本以前,虽然ROW格式有各种各样的好处。
  • 比如加快从库重放日志;ROW直接调用mysql的存储引擎接口(handler API) 来执行行的插入、删除和更新,完全跳过了mysql的优化器的处理逻辑。

  • 保证主从数据的一致性。记录的每一行的变更。

  • 可以通过对binlog的逆向解析实现闪回功能。

  • 那么实际上还是有一个缺陷,那就是由于记录的是每一行的变更,会带来磁盘IO上的开销,同时由于binlog日志变大,网络开销也变大了。

  • 那么在MySQL 5.7以后binlog的格式默认就是 ROW 了,同时引入了新的参数 binlog_row_image,这个参数默认值是FULL,其还有一个值是minimal。由于 5.6 版本的其他功能都有人提到了,这个没人提到,我也简单的介绍一下。

  • FULL 记录每一行的变更minimal 只记录影响后的行。下面简单的测试一下大家就明白了。

一、验证FULL模式

  • 默认就是 FULL 模式
mysql> show variables like 'binlog_row_image';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
1 row in set (0.00 sec)
1.1 修改一行数据
mysql> update t1 set n1='qq' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
1.2 解析binlog日志
  • FULL 模式是binlog记录每一行,每一个字段都会记录。

  • binlog 日志前镜像后镜像均记录所有列

[root@centos ~]# /data/mysql/base/5.7.30/bin/mysqlbinlog -vvv --base64-output=decode-rows /data/mysql/log/binlog/3307/mysql-bin.000002 | grep -v '# at' | less

#200720  4:59:51 server id 1595219786  end_log_pos 939 CRC32 0xe71eb015         GTID    last_committed=3        sequence_number=4       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '934bb8fb-ca42-11ea-a53b-02000aba3c36:4'/*!*/;
#200720  4:59:51 server id 1595219786  end_log_pos 1013 CRC32 0xcb6ddf2e        Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1595221191/*!*/;
BEGIN
/*!*/;
#200720  4:59:51 server id 1595219786  end_log_pos 1071 CRC32 0x691ea7d4        Rows_query
# update t1 set n1='qq' where id = 1
#200720  4:59:51 server id 1595219786  end_log_pos 1124 CRC32 0x1802bc72        Table_map: `testdb`.`t1` mapped to number 108
#200720  4:59:51 server id 1595219786  end_log_pos 1186 CRC32 0x30a6fc6f        Update_rows: table id 108 flags: STMT_END_F
### UPDATE `testdb`.`t1`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='aa' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @3='aaaa' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='qq' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @3='aaaa' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
#200720  4:59:51 server id 1595219786  end_log_pos 1217 CRC32 0xfe3f2ece        Xid = 15
COMMIT/*!*/;

二、验证minimal模式

  • 会话级修改 binlog_row_image 参数模式
mysql> show variables like 'binlog_row_image';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| binlog_row_image | MINIMAL |
+------------------+---------+
1 row in set (0.01 sec)
2.1 修改一行数据
mysql> update t1 set n1='qq' where id = 2;
Query OK, 1 row affected (0.14 sec)
Rows matched: 1  Changed: 1  Warnings: 0
2.2 解析binlog日志
  • minimal 模式 binlog 记录的就只是影响后的行与字段

  • ,binlog日志的前镜像只记录唯一识别列( 唯一索引列、主键列 ),后镜像只记录修改列【当没有主键、唯一键时,前镜像记录所有列,后镜像记录修改列

[root@centos ~]# /data/mysql/base/5.7.30/bin/mysqlbinlog -vvv --base64-output=decode-rows /data/mysql/log/binlog/3307/mysql-bin.000002 | grep -v '# at' | less

#200720  5:08:43 server id 1595219786  end_log_pos 1282 CRC32 0x5d196be8        GTID    last_committed=4        sequence_number=5       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '934bb8fb-ca42-11ea-a53b-02000aba3c36:5'/*!*/;
#200720  5:08:43 server id 1595219786  end_log_pos 1356 CRC32 0xb5fdd9e8        Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1595221723/*!*/;
BEGIN
/*!*/;
#200720  5:08:43 server id 1595219786  end_log_pos 1414 CRC32 0x7994c23d        Rows_query
# update t1 set n1='qq' where id = 2
#200720  5:08:43 server id 1595219786  end_log_pos 1467 CRC32 0x5c2d0be3        Table_map: `testdb`.`t1` mapped to number 108
#200720  5:08:43 server id 1595219786  end_log_pos 1520 CRC32 0x7949a0dc        Update_rows: table id 108 flags: STMT_END_F
### UPDATE `testdb`.`t1`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='bb' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @3='bbbb' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
### SET
###   @2='qq' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
#200720  5:08:43 server id 1595219786  end_log_pos 1551 CRC32 0x9871af19        Xid = 18
COMMIT/*!*/;

三、总结

  • 当 binlog_row_image 参数设置为 FULL 时,在 row 模式下,binlog 会记录前镜像和后镜像的所有列的数据信息。

  • 当 binlog_row_image 参数设置为 MINIMAL 时,在 row 模式下,当表没有主键或唯一键时,前镜像记录所有列后镜像记录被修改的列

  • 当 binlog_row_image 参数设置为 MINIMAL 时,在 row 模式下,如果存在主键或唯一键不管是前镜像还是后镜像,都会只记录有影响的列

mysql> update t1 set n1='mm' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

「点点赞赏,手留余香」

    还没有人赞赏,快来当第一个赞赏的人吧!
0 条回复 A 作者 M 管理员
    所有的伟大,都源于一个勇敢的开始!
欢迎您,新朋友,感谢参与互动!欢迎您 {{author}},您在本站有{{commentsCount}}条评论