你的位置:首页 > 数据库

[数据库]浅析MySQL基于ROW格式的二进制日志


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

 

首先,来几条测试数据

mysql> set binlog_format=row;Query OK, 0 rows affected (0.00 sec)mysql> flush logs;Query OK, 0 rows affected (0.01 sec)mysql> insert into test.t1 values(1,'a');Query OK, 1 row affected (0.00 sec)mysql> use testDatabase changed
mysql> insert into t1 values(2,'b');Query OK, 1 row affected (0.00 sec)mysql> update t1 set name='c' where id=2;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> delete from t1 where id=1;Query OK, 1 row affected (0.01 sec)

 

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

mysql> show binlog events in 'mysql-bin.000025';+------------------+-----+-------------+-----------+-------------+---------------------------------------+| Log_name     | Pos | Event_type | Server_id | End_log_pos | Info                 |+------------------+-----+-------------+-----------+-------------+---------------------------------------+| mysql-bin.000025 |  4 | Format_desc |     1 |     120 | Server ver: 5.6.31-log, Binlog ver: 4 || mysql-bin.000025 | 120 | Query    |     1 |     188 | BEGIN                 || mysql-bin.000025 | 188 | Table_map  |     1 |     236 | table_id: 79 (test.t1)        || mysql-bin.000025 | 236 | Write_rows |     1 |     278 | table_id: 79 flags: STMT_END_F    || mysql-bin.000025 | 278 | Xid     |     1 |     309 | COMMIT /* xid=175 */         || mysql-bin.000025 | 309 | Query    |     1 |     381 | BEGIN                 || mysql-bin.000025 | 381 | Table_map  |     1 |     429 | table_id: 79 (test.t1)        || mysql-bin.000025 | 429 | Write_rows |     1 |     471 | table_id: 79 flags: STMT_END_F    || mysql-bin.000025 | 471 | Xid     |     1 |     502 | COMMIT /* xid=183 */         || mysql-bin.000025 | 502 | Query    |     1 |     574 | BEGIN                 || mysql-bin.000025 | 574 | Table_map  |     1 |     622 | table_id: 79 (test.t1)        || mysql-bin.000025 | 622 | Update_rows |     1 |     672 | table_id: 79 flags: STMT_END_F    || mysql-bin.000025 | 672 | Xid     |     1 |     703 | COMMIT /* xid=184 */         || mysql-bin.000025 | 703 | Query    |     1 |     775 | BEGIN                 || mysql-bin.000025 | 775 | Table_map  |     1 |     823 | table_id: 79 (test.t1)        || mysql-bin.000025 | 823 | Delete_rows |     1 |     865 | table_id: 79 flags: STMT_END_F    || mysql-bin.000025 | 865 | Xid     |     1 |     896 | COMMIT /* xid=185 */         |+------------------+-----+-------------+-----------+-------------+---------------------------------------+17 rows in set (0.00 sec)

 

