【DB宝43】MySQL误操作闪回恢复利器之my2sql

目录

• 一、my2sql简介
• 二、my2sql用途
• 三、产品性能对比
• 四、安装
• 五、重要参数说明
• 六、使用案例
  • 6.1 解析出标准SQL
    • 根据时间点解析出标准SQL
    • 根据pos点解析出标准SQL
  • 6.2 解析出回滚SQL
    • 根据时间点解析出回滚SQL
    • 根据pos点解析出回滚SQL
  • 6.3 统计DML以及大事务
    • 统计时间范围各个表的DML操作数量，统计一个事务大于500条、时间大于300秒的事务
    • 统计一段pos点范围各个表的DML操作数量，统计一个事务大于500条、时间大于300秒的事务
  • 6.4 从某一个pos点解析出标准SQL，并且持续打印到屏幕
• 七、限制
• 八、案例演示
  • 8.1 准备MySQL环境
  • 8.2 执行DML操作
  • 8.3 解析binlog生成标准SQL
  • 8.4 执行闪回操作
  • 8.5 解析binlog 统计DML、长事务与大事务分析

一、my2sql简介

可以用于MySQL误操作闪回的工具包括my2sql、binlog2sql和MyFlash等工具，其中，个人感觉my2sql最好用。

my2sql是使用go语言开发的MySQL binlog解析工具，通过解析MySQL binlog ，可以生成原始SQL、回滚SQL、去除主键的INSERT SQL等，也可以生成DML统计信息。类似工具有binlog2sql、MyFlash、my2fback等，本工具基于my2fback、binlog_rollback工具二次开发而来。

my2sql的GitHub地址：https://github.com/liuhr/my2sql

优点：

• 功能丰富，不仅支持回滚操作，还有其他实用功能。
• 基于golang实现，速度快，全量解析1.1Gbinlog只需要1分30秒左右，当前其他类似开源工具一般要几十分钟。

二、my2sql用途

• 数据快速回滚(闪回)
• 主从切换后新master丢数据的修复
• 从binlog生成标准SQL，带来的衍生功能
• 生成DML统计信息，可以找到哪些表更新的比较频繁
• IO高TPS高， 查出哪些表在频繁更新
• 找出某个时间点数据库是否有大事务或者长事务
• 主从延迟，分析主库执行的SQL语句
• 除了支持常规数据类型，对大部分工具不支持的数据类型做了支持，比如json、blob、text、emoji等数据类型sql生成

三、产品性能对比

binlog2sql当前是业界使用最广泛的MySQL回滚工具，下面对my2sql和binlog2sql做个性能对比。

	my2sql	binlog2sql
1.1G binlog生成回滚SQL	1分40秒	65分钟
1.1G binlog生成原始SQL	1分30秒	50分钟
1.1G binlog生成表DML统计信息、以及事务统计信息	40秒	不支持

四、安装

-- 申请一台全新的主机
docker rm -f lhrmy2sql
docker run -d --name lhrmy2sql -h lhrmy2sql 
  --privileged=true lhrbest/lhrcentos76:8.0 
  /usr/sbin/init

docker exec -it lhrmy2sql bash



-- 方法1：编译安装
yum install -y golang
go version
go env | grep GOPATH

mkdir -p /root/go/src
cd /root/go/src
git clone https://github.com/liuhr/my2sql.git
cd my2sql/
go build .


-- 方法2：直接下载编译好的二进制文件。
-- https://github.com/liuhr/my2sql/blob/master/releases/my2sql
wget https://raw.githubusercontent.com/liuhr/my2sql/master/releases/my2sql
mv my2sql /usr/local/bin/my2sql
chmod +x /usr/local/bin/my2sql


-- 若不能下载，请添加以下解析：
echo "
13.229.188.59 github.com
199.232.4.133 raw.githubusercontent.com
" >> /etc/hosts

五、重要参数说明

• -U 优先使用unique key作为where条件，默认false

• -add-extraInfo 是否把database/table/datetime/binlogposition...信息以注释的方式加入生成的每条sql前，默认false

datetime=2020-07-16_10:44:09 database=orchestrator table=cluster_domain_name binlog=mysql-bin.011519 startpos=15552 stoppos=15773

UPDATE `orchestrator`.`cluster_domain_name` SET `last_registered`='2020-07-16 10:44:09' WHERE `cluster_name`='192.168.1.1:3306'

• -big-trx-row-limit n
  transaction with affected rows greater or equal to this value is considerated as big transaction
  找出影响了n行数据的事务，默认500条

• -databases 、 -tables 库及表条件过滤, 以逗号分隔

• -sql 要解析的sql类型，可选参数insert、update、delete，默认全部解析

• -doNotAddPrifixDb
  Prefix table name witch database name in sql,ex: insert into db1.tb1 (x1, x1) values (y1, y1)
  默认生成insert into db1.tb1 (x1, x1) values (y1, y1)类sql，也可以生成不带库名的sql

• -file-per-table 为每个表生成一个sql文件

• -full-columns
  For update sql, include unchanged columns. for update and delete, use all columns to build where condition.
  default false, this is, use changed columns to build set part, use primary/unique key to build where condition
  生成的sql是否带全列信息，默认false

