解析mysqlbinlog日志_MySQL binlog日志内容分析

1> MySQL的binlog 日志对于生产环境非常有用，任何时间对数据库的修改都会记录在binglog中；

当数据发生增删改，创建数据库对象都会记录到binlog中，数据库的复制也是基于binlog进行同步数据；

和SQL SERVER 数据库开启完整模式的原理一样，每一次的数据的变动都会记录在案；(对数据库的select,show这些操作不会记录在binlog)

下面介绍mysqlbinlog工具查看 二进制日志内容；

示例

1> 产生新的binlog日志 flush logs

1> 测试数据

1 create table test1 (id int);

2 insert into test1 values (1);

3 insert into test1 values (2);

4 insert into test1 values (3);

5 delete from test1 where id=2;

2> 最新的binlog mysqlbinlog.000012

1 [root@localhost][(none)]> show binlog events in 'mysqlbinlog.000012';

2 +--------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+

3 | Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

4 +--------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+

5 | mysqlbinlog.000012 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.25-log, Binlog ver: 4 |

6 | mysqlbinlog.000012 | 123 | Previous_gtids | 1 | 194 | 6e854266-e13f-11e9-9630-000c29ec17fe:8-40225 |

7 | mysqlbinlog.000012 | 194 | Gtid | 1 | 259 | SET @@SESSION.GTID_NEXT= '6e854266-e13f-11e9-9630-000c29ec17fe:40226' |

8 | mysqlbinlog.000012 | 259 | Query | 1 | 334 | BEGIN |

9 | mysqlbinlog.000012 | 334 | Rows_query | 1 | 386 | # insert into test1 values (1) |

10 | mysqlbinlog.000012 | 386 | Table_map | 1 | 437 | table_id: 111 (ecology.test1) |

11 | mysqlbinlog.000012 | 437 | Write_rows | 1 | 477 | table_id: 111 flags: STMT_END_F |

12 | mysqlbinlog.000012 | 477 | Xid | 1 | 508 | COMMIT /* xid=24229 */ |

13 | mysqlbinlog.000012 | 508 | Gtid | 1 | 573 | SET @@SESSION.GTID_NEXT= '6e854266-e13f-11e9-9630-000c29ec17fe:40227' |

14 | mysqlbinlog.000012 | 573 | Query | 1 | 648 | BEGIN |

15 | mysqlbinlog.000012 | 648 | Rows_query | 1 | 700 | # insert into test1 values (2) |

16 | mysqlbinlog.000012 | 700 | Table_map | 1 | 751 | table_id: 111 (ecology.test1) |

17 | mysqlbinlog.000012 | 751 | Write_rows | 1 | 791 | table_id: 111 flags: STMT_END_F |

18 | mysqlbinlog.000012 | 791 | Xid | 1 | 822 | COMMIT /* xid=24230 */ |

19 | mysqlbinlog.000012 | 822 | Gtid | 1 | 887 | SET @@SESSION.GTID_NEXT= '6e854266-e13f-11e9-9630-000c29ec17fe:40228' |

20 | mysqlbinlog.000012 | 887 | Query | 1 | 962 | BEGIN |

21 | mysqlbinlog.000012 | 962 | Rows_query | 1 | 1014 | # insert into test1 values (3) |

22 | mysqlbinlog.000012 | 1014 | Table_map | 1 | 1065 | table_id: 111 (ecology.test1) |

23 | mysqlbinlog.000012 | 1065 | Write_rows | 1 | 1105 | table_id: 111 flags: STMT_END_F |

24 | mysqlbinlog.000012 | 1105 | Xid | 1 | 1136 | COMMIT /* xid=24231 */ |

25 | mysqlbinlog.000012 | 1136 | Gtid | 1 | 1201 | SET @@SESSION.GTID_NEXT= '6e854266-e13f-11e9-9630-000c29ec17fe:40229' |

26 | mysqlbinlog.000012 | 1201 | Query | 1 | 1276 | BEGIN |

27 | mysqlbinlog.000012 | 1276 | Rows_query | 1 | 1329 | # delete from test1 where id =2 |

28 | mysqlbinlog.000012 | 1329 | Table_map | 1 | 1380 | table_id: 111 (ecology.test1) |

29 | mysqlbinlog.000012 | 1380 | Delete_rows | 1 | 1420 | table_id: 111 flags: STMT_END_F |

30 | mysqlbinlog.000012 | 1420 | Xid | 1 | 1451 | COMMIT /* xid=24234 */ |

31 +--------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+

3> 用mysqlbinlog 工具查看

1 [mysql@mysql mysqlbinlog]$ which mysqlbinlog

2 /mysql/mysql57/bin/mysqlbinlog

所有的内容全部转换

1 mysqlbinlog --base64-output=decode-rows -v mysqlbinlog.000012 > mysqlbinlog.000012.sql

也可以筛选时间

1 mysqlbinlog --no-defaults --database=ecology --base64-output=decode-rows -v --start-datetime='2019-11-19 00:00:00' --stop-datetime='2019-11-19 15:00:00' mysqlbinlog.000012 | more

如下

1 [mysql@mysql mysqlbinlog]$ cat mysqlbinlog.000012.sql

2 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

3 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

4 DELIMITER /*!*/;

5 # at 4

6 #191119 3:41:22 server id 1 end_log_pos 123 CRC32 0xece7d88c Start: binlog v 4, server v 5.7.25-log created 191119 3:41:22

7 # Warning: this binlog is either in use or was not closed properly.

8 # at 123

9 #191119 3:41:22 server id 1 end_log_pos 194 CRC32 0x5061dfbc Previous-GTIDs

