上文分析的二进制日志实际上是基于 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 原理与实现
来源: http://www.cnblogs.com/ivictor/p/5780037.html