• -ignorePrimaryKeyForInsert 生成的insert语句是否去掉主键，默认false

• -output-dir 将生成的结果存放到制定目录

• -output-toScreen 将生成的结果打印到屏幕，默认写到文件

• -threads 线程数，默认8个

• -work-type 2sql表示生成原始sql，rollback表示生成回滚sql，stats表示只统计DML、事务信息

六、使用案例

6.1 解析出标准SQL

根据时间点解析出标准SQL

./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type 2sql  
-start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" 
-output-dir ./tmpdir

根据pos点解析出标准SQL

./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type 2sql  
-start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  
-output-dir ./tmpdir

6.2 解析出回滚SQL

根据时间点解析出回滚SQL

./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type rollback  
-start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" 
-output-dir ./tmpdir

根据pos点解析出回滚SQL

./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type rollback  
-start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  
-output-dir ./tmpdir

6.3 统计DML以及大事务

统计时间范围各个表的DML操作数量，统计一个事务大于500条、时间大于300秒的事务

./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type stats  
-start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00"  
-big-trx-row-limit 500 -long-trx-seconds 300   -output-dir ./tmpdir

统计一段pos点范围各个表的DML操作数量，统计一个事务大于500条、时间大于300秒的事务

./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type stats  
-start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  
-big-trx-row-limit 500 -long-trx-seconds 300   -output-dir ./tmpdir

6.4 从某一个pos点解析出标准SQL，并且持续打印到屏幕

./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type 2sql  
-start-file mysql-bin.011259  -start-pos 4   -output-toScreen 

七、限制

• 使用回滚/闪回功能时，binlog格式必须为row,且binlog_row_image=full， DML统计以及大事务分析不受影响
• 只能回滚DML， 不能回滚DDL
• 支持指定-tl时区来解释binlog中time/datetime字段的内容。开始时间-start-datetime与结束时间-stop-datetime也会使用此指定的时区，
  但注意此开始与结束时间针对的是binlog event header中保存的unix timestamp。结果中的额外的datetime时间信息都是binlog event header中的unix timestamp
• 此工具是伪装成从库拉取binlog，需要连接数据库的用户有SELECT, REPLICATION SLAVE, REPLICATION CLIENT权限
• MySQL8.0版本需要在配置文件中加入default_authentication_plugin =mysql_native_password，用户密码认证必须是mysql_native_password才能解析

八、案例演示

8.1 准备MySQL环境

docker rm -f mysql3306
docker run -d --name mysql3306 -h mysql3306 -p 3306:3306 
  -v /etc/mysql/mysql3306/conf:/etc/mysql/conf.d 
  -e MYSQL_ROOT_PASSWORD=lhr -e TZ=Asia/Shanghai 
  mysql:5.7.30
  
  
docker exec -it mysql3306 bash
docker logs -f mysql3306


cat >  /etc/mysql/mysql3306/conf/my.cnf <<"EOF"
[mysqld]
default-time-zone = '+8:00'
log_timestamps = SYSTEM
skip-name-resolve
log-bin
server_id=573306
character_set_server=utf8mb4
EOF

docker restart mysql3306



mysql -uroot -plhr -h192.168.66.35 -P3306
create database lhrdb default character set utf8mb4;

8.2 执行DML操作

show master logs;
flush logs;


select now();

use lhrdb;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `add_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加的时间',
  `content` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name` (`number`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1233,26,'ranran','2020-07-15 19:06:03',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1232,134,'asdf','2020-07-12 11:08:41',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1231,21,'chenxi','2020-07-12 10:12:45',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1229,20,'chenxi','2020-07-11 16:20:50',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1227,18,'hanran','2020-07-06 21:55:48','{"age":13,"author":"liuhan"}');

select * from student;
select now();

show master logs;
show binlog events in 'mysql3306-bin.000002';

执行过程：

MySQL [(none)]> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql3306-bin.000001 |   3071539 |
| mysql3306-bin.000002 |       154 |
| mysql3306-bin.000003 |       154 |
+----------------------+-----------+
3 rows in set (0.05 sec)

MySQL [(none)]>
MySQL [(none)]> create database lhrdb default character set utf8mb4;
Query OK, 1 row affected (0.05 sec)

MySQL [(none)]> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql3306-bin.000001 |   3071539 |
| mysql3306-bin.000002 |       154 |
| mysql3306-bin.000003 |       346 |
+----------------------+-----------+
3 rows in set (0.05 sec)

MySQL [(none)]> flush logs;
Query OK, 0 rows affected (0.06 sec)

MySQL [(none)]>
MySQL [(none)]> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-02-26 12:22:38 |
+---------------------+
1 row in set (0.05 sec)