10 # 6e854266-e13f-11e9-9630-000c29ec17fe:8-40225

11 # at 194

12 #191119 3:42:07 server id 1 end_log_pos 259 CRC32 0xec2ec1b9 GTID last_committed=0sequence_number=1 rbr_only=yes

13 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

14 SET @@SESSION.GTID_NEXT= '6e854266-e13f-11e9-9630-000c29ec17fe:40226'/*!*/;

15 # at 259

16 #191119 3:42:07 server id 1 end_log_pos 334 CRC32 0x9bc6ec1d Query thread_id=2011 exec_time=0 error_code=0

17 SET TIMESTAMP=1574163727/*!*/;

18 SET @@session.pseudo_thread_id=2011/*!*/;

19 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

20 SET @@session.sql_mode=1436549120/*!*/;

21 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

22 /*!\C utf8 *//*!*/;

23 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;

24 SET @@session.lc_time_names=0/*!*/;

25 SET @@session.collation_database=DEFAULT/*!*/;

26 BEGIN

27 /*!*/;

28 # at 334

29 # at 386

30 #191119 3:42:07 server id 1 end_log_pos 437 CRC32 0xf0b1b42a Table_map: `ecology`.`test1` mapped to number 111

31 # at 437

32 #191119 3:42:07 server id 1 end_log_pos 477 CRC32 0x0a785505 Write_rows: table id 111 flags: STMT_END_F

33 ### INSERT INTO `ecology`.`test1`

34 ### SET

35 ### @1=1

36 # at 477

37 #191119 3:42:07 server id 1 end_log_pos 508 CRC32 0x22330a62 Xid = 24229

38 COMMIT/*!*/;

39 # at 508

40 #191119 3:42:11 server id 1 end_log_pos 573 CRC32 0x1a4a0ef6 GTID last_committed=1sequence_number=2 rbr_only=yes

41 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

42 SET @@SESSION.GTID_NEXT= '6e854266-e13f-11e9-9630-000c29ec17fe:40227'/*!*/;

43 # at 573

44 #191119 3:42:11 server id 1 end_log_pos 648 CRC32 0xaa2c2f1c Query thread_id=2011 exec_time=0 error_code=0

45 SET TIMESTAMP=1574163731/*!*/;

46 BEGIN

47 /*!*/;

48 # at 648

49 # at 700

50 #191119 3:42:11 server id 1 end_log_pos 751 CRC32 0x0c3582e7 Table_map: `ecology`.`test1` mapped to number 111

51 # at 751

52 #191119 3:42:11 server id 1 end_log_pos 791 CRC32 0x7c5d0311 Write_rows: table id 111 flags: STMT_END_F

53 ### INSERT INTO `ecology`.`test1`

54 ### SET

55 ### @1=2

56 # at 791

57 #191119 3:42:11 server id 1 end_log_pos 822 CRC32 0x21aa6cd8 Xid = 24230

58 COMMIT/*!*/;

59 # at 822

60 #191119 3:42:14 server id 1 end_log_pos 887 CRC32 0x28978885 GTID last_committed=2sequence_number=3 rbr_only=yes

61 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

62 SET @@SESSION.GTID_NEXT= '6e854266-e13f-11e9-9630-000c29ec17fe:40228'/*!*/;

63 # at 887

64 #191119 3:42:14 server id 1 end_log_pos 962 CRC32 0x1eb58956 Query thread_id=2011 exec_time=0 error_code=0

65 SET TIMESTAMP=1574163734/*!*/;

66 BEGIN

67 /*!*/;

68 # at 962

69 # at 1014

70 #191119 3:42:14 server id 1 end_log_pos 1065 CRC32 0x16e28b1c Table_map: `ecology`.`test1` mapped to number 111

71 # at 1065

72 #191119 3:42:14 server id 1 end_log_pos 1105 CRC32 0xe418bd7c Write_rows: table id 111 flags: STMT_END_F

73 ### INSERT INTO `ecology`.`test1`

74 ### SET

75 ### @1=3

76 # at 1105

77 #191119 3:42:14 server id 1 end_log_pos 1136 CRC32 0x26c5a65d Xid = 24231

78 COMMIT/*!*/;

79 # at 1136

80 #191119 3:42:53 server id 1 end_log_pos 1201 CRC32 0xb302f8f8 GTID last_committed=3 sequence_number=4 rbr_only=yes

81 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

82 SET @@SESSION.GTID_NEXT= '6e854266-e13f-11e9-9630-000c29ec17fe:40229'/*!*/;

83 # at 1201

84 #191119 3:42:53 server id 1 end_log_pos 1276 CRC32 0x068cf9e9 Query thread_id=2011 exec_time=0 error_code=0

85 SET TIMESTAMP=1574163773/*!*/;

86 BEGIN

87 /*!*/;

88 # at 1276

89 # at 1329

90 #191119 3:42:53 server id 1 end_log_pos 1380 CRC32 0xfbcaff53 Table_map: `ecology`.`test1` mapped to number 111

91 # at 1380

92 #191119 3:42:53 server id 1 end_log_pos 1420 CRC32 0x5f3583dd Delete_rows: table id 111 flags: STMT_END_F

93 ### DELETE FROM `ecology`.`test1`

94 ### WHERE

95 ### @1=2

96 # at 1420

97 #191119 3:42:53 server id 1 end_log_pos 1451 CRC32 0x46e9d878 Xid = 24234

98 COMMIT/*!*/;

99 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

100 DELIMITER ;

101 # End of log file

102 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

103 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;