MySQL mysqlbinlog 解析出的 SQL 语句被注释是怎么回事

2019独角兽企业重金招聘Python工程师标准>>>

一网友反馈使用mysqlbinlog解析出的二进制日志中的内容中，有些SQL语句有#注释的情况，这个是怎么回事呢？我们通过实验来了解一下具体细节情况，如下所示，实验环境为5.6.20-enterprise-commercial-advanced-log

# whereis mysqlbinlog

mysqlbinlog: /usr/bin/mysqlbinlog /usr/share/man/man1/mysqlbinlog.1.gz

我们先在参数文件my.cnf里面设置binlog_format=ROW ，然后重启一下MySQL服务

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

mysql> show variables like 'binlog_format'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW   | +---------------+-------+ 1 row in set (0.00 sec)    mysql> show master status; +----------------------+----------+--------------+------------------+-------------------+ | File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------+----------+--------------+------------------+-------------------+ | DB-Server-bin.000005 |      512 |              |                  |                   | +----------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)    mysql>          mysql> drop table kkk; Query OK, 0 rows affected (0.01 sec)    mysql> create table kkk (id int ,name varchar(32)); Query OK, 0 rows affected (0.02 sec)    mysql> insert into kkk     -> select 100, 'name' union all     -> select 200, 'kerry' union all     -> select 300, 'k3'; Query OK, 3 rows affected (0.02 sec) Records: 3  Duplicates: 0  Warnings: 0    mysql>

默认情况下只能看到一些经过base-64编码的信息，如下所示：

[root@DB-Server ~]# /usr/bin/mysqlbinlog  /data/mysql/DB-Server-bin.000005

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

use `MyDB`/*!*/; SET TIMESTAMP=1530288296/*!*/; DROP TABLE `kkk` /* generated by server */ /*!*/; # at 628 #180629 12:05:14 server id 1  end_log_pos 745 CRC32 0xc6037a3f  Query   thread_id=3     exec_time=0     error_code=0 SET TIMESTAMP=1530288314/*!*/; create table kkk (id int ,name varchar(32)) /*!*/; # at 745 #180629 12:06:07 server id 1  end_log_pos 817 CRC32 0x74fd4efb  Query   thread_id=3     exec_time=0     error_code=0 SET TIMESTAMP=1530288367/*!*/; BEGIN /*!*/; # at 817 #180629 12:06:07 server id 1  end_log_pos 866 CRC32 0xfb1391dd  Table_map: `MyDB`.`kkk` mapped to number 73 # at 866 #180629 12:06:07 server id 1  end_log_pos 930 CRC32 0xecb4e812  Write_rows: table id 73 flags: STMT_END_F    BINLOG ' 71g2WxMBAAAAMQAAAGIDAAAAAEkAAAAAAAEABE15REIAA2trawACAw8CYAAD3ZET+w== 71g2Wx4BAAAAQAAAAKIDAAAAAEkAAAAAAAEAAgAC//xkAAAABG5hbWX8yAAAAAVrZXJyefwsAQAA AmszEui07A== '/*!*/; # at 930 #180629 12:06:07 server id 1  end_log_pos 961 CRC32 0x7ca988e3  Xid = 37 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

mysqlbinlog有一个参数–verbose(或-v)，将自动生成带注释的SQL语句（在行事件中重构伪SQL语句），其实这个并非原始SQL语句，而是伪SQL，如果使用这个参数两次(如-v -v)，则输出列的描述信息，会生成字段的类型、长度、是否为null等属性信息：

-v, –verbose       Reconstruct pseudo-SQL statements out of row events. -v  -v adds comments on column data types.

[root@DB-Server ~]# /usr/bin/mysqlbinlog -v /data/mysql/DB-Server-bin.000005

如上所示，其实这里的SQL语句不是原始SQL语句，那么能否看到原始SQL语句呢？答案是可以，但是必须设置系统变量binlog_rows_query_log_events

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

mysql> show variables like 'binlog_rows_query_log_events'; +------------------------------+-------+ | Variable_name                | Value | +------------------------------+-------+ | binlog_rows_query_log_events | OFF   | +------------------------------+-------+ 1 row in set (0.00 sec)    mysql> set binlog_rows_query_log_events=1; Query OK, 0 rows affected (0.00 sec)    mysql> flush logs; Query OK, 0 rows affected (0.01 sec)    mysql>  show master status; +----------------------+----------+--------------+------------------+-------------------+ | File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------+----------+--------------+------------------+-------------------+ | DB-Server-bin.000026 |      120 |              |                  |                   | +----------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)    mysql> mysql> insert into kkk select 600, 'k600'; Query OK, 1 row affected (0.00 sec) Records: 1  Duplicates: 0  Warnings: 0

[root@DB-Server ~]# /usr/bin/mysqlbinlog  –base64-output=DECODE-ROWS  -v -v /data/mysql/DB-Server-bin.000026

在二进制日志格式为MIXED模式下，简单测试没有发现SQL被注释的情况，记录的都是原始的SQL语句。不清楚是否存在某些特殊情况也会出现这种情况。网友反馈腾讯云的MySQL在MIXED模式下，也会出现这种情况，不过没有使用过腾讯的MySQL，手头也没有测试环境，只能作罢！

转载于:https://my.oschina.net/u/3906190/blog/1920299