MySQL [(none)]>
MySQL [(none)]> use lhrdb;
Database changed
MySQL [lhrdb]> CREATE TABLE `student` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `number` int(11) NOT NULL,
    ->   `name` varchar(255) DEFAULT NULL,
    ->   `add_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加的时间',
    ->   `content` json DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `idx_name` (`number`,`name`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.07 sec)

MySQL [lhrdb]> INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1233,26,'ranran','2020-07-15 19:06:03',null);
Query OK, 1 row affected (0.08 sec)

MySQL [lhrdb]> INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1232,134,'asdf','2020-07-12 11:08:41',null);
Query OK, 1 row affected (0.05 sec)

MySQL [lhrdb]> INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1231,21,'chenxi','2020-07-12 10:12:45',null);
Query OK, 1 row affected (0.06 sec)

MySQL [lhrdb]> INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1229,20,'chenxi','2020-07-11 16:20:50',null);
Query OK, 1 row affected (0.05 sec)

MySQL [lhrdb]> INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1227,18,'hanran','2020-07-06 21:55:48','{"age":13,"author":"liuhan"}');
Query OK, 1 row affected (0.05 sec)

MySQL [lhrdb]>
MySQL [lhrdb]> select * from student;
+------+--------+--------+---------------------+---------------------------------+
| id   | number | name   | add_time            | content                         |
+------+--------+--------+---------------------+---------------------------------+
| 1227 |     18 | hanran | 2020-07-06 21:55:48 | {"age": 13, "author": "liuhan"} |
| 1229 |     20 | chenxi | 2020-07-11 16:20:50 | NULL                            |
| 1231 |     21 | chenxi | 2020-07-12 10:12:45 | NULL                            |
| 1232 |    134 | asdf   | 2020-07-12 11:08:41 | NULL                            |
| 1233 |     26 | ranran | 2020-07-15 19:06:03 | NULL                            |
+------+--------+--------+---------------------+---------------------------------+
5 rows in set (0.05 sec)

MySQL [lhrdb]> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-02-26 12:23:16 |
+---------------------+
1 row in set (0.05 sec)

MySQL [lhrdb]>
MySQL [lhrdb]> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql3306-bin.000001 |   3071539 |
| mysql3306-bin.000002 |       154 |
| mysql3306-bin.000003 |       397 |
| mysql3306-bin.000004 |      2131 |
+----------------------+-----------+
4 rows in set (0.05 sec)

MySQL [lhrdb]> show binlog events in 'mysql3306-bin.000004';
+----------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name             | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                                                                                                                                                                                                           |
+----------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql3306-bin.000004 |    4 | Format_desc    |    573306 |         123 | Server ver: 5.7.30-log, Binlog ver: 4                                                                                                                                                                                                                                                                                                                          |
| mysql3306-bin.000004 |  123 | Previous_gtids |    573306 |         154 |                                                                                                                                                                                                                                                                                                                                                                |
| mysql3306-bin.000004 |  154 | Anonymous_Gtid |    573306 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                                                                                                                           |
| mysql3306-bin.000004 |  219 | Query          |    573306 |         634 | use `lhrdb`; CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `add_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加的时间',
  `content` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name` (`number`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| mysql3306-bin.000004 |  634 | Anonymous_Gtid |    573306 |         699 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                                                                                                                           |
| mysql3306-bin.000004 |  699 | Query          |    573306 |         780 | BEGIN                                                                                                                                                                                                                                                                                                                                                          |
| mysql3306-bin.000004 |  780 | Table_map      |    573306 |         839 | table_id: 108 (lhrdb.student)                                                                                                                                                                                                                                                                                                                                  |
| mysql3306-bin.000004 |  839 | Write_rows     |    573306 |         895 | table_id: 108 flags: STMT_END_F                                                                                                                                                                                                                                                                                                                                |
| mysql3306-bin.000004 |  895 | Xid            |    573306 |         926 | COMMIT /* xid=13 */                                                                                                                                                                                                                                                                                                                                            |
| mysql3306-bin.000004 |  926 | Anonymous_Gtid |    573306 |         991 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                                                                                                                           |
| mysql3306-bin.000004 |  991 | Query          |    573306 |        1072 | BEGIN                                                                                                                                                                                                                                                                                                                                                          |
| mysql3306-bin.000004 | 1072 | Table_map      |    573306 |        1131 | table_id: 108 (lhrdb.student)                                                                                                                                                                                                                                                                                                                                  |
| mysql3306-bin.000004 | 1131 | Write_rows     |    573306 |        1185 | table_id: 108 flags: STMT_END_F                                                                                                                                                                                                                                                                                                                                |
| mysql3306-bin.000004 | 1185 | Xid            |    573306 |        1216 | COMMIT /* xid=14 */                                                                                                                                                                                                                                                                                                                                            |
| mysql3306-bin.000004 | 1216 | Anonymous_Gtid |    573306 |        1281 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                                                                                                                           |
| mysql3306-bin.000004 | 1281 | Query          |    573306 |        1362 | BEGIN                                                                                                                                                                                                                                                                                                                                                          |
| mysql3306-bin.000004 | 1362 | Table_map      |    573306 |        1421 | table_id: 108 (lhrdb.student)                                                                                                                                                                                                                                                                                                                                  |
| mysql3306-bin.000004 | 1421 | Write_rows     |    573306 |        1477 | table_id: 108 flags: STMT_END_F                                                                                                                                                                                                                                                                                                                                |
| mysql3306-bin.000004 | 1477 | Xid            |    573306 |        1508 | COMMIT /* xid=15 */                                                                                                                                                                                                                                                                                                                                            |
| mysql3306-bin.000004 | 1508 | Anonymous_Gtid |    573306 |        1573 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                                                                                                                           |
| mysql3306-bin.000004 | 1573 | Query          |    573306 |        1654 | BEGIN                                                                                                                                                                                                                                                                                                                                                          |
| mysql3306-bin.000004 | 1654 | Table_map      |    573306 |        1713 | table_id: 108 (lhrdb.student)                                                                                                                                                                                                                                                                                                                                  |
| mysql3306-bin.000004 | 1713 | Write_rows     |    573306 |        1769 | table_id: 108 flags: STMT_END_F                                                                                                                                                                                                                                                                                                                                |
| mysql3306-bin.000004 | 1769 | Xid            |    573306 |        1800 | COMMIT /* xid=16 */                                                                                                                                                                                                                                                                                                                                            |
| mysql3306-bin.000004 | 1800 | Anonymous_Gtid |    573306 |        1865 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                                                                                                                           |
| mysql3306-bin.000004 | 1865 | Query          |    573306 |        1946 | BEGIN                                                                                                                                                                                                                                                                                                                                                          |
| mysql3306-bin.000004 | 1946 | Table_map      |    573306 |        2005 | table_id: 108 (lhrdb.student)                                                                                                                                                                                                                                                                                                                                  |
| mysql3306-bin.000004 | 2005 | Write_rows     |    573306 |        2100 | table_id: 108 flags: STMT_END_F                                                                                                                                                                                                                                                                                                                                |
| mysql3306-bin.000004 | 2100 | Xid            |    573306 |        2131 | COMMIT /* xid=17 */                                                                                                                                                                                                                                                                                                                                            |
+----------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
29 rows in set (0.05 sec)

8.3 解析binlog生成标准SQL

可以根据时间点解析出标准SQL：

my2sql  -user root -password lhr  -port 3306 
-host 192.168.66.35 -databases lhrdb  -tables student 
-work-type 2sql   -start-file mysql3306-bin.000004 
-start-datetime "2021-02-26 12:22:38" --stop-datetime "2021-02-26 12:23:16" 
-output-dir /my2sql/

也可以根据binlog的pos点解析出标准SQL：

my2sql  -user root -password lhr  -port 3306 
-host 192.168.66.35 -databases lhrdb  -tables student 
-work-type 2sql   -start-file mysql3306-bin.000004 
-start-pos 154 -stop-file  mysql3306-bin.000004 -stop-pos  2131 
-output-dir /my2sql/

执行过程：

[root@lhrmy2sql my2sql]# my2sql  -user root -password lhr  -port 3306 
> -host 192.168.66.35 -databases lhrdb  -tables student 
> -work-type 2sql   -start-file mysql3306-bin.000004 
> -start-pos 154 -stop-file  mysql3306-bin.000004 -stop-pos  2131 
> -output-dir /my2sql/
[2021/02/26 12:27:42] [info] events.go:208 start thread to write redo/rollback sql into file
[2021/02/26 12:27:42] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 192.168.66.35 3306 root   utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2021/02/26 12:27:42] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql3306-bin.000004, 154)
[2021/02/26 12:27:42] [info] events.go:58 start thread 2 to generate redo/rollback sql
[2021/02/26 12:27:42] [info] events.go:58 start thread 1 to generate redo/rollback sql
[2021/02/26 12:27:42] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/02/26 12:27:42] [info] repl.go:15 start to get binlog from mysql
[2021/02/26 12:27:42] [info] binlogsyncer.go:777 rotate to (mysql3306-bin.000004, 154)
[2021/02/26 12:27:42] [info] com.go:57 stop to get event. StopFilePos set. currentBinlog (mysql3306-bin.000004, 2131) StopFilePos (mysql3306-bin.000004, 2131)
[2021/02/26 12:27:42] [info] repl.go:17 finish getting binlog from mysql
[2021/02/26 12:27:42] [info] events.go:183 exit thread 1 to generate redo/rollback sql
[2021/02/26 12:27:42] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2021/02/26 12:27:42] [info] events.go:183 exit thread 2 to generate redo/rollback sql
[2021/02/26 12:27:42] [info] events.go:272 finish writing redo/forward sql into file
[2021/02/26 12:27:42] [info] events.go:275 exit thread to write redo/rollback sql into file
[root@lhrmy2sql my2sql]# ll
total 12
-rw-r--r-- 1 root root 107 Feb 26 12:27 biglong_trx.txt
-rw-r--r-- 1 root root 291 Feb 26 12:27 binlog_status.txt
-rw-r--r-- 1 root root 671 Feb 26 12:27 forward.4.sql
[root@lhrmy2sql my2sql]# more biglong_trx.txt 
binlog            starttime           stoptime            startpos   stoppos    rows     duration   tables
[root@lhrmy2sql my2sql]# more binlog_status.txt 
binlog            starttime           stoptime            startpos   stoppos    inserts  updates  deletes  database        table               
mysql3306-bin.000004 2021-02-26_12:23:06 2021-02-26_12:23:06 780        2100       5        0        0        lhrdb           student             
[root@lhrmy2sql my2sql]# more forward.4.sql 
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1233,26,'ranran','2020-07-15 19:06:03',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1232,134,'asdf','2020-07-12 11:08:41',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1231,21,'chenxi','2020-07-12 10:12:45',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1229,20,'chenxi','2020-07-11 16:20:50',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1227,18,'hanran','2020-07-06 21:55:48','{"age":13,"author":"liuhan"}');

可以看到，原始的SQL插入语句已经被解析出来了。

8.4 执行闪回操作

根据binlog的pos点解析出回滚SQL

my2sql  -user root -password lhr  -port 3306 
-host 192.168.66.35 -databases lhrdb  -tables student 
-work-type rollback   -start-file mysql3306-bin.000004 
-start-pos 154 -stop-file  mysql3306-bin.000004 -stop-pos  2131 
-output-dir /my2sql/

执行过程：

[root@lhrmy2sql my2sql]# my2sql  -user root -password lhr  -port 3306 
> -host 192.168.66.35 -databases lhrdb  -tables student 
> -work-type rollback   -start-file mysql3306-bin.000004 
> -start-pos 154 -stop-file  mysql3306-bin.000004 -stop-pos  2131 
> -output-dir /my2sql/
[2021/02/26 12:29:51] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 192.168.66.35 3306 root   utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2021/02/26 12:29:51] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql3306-bin.000004, 154)
[2021/02/26 12:29:51] [info] events.go:208 start thread to write redo/rollback sql into file
[2021/02/26 12:29:51] [info] events.go:58 start thread 2 to generate redo/rollback sql
[2021/02/26 12:29:51] [info] events.go:58 start thread 1 to generate redo/rollback sql
[2021/02/26 12:29:51] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/02/26 12:29:51] [info] repl.go:15 start to get binlog from mysql
[2021/02/26 12:29:51] [info] binlogsyncer.go:777 rotate to (mysql3306-bin.000004, 154)
[2021/02/26 12:29:51] [info] com.go:57 stop to get event. StopFilePos set. currentBinlog (mysql3306-bin.000004, 2131) StopFilePos (mysql3306-bin.000004, 2131)
[2021/02/26 12:29:51] [info] repl.go:17 finish getting binlog from mysql
[2021/02/26 12:29:51] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2021/02/26 12:29:51] [info] events.go:183 exit thread 1 to generate redo/rollback sql
[2021/02/26 12:29:51] [info] events.go:183 exit thread 2 to generate redo/rollback sql
[2021/02/26 12:29:51] [info] events.go:257 finish writing rollback sql into tmp files, start to revert content order of tmp files
[2021/02/26 12:29:51] [info] rollback_process.go:15 start thread 1 to revert rollback sql files
[2021/02/26 12:29:51] [info] rollback_process.go:41 start to revert tmp file /my2sql/.rollback.4.sql into /my2sql/rollback.4.sql
[2021/02/26 12:29:51] [info] rollback_process.go:156 finish reverting tmp file /my2sql/.rollback.4.sql into /my2sql/rollback.4.sql
[2021/02/26 12:29:51] [info] rollback_process.go:25 exit thread 1 to revert rollback sql files
[2021/02/26 12:29:51] [info] events.go:270 finish reverting content order of tmp files
[2021/02/26 12:29:51] [info] events.go:275 exit thread to write redo/rollback sql into file
[root@lhrmy2sql my2sql]# ll
total 12
-rw-r--r-- 1 root root 107 Feb 26 12:29 biglong_trx.txt
-rw-r--r-- 1 root root 291 Feb 26 12:29 binlog_status.txt
-rw-r--r-- 1 root root 235 Feb 26 12:29 rollback.4.sql
[root@lhrmy2sql my2sql]# more biglong_trx.txt 
binlog            starttime           stoptime            startpos   stoppos    rows     duration   tables
[root@lhrmy2sql my2sql]# more binlog_status.txt 
binlog            starttime           stoptime            startpos   stoppos    inserts  updates  deletes  database        table               
mysql3306-bin.000004 2021-02-26_12:23:06 2021-02-26_12:23:06 780        2100       5        0        0        lhrdb           student             
[root@lhrmy2sql my2sql]# more rollback.4.sql 
DELETE FROM `lhrdb`.`student` WHERE `id`=1227;
DELETE FROM `lhrdb`.`student` WHERE `id`=1229;
DELETE FROM `lhrdb`.`student` WHERE `id`=1231;
DELETE FROM `lhrdb`.`student` WHERE `id`=1232;
DELETE FROM `lhrdb`.`student` WHERE `id`=1233;

可以看到，回滚SQL是DELETE，已经生成。

要回滚该事务，则执行该SQL即可：

mysql -uroot -plhr -h192.168.66.35 -P3306  < rollback.4.sql 

8.5 解析binlog 统计DML、长事务与大事务分析

MySQL [lhrdb]> flush logs;
Query OK, 0 rows affected (0.06 sec)

MySQL [lhrdb]> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql3306-bin.000001 |   3071539 |
| mysql3306-bin.000002 |       154 |
| mysql3306-bin.000003 |       397 |
| mysql3306-bin.000004 |      2182 |
| mysql3306-bin.000005 |       154 |
+----------------------+-----------+
5 rows in set (0.05 sec)

-- sysbench创建表并插入数据
[root@lhrmy2sql my2sql]# sysbench /usr/share/sysbench/oltp_common.lua --time=100 --mysql-host=192.168.66.35 --mysql-port=3306 --mysql-user=root --mysql-password=lhr --mysql-db=lhrdb --table-size=10000 --tables=10 --threads=16 --events=999999999   prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Initializing worker threads...

Creating table 'sbtest8'...
Creating table 'sbtest4'...
Creating table 'sbtest2'...
Creating table 'sbtest7'...
Creating table 'sbtest1'...
Creating table 'sbtest5'...
Creating table 'sbtest3'...
Creating table 'sbtest10'...
Creating table 'sbtest6'...
Creating table 'sbtest9'...
Inserting 10000 records into 'sbtest2'
Inserting 10000 records into 'sbtest5'
Inserting 10000 records into 'sbtest8'
Inserting 10000 records into 'sbtest10'
Inserting 10000 records into 'sbtest7'
Inserting 10000 records into 'sbtest3'
Inserting 10000 records into 'sbtest9'
Inserting 10000 records into 'sbtest1'
Inserting 10000 records into 'sbtest6'
Inserting 10000 records into 'sbtest4'
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest1'...

使用my2sql进行解析：

my2sql  -user root -password lhr  -port 3306 
-host 192.168.66.35 -databases lhrdb  -tables student 
-work-type rollback   -start-file mysql3306-bin.000004 
-start-pos 154 -stop-file  mysql3306-bin.000004 -stop-pos  2131 
-output-dir /my2sql/

执行过程：

[root@lhrmy2sql my2sql]# my2sql  -user root -password lhr  -port 3306 
> -host 192.168.66.35 -databases lhrdb 
> -big-trx-row-limit 500 -long-trx-seconds 300 
> -work-type stats   -start-file mysql3306-bin.000005 
> -start-pos 154 -stop-file  mysql3306-bin.000005 -stop-pos  19097041 
> -output-dir /my2sql/
[2021/02/26 12:44:51] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 192.168.66.35 3306 root   utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2021/02/26 12:44:51] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/02/26 12:44:51] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql3306-bin.000005, 154)
[2021/02/26 12:44:51] [info] repl.go:15 start to get binlog from mysql
[2021/02/26 12:44:51] [info] binlogsyncer.go:777 rotate to (mysql3306-bin.000005, 154)
[2021/02/26 12:44:51] [info] com.go:57 stop to get event. StopFilePos set. currentBinlog (mysql3306-bin.000005, 19097041) StopFilePos (mysql3306-bin.000005, 19097041)
[2021/02/26 12:44:51] [info] repl.go:17 finish getting binlog from mysql
[2021/02/26 12:44:51] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[root@lhrmy2sql my2sql]# 
[root@lhrmy2sql my2sql]# ll
total 12
-rw-r--r-- 1 root root 6311 Feb 26 12:44 biglong_trx.txt
-rw-r--r-- 1 root root 1614 Feb 26 12:44 binlog_status.txt
[root@lhrmy2sql my2sql]# more biglong_trx.txt 
binlog            starttime           stoptime            startpos   stoppos    rows     duration   tables
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 3710       522152     2716     0          [lhrdb.sbtest2(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 522217     1040659    2716     0          [lhrdb.sbtest5(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 1040724    1559166    2716     0          [lhrdb.sbtest2(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 1559231    2077673    2716     0          [lhrdb.sbtest5(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 2077738    2596180    2716     0          [lhrdb.sbtest8(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 2596245    3114687    2716     0          [lhrdb.sbtest2(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 3114752    3633194    2716     0          [lhrdb.sbtest8(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 3633259    4151701    2716     0          [lhrdb.sbtest5(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 4151766    4670208    2716     0          [lhrdb.sbtest7(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 4670273    5023855    1852     0          [lhrdb.sbtest2(inserts=1852, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 5023920    5542362    2716     0          [lhrdb.sbtest8(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 5542427    5896009    1852     0          [lhrdb.sbtest5(inserts=1852, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 5896074    6414517    2716     0          [lhrdb.sbtest10(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 6414582    6933024    2716     0          [lhrdb.sbtest7(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 6933089    7451531    2716     0          [lhrdb.sbtest1(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 7451596    7970038    2716     0          [lhrdb.sbtest3(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 7970103    8488545    2716     0          [lhrdb.sbtest9(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 8488610    8842192    1852     0          [lhrdb.sbtest8(inserts=1852, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 8842257    9360700    2716     0          [lhrdb.sbtest10(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 9360765    9879207    2716     0          [lhrdb.sbtest6(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 9879272    10397714   2716     0          [lhrdb.sbtest4(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 10397779   10916221   2716     0          [lhrdb.sbtest7(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 10916457   11434899   2716     0          [lhrdb.sbtest3(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 11434964   11953406   2716     0          [lhrdb.sbtest1(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 11953642   12472084   2716     0          [lhrdb.sbtest9(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 12472149   12990591   2716     0          [lhrdb.sbtest4(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 12990656   13509098   2716     0          [lhrdb.sbtest6(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 13509163   14027606   2716     0          [lhrdb.sbtest10(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 14027671   14381253   1852     0          [lhrdb.sbtest7(inserts=1852, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 14381489   14899931   2716     0          [lhrdb.sbtest3(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 14899996   15253579   1852     0          [lhrdb.sbtest10(inserts=1852, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 15253644   15772086   2716     0          [lhrdb.sbtest9(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 15772151   16290593   2716     0          [lhrdb.sbtest6(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 16290658   16809100   2716     0          [lhrdb.sbtest4(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 16809165   17327607   2716     0          [lhrdb.sbtest1(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 17327843   17681425   1852     0          [lhrdb.sbtest3(inserts=1852, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 17681490   18035072   1852     0          [lhrdb.sbtest9(inserts=1852, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 18035137   18388719   1852     0          [lhrdb.sbtest4(inserts=1852, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 18388784   18742366   1852     0          [lhrdb.sbtest6(inserts=1852, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 18742431   19096013   1852     0          [lhrdb.sbtest1(inserts=1852, updates=0, deletes=0)]
[root@lhrmy2sql my2sql]# more binlog_status.txt 
binlog            starttime           stoptime            startpos   stoppos    inserts  updates  deletes  database        table               
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:45 522290     5895978    10000    0        0        lhrdb           sbtest5             
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:46 4151839    14381222   10000    0        0        lhrdb           sbtest7             
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:46 5896147    15253548   10000    0        0        lhrdb           sbtest10            
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:46 6933162    19095982   10000    0        0        lhrdb           sbtest1             
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:46 7451669    17681394   10000    0        0        lhrdb           sbtest3             
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:46 9360838    18742335   10000    0        0        lhrdb           sbtest6             
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:46 9879345    18388688   10000    0        0        lhrdb           sbtest4             
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:45 3783       5023824    10000    0        0        lhrdb           sbtest2             
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:45 2077811    8842161    10000    0        0        lhrdb           sbtest8             
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:46 7970176    18035041   10000    0        0        lhrdb           sbtest9  

若继续对数据库做压测，继续分析，可以统计到相关的SQL：

[root@lhrmy2sql my2sql]# sysbench /usr/share/sysbench/oltp_read_write.lua --time=60 --mysql-host=192.168.66.35 --mysql-port=3306 --mysql-user=root --mysql-password=lhr --mysql-db=lhrdb --table-size=10000 --tables=10 --threads=16 --events=999999999  --report-interval=10 --db-ps-mode=disable --forced-shutdown=1 run
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 16
Report intermediate results every 10 second(s)
Initializing random number generator from current time

Forcing shutdown in 61 seconds

Initializing worker threads...

Threads started!

[ 10s ] thds: 16 tps: 339.98 qps: 6815.40 (r/w/o: 4773.15/1360.70/681.55) lat (ms,95%): 70.55 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 325.92 qps: 6524.67 (r/w/o: 4567.13/1305.69/651.85) lat (ms,95%): 75.82 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 323.20 qps: 6459.69 (r/w/o: 4521.49/1291.80/646.40) lat (ms,95%): 89.16 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 350.30 qps: 7000.31 (r/w/o: 4899.94/1399.78/700.59) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 349.38 qps: 6995.89 (r/w/o: 4897.68/1399.44/698.77) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 16 tps: 341.42 qps: 6824.69 (r/w/o: 4776.47/1365.48/682.74) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            284466
        write:                           81276
        other:                           40638
        total:                           406380
    transactions:                        20319  (338.41 per sec.)
    queries:                             406380 (6768.29 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0385s
    total number of events:              20319

Latency (ms):
         min:                                   19.25
         avg:                                   47.25
         max:                                  178.19
         95th percentile:                       74.46
         sum:                               960171.80

Threads fairness:
    events (avg/stddev):           1269.9375/6.81
    execution time (avg/stddev):   60.0107/0.01

[root@lhrmy2sql my2sql]# ll
total 12
-rw-r--r-- 1 root root 6311 Feb 26 12:44 biglong_trx.txt
-rw-r--r-- 1 root root 1614 Feb 26 12:44 binlog_status.txt
[root@lhrmy2sql my2sql]# rm -rf *
[root@lhrmy2sql my2sql]# my2sql  -user root -password lhr  -port 3306 
> -host 192.168.66.35 -databases lhrdb 
> -big-trx-row-limit 500 -long-trx-seconds 300 
> -work-type stats   -start-file mysql3306-bin.000005 
> -start-pos 19097041 -stop-file  mysql3306-bin.000005 -stop-pos  53302083 
> -output-dir /my2sql/
[2021/02/26 12:47:46] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 192.168.66.35 3306 root   utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2021/02/26 12:47:46] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql3306-bin.000005, 19097041)
[2021/02/26 12:47:46] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/02/26 12:47:46] [info] repl.go:15 start to get binlog from mysql
[2021/02/26 12:47:46] [info] binlogsyncer.go:777 rotate to (mysql3306-bin.000005, 19097041)
[2021/02/26 12:47:47] [info] com.go:57 stop to get event. StopFilePos set. currentBinlog (mysql3306-bin.000005, 53302083) StopFilePos (mysql3306-bin.000005, 53302083)
[2021/02/26 12:47:47] [info] repl.go:17 finish getting binlog from mysql
[2021/02/26 12:47:47] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[root@lhrmy2sql my2sql]# ll
total 12
-rw-r--r-- 1 root root  107 Feb 26 12:47 biglong_trx.txt
-rw-r--r-- 1 root root 4554 Feb 26 12:47 binlog_status.txt
[root@lhrmy2sql my2sql]# more biglong_trx.txt 
binlog            starttime           stoptime            startpos   stoppos    rows     duration   tables
[root@lhrmy2sql my2sql]# more binlog_status.txt 
binlog            starttime           stoptime            startpos   stoppos    inserts  updates  deletes  database        table               
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19102706   35743680   990      1952     990      lhrdb           sbtest1             
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19105598   35739197   964      1931     964      lhrdb           sbtest9             
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19097179   35744246   956      1921     956      lhrdb           sbtest4             
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19097653   35741997   945      1958     945      lhrdb           sbtest6             
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19098864   35741523   989      1981     989      lhrdb           sbtest2             
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19099338   35736472   996      2036     996      lhrdb           sbtest5             
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19101497   35734145   1019     1994     1019     lhrdb           sbtest7             
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19098127   35738157   1013     2023     1013     lhrdb           sbtest10            
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19100549   35743206   993      2008     993      lhrdb           sbtest8             
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19101023   35740880   1024     1974     1024     lhrdb           sbtest3             
mysql3306-bin.000005 2021-02-26_12:46:15 2021-02-26_12:46:44 35761728   53261637   1017     2046     1017     lhrdb           sbtest2             
mysql3306-bin.000005 2021-02-26_12:46:14 2021-02-26_12:46:44 35744890   53263965   1041     2101     1041     lhrdb           sbtest5             
mysql3306-bin.000005 2021-02-26_12:46:14 2021-02-26_12:46:44 35746573   53262280   1080     2059     1080     lhrdb           sbtest6             
mysql3306-bin.000005 2021-02-26_12:46:14 2021-02-26_12:46:44 35750416   53261071   1012     2098     1012     lhrdb           sbtest4             
mysql3306-bin.000005 2021-02-26_12:46:14 2021-02-26_12:46:44 35751625   53262754   1095     2045     1095     lhrdb           sbtest7             
mysql3306-bin.000005 2021-02-26_12:46:15 2021-02-26_12:46:44 35754518   53256021   1062     2191     1062     lhrdb           sbtest8             
mysql3306-bin.000005 2021-02-26_12:46:14 2021-02-26_12:46:44 35744415   53263322   1001     2093     1001     lhrdb           sbtest10            
mysql3306-bin.000005 2021-02-26_12:46:15 2021-02-26_12:46:45 35745364   53264439   1052     2054     1052     lhrdb           sbtest3             
mysql3306-bin.000005 2021-02-26_12:46:14 2021-02-26_12:46:44 35746099   53254338   1037     2072     1037     lhrdb           sbtest1             
mysql3306-bin.000005 2021-02-26_12:46:14 2021-02-26_12:46:44 35747782   53253220   1010     2057     1010     lhrdb           sbtest9             
mysql3306-bin.000005 2021-02-26_12:46:45 2021-02-26_12:46:45 53270228   53284649   1        3        1        lhrdb           sbtest4             
mysql3306-bin.000005 2021-02-26_12:46:45 2021-02-26_12:46:45 53286333   53291384   1        1        1        lhrdb           sbtest1             
mysql3306-bin.000005 2021-02-26_12:46:45 2021-02-26_12:46:45 53271176   53302052   4        3        4        lhrdb           sbtest8             
mysql3306-bin.000005 2021-02-26_12:46:45 2021-02-26_12:46:45 53272860   53298684   2        1        2        lhrdb           sbtest9             
mysql3306-bin.000005 2021-02-26_12:46:45 2021-02-26_12:46:45 53277911   53299327   2        5        2        lhrdb           sbtest7             
mysql3306-bin.000005 2021-02-26_12:46:45 2021-02-26_12:46:45 53264439   53295317   4        5        4        lhrdb           sbtest5             
mysql3306-bin.000005 2021-02-26_12:46:44 2021-02-26_12:46:45 53265174   53301011   2        4        2        lhrdb           sbtest6             
mysql3306-bin.000005 2021-02-26_12:46:44 2021-02-26_12:46:45 53266122   53295960   3        5        3        lhrdb           sbtest2             
mysql3306-bin.000005 2021-02-26_12:46:44 2021-02-26_12:46:45 53266857   53301486   4        13       4        lhrdb           sbtest10            
mysql3306-bin.000005 2021-02-26_12:46:45 2021-02-26_12:46:45 53270702   53298118   0        4        0        lhrdb           sbtest3             
[root@lhrmy2sql my2sql]# 

About Me

---

● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除
● 本文在个人微 信公众号（DB宝）上有同步更新
● QQ群号： 230161599 、618766405，微信群私聊
● 个人QQ号（646634621），微 信号（db_bao），注明添加缘由
● 于 2021年3月 在西安完成
● 最新修改时间：2021年3月
● 版权所有，欢迎分享本文，转载请保留出处

---

●小麦苗的微店： https://weidian.com/?userid=793741433
●小麦苗出版的数据库类丛书： http://blog.itpub.net/26736162/viewspace-2142121/
●小麦苗OCP、OCM、高可用、DBA学习班（Oracle、MySQL、NoSQL）： http://blog.itpub.net/26736162/viewspace-2148098/
●数据库笔试面试题库及解答： https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w

---

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（DB宝）及QQ群（DBA宝典）、添加小麦苗微信， 学习最实用的数据库技术。

---