mysql 查看binlogformat_mysqlbinlog 查看具体的sql语句 (binlog

本文链接：https://blog.csdn.net/weixin_35969301/article/details/113218180

当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模式)