mysql 设置binlog row_用mysqlbinlog查看row格式的事件

小鱼儿 2023-01-11 12:00 90阅读 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