再来通过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 160817 10:20:16# Warning: this binlog is either in use or was not closed properly.BINLOG '4MmzVw8BAAAAdAAAAHgAAAABAAQANS42LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAU+sFVs='/*!*/;# at 120#160817 10:20:22 server id 1 end_log_pos 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, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1075838976/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;BEGIN/*!*/;# at 188#160817 10:20:22 server id 1 end_log_pos 236 CRC32 0x2b8d2069   Table_map: `test`.`t1` mapped to number 79# at 236#160817 10:20:22 server id 1 end_log_pos 278 CRC32 0xadc98fbc   Write_rows: table id 79 flags: STMT_END_FBINLOG '5smzVxMBAAAAMAAAAOwAAAAAAE8AAAAAAAEABHRlc3QAAnQxAAIDDwIeAANpII0r5smzVx4BAAAAKgAAABYBAAAAAE8AAAAAAAEAAgAC//wBAAAAAWG8j8mt'/*!*/;# at 278#160817 10:20:22 server id 1 end_log_pos 309 CRC32 0x552dc682   Xid = 175COMMIT/*!*/;# at 309#160817 10:20:34 server id 1 end_log_pos 381 CRC32 0x17d8173e   Query  thread_id=12  exec_time=0  error_code=0SET TIMESTAMP=1471400434/*!*/;BEGIN/*!*/;# at 381#160817 10:20:34 server id 1 end_log_pos 429 CRC32 0x71a27e19   Table_map: `test`.`t1` mapped to number 79# at 429#160817 10:20:34 server id 1 end_log_pos 471 CRC32 0xefda98ca   Write_rows: table id 79 flags: STMT_END_FBINLOG '8smzVxMBAAAAMAAAAK0BAAAAAE8AAAAAAAEABHRlc3QAAnQxAAIDDwIeAAMZfqJx8smzVx4BAAAAKgAAANcBAAAAAE8AAAAAAAEAAgAC//wCAAAAAWLKmNrv'/*!*/;# at 471#160817 10:20:34 server id 1 end_log_pos 502 CRC32 0x7bed11c4   Xid = 183COMMIT/*!*/;# at 502#160817 10:20:38 server id 1 end_log_pos 574 CRC32 0xd164b750   Query  thread_id=12  exec_time=0  error_code=0SET TIMESTAMP=1471400438/*!*/;BEGIN/*!*/;# at 574#160817 10:20:38 server id 1 end_log_pos 622 CRC32 0x9fa3cabc   Table_map: `test`.`t1` mapped to number 79# at 622#160817 10:20:38 server id 1 end_log_pos 672 CRC32 0xb1646398   Update_rows: table id 79 flags: STMT_END_FBINLOG '9smzVxMBAAAAMAAAAG4CAAAAAE8AAAAAAAEABHRlc3QAAnQxAAIDDwIeAAO8yqOf9smzVx8BAAAAMgAAAKACAAAAAE8AAAAAAAEAAgAC///8AgAAAAFi/AIAAAABY5hjZLE='/*!*/;# at 672#160817 10:20:38 server id 1 end_log_pos 703 CRC32 0x91a90c52   Xid = 184COMMIT/*!*/;# at 703#160817 10:20:43 server id 1 end_log_pos 775 CRC32 0x5ae24c0b   Query  thread_id=12  exec_time=0  error_code=0SET TIMESTAMP=1471400443/*!*/;BEGIN/*!*/;# at 775#160817 10:20:43 server id 1 end_log_pos 823 CRC32 0x33c52e84   Table_map: `test`.`t1` mapped to number 79# at 823#160817 10:20:43 server id 1 end_log_pos 865 CRC32 0x77e907a2   Delete_rows: table id 79 flags: STMT_END_FBINLOG '+8mzVxMBAAAAMAAAADcDAAAAAE8AAAAAAAEABHRlc3QAAnQxAAIDDwIeAAOELsUz+8mzVyABAAAAKgAAAGEDAAAAAE8AAAAAAAEAAgAC//wBAAAAAWGiB+l3'/*!*/;# at 865#160817 10:20:43 server id 1 end_log_pos 896 CRC32 0xb0988385   Xid = 185COMMIT/*!*/;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语句

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

# mysqlbinlog mysql-bin.000025 -vv --base64-output=decode-rows

