怎么用mysqlbinlog查看row格式的事件

这篇文章主要介绍了怎么用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

感谢你能够认真阅读完这篇文章,希望小编分享的“怎么用mysqlbinlog查看row格式的事件”这篇文章对大家有帮助,同时也希望大家多多支持云搜网,关注云搜网行业资讯频道,更多相关知识等着你来学习!


【AD】美国洛杉矶/香港/日本VPS推荐,回程电信CN2 GIA线路,延迟低、稳定性高、免费备份_搬瓦工