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

本文源自：mysqlbinlog 查看具体的sql语句 (binlog_format=row模式)_雅冰石的专栏-CSDN博客当binlog_format=row时，用mysqlbinlog想查看二进制日志时需要加上-v参数。下面分别对比下加-v前与-v后不同的效果：mysql> use dba;Reading table information for completion of tabl

lhdz_bj

3365人浏览 · 2021-09-24 17:56:41

lhdz_bj · 2021-09-24 17:56:41 发布

本文源自：mysqlbinlog 查看具体的sql语句 (binlog_format=row模式)_雅冰石的专栏-CSDN博客

当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';
+------------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------+
| 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下面的内容即可）：

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下面的内容即可）：

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/*!*/;
————————————————
版权声明：本文为CSDN博主「雅冰石」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/yabingshi_tech/article/details/50988113