Shulou(Shulou.com)06/01 Report--

Since MySQL binary log replication in the business environment is based on rows, the developer came over yesterday to query whether anyone had inserted the library.

The logs queried by the mysqlbinlog tool are all base-64-encoded information.

This is because binlog supports the row-based format since MySQL 5.1. by default, you can only see some information encoded by base-64.

Click (here) to collapse or open

# 151224 23:29:48 server id 1 end_log_pos 13376153 CRC32 0x974f9a2e Query thread_id=164727 exec_time=0 error_code=0

SET timestamp 1450970988

BEGIN

/ *! * /

# at 13376153

# 151224 23:29:48 server id 1 end_log_pos 13376236 CRC32 0x08e3e7fe Table_map: `guoqing`.`test` mapped to number

# at 13376236

# 151224 23:29:48 server id 1 end_log_pos 13376532 CRC32 0xbb7ed638 Update_rows: table id 255 flags: STMT_END_F

BINLOG'

BA98VhMBAAAAUwAAAOwazAAAAP8AAAAAAAEAB25pcnZhbmEACHRlcm1pbmFsAAwIDw8PDwgREREQ

Dw8RwADAAP0C/QIAAAABAJYAlgDcD/7n4wg=

BA98Vh8BAAAAKAEAABQczAAAAP8AAAAAAAEAAgAM/wDw5RUAAAAAAAAkRDVFRUJCNDYtRDI5

RC00QTVCLTk5QUYtMkEzRTIwRjE0RkU2IDAxMjlhYWUzYzJkYzQyYTBiODlmMTVjMDk2NmY0Mzdl

BwBDQVNISUVSAgBCWOSwAQAAAAAAVnwPKVZv1ydWfA8oAAANQ29mZmVlLzIuMC4xIADw5RUAAAAA

AAAkRDVFRUJCNDYtRDI5RC00QTVCLTk5QUYtMkEzRTIwRjE0RkU2IDAxMjlhYWUzYzJkYzQyYTBi

ODlmMTVjMDk2NmY0MzdlBwBDQVNISUVSAgBCWOSwAQAAAAAAVnwPbVZv1ydWfA9sAAANQ29mZmVl

LzIuMC4xIDjWfrs=

'/ *! * /

# at 13376532

# 151224 23:29:48 server id 1 end_log_pos 13376563 CRC32 0xa58e318d Xid = 486691

Com _ MIT _ blank /

# at 13376563

# 151224 23:30:00 server id 1 end_log_pos 13376647 CRC32 0xd718f5ce Query thread_id=123940 exec_time=0 error_code=0

We can only see that the `guoqing`.`test` 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 an annotated statement

If you use this parameter twice (for example,-v-v), attribute information such as the type, length, and null of the field will be generated. As follows:

Parameter: 0112inin0000-v-v

Click (here) to collapse or open

BINLOG'

BA98VhMBAAAAUwAAAOwazAAAAP8AAAAAAAEAB25pcnZhbmEACHRlcm1pbmFsAAwIDw8PDwgREREQ

Dw8RwADAAP0C/QIAAAABAJYAlgDcD/7n4wg=

BA98Vh8BAAAAKAEAABQczAAAAP8AAAAAAAEAAgAM/wDw5RUAAAAAAAAkRDVFRUJCNDYtRDI5

RC00QTVCLTk5QUYtMkEzRTIwRjE0RkU2IDAxMjlhYWUzYzJkYzQyYTBiODlmMTVjMDk2NmY0Mzdl

BwBDQVNISUVSAgBCWOSwAQAAAAAAVnwPKVZv1ydWfA8oAAANQ29mZmVlLzIuMC4xIADw5RUAAAAA

AAAkRDVFRUJCNDYtRDI5RC00QTVCLTk5QUYtMkEzRTIwRjE0RkU2IDAxMjlhYWUzYzJkYzQyYTBi

ODlmMTVjMDk2NmY0MzdlBwBDQVNISUVSAgBCWOSwAQAAAAAAVnwPbVZv1ydWfA9sAAANQ29mZmVl

LzIuMC4xIDjWfrs=

'/ *! * /

