MySQL mysqlbinlog 解析出的 SQL 語句被注釋是怎么回事
2018-07-28 來源:importnew

一網友反饋使用mysqlbinlog解析出的二進制日志中的內容中,有些SQL語句有#注釋的情況,這個是怎么回事呢?我們通過實驗來了解一下具體細節(jié)情況,如下所示,實驗環(huán)境為5.6.20-enterprise-commercial-advanced-log
# whereis mysqlbinlog
mysqlbinlog: /usr/bin/mysqlbinlog /usr/share/man/man1/mysqlbinlog.1.gz
我們先在參數(shù)文件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有一個參數(shù)–verbose(或-v),將自動生成帶注釋的SQL語句(在行事件中重構偽SQL語句),其實這個并非原始SQL語句,而是偽SQL,如果使用這個參數(shù)兩次(如-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語句呢?答案是可以,但是必須設置系統(tǒng)變量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
在二進制日志格式為MIXED模式下,簡單測試沒有發(fā)現(xiàn)SQL被注釋的情況,記錄的都是原始的SQL語句。不清楚是否存在某些特殊情況也會出現(xiàn)這種情況。網友反饋騰訊云的MySQL在MIXED模式下,也會出現(xiàn)這種情況,不過沒有使用過騰訊的MySQL,手頭也沒有測試環(huán)境,只能作罷!
標簽: Mysql
版權申明:本站文章部分自網絡,如有侵權,請聯(lián)系:west999com@outlook.com
特別注意:本站所有轉載文章言論不代表本站觀點!
本站所提供的圖片等素材,版權歸原作者所有,如需使用,請與原作者聯(lián)系。