浅析MySQL基于ROW格式的二进制日志

概述上文分析的二进制日志实际上是基于STATEMENT格式的，下面我们来看看基于ROW格式的二进制日志，毕竟，两者对应的binlog事件类型也不一样，同时，很多童鞋反映基于ROW格式的二进制日志无法查到原

上文分析的二进制日志实际上是基于STATEMENT格式的，下面我们来看看基于ROW格式的二进制日志，毕竟，两者对应的binlog事件类型也不一样，同时，很多童鞋反映基于ROW格式的二进制日志无法查到原生的DML语句，关于这个问题，其实官方也给出了解决方案，下面，将一一揭晓。

 

首先，来几条测试数据

MysqL> set binlog_format=row;query OK,0 rows affected (0.00 sec)MysqL> flush logs;query OK,1); Font-weight: bold">0.01insert into test.t1 values(1,@H_301_34@'@H_301_34@a@H_301_34@');query OK,1); Font-weight: bold">1 row affected (use testDatabase changed
MysqLinto t1 2,1)">b@H_301_34@update t1 set name=@H_301_34@c@H_301_34@' where ID=2;query OK,1)"> sec)Rows matched: 1  Changed: 1  Warnings: 0MysqLdelete from t1 10.01 sec)

 

@H_301_34@首先通过SHOW binlog EVENTS查看二进制日志中的内容

MysqL> show binlog events in @[email protected]@H_301_34@;+------------------+-----+-------------+-----------+-------------+---------------------------------------+| Log_name         | Pos | Event_type  | Server_ID | End_log_pos | Info                                  || MysqL-bin.000025 |   4 | Format_desc |         1 120 | Server ver: 5.6.31-@H_301_34@log,binlog ver: | | query       188 | BEGIN                                 | table_map   236 | table_ID: 79 (test.t1)                | Write_rows  278 79 flags: STMT_END_F        | XID         309 COMMIT /* xID=175 */                  381 429 471 502  xID=183 574 622 | Update_rows 672 703  xID=184 775 823 | Delete_rows 865 896  xID=185 17 rows in set (0.00 sec)

 

@H_301_34@再来通过MysqLbinlog查看

# MysqLbinlog MysqL-bin.000025

!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;!40019 SET @@session.max_insert_delayed_threads=0!50003 SET @olD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0;DEliMITER !;# at 4#160817 10:20:16 server ID 1  end_log_pos 120 CRC32 0x5b15ac4f     Start: binlog v 4,server v 5.6.31-log created 16# Warning: this binlog is either in use or was not closed properly.binlog '4MmzVw8BAAAAdAAAAHgAAAABAAQANS42LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAU+sFVs='/*!*/;# at 12022 server 188 CRC32 0x005847f0     query    thread_ID=12    exec_time=0    error_code=0SET TIMESTAMP=1471400422;SET @@session.pseudo_thread_ID=12;SET @@session.foreign_key_checks=1,@@session.sql_auto_is_null=0,@@session.unique_checks=1;SET @@session.sql_mode=1075838976;SET @@session.auto_increment_increment=!\C utf8 *//*;SET @@session.character_set_clIEnt=33,@@session.collation_connection=33;SET @@session.lc_time_names=;SET @@session.collation_database=DEFAulT;BEGIN188236 CRC32 0x2b8d2069     table_map: `test`.`t1` mapped to number 79# at 236278 CRC32 0xadc98fbc     Write_rows: table  flags: STMT_END_Fbinlog 5smzVxMBAAAAMAAAAOwAAAAAAE8AAAAAAAEABHRlc3QAAnQxAAIDDWIEAANpII0r5smzVx4BAAAAKgAAABYBAAAAAE8AAAAAAAEAAgAC//wBAAAAAWG8j8mt278309 CRC32 0x552dc682     XID = 175COMMIT30934 server 381 CRC32 0x17d8173e     query    thread_ID=1471400434381429 CRC32 0x71a27e19     table_map: `test`.`t1` mapped to number 429471 CRC32 0xefda98ca     Write_rows: table 8smzVxMBAAAAMAAAAK0BAAAAAE8AAAAAAAEABHRlc3QAAnQxAAIDDWIEAAMZfqJx8smzVx4BAAAAKgAAANcBAAAAAE8AAAAAAAEAAgACwCAAAAAWLKmNrv471502 CRC32 0x7bed11c4     XID = 18350238 server 574 CRC32 0xd164b750     query    thread_ID=1471400438574622 CRC32 0x9fa3cabc     table_map: `test`.`t1` mapped to number 622672 CRC32 0xb1646398     Update_rows: table 9smzVxMBAAAAMAAAAG4CAAAAAE8AAAAAAAEABHRlc3QAAnQxAAIDDWIEAAO8yqOf9smzVx8BAAAAMgAAAKACAAAAAE8AAAAAAAEAAgAC///8AgAAAAFi/AIAAAABY5hjZLE=672703 CRC32 0x91a90c52     XID = 18470343 server 775 CRC32 0x5ae24c0b     query    thread_ID=1471400443775823 CRC32 0x33c52e84     table_map: `test`.`t1` mapped to number 823865 CRC32 0x77e907a2     Delete_rows: table '+8mzVxMBAAAAMAAAADcdaAAAAE8AAAAAAAEABHRlc3QAAnQxAAIDDWIEAAOELsUz+8mzVyABAAAAKgAAAGEDAAAAAE8AAAAAAAEAAgACwBAAAAAWGiB+l3865896 CRC32 0xb0988385     XID = 185;DEliMITER ;# End of log fileRolLBACK  added by MysqLbinlog !50003 SET COMPLETION_TYPE=@olD_COMPLETION_TYPE!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 

