Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.