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开始， mysql binlog多了个参数–verbose(或-v)，将改动生成带注释的语句，如果使用两次这个参数(如-v -v)，会生成字段的类型、长度、是否为null等属性信息。如下：
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参数。如下
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"
查看更多关于mysqlbinlogrow格式查看 - mysql数据库栏目 - 自学php的详细内容...