|
/*!*/;
# at 4291#160722 17:02:38 server id 62 end_log_pos 5079 32:38 server read_id=60 exec_time=4294967271 error_code=0 use `percona`/*!*/;
SET TIMESTAMP=1469178158/*!*/;
CREATE TABLE IF NOT EXISTS `percona`.`checksums` (🠟 char(64) NOT NULL,
tbl char(64) NOT NULL,
chunk int NULL,
chunk_index varchar(200) NULL,🎠
upper_boundary text NULL,
,
this_cnt int NOT NULL,
master_crc char(40) NULL,
master_cnt int NULL,
ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (db, tbl, chunk),
INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB
/*!*/;
# at 5079
#160820 10:21:1050 8e Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1471659670/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
GRANT ALL PRIVWE ON. BY PASSWORD '*2976819BD2CCD13612E03F812A2CD297C1A18B23'
/*!*/;
# at 5280
#16088 CRC32 0x85811be7 Query thread_id=18 exec_time=0 error_code=0 testtest `/*!*/;
SET TIMESTAMP=1471659760/*!*/;
create table test1(id int,c1 varchar(20),type int,address varchar(20),create_time date)* !*/;
# at 5445
#160820 10:24:34 server id 63 end_log_pos 5580 CRC exec_time=0 error_code=0
SET TIMESTAMP=1471659874/*!*/;
alter table test1 modify `address` varchar(200) DEFAULT NULL
/*!*/;
# at 5580
#160820 10:24:36 伺服器id 63 end_log_pos 5660 CRC32 0x7b7c645f 查詢 thread_id=1800time
SET TIMESTAMP=1471659876/*!*/ ;
SET @@session.time_zone='SYSTEM'/ *!*/;
BEGIN
/*!*/;
# 在5660
# 在5764
#160820 10:24:36 CRCID 63 58 Table_map : `測試`.`測試1 ` 對應編號74
#，位於5821
#160820 10:24:36 伺服器id 63 end_log_pos 5911 CRC32 0x2f577f52 STM 標記： BINLOG 'ZL+3VxM/AAAAOQAAAL0WAAAAAEoAAAAAAAEABHRlc3QABXRlc3QxAAUDDwMPEgU8AFgCAB/ DlLwI ZL+3Vx4/AAAAWgAAABcXAAAAAEoAAAAAAAEAAgAF/+ABAAAACHpoYW5nc2FuAQAAAB4AemhhbmdzYW4gcm9hZCBObyA4NemhhAmmA4NN; # 在5911
#160820 10:24:36 伺服器ID 63 end_log_pos 5942 CRC32 0xb26af81b Xid = 19999926 /* !*/;
# 在5942
#160820 10:24:48 伺服器id 63 end_log_pos 6022 CRC exec_time=0 error_code=0
設定時間戳=1471659888/*!*/;
開始
/*!*/;
# at 6022
# at 6122
#160820 10:24:48 伺服器ID 63 ：`test`.`test1` 映射到數字7 4
#在6179
#160820 10:24:48 伺服器id 63 end_log_pos 6265 CRC32 0x5f7ad700 '
cL+3VxM /AAAAOQAAACMYAAAAAAEoAAAAAAAEABHRlc3QABXRlc3QxAAUDDwMPEgU8AFgCAB9Lw23c
cL+3Vx4/AAAAVgAAAHAAAAAAAAAAAAAAAAAEA /+ACAAAABGxpc2kBAAAAHgB6aGFuZ3NhbiBy b2FkIE5vIDg3MCxmbG9vciA2MDKZmiimMADXel8=
'/*!*/; end_log_pos 6296 CRC32 0xf6833d28 Xid = 200
COMMIT/*!*/; # at 6296
#160820 10:31:30 伺服器id 63 end_log_pos 6343 CRC32 0xcfcdd344 日誌檔案結束
ROLLBACK /*由mysqlbinlog 新增*/ ;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SL*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/test _test
3、透過--base64-output 執行指令是：/usr /local/mysql/bin/mysqlbinlog --base64-output=decode-rows -v mysql-bin.000215
可以看到以下效果，不過都是最原始的d
[root@hch_test_dbm1_121_63 binlog]# /usr/local/mysql/bin/mysqlbinlog --base64-output=decode-rows -v binlog --base64-output=decode-rows -v mylog-bin EUDO_SLAVE_MODE= 1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@COMPLETION_TYPE,COMPLETIONS888/LIS/E:202; ……#160820 10:24:36 server id 63 end_log_pos 5821 CRC32 0x08bc94c3 Table_map: `test`.`157775 月#160820 10:24:36 server id 63 end_log_pos 5911 CRC32 0x2f577f52 寫| ### @1=1
### @2='zhangsan'
### @3=1
### @4='zhangsan road No ### @4='zhangsan road No 870, oor 6050 =' 2016-08-20 10:24:36'
# at 5911#160820 10:24:36 server idaf3 end_log_pos 5942 26910bx MIT/*!*/; # at 5942 #160820 10:24:48 server id 63 end_log_pos 6022 CRC32 0x09eab31d Query 0 SET TIMESTAMP=1471659888/*!*/;BEGIN /*!*/; # at 6022 # at 6122#160820 10:24:48 server id 63 end_log_pos 6179 CRC32 0xdc634b test test34b test_app # at 6179
#160820 10:24 :48 server id 63 end_log_pos 6265 CRC32 0x5f7ad700 Write_rows: table id 74 flags: STMT_END_F# test_ ERT INTO ``test11``test INS`test_``test.
### @1= 2
### @2='lisi'
### @3=11
### @3=1
### @3=1
## # @5='2016-08-20 10:24:48'
# at 6265
#160820 10:24:48 server id 63 #160820 10:24:48 server id 63 69385 200
COMMIT/*!*/; # at 6296
#160820 10:31:30 server id 63 end_log_pos 6343 CRC32 0xcfcdd344 # End of log file
ROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
PS：這樣是可以看到了 塊，而看不到原始的過來的insert語句，很多時候排查問題需要原始的insert語句而不是底層的insert塊。所以問題來了，如何看到最原始的insert sql語句呢？
4、透過查看
（4.1）先在線上設置全域的binlog_rows_query_log_events參數，刷新日誌：
mysql> Query OK, 0 位logs;Query OK, 0 rows affected (0.01 sec) mysql>exit
|
（4.2）然後重新登錄錄入新的數據記錄：
| [root@hch_test_dbm1_121_63 ~]# mysql -utim -ptimgood -h192.168.121.63facesql -utim -ptimgood -h192.168.121.633faced inface faces.
Welcome to the MySQL monitor . Commands end with ; or g.
Your MySQL connection id is 19
Server version: 5.6.12-log Source distribution. served . Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveiliates. Other names may be trademarks of their respectiveiliates. Other names may be trademarks of their respectiveiliates. help. Type 'c' to clear the current input statement.
mysql>|
mysql> use test;
Database changed
my> sert
Database ,floor 603',now();
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
（4.3）解析binlog，没有看到原始的insert语句
|
[root@hch_test_dbm1_121_63 binlog]# /usr/local/mysql/bin/mysqlbinlog --base64-output=decode-rows -v mysql-bin.000216 >1.sql
[root@hch_test_dbm1_121_63 binlog]# more 1.sql
/*!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
#160820 10:31:30 server id 63 end_log_pos 120 CRC32 0x1e4d0366 Start: binlog v 4, server v 5.6.12-log created 160820 10:31:30
# Warning: this binlog is either in use or was not closed properly.
# at 120
#160820 10:32:04 server id 63 end_log_pos 200 CRC32 0xab0e625e Query thread_id=19 exec_time=0 error_code=0
SET TIMESTAMP=1471660324/*!*/;
SET @@session.pseudo_thread_id=19/*!*/;
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.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 200
# at 302
#160820 10:32:04 server id 63 end_log_pos 359 CRC32 0x41bf2876 Table_map: `test`.`test1` mapped to number 74
# at 359
#160820 10:32:04 server id 63 end_log_pos 447 CRC32 0x1a3ab3d8 Write_rows: table id 74 flags: STMT_END_F
### INSERT INTO `test`.`test1`
### SET
### @1=3
### @2='wanger'
### @3=3
### @4='zhangsan road No 870,floor 603'
### @5='2016-08-20 10:32:04'
# at 447
#160820 10:32:04 server id 63 end_log_pos 478 CRC32 0xc5081021 Xid = 208
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@hch_test_dbm1_121_63 binlog]#
|
（4.4）通过mysql的命令行查看最原始的insert sql语句，如下所示：
|
mysql> show binlog events in 'mysql-bin.000216';
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------+
| mysql-bin.000216 | 4 | Format_desc | 63 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 |
| mysql-bin.000216 | 120 | Query | 63 | 200 | BEGIN || mysql-bin.000216 | 200 | Rows_query | 63 | 302 | # insert into test1 select 3,'wanger','3','zhangsan road No 870,floor 603',now() || mysql-bin.000216 | 302 | Table_map | 63 | 359 | table_id: 74 (test.test1) |
| mysql-bin.000216 | 359 | Write_rows | 63 | 447 | table_id: 74 flags: STMT_END_F |
| mysql-bin.000216 | 447 | Xid | 63 | 478 | COMMIT /* xid=208 */ |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
mysql>
|
5、試驗總結
基於以上的試驗，可以得出在row模式下，可以通過mysqlbinlog --base64-output=decode-rows –v查看最底層的insert資料模組，也可以透過命令列show binlog events in 'mysql-bin.000216';來即時查看最原始的insertsql記錄。
我們可以在搭建資料庫的時候，在啟動參數檔案my.cnf裡面事先設定好，如下圖所示：
|
#vim my..commcn =row # binlog
日誌格式
binlog_rows_query_log_events = 1 #
以上是MySQL 解密--> 如何查看二進位日誌ROW模式下最原始的SQL語句的內容，更多相關內容請關注PHP中文網（www.php.cn）！
|
|
|
|
