Mysql通过binlog分析执行情况

林然_
159 阅读6分钟 
# 将binlog转换成sql文件，不同的mysql的mysqlbinlog 位置是不一样的，查看/etc/my.conf便知道
/home/yh/mysql/bin/mysqlbinlog --start-datetime="2022-02-23 00:00:00" --stop-datetime="2022-02-23 23:59:59" binlog.000055 -r test2.sql
# 报二进制或者转换错误的，就是 --no-defaults 转一下即可
/home/yh/mysql/bin/mysqlbinlog --no-defaults binlog.000053 -r test4.sql

[mysql@mysql mysqlbinlog]$ cat mysqlbinlog.000012.sql
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#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
# Warning: this binlog is either in use or was not closed properly.
# at 123
#191119 3:41:22 server id 1 end_log_pos 194 CRC32 0x5061dfbc Previous-GTIDs
# 6e854266-e13f-11e9-9630-000c29ec17fe:8-40225
# at 194
#191119 3:42:07 server id 1 end_log_pos 259 CRC32 0xec2ec1b9 GTID last_committed=0sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '6e854266-e13f-11e9-9630-000c29ec17fe:40226'/*!*/;
# at 259
#191119 3:42:07 server id 1 end_log_pos 334 CRC32 0x9bc6ec1d Query thread_id=2011 exec_time=0 error_code=0
SET TIMESTAMP=1574163727/*!*/;
SET @@session.pseudo_thread_id=2011/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549120/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 334
# at 386
#191119 3:42:07 server id 1 end_log_pos 437 CRC32 0xf0b1b42a Table_map: `ecology`.`test1` mapped to number 111
# at 437
#191119 3:42:07 server id 1 end_log_pos 477 CRC32 0x0a785505 Write_rows: table id 111 flags: STMT_END_F
### INSERT INTO `ecology`.`test1`
### SET
### @1=1
# at 477
#191119 3:42:07 server id 1 end_log_pos 508 CRC32 0x22330a62 Xid = 24229
COMMIT/*!*/;
# at 508
#191119 3:42:11 server id 1 end_log_pos 573 CRC32 0x1a4a0ef6 GTID last_committed=1sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '6e854266-e13f-11e9-9630-000c29ec17fe:40227'/*!*/;
# at 573
#191119 3:42:11 server id 1 end_log_pos 648 CRC32 0xaa2c2f1c Query thread_id=2011 exec_time=0 error_code=0
SET TIMESTAMP=1574163731/*!*/;
BEGIN
/*!*/;
# at 648
# at 700
#191119 3:42:11 server id 1 end_log_pos 751 CRC32 0x0c3582e7 Table_map: `ecology`.`test1` mapped to number 111
# at 751
#191119 3:42:11 server id 1 end_log_pos 791 CRC32 0x7c5d0311 Write_rows: table id 111 flags: STMT_END_F
### INSERT INTO `ecology`.`test1`
### SET
### @1=2
# at 791
#191119 3:42:11 server id 1 end_log_pos 822 CRC32 0x21aa6cd8 Xid = 24230
COMMIT/*!*/;
# at 822
#191119 3:42:14 server id 1 end_log_pos 887 CRC32 0x28978885 GTID last_committed=2sequence_number=3 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '6e854266-e13f-11e9-9630-000c29ec17fe:40228'/*!*/;
# at 887
#191119 3:42:14 server id 1 end_log_pos 962 CRC32 0x1eb58956 Query thread_id=2011 exec_time=0 error_code=0
SET TIMESTAMP=1574163734/*!*/;
BEGIN
/*!*/;
# at 962
# at 1014
#191119 3:42:14 server id 1 end_log_pos 1065 CRC32 0x16e28b1c Table_map: `ecology`.`test1` mapped to number 111
# at 1065
#191119 3:42:14 server id 1 end_log_pos 1105 CRC32 0xe418bd7c Write_rows: table id 111 flags: STMT_END_F
### INSERT INTO `ecology`.`test1`
### SET
### @1=3
# at 1105
#191119 3:42:14 server id 1 end_log_pos 1136 CRC32 0x26c5a65d Xid = 24231
COMMIT/*!*/;
# at 1136
#191119 3:42:53 server id 1 end_log_pos 1201 CRC32 0xb302f8f8 GTID last_committed=3 sequence_number=4 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '6e854266-e13f-11e9-9630-000c29ec17fe:40229'/*!*/;
# at 1201
#191119 3:42:53 server id 1 end_log_pos 1276 CRC32 0x068cf9e9 Query thread_id=2011 exec_time=0 error_code=0
SET TIMESTAMP=1574163773/*!*/;
BEGIN
/*!*/;
# at 1276
# at 1329
#191119 3:42:53 server id 1 end_log_pos 1380 CRC32 0xfbcaff53 Table_map: `ecology`.`test1` mapped to number 111
# at 1380
#191119 3:42:53 server id 1 end_log_pos 1420 CRC32 0x5f3583dd Delete_rows: table id 111 flags: STMT_END_F
### DELETE FROM `ecology`.`test1`
### WHERE
### @1=2
# at 1420
#191119 3:42:53 server id 1 end_log_pos 1451 CRC32 0x46e9d878 Xid = 24234
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