# UPDATE `guoqing`.`test`

# WHERE

# @ 1mm 537 / * LONGINT meta=0 nullable=0 is_null=0 * /

# @ 2pm 10.00 / * DECIMAL (10jue 2) meta=2562 nullable=1 is_null=0 * /

# @ 3000000 / * LONGINT meta=0 nullable=1 is_null=0 * /

# @ 4room0 / * LONGINT meta=0 nullable=1 is_null=0 * /

# @ 5 million 1000609 / * LONGINT meta=0 nullable=1 is_null=0 * /

# @ 6 hours shop / * VARSTRING meta=765 nullable=1 is_null=0 * /

# @ 7 TIMESTAMP 1450950696 / * TIMESTAMP (0) meta=0 nullable=0 is_null=0 * /

# SET

# @ 1mm 537 / * LONGINT meta=0 nullable=0 is_null=0 * /

# @ 2pm 10.00 / * DECIMAL (10jue 2) meta=2562 nullable=1 is_null=0 * /

# @ 3room999 / * LONGINT meta=0 nullable=1 is_null=0 * /

# @ 4room0 / * LONGINT meta=0 nullable=1 is_null=0 * /

# @ 5 million 1000609 / * LONGINT meta=0 nullable=1 is_null=0 * /

# @ 6 hours shop / * VARSTRING meta=765 nullable=1 is_null=0 * /

# @ 7 TIMESTAMP 1450950696 / * TIMESTAMP (0) meta=0 nullable=0 is_null=0 * /

If you want to remove base64 code, you need to add parameters

-- base64-output=DECODE-ROWS

Click (here) to collapse or open

Mysqlbinlog-v-v-- base64-output=DECODE-ROWS mysql-bin.000002 | grep-B 70-A 70 'guoqing' > / home/dba/guoqing.log

# UPDATE `guoqing`.`test`

# WHERE

# @ 1mm 537 / * LONGINT meta=0 nullable=0 is_null=0 * /

# @ 2pm 10.00 / * DECIMAL (10jue 2) meta=2562 nullable=1 is_null=0 * /

# @ 3000000 / * LONGINT meta=0 nullable=1 is_null=0 * /

# @ 4room0 / * LONGINT meta=0 nullable=1 is_null=0 * /

# @ 5 million 1000609 / * LONGINT meta=0 nullable=1 is_null=0 * /

# @ 6 hours shop / * VARSTRING meta=765 nullable=1 is_null=0 * /

# @ 7 TIMESTAMP 1450950696 / * TIMESTAMP (0) meta=0 nullable=0 is_null=0 * /

# SET

# @ 1mm 537 / * LONGINT meta=0 nullable=0 is_null=0 * /

# @ 2pm 10.00 / * DECIMAL (10jue 2) meta=2562 nullable=1 is_null=0 * /

# @ 3room999 / * LONGINT meta=0 nullable=1 is_null=0 * /

# @ 4room0 / * LONGINT meta=0 nullable=1 is_null=0 * /

# @ 5 million 1000609 / * LONGINT meta=0 nullable=1 is_null=0 * /

# @ 6 hours shop / * VARSTRING meta=765 nullable=1 is_null=0 * /

# @ 7room1450950696 / * TIMESTAMP (0) meta=0 nullable=0 is_null=0 * / generally does not need to add two-v, so the readability is not strong

Click (here) to collapse or open

Mysqlbinlog-v-- base64-output=DECODE-ROWS mysql-bin.000002 | grep-B 70-A 70 'guoqing' > / home/dba/guoqing.log

# 151224 17:51:43 server id 1 end_log_pos 12053052 CRC32 0x2d03726a Update_rows: table id 296 flags: STMT_END_F

# UPDATE `guoqing`.`test`

# WHERE

# @ 1mm 537

# @ 2dream 10.00

# @ 3x1000

# @ 4500

# @ 5 million 1000609

# @ 6 thanks to SHOP'

# @ 7 # 1450950696

# SET

# @ 1mm 537

# @ 2dream 10.00

# @ 3room999

# @ 4500

# @ 5 million 1000609

# @ 6 thanks to SHOP'

# @ 7 # 1450950696

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.