/*!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 160817 10:20:16# Warning: this binlog is either in use or was not closed properly.
# at 120#160817 10:20:22 server id 1 end_log_pos 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, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1075838976/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;BEGIN/*!*/;
# at 188#160817 10:20:22 server id 1 end_log_pos 236 CRC32 0x2b8d2069 Table_map: `test`.`t1` mapped to number 79# at 236#160817 10:20:22 server id 1 end_log_pos 278 CRC32 0xadc98fbc Write_rows: table id 79 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 */
# at 278#160817 10:20:22 server id 1 end_log_pos 309 CRC32 0x552dc682 Xid = 175COMMIT/*!*/;
# at 309#160817 10:20:34 server id 1 end_log_pos 381 CRC32 0x17d8173e Query thread_id=12 exec_time=0 error_code=0SET TIMESTAMP=1471400434/*!*/;BEGIN/*!*/;
# at 381#160817 10:20:34 server id 1 end_log_pos 429 CRC32 0x71a27e19 Table_map: `test`.`t1` mapped to number 79
# at 429#160817 10:20:34 server id 1 end_log_pos 471 CRC32 0xefda98ca Write_rows: table id 79 flags: STMT_END_F### INSERT INTO `test`.`t1`### SET### @1=2 /* INT meta=0 nullable=1 is_null=0 */### @2='b' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
# at 471#160817 10:20:34 server id 1 end_log_pos 502 CRC32 0x7bed11c4 Xid = 183COMMIT/*!*/;
# at 502#160817 10:20:38 server id 1 end_log_pos 574 CRC32 0xd164b750 Query thread_id=12 exec_time=0 error_code=0SET TIMESTAMP=1471400438/*!*/;BEGIN/*!*/;
# at 574#160817 10:20:38 server id 1 end_log_pos 622 CRC32 0x9fa3cabc Table_map: `test`.`t1` mapped to number 79
# at 622#160817 10:20:38 server id 1 end_log_pos 672 CRC32 0xb1646398 Update_rows: table id 79 flags: STMT_END_F### UPDATE `test`.`t1`### WHERE### @1=2 /* INT meta=0 nullable=1 is_null=0 */### @2='b' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */### SET### @1=2 /* INT meta=0 nullable=1 is_null=0 */### @2='c' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
# at 672#160817 10:20:38 server id 1 end_log_pos 703 CRC32 0x91a90c52 Xid = 184COMMIT/*!*/;
# at 703#160817 10:20:43 server id 1 end_log_pos 775 CRC32 0x5ae24c0b Query thread_id=12 exec_time=0 error_code=0SET TIMESTAMP=1471400443/*!*/;BEGIN/*!*/;
# at 775#160817 10:20:43 server id 1 end_log_pos 823 CRC32 0x33c52e84 Table_map: `test`.`t1` mapped to number 79
# at 823#160817 10:20:43 server id 1 end_log_pos 865 CRC32 0x77e907a2 Delete_rows: table id 79 flags: STMT_END_F### DELETE FROM `test`.`t1`### WHERE### @1=1 /* INT meta=0 nullable=1 is_null=0 */### @2='a' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
# at 865#160817 10:20:43 server id 1 end_log_pos 896 CRC32 0xb0988385 Xid = 185COMMIT/*!*/;
DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 

对于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格式下库级别的复制的复制规则不一致。

 

如何在ROW格式中输出原生的DML语句?

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

如下所示,

mysql> flush logs;Query OK, 0 rows affected (0.01 sec)mysql> set binlog_rows_query_log_events=1;Query OK, 0 rows affected (0.01 sec)mysql> insert into t1 values(3,'c');Query OK, 1 row affected (0.00 sec)

 

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

mysql> show binlog events in 'mysql-bin.000026';+------------------+-----+-------------+-----------+-------------+---------------------------------------+| Log_name     | Pos | Event_type | Server_id | End_log_pos | Info                 |+------------------+-----+-------------+-----------+-------------+---------------------------------------+| mysql-bin.000026 |  4 | Format_desc |     1 |     120 | Server ver: 5.6.31-log, Binlog ver: 4 || mysql-bin.000026 | 120 | Query    |     1 |     192 | BEGIN                 || mysql-bin.000026 | 192 | Rows_query |     1 |     244 | # insert into t1 values(3,'c')    || mysql-bin.000026 | 244 | Table_map  |     1 |     292 | table_id: 79 (test.t1)        || mysql-bin.000026 | 292 | Write_rows |     1 |     334 | table_id: 79 flags: STMT_END_F    || mysql-bin.000026 | 334 | Xid     |     1 |     365 | COMMIT /* xid=189 */         |+------------------+-----+-------------+-----------+-------------+---------------------------------------+6 rows in set (0.00 sec)

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

 

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

 

参考

1. MariaDB原理与实现