一网友反馈利用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处事

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

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

劳务调派系统arnings: 0 mysql 默认环境下只能看到一些颠末base-64编码的信息" class="aligncenter size-full wp-image-29251" title="73542-20180716084651591-1183477897" src="/uploads/allimg/c180728/1532H209214610-11G0.png" />

如上所示，其实这里的SQL语句不是原始SQL语句，那么可否看到原始SQL语句呢？谜底是可以，可是必需配置系统变量binlog_rows_query_log_events

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

劳务调派系统arnings: 0 mysql 默认环境下只能看到一些颠末base-64编码的信息" class="aligncenter size-full wp-image-29252" title="73542-20180716084653386-547825400" src="/uploads/allimg/c180728/1532H20921VP-2U09.png" />

在二进制日志名目为MIXED模式下，简朴测试没有发明SQL被注释的环境，昆山软件开发，记录的都是原始的SQL语句。不清楚是否存在某些非凡环境也会呈现这种环境。网友反馈腾讯云的MySQL在MIXED模式下，也会呈现这种环境，不外没有利用过腾讯的MySQL，手头也没有测试情况，只能作罢！