mysql 查看binlogformat_mysqlbinlog 查看具体的sql语句 (binlog_format=row模式)

曾经终败给现在 2022-11-19 07:59 493阅读 0赞

当binlog\_format=row时，用mysqlbinlog想查看二进制日志时需要加上-v参数。

下面分别对比下加-v前与-v后不同的效果：

mysql> use dba;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select \* from t1;

\+----+

| id |

\+----+

|  2 |

| 11 |

\+----+

2 rows in set (0.00 sec)

mysql> insert into t1(id) values(1);

Query OK, 1 row affected (0.02 sec)

mysql> delete from t1 where id = 11;

Query OK, 1 row affected (0.07 sec)

mysql> show binlog events in 'mysql-bin.000016';

\+------------------+-----+----------------+-----------+-------------+--------------------------------------+

\+------------------+-----+----------------+-----------+-------------+--------------------------------------+

| mysql-bin.000016 |   4 | Format\_desc    |         1 |         123 | Server ver: 5.7.9-log, Binlog ver: 4 |

| mysql-bin.000016 | 123 | Previous\_gtids |         1 |         154 |                                      |

| mysql-bin.000016 | 154 | Anonymous\_Gtid |         1 |         219 | SET @@SESSION.GTID\_NEXT= 'ANONYMOUS' |

| mysql-bin.000016 | 219 | Query          |         1 |         295 | BEGIN                                |

| mysql-bin.000016 | 295 | Table\_map      |         1 |         339 | table\_id: 28 (dba.t1)                |

| mysql-bin.000016 | 339 | Write\_rows     |         1 |         379 | table\_id: 28 flags: STMT\_END\_F      |

| mysql-bin.000016 | 379 | Xid            |         1 |         410 | COMMIT /\* xid=31 \*/                  |

| mysql-bin.000016 | 410 | Anonymous\_Gtid |         1 |         475 | SET @@SESSION.GTID\_NEXT= 'ANONYMOUS' |

| mysql-bin.000016 | 475 | Query          |         1 |         551 | BEGIN                                |

| mysql-bin.000016 | 551 | Table\_map      |         1 |         595 | table\_id: 28 (dba.t1)                |

| mysql-bin.000016 | 595 | Delete\_rows    |         1 |         635 | table\_id: 28 flags: STMT\_END\_F       |

| mysql-bin.000016 | 635 | Xid            |         1 |         666 | COMMIT /\* xid=33 \*/                  |

\+------------------+-----+----------------+-----------+-------------+--------------------------------------+

12 rows in set (0.00 sec)

一：加-v前

/usr/local/mysql/bin/mysqlbinlog   /data/server/mysql\_3307/binlog/mysql-bin.000016  > aa.log

