用mysqlbinlog檢視row格式的事件

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等屬性資訊。如下：

[@more@]

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

這樣看起來清晰多了吧？