show master status;可以看到当前用的那个二进制日志文件
（在windows上测试mysql.ini中mysqld下配置log-bin=mysql-bin）
show binlog events in ‘mysql-bin.000001’;
当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下面的内容即可）：
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/!/;
评论 (0)