插入ID=1的记录对应的二进制日志为(重点关注\# at 339下面那行内容即可)：

\# at 219

\#160326 18:14:39 server id 1  end\_log\_pos 295 CRC32 0x8ba4aaa0  Query   thread\_id=7     exec\_time=0     error\_code=0

SET TIMESTAMP=1458987279/\*!\*/;

SET @@session.pseudo\_thread\_id=7/\*!\*/;

SET @@session.foreign\_key\_checks=1, @@session.sql\_auto\_is\_null=0, @@session.unique\_checks=1, @@session.autocommit=1/\*!\*/;

SET @@session.sql\_mode=1436549152/\*!\*/;

SET @@session.auto\_increment\_increment=2, @@session.auto\_increment\_offset=1/\*!\*/;

/\*!\\C latin1 \*//\*!\*/;

SET @@session.character\_set\_client=8,@@session.collation\_connection=8,@@session.collation\_server=8/\*!\*/;

SET @@session.lc\_time\_names=0/\*!\*/;

SET @@session.collation\_database=DEFAULT/\*!\*/;

BEGIN

/\*!\*/;

\# at 295

\#160326 18:14:39 server id 1  end\_log\_pos 339 CRC32 0x60889189  Table\_map: \`dba\`.\`t1\` mapped to number 28

\# at 339

\#160326 18:14:39 server id 1  end\_log\_pos 379 CRC32 0x592d2df0  Write\_rows: table id 28 flags: STMT\_END\_F

BINLOG '

D2H2VhMBAAAALAAAAFMBAAAAABwAAAAAAAEAA2RiYQACdDEAAQMAAImRiGA=

D2H2Vh4BAAAAKAAAAHsBAAAAABwAAAAAAAEAAgAB//4BAAAA8C0tWQ==

'/\*!\*/;

\# at 379

\#160326 18:14:39 server id 1  end\_log\_pos 410 CRC32 0x5b2d22d7  Xid = 31

COMMIT/\*!\*/;

删除ID=11的记录对应的二进制日志为(重点关注\# at 595下面那行内容即可)：

\# at 475

\#160326 18:17:23 server id 1  end\_log\_pos 551 CRC32 0x671ebb86  Query   thread\_id=7     exec\_time=0     error\_code=0

SET TIMESTAMP=1458987443/\*!\*/;

BEGIN

/\*!\*/;

\# at 551

\#160326 18:17:23 server id 1  end\_log\_pos 595 CRC32 0x3489bae6  Table\_map: \`dba\`.\`t1\` mapped to number 28

\# at 595

\#160326 18:17:23 server id 1  end\_log\_pos 635 CRC32 0x55d5b27c  Delete\_rows: table id 28 flags: STMT\_END\_F

BINLOG '

s2H2VhMBAAAALAAAAFMCAAAAABwAAAAAAAEAA2RiYQACdDEAAQMAAOa6iTQ=

s2H2ViABAAAAKAAAAHsCAAAAABwAAAAAAAEAAgAB//4LAAAAfLLVVQ==

'/\*!\*/;

\# at 635

\#160326 18:17:23 server id 1  end\_log\_pos 666 CRC32 0xa9c81f0d  Xid = 33

COMMIT/\*!\*/;

二：加-v后

/usr/local/mysql/bin/mysqlbinlog   /data/server/mysql\_3307/binlog/mysql-bin.000016 -v > a.log

插入ID=1的记录对应的二进制日志为(重点关注\# at 339下面的内容即可)：

\# at 219

\#160326 18:14:39 server id 1  end\_log\_pos 295 CRC32 0x8ba4aaa0  Query   thread\_id=7     exec\_time=0     error\_code=0

SET TIMESTAMP=1458987279/\*!\*/;

SET @@session.pseudo\_thread\_id=7/\*!\*/;

SET @@session.foreign\_key\_checks=1, @@session.sql\_auto\_is\_null=0, @@session.unique\_checks=1, @@session.autocommit=1/\*!\*/;

SET @@session.sql\_mode=1436549152/\*!\*/;

SET @@session.auto\_increment\_increment=2, @@session.auto\_increment\_offset=1/\*!\*/;

/\*!\\C latin1 \*//\*!\*/;

SET @@session.character\_set\_client=8,@@session.collation\_connection=8,@@session.collation\_server=8/\*!\*/;

SET @@session.lc\_time\_names=0/\*!\*/;

SET @@session.collation\_database=DEFAULT/\*!\*/;

BEGIN

/\*!\*/;

\# at 295

\#160326 18:14:39 server id 1  end\_log\_pos 339 CRC32 0x60889189  Table\_map: \`dba\`.\`t1\` mapped to number 28

\# at 339

\#160326 18:14:39 server id 1  end\_log\_pos 379 CRC32 0x592d2df0  Write\_rows: table id 28 flags: STMT\_END\_F

BINLOG '

D2H2VhMBAAAALAAAAFMBAAAAABwAAAAAAAEAA2RiYQACdDEAAQMAAImRiGA=

D2H2Vh4BAAAAKAAAAHsBAAAAABwAAAAAAAEAAgAB//4BAAAA8C0tWQ==

'/\*!\*/;

\#\#\# INSERT INTO \`dba\`.\`t1\`

\#\#\# SET

\#\#\#   @1=1

\# at 379

\#160326 18:14:39 server id 1  end\_log\_pos 410 CRC32 0x5b2d22d7  Xid = 31

COMMIT/\*!\*/;

删除ID=11的记录对应的二进制日志为(重点关注\# at 595下面的内容即可)：

\# at 475

\#160326 18:17:23 server id 1  end\_log\_pos 551 CRC32 0x671ebb86  Query   thread\_id=7     exec\_time=0     error\_code=0

SET TIMESTAMP=1458987443/\*!\*/;

BEGIN

/\*!\*/;

\# at 551

\#160326 18:17:23 server id 1  end\_log\_pos 595 CRC32 0x3489bae6  Table\_map: \`dba\`.\`t1\` mapped to number 28

\# at 595

\#160326 18:17:23 server id 1  end\_log\_pos 635 CRC32 0x55d5b27c  Delete\_rows: table id 28 flags: STMT\_END\_F

BINLOG '

s2H2VhMBAAAALAAAAFMCAAAAABwAAAAAAAEAA2RiYQACdDEAAQMAAOa6iTQ=

s2H2ViABAAAAKAAAAHsCAAAAABwAAAAAAAEAAgAB//4LAAAAfLLVVQ==

'/\*!\*/;

\#\#\# DELETE FROM \`dba\`.\`t1\` \#\#\# WHERE \#\#\#   @1=11 \# at 635 \#160326 18:17:23 server id 1  end\_log\_pos 666 CRC32 0xa9c81f0d  Xid = 33 COMMIT/\*!\*/;

mysql 查看binlogformat_mysqlbinlog 查看具体的sql语句 (binlog_format=row模式)

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 mysql 查看binlogformat_mysqlbinlog 查看具体的sql语句 (binlog_format=row模式)

相关 SQL 语句查看表结构

相关 mysql profiles 查看sql语句执行的性能

相关 Django查看执行的sql语句

相关查看MySQL语句耗时

相关 MySQL 语句分析及sql进程查看

相关如何查看一个mysql的sql语句的性能

相关查看当前运行的SQL语句

相关（转）mysql查看正在执行的sql语句

相关【MySQL】SHOW WARNINGS查看重构sql语句