How to use mysqlbinlog to view events in row format
This article mainly introduces how to use mysqlbinlog to view events in row format, with a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand it.
Since MySQL 5.1, binlog supports the format of row-based. By default, you can only see some base-64-encoded information, such as
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
Com _ MIT _ blank /
DELIMITER
# End of log file
Here, we can only see that the `test`.`test1` table has been changed, but I don't know what has been changed, so how can we see what has been changed? Starting from MySQL 5.1.28, mysqlbinlog has an extra parameter-verbose (or-v), which will be changed to generate annotated statements. If you use this parameter twice (such as-v-v), attribute information such as field type, length, and whether it is null will be generated. As follows:
[@ 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
# @ 1room3 / * INT meta=0 nullable=1 is_null=0 * /
# @ 2 VARSTRING (60) meta=60 nullable=1 is_null=0 * /
# at 7494045
# 090827 13:20:40 server id 1 end_log_pos 7494072 Xid = 2525562
Com _ MIT _ blank /
DELIMITER
# End of log file
Can you read it now? But there is also a problem, the information at the beginning of BINLOG will still be displayed, it is very ugly, can you remove it? The answer is yes, plus the-- base64-output=DECODE-ROWS parameter. As follows
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
# @ 1room3 / * INT meta=0 nullable=1 is_null=0 * /
# @ 2 VARSTRING (60) meta=60 nullable=1 is_null=0 * /
# at 7494045
# 090827 13:20:40 server id 1 end_log_pos 7494072 Xid = 2525562
Com _ MIT _ blank /
DELIMITER
# End of log file
Thank you for reading this article carefully. I hope the article "how to use mysqlbinlog to view events in row format" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!
