用mysqlbinlog查看row格式的事件

Love The Way You Lie 2022-08-21 02:26 158阅读 0赞

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

这样看起来清晰多了吧？