mysql binlog row格式查看

來源:互聯網
上載者:User

標籤:style   使用   os   ar   2014   art   問題   line   

MySQL 5.1開始,binlog支援row-based的格式,預設情況下只能看到一些經過base-64編碼的資訊,如

DELIMITER /*!*/;
# at 7493962
#090827 5:25:03 server id 1 end_log_pos 0 Start: binlog v 4, server v 5.1.26-rc-community-log created 090827 5:25:03
BINLOG ‘
L6iVSg8BAAAAZgAAAAAAAAAAAAQANS4xLjI2LXJjLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
‘/*!*/;
# at 7493962
# at 7494009
#090827 13:20:40 server id 1 end_log_pos 7494009 Table_map: `test`.`test1` mapped to number 96991
#090827 13:20:40 server id 1 end_log_pos 7494045 Write_rows: table id 96991 flags: STMT_END_F

BINLOG ‘
qBeWShMBAAAALwAAAHlZcgAAAN96AQAAAAAABHRlc3QABXRlc3QxAAIDDwI8AAM=
qBeWShcBAAAAJAAAAJ1ZcgAQAN96AQAAAAEAAv/8AwAAAAEz
‘/*!*/;
# at 7494045
#090827 13:20:40 server id 1 end_log_pos 7494072 Xid = 2525562
COMMIT/*!*/;
DELIMITER ;
# End of log file

這裡只能看到`test`.`test1`表做了改動,但具體改了什麼,就不知道了,那麼怎樣才能看到到底改了什麼呢?從MySQL 5.1.28開始,mysqlbinlog多了個參數–verbose(或-v),將改動產生帶注釋的語句,如果使用兩次這個參數(如-v -v),會產生欄位的類型、長度、是否為null等屬性資訊。如下:

mysqlbinlog -v -v mysql-bin.001912

DELIMITER /*!*/;
# at 7493962
#090827 5:25:03 server id 1 end_log_pos 0 Start: binlog v 4, server v 5.1.26-rc-community-log created 090827 5:25:03
BINLOG ‘
L6iVSg8BAAAAZgAAAAAAAAAAAAQANS4xLjI2LXJjLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
‘/*!*/;
# at 7493962
# at 7494009
#090827 13:20:40 server id 1 end_log_pos 7494009 Table_map: `test`.`test1` mapped to number 96991
#090827 13:20:40 server id 1 end_log_pos 7494045 Write_rows: table id 96991 flags: STMT_END_F

BINLOG ‘
qBeWShMBAAAALwAAAHlZcgAAAN96AQAAAAAABHRlc3QABXRlc3QxAAIDDwI8AAM=
qBeWShcBAAAAJAAAAJ1ZcgAQAN96AQAAAAEAAv/8AwAAAAEz
‘/*!*/;
### INSERT INTO test.test1
### SET
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### @2=’3′ /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 7494045
#090827 13:20:40 server id 1 end_log_pos 7494072 Xid = 2525562
COMMIT/*!*/;
DELIMITER ;
# End of log file

這時能看懂了吧?但還有個問題,BINLOG開頭的那些資訊還是會顯示出來,很難看,能不能去掉呢?答案是肯定的,加–base64-output=DECODE-ROWS參數。如下

mysqlbinlog -v -v –base64-output=DECODE-ROWS mysql-bin.001912

DELIMITER /*!*/;
# at 7493962
#090827 5:25:03 server id 1 end_log_pos 0 Start: binlog v 4, server v 5.1.26-rc-community-log created 090827 5:25:03
# at 7493962
# at 7494009
#090827 13:20:40 server id 1 end_log_pos 7494009 Table_map: `test`.`test1` mapped to number 96991
#090827 13:20:40 server id 1 end_log_pos 7494045 Write_rows: table id 96991 flags: STMT_END_F
### INSERT INTO test.test1
### SET
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### @2=’3′ /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 7494045
#090827 13:20:40 server id 1 end_log_pos 7494072 Xid = 2525562
COMMIT/*!*/;
DELIMITER ;
# End of log file




樣本:  sudo mysqlbinlog -v -v --base64-output=DECODE-ROWS --start-datetime="2014-08-09 10:42:36" --stop-datetime="2014-08-10 10:42:36" mysql-bin.000005 |grep -A 65 "empolyee"

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.