额，what is this，竟然没看到一条明文的DML语句

@H_301_34@实际上，对于ROW格式的二进制日志，需要使用如下方式查看，这也是STATEMENT和ROW格式的差异之一

# MysqLbinlog MysqL-bin.000025 -vv --base64-output=decode-rows

;
# at  use or was not closed properly.
# at  flags: STMT_END_F### INSERT INTO `test`.`t1`### SET###   @1=1  INT Meta=0 nullable=1 is_null=0 ###   @2=a VARSTRING(30) Meta=30 nullable=1 is_null=0 */
79
2 b flags: STMT_END_F### UPDATE `test`.`t1`### WHERE###   @### SET###   @c flags: STMT_END_F### DELETE FROM `test`.`t1`### WHERE###   @;
DEliMITER ;# End of log */;

 

对于STATEMENT格式的binlog，所有的DML *** 作都记录在query_EVENT中，而对于ROW格式的binlog，所有的DML *** 作都记录在ROWS_EVENT中，ROWS_EVENT分为三种：WRITE_ROWS_EVENT，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT，分别对应insert，update和delete *** 作。

对于insert *** 作，WRITE_ROWS_EVENT包含了要插入的数据

对于update *** 作，UPDATE_ROWS_EVENT不仅包含了修改后的数据，还包含了修改前的值。

对于delete *** 作，仅仅需要指定删除的主键（在没有主键的情况下，会给定所有列）

 

事实上，在ROW格式的binlog文件中， 每个ROWS_EVENT事件前都会有一个table_MAP_EVENT，用于描述表的内部ID和结构定义。

即便上述两个insert *** 作，一个没有执行use test *** 作，都不影响table_MAP_EVENT的内容，这也会导致基于ROW格式下的库级别的复制和基于STATEMENT格式下库级别的复制的复制规则不一致。

 

@H_301_34@如何在ROW格式中输出原生的DML语句？

MysqL实际上提供了一个参数，可以用于输出原生的DML语句，但是该语句仅仅是其注释的作用，并不会被应用。

如下所示，

MysqLset binlog_rows_query_log_events3,1); Font-weight: bold">0.00 sec)

 

对应的二进制的内容如下：

MysqLMysqL-bin.000026000026 192 | Rows_query  244 | # ')        292 334 365  xID=189 6 rows 0.00 sec)

实际上，MysqL新增了一个事务类型来输出ROW格式中原生的DML语句，即ROWS_query_EVENT。

 

自此以后，再也不用顾虑ROW格式的二进制日志中无法查看原生的DML语句了。

 

参考

1. MariaDB原理与实现

 

总结

以上是内存溢出为你收集整理的浅析MySQL基于ROW格式的二进制日志全部内容，希望文章能够帮你解决浅析MySQL基于ROW格式的二进制日志所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。