# 在mysql命令中可以直接查看binlog情况
show binlog events in 'binlog.000055’

[root@localhost][(none)]> show binlog events in 'mysqlbinlog.000012';
+--------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
| mysqlbinlog.000012 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.25-log, Binlog ver: 4 |
| mysqlbinlog.000012 | 123 | Previous_gtids | 1 | 194 | 6e854266-e13f-11e9-9630-000c29ec17fe:8-40225 |
| mysqlbinlog.000012 | 194 | Gtid | 1 | 259 | SET @@SESSION.GTID_NEXT= '6e854266-e13f-11e9-9630-000c29ec17fe:40226' |
| mysqlbinlog.000012 | 259 | Query | 1 | 334 | BEGIN |
| mysqlbinlog.000012 | 334 | Rows_query | 1 | 386 | # insert into test1 values (1) |
| mysqlbinlog.000012 | 386 | Table_map | 1 | 437 | table_id: 111 (ecology.test1) |
| mysqlbinlog.000012 | 437 | Write_rows | 1 | 477 | table_id: 111 flags: STMT_END_F |
| mysqlbinlog.000012 | 477 | Xid | 1 | 508 | COMMIT /* xid=24229 */ |
| mysqlbinlog.000012 | 508 | Gtid | 1 | 573 | SET @@SESSION.GTID_NEXT= '6e854266-e13f-11e9-9630-000c29ec17fe:40227' |
| mysqlbinlog.000012 | 573 | Query | 1 | 648 | BEGIN |
| mysqlbinlog.000012 | 648 | Rows_query | 1 | 700 | # insert into test1 values (2) |
| mysqlbinlog.000012 | 700 | Table_map | 1 | 751 | table_id: 111 (ecology.test1) |
| mysqlbinlog.000012 | 751 | Write_rows | 1 | 791 | table_id: 111 flags: STMT_END_F |
| mysqlbinlog.000012 | 791 | Xid | 1 | 822 | COMMIT /* xid=24230 */ |
| mysqlbinlog.000012 | 822 | Gtid | 1 | 887 | SET @@SESSION.GTID_NEXT= '6e854266-e13f-11e9-9630-000c29ec17fe:40228' |
| mysqlbinlog.000012 | 887 | Query | 1 | 962 | BEGIN |
| mysqlbinlog.000012 | 962 | Rows_query | 1 | 1014 | # insert into test1 values (3) |
| mysqlbinlog.000012 | 1014 | Table_map | 1 | 1065 | table_id: 111 (ecology.test1) |
| mysqlbinlog.000012 | 1065 | Write_rows | 1 | 1105 | table_id: 111 flags: STMT_END_F |
| mysqlbinlog.000012 | 1105 | Xid | 1 | 1136 | COMMIT /* xid=24231 */ |
| mysqlbinlog.000012 | 1136 | Gtid | 1 | 1201 | SET @@SESSION.GTID_NEXT= '6e854266-e13f-11e9-9630-000c29ec17fe:40229' |
| mysqlbinlog.000012 | 1201 | Query | 1 | 1276 | BEGIN |
| mysqlbinlog.000012 | 1276 | Rows_query | 1 | 1329 | # delete from test1 where id =2 |
| mysqlbinlog.000012 | 1329 | Table_map | 1 | 1380 | table_id: 111 (ecology.test1) |
| mysqlbinlog.000012 | 1380 | Delete_rows | 1 | 1420 | table_id: 111 flags: STMT_END_F |
| mysqlbinlog.000012 | 1420 | Xid | 1 | 1451 | COMMIT /* xid=24234 */ |
+--------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
avatar
文章
阅读
粉丝