中文字幕在线观看,亚洲а∨天堂久久精品9966,亚洲成a人片在线观看你懂的,亚洲av成人片无码网站,亚洲国产精品无码久久久五月天

MySQL mysqlbinlog 解析出的 SQL 語(yǔ)句被注釋是怎么回事

2018-07-28    來(lái)源:importnew

容器云強(qiáng)勢(shì)上線!快速搭建集群,上萬(wàn)Linux鏡像隨意使用

一網(wǎng)友反饋使用mysqlbinlog解析出的二進(jìn)制日志中的內(nèi)容中,有些SQL語(yǔ)句有#注釋的情況,這個(gè)是怎么回事呢?我們通過(guò)實(shí)驗(yàn)來(lái)了解一下具體細(xì)節(jié)情況,如下所示,實(shí)驗(yàn)環(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里面設(shè)置binlog_format=ROW ,然后重啟一下MySQL服務(wù)

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>

默認(rèn)情況下只能看到一些經(jīng)過(guò)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有一個(gè)參數(shù)–verbose(或-v),將自動(dòng)生成帶注釋的SQL語(yǔ)句(在行事件中重構(gòu)偽SQL語(yǔ)句),其實(shí)這個(gè)并非原始SQL語(yǔ)句,而是偽SQL,如果使用這個(gè)參數(shù)兩次(如-v -v),則輸出列的描述信息,會(huì)生成字段的類型、長(zhǎng)度、是否為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

如上所示,其實(shí)這里的SQL語(yǔ)句不是原始SQL語(yǔ)句,那么能否看到原始SQL語(yǔ)句呢?答案是可以,但是必須設(shè)置系統(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

在二進(jìn)制日志格式為MIXED模式下,簡(jiǎn)單測(cè)試沒(méi)有發(fā)現(xiàn)SQL被注釋的情況,記錄的都是原始的SQL語(yǔ)句。不清楚是否存在某些特殊情況也會(huì)出現(xiàn)這種情況。網(wǎng)友反饋騰訊云的MySQL在MIXED模式下,也會(huì)出現(xiàn)這種情況,不過(guò)沒(méi)有使用過(guò)騰訊的MySQL,手頭也沒(méi)有測(cè)試環(huán)境,只能作罷!

標(biāo)簽: Mysql

版權(quán)申明:本站文章部分自網(wǎng)絡(luò),如有侵權(quán),請(qǐng)聯(lián)系:west999com@outlook.com
特別注意:本站所有轉(zhuǎn)載文章言論不代表本站觀點(diǎn)!
本站所提供的圖片等素材,版權(quán)歸原作者所有,如需使用,請(qǐng)與原作者聯(lián)系。

上一篇:SpringBoot | 第零章:前言

下一篇:Linux 中如何通過(guò)設(shè)備號(hào)找到設(shè)備