TA貢獻2012條經驗 獲得超12個贊
mysqlbinlog功能是將MySQL的binlog日志轉換成MySQL文本SQL語句，默認情況下binlog日志是二進制文件.用file命令查看文件類型$filemysql-bin.002149mysql-bin.002149:MySQLreplicationlog直接用xxd命令查看（二進制）$xxd-l1024mysql-bin.00214900000000:fe62696e562a5f5d0f01000000770000.binV*_].....w..00000010:007b00000000000400352e372e32372d.{.......5.7.27-00000020:307562756e7475302e31362e30342e310ubuntu0.16.04.100000030:2d6c6f67000000000000000000000000-log............00000040:00000000000000000000000000000013................轉換日志成文本的sql看一下$mysqlbinlog--no-defaults/var/lib/mysql/mysql-bin.002149>~/test.sql$head~/test.sql輸出/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER/*!*/;#at4#1908227:51:43serverid1end_log_pos123CRC320x6c47a911Start:binlogv4,serverv5.7.27-0ubuntu0.16.04.1-logcreated1908227:51:43BINLOG'D9ldXQ8BAAAAdwAAAHsAAAAAAAQANS43LjI3LTB1YnVudHUwLjE2LjA0LjEtbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQAARGpR2w='/*!*/;輸出雖然大部分是文本，但因為采用的mixed模式，也有類似BINLOG類似的指令，這部分是無法轉成sql語句的。也就無法查看了。對這段神秘的BINLOG解析一下：echo"D9ldXQ8BAAAAdwAAAHsAAAAAAAQANS43LjI3LTB1YnVudHUwLjE2LjA0LjEtbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQAARGpR2w="|base64-d|xxd將會輸出：00000000:0fd95d5d0f01000000770000007b0000..]].....w...{..00000010:0000000400352e372e32372d30756275.....5.7.27-0ubu00000020:6e7475302e31362e30342e312d6c6f67ntu0.16.04.1-log00000030:00000000000000000000000000000000................00000040:000000000000000000000013380d0008............8...00000050:001200040404041200005f00041a0800.........._.....00000060:0000080808020000000a0a0a2a2a0012............**..00000070:34000111a9476c4....Gl和前面那段似曾相識。mysqlbinlog加–base64-output=DECODE-ROWS-v參數可以把BINLOG部分轉化成可讀的注釋掉的SQL,但不是所有的都能成功轉化。
舉報