浅析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: 0 mysql> 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 ' 4MmzVw8BAAAAdAAAAHgAAAABAAQANS42LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAU+s FVs= '/*!*/; # at 120 #160817 10:20:22 server id 1 end_log_pos 188 CRC32 0x005847f0 Query thread_id=12 exec_time=0 error_code=0 SET 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 BINLOG ' 5smzVxMBAAAAMAAAAOwAAAAAAE8AAAAAAAEABHRlc3QAAnQxAAIDDwIeAANpII0r 5smzVx4BAAAAKgAAABYBAAAAAE8AAAAAAAEAAgAC//wBAAAAAWG8j8mt '/*!*/; # at 278 #160817 10:20:22 server id 1 end_log_pos 309 CRC32 0x552dc682 Xid = 175 COMMIT/*!*/; # at 309 #160817 10:20:34 server id 1 end_log_pos 381 CRC32 0x17d8173e Query thread_id=12 exec_time=0 error_code=0 SET 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 BINLOG ' 8smzVxMBAAAAMAAAAK0BAAAAAE8AAAAAAAEABHRlc3QAAnQxAAIDDwIeAAMZfqJx 8smzVx4BAAAAKgAAANcBAAAAAE8AAAAAAAEAAgAC//wCAAAAAWLKmNrv '/*!*/; # at 471 #160817 10:20:34 server id 1 end_log_pos 502 CRC32 0x7bed11c4 Xid = 183 COMMIT/*!*/; # at 502 #160817 10:20:38 server id 1 end_log_pos 574 CRC32 0xd164b750 Query thread_id=12 exec_time=0 error_code=0 SET 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 BINLOG ' 9smzVxMBAAAAMAAAAG4CAAAAAE8AAAAAAAEABHRlc3QAAnQxAAIDDwIeAAO8yqOf 9smzVx8BAAAAMgAAAKACAAAAAE8AAAAAAAEAAgAC///8AgAAAAFi/AIAAAABY5hjZLE= '/*!*/; # at 672 #160817 10:20:38 server id 1 end_log_pos 703 CRC32 0x91a90c52 Xid = 184 COMMIT/*!*/; # at 703 #160817 10:20:43 server id 1 end_log_pos 775 CRC32 0x5ae24c0b Query thread_id=12 exec_time=0 error_code=0 SET 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 BINLOG ' +8mzVxMBAAAAMAAAADcDAAAAAE8AAAAAAAEABHRlc3QAAnQxAAIDDwIeAAOELsUz +8mzVyABAAAAKgAAAGEDAAAAAE8AAAAAAAEAAgAC//wBAAAAAWGiB+l3 '/*!*/; # at 865 #160817 10:20:43 server id 1 end_log_pos 896 CRC32 0xb0988385 Xid = 185 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* 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=0 SET 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 = 175 COMMIT/*!*/;
# at 309 #160817 10:20:34 server id 1 end_log_pos 381 CRC32 0x17d8173e Query thread_id=12 exec_time=0 error_code=0 SET 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 = 183 COMMIT/*!*/;
# at 502 #160817 10:20:38 server id 1 end_log_pos 574 CRC32 0xd164b750 Query thread_id=12 exec_time=0 error_code=0 SET 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 = 184 COMMIT/*!*/;
# at 703 #160817 10:20:43 server id 1 end_log_pos 775 CRC32 0x5ae24c0b Query thread_id=12 exec_time=0 error_code=0 SET 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 = 185 COMMIT/*!*/;
DELIMITER ; # End of log file ROLLBACK /* 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原理与实现
