一、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秒 | 不支持 |
四、安装
1
-- 申请一台全新的主机
2docker rm -f lhrmy2sql
3docker run -d
--name lhrmy2sql -h lhrmy2sql \
4
--privileged=true lhrbest/lhrcentos76:8.0 \
5 /usr/sbin/init
6
7docker exec -it lhrmy2sql bash
8
9
10
11
-- 方法1：编译安装
12yum
install -y golang
13
go
version
14
go env | grep GOPATH
15
16mkdir -p /root/
go/src
17cd /root/
go/src
18git
clone https://github.com/liuhr/my2sql.git
19cd my2sql/
20
go
build .
21
22
23
-- 方法2：直接下载编译好的二进制文件。
24
-- https://github.com/liuhr/my2sql/tree/master/releases
25wget https://raw.githubusercontent.com/liuhr/my2sql/
master/releases/centOS_release_6.x/my2sql
26wget https://raw.githubusercontent.com/liuhr/my2sql/
master/releases/centOS_release_7.x/my2sql
27mv my2sql /usr/
local/
bin/my2sql
28chmod +x /usr/
local/
bin/my2sql
29
30
31
-- 若不能下载，请添加以下解析：
32echo
"
3313.229.188.59 github.com
34199.232.4.133 raw.githubusercontent.com
35" >> /etc/
hosts
五、重要参数说明
-U 优先使用unique key作为where条件，默认false
-add-extraInfo 是否把database/table/datetime/binlogposition…信息以注释的方式加入生成的每条sql前，默认false
1datetime=2020-07-16_10:44:09 database=orchestrator table=cluster_domain_name binlog=mysql-bin.011519 startpos=15552 stoppos=15773
2
3 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
1./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -work-type 2sql \
2-
start-
file mysql-
bin
.011259 -
start-datetime
"2020-07-16 10:20:00" -
stop-datetime
"2020-07-16 11:00:00" \
3-
output-dir ./tmpdir
根据pos点解析出标准SQL
1./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -work-type 2sql \
2-
start-
file mysql-
bin
.011259 -
start-pos
4 -
stop-
file mysql-
bin
.011259 -
stop-pos
583918266 \
3-
output-dir ./tmpdir
6.2 解析出回滚SQL
根据时间点解析出回滚SQL
1./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -work-type
rollback \
2-
start-
file mysql-
bin
.011259 -
start-datetime
"2020-07-16 10:20:00" -
stop-datetime
"2020-07-16 11:00:00" \
3-
output-dir ./tmpdir
根据pos点解析出回滚SQL
1./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -work-type
rollback \
2-
start-
file mysql-
bin
.011259 -
start-pos
4 -
stop-
file mysql-
bin
.011259 -
stop-pos
583918266 \
3-
output-dir ./tmpdir
6.3 统计DML以及大事务
统计时间范围各个表的DML操作数量，统计一个事务大于500条、时间大于300秒的事务
1./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -work-type stats \
2-
start-
file mysql-
bin
.011259 -
start-datetime
"2020-07-16 10:20:00" -
stop-datetime
"2020-07-16 11:00:00" \
3-
big-trx-
row-
limit
500 -
long-trx-seconds
300 -
output-dir ./tmpdir
统计一段pos点范围各个表的DML操作数量，统计一个事务大于500条、时间大于300秒的事务
1./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -work-type stats \
2-
start-
file mysql-
bin
.011259 -
start-pos
4 -
stop-
file mysql-
bin
.011259 -
stop-pos
583918266 \
3-
big-trx-
row-
limit
500 -
long-trx-seconds
300 -
output-dir ./tmpdir
6.4 从某一个pos点解析出标准SQL，并且持续打印到屏幕
1./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -work-type 2sql \
2-
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环境
1docker rm -f mysql3306
2docker run -d
--name mysql3306 -h mysql3306 -p 3306:3306 \
3 -v /etc/mysql/mysql3306/conf:/etc/mysql/conf.d \
4 -e MYSQL_ROOT_PASSWORD=lhr -e TZ=Asia/Shanghai \
5 mysql:5.7.30
6
7
8docker exec -it mysql3306 bash
9docker logs -f mysql3306
10
11
12cat > /etc/mysql/mysql3306/conf/my.cnf <<"EOF"
13[mysqld]
14default-time-zone = '+8:00'
15log_timestamps = SYSTEM
16skip-name-resolve
17log-bin
18server_id=573306
19character_set_server=utf8mb4
20EOF
21
22docker restart mysql3306
23
24
25
26mysql -uroot -plhr -h192.168.66.35 -P3306
27
create
database lhrdb
default
character
set utf8mb4;
8.2 执行DML操作
1
show
master
logs;
2
flush
logs;
3
4
5
select
now();
6
7
use lhrdb;
8
CREATE
TABLE
`student` (
9
`id`
int(
11)
NOT
NULL AUTO_INCREMENT,
10
`number`
int(
11)
NOT
NULL,
11
`name`
varchar(
255)
DEFAULT
NULL,
12
`add_time`
timestamp
NULL
DEFAULT
CURRENT_TIMESTAMP
COMMENT
'添加的时间',
13
`content`
json
DEFAULT
NULL,
14 PRIMARY
KEY (
`id`),
15
UNIQUE
KEY
`idx_name` (
`number`,
`name`)
16)
ENGINE=
InnoDB
DEFAULT
CHARSET=utf8;
17
18
19
INSERT
INTO
`lhrdb`.
`student` (
`id`,
`number`,
`name`,
`add_time`,
`content`)
VALUES (
1233,
26,
'ranran',
'2020-07-15 19:06:03',
null);
20
INSERT
INTO
`lhrdb`.
`student` (
`id`,
`number`,
`name`,
`add_time`,
`content`)
VALUES (
1232,
134,
'asdf',
'2020-07-12 11:08:41',
null);
21
INSERT
INTO
`lhrdb`.
`student` (
`id`,
`number`,
`name`,
`add_time`,
`content`)
VALUES (
1231,
21,
'chenxi',
'2020-07-12 10:12:45',
null);
22
INSERT
INTO
`lhrdb`.
`student` (
`id`,
`number`,
`name`,
`add_time`,
`content`)
VALUES (
1229,
20,
'chenxi',
'2020-07-11 16:20:50',
null);
23
INSERT
INTO
`lhrdb`.
`student` (
`id`,
`number`,
`name`,
`add_time`,
`content`)
VALUES (
1227,
18,
'hanran',
'2020-07-06 21:55:48',
'{\"age\":13,\"author\":\"liuhan\"}');
24
25
select *
from student;
26
select
now();
27
28
show
master
logs;
29
show
binlog
events
in
'mysql3306-bin.000002';
执行过程：
1MySQL [(none)]>
show
master
logs;
2+
----------------------+-----------+
3| Log_name | File_size |
4+
----------------------+-----------+
5| mysql3306-bin.000001 | 3071539 |
6| mysql3306-bin.000002 | 154 |
7| mysql3306-bin.000003 | 154 |
8+
----------------------+-----------+
93 rows in
set (
0.05 sec)
10
11MySQL [(
none)]>
12MySQL [(
none)]>
create
database lhrdb
default
character
set utf8mb4;
13Query OK, 1 row affected (0.05 sec)
14
15MySQL [(none)]>
show
master
logs;
16+
----------------------+-----------+
17| Log_name | File_size |
18+
----------------------+-----------+
19| mysql3306-bin.000001 | 3071539 |
20| mysql3306-bin.000002 | 154 |
21| mysql3306-bin.000003 | 346 |
22+
----------------------+-----------+
233 rows in
set (
0.05 sec)
24
25MySQL [(
none)]>
flush
logs;
26Query OK, 0 rows affected (0.06 sec)
27
28MySQL [(none)]>
29MySQL [(none)]>
select
now();
30+
---------------------+
31| now() |
32+
---------------------+
33| 2021-02-26 12:22:38 |
34+
---------------------+
351 row in
set (
0.05 sec)
36
37MySQL [(
none)]>
38MySQL [(
none)]>
use lhrdb;
39Database changed
40MySQL [lhrdb]>
CREATE
TABLE
`student` (
41 ->
`id`
int(
11)
NOT
NULL AUTO_INCREMENT,
42 ->
`number`
int(
11)
NOT
NULL,
43 ->
`name`
varchar(
255)
DEFAULT
NULL,
44 ->
`add_time`
timestamp
NULL
DEFAULT
CURRENT_TIMESTAMP
COMMENT
'添加的时间',
45 ->
`content`
json
DEFAULT
NULL,
46 -> PRIMARY
KEY (
`id`),
47 ->
UNIQUE
KEY
`idx_name` (
`number`,
`name`)
48 -> )
ENGINE=
InnoDB
DEFAULT
CHARSET=utf8;
49Query OK, 0 rows affected (0.07 sec)
50
51MySQL [lhrdb]>
INSERT
INTO
`lhrdb`.
`student` (
`id`,
`number`,
`name`,
`add_time`,
`content`)
VALUES (
1233,
26,
'ranran',
'2020-07-15 19:06:03',
null);
52Query OK, 1 row affected (0.08 sec)
53
54MySQL [lhrdb]>
INSERT
INTO
`lhrdb`.
`student` (
`id`,
`number`,
`name`,
`add_time`,
`content`)
VALUES (
1232,
134,
'asdf',
'2020-07-12 11:08:41',
null);
55Query OK, 1 row affected (0.05 sec)
56
57MySQL [lhrdb]>
INSERT
INTO
`lhrdb`.
`student` (
`id`,
`number`,
`name`,
`add_time`,
`content`)
VALUES (
1231,
21,
'chenxi',
'2020-07-12 10:12:45',
null);
58Query OK, 1 row affected (0.06 sec)
59
60MySQL [lhrdb]>
INSERT
INTO
`lhrdb`.
`student` (
`id`,
`number`,
`name`,
`add_time`,
`content`)
VALUES (
1229,
20,
'chenxi',
'2020-07-11 16:20:50',
null);
61Query OK, 1 row affected (0.05 sec)
62
63MySQL [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\"}');
64Query OK, 1 row affected (0.05 sec)
65
66MySQL [lhrdb]>
67MySQL [lhrdb]>
select *
from student;
68+
------+--------+--------+---------------------+---------------------------------+
69| id | number | name | add_time | content |
70+
------+--------+--------+---------------------+---------------------------------+
71| 1227 | 18 | hanran | 2020-07-06 21:55:48 | {"age": 13, "author": "liuhan"} |
72| 1229 | 20 | chenxi | 2020-07-11 16:20:50 | NULL |
73| 1231 | 21 | chenxi | 2020-07-12 10:12:45 | NULL |
74| 1232 | 134 | asdf | 2020-07-12 11:08:41 | NULL |
75| 1233 | 26 | ranran | 2020-07-15 19:06:03 | NULL |
76+
------+--------+--------+---------------------+---------------------------------+
775 rows in
set (
0.05 sec)
78
79MySQL [lhrdb]>
select
now();
80+
---------------------+
81| now() |
82+
---------------------+
83| 2021-02-26 12:23:16 |
84+
---------------------+
851 row in
set (
0.05 sec)
86
87MySQL [lhrdb]>
88MySQL [lhrdb]>
show
master
logs;
89+
----------------------+-----------+
90| Log_name | File_size |
91+
----------------------+-----------+
92| mysql3306-bin.000001 | 3071539 |
93| mysql3306-bin.000002 | 154 |
94| mysql3306-bin.000003 | 397 |
95| mysql3306-bin.000004 | 2131 |
96+
----------------------+-----------+
974 rows in
set (
0.05 sec)
98
99MySQL [lhrdb]>
show
binlog
events
in
'mysql3306-bin.000004';
100+
----------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
101| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
102+
----------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
103| mysql3306-bin.000004 | 4 | Format_desc | 573306 | 123 | Server ver: 5.7.30-log, Binlog ver: 4 |
104| mysql3306-bin.000004 | 123 | Previous_gtids | 573306 | 154 | |
105| mysql3306-bin.000004 | 154 | Anonymous_Gtid | 573306 | 219 |
SET @@SESSION.GTID_NEXT=
'ANONYMOUS' |
106| mysql3306-
bin
.000004 |
219 |
Query |
573306 |
634 |
use
`lhrdb`;
CREATE
TABLE
`student` (
107
`id`
int(
11)
NOT
NULL AUTO_INCREMENT,
108
`number`
int(
11)
NOT
NULL,
109
`name`
varchar(
255)
DEFAULT
NULL,
110
`add_time`
timestamp
NULL
DEFAULT
CURRENT_TIMESTAMP
COMMENT
'添加的时间',
111
`content`
json
DEFAULT
NULL,
112 PRIMARY
KEY (
`id`),
113
UNIQUE
KEY
`idx_name` (
`number`,
`name`)
114)
ENGINE=
InnoDB
DEFAULT
CHARSET=utf8 |
115| mysql3306-
bin
.000004 |
634 | Anonymous_Gtid |
573306 |
699 |
SET @@SESSION.GTID_NEXT=
'ANONYMOUS' |
116| mysql3306-
bin
.000004 |
699 |
Query |
573306 |
780 |
BEGIN |
117| mysql3306-
bin
.000004 |
780 | Table_map |
573306 |
839 | table_id:
108 (lhrdb.student) |
118| mysql3306-
bin
.000004 |
839 | Write_rows |
573306 |
895 | table_id:
108 flags: STMT_END_F |
119| mysql3306-
bin
.000004 |
895 | Xid |
573306 |
926 |
COMMIT
/* xid=13 */ |
120| mysql3306-
bin
.000004 |
926 | Anonymous_Gtid |
573306 |
991 |
SET @@SESSION.GTID_NEXT=
'ANONYMOUS' |
121| mysql3306-
bin
.000004 |
991 |
Query |
573306 |
1072 |
BEGIN |
122| mysql3306-
bin
.000004 |
1072 | Table_map |
573306 |
1131 | table_id:
108 (lhrdb.student) |
123| mysql3306-
bin
.000004 |
1131 | Write_rows |
573306 |
1185 | table_id:
108 flags: STMT_END_F |
124| mysql3306-
bin
.000004 |
1185 | Xid |
573306 |
1216 |
COMMIT
/* xid=14 */ |
125| mysql3306-
bin
.000004 |
1216 | Anonymous_Gtid |
573306 |
1281 |
SET @@SESSION.GTID_NEXT=
'ANONYMOUS' |
126| mysql3306-
bin
.000004 |
1281 |
Query |
573306 |
1362 |
BEGIN |
127| mysql3306-
bin
.000004 |
1362 | Table_map |
573306 |
1421 | table_id:
108 (lhrdb.student) |
128| mysql3306-
bin
.000004 |
1421 | Write_rows |
573306 |
1477 | table_id:
108 flags: STMT_END_F |
129| mysql3306-
bin
.000004 |
1477 | Xid |
573306 |
1508 |
COMMIT
/* xid=15 */ |
130| mysql3306-
bin
.000004 |
1508 | Anonymous_Gtid |
573306 |
1573 |
SET @@SESSION.GTID_NEXT=
'ANONYMOUS' |
131| mysql3306-
bin
.000004 |
1573 |
Query |
573306 |
1654 |
BEGIN |
132| mysql3306-
bin
.000004 |
1654 | Table_map |
573306 |
1713 | table_id:
108 (lhrdb.student) |
133| mysql3306-
bin
.000004 |
1713 | Write_rows |
573306 |
1769 | table_id:
108 flags: STMT_END_F |
134| mysql3306-
bin
.000004 |
1769 | Xid |
573306 |
1800 |
COMMIT
/* xid=16 */ |
135| mysql3306-
bin
.000004 |
1800 | Anonymous_Gtid |
573306 |
1865 |
SET @@SESSION.GTID_NEXT=
'ANONYMOUS' |
136| mysql3306-
bin
.000004 |
1865 |
Query |
573306 |
1946 |
BEGIN |
137| mysql3306-
bin
.000004 |
1946 | Table_map |
573306 |
2005 | table_id:
108 (lhrdb.student) |
138| mysql3306-
bin
.000004 |
2005 | Write_rows |
573306 |
2100 | table_id:
108 flags: STMT_END_F |
139| mysql3306-
bin
.000004 |
2100 | Xid |
573306 |
2131 |
COMMIT
/* xid=17 */ |
140+
----------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
141
29
rows
in
set (
0.05 sec)
8.3 解析binlog生成标准SQL
可以根据时间点解析出标准SQL：
1my2sql -user root -password lhr -port 3306 \
2-host 192.168.66.35 -databases lhrdb -tables student \
3-work-type 2sql -
start-
file mysql3306-
bin
.000004 \
4-
start-datetime
"2021-02-26 12:22:38"
--stop-datetime "2021-02-26 12:23:16" \
5-
output-dir /my2sql/
也可以根据binlog的pos点解析出标准SQL：
1my2sql -user root -password lhr -port 3306 \
2-host 192.168.66.35 -databases lhrdb -tables student \
3-work-type 2sql -
start-
file mysql3306-
bin
.000004 \
4-
start-pos
154 -
stop-
file mysql3306-
bin
.000004 -
stop-pos
2131 \
5-
output-dir /my2sql/
执行过程：
1[root@lhrmy2sql my2sql]
# my2sql -user root -password lhr -port 3306 \
2> -host 192.168.66.35 -databases lhrdb -tables student \
3> -work-type 2sql -
start-
file mysql3306-
bin
.000004 \
4> -
start-pos
154 -
stop-
file mysql3306-
bin
.000004 -
stop-pos
2131 \
5> -
output-dir /my2sql/
6[
2021/
02/
26
12:
27:
42] [info] events.go:
208
start
thread
to write
redo/
rollback
sql
into
file
7[
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
false
Local
false
0
0s
0s
0
false
false
0}
8[
2021/
02/
26
12:
27:
42] [info] binlogsyncer.go:
360
begin
to
sync
binlog
from
position (mysql3306-
bin
.000004,
154)
9[
2021/
02/
26
12:
27:
42] [info] events.go:
58
start
thread
2
to generate
redo/
rollback
sql
10[
2021/
02/
26
12:
27:
42] [info] events.go:
58
start
thread
1
to generate
redo/
rollback
sql
11[
2021/
02/
26
12:
27:
42] [info] stats_process.go:
166
start
thread
to
analyze
statistics
from
binlog
12[
2021/
02/
26
12:
27:
42] [info] repl.go:
15
start
to
get
binlog
from mysql
13[
2021/
02/
26
12:
27:
42] [info] binlogsyncer.go:
777 rotate
to (mysql3306-
bin
.000004,
154)
14[
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)
15[
2021/
02/
26
12:
27:
42] [info] repl.go:
17
finish getting
binlog
from mysql
16[
2021/
02/
26
12:
27:
42] [info] events.go:
183
exit
thread
1
to generate
redo/
rollback
sql
17[
2021/
02/
26
12:
27:
42] [info] stats_process.go:
266
exit
thread
to
analyze
statistics
from
binlog
18[
2021/
02/
26
12:
27:
42] [info] events.go:
183
exit
thread
2
to generate
redo/
rollback
sql
19[
2021/
02/
26
12:
27:
42] [info] events.go:
272
finish writing
redo/forward
sql
into
file
20[
2021/
02/
26
12:
27:
42] [info] events.go:
275
exit
thread
to write
redo/
rollback
sql
into
file
21[root@lhrmy2sql my2sql]
# ll
22total
12
23-rw-r
--r-- 1 root root 107 Feb 26 12:27 biglong_trx.txt
24-rw-r
--r-- 1 root root 291 Feb 26 12:27 binlog_status.txt
25-rw-r
--r-- 1 root root 671 Feb 26 12:27 forward.4.sql
26[root@lhrmy2sql my2sql]
# more biglong_trx.txt
27
binlog starttime stoptime startpos stoppos
rows
duration
tables
28[root@lhrmy2sql my2sql]
# more binlog_status.txt
29
binlog starttime stoptime startpos stoppos inserts updates deletes
database
table
30mysql3306-
bin
.000004
2021
-02
-26_12:
23:
06
2021
-02
-26_12:
23:
06
780
2100
5
0
0 lhrdb student
31[root@lhrmy2sql my2sql]
# more forward.4.sql
32
INSERT
INTO
`lhrdb`.
`student` (
`id`,
`number`,
`name`,
`add_time`,
`content`)
VALUES (
1233,
26,
'ranran',
'2020-07-15 19:06:03',
null);
33
INSERT
INTO
`lhrdb`.
`student` (
`id`,
`number`,
`name`,
`add_time`,
`content`)
VALUES (
1232,
134,
'asdf',
'2020-07-12 11:08:41',
null);
34
INSERT
INTO
`lhrdb`.
`student` (
`id`,
`number`,
`name`,
`add_time`,
`content`)
VALUES (
1231,
21,
'chenxi',
'2020-07-12 10:12:45',
null);
35
INSERT
INTO
`lhrdb`.
`student` (
`id`,
`number`,
`name`,
`add_time`,
`content`)
VALUES (
1229,
20,
'chenxi',
'2020-07-11 16:20:50',
null);
36
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
1my2sql -user root -password lhr -port 3306 \
2-host 192.168.66.35 -databases lhrdb -tables student \
3-work-type
rollback -
start-
file mysql3306-
bin
.000004 \
4-
start-pos
154 -
stop-
file mysql3306-
bin
.000004 -
stop-pos
2131 \
5-
output-dir /my2sql/
执行过程：
1[root@lhrmy2sql my2sql]
# my2sql -user root -password lhr -port 3306 \
2> -host 192.168.66.35 -databases lhrdb -tables student \
3> -work-type
rollback -
start-
file mysql3306-
bin
.000004 \
4> -
start-pos
154 -
stop-
file mysql3306-
bin
.000004 -
stop-pos
2131 \
5> -
output-dir /my2sql/
6[
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
false
Local
false
0
0s
0s
0
false
false
0}
7[
2021/
02/
26
12:
29:
51] [info] binlogsyncer.go:
360
begin
to
sync
binlog
from
position (mysql3306-
bin
.000004,
154)
8[
2021/
02/
26
12:
29:
51] [info] events.go:
208
start
thread
to write
redo/
rollback
sql
into
file
9[
2021/
02/
26
12:
29:
51] [info] events.go:
58
start
thread
2
to generate
redo/
rollback
sql
10[
2021/
02/
26
12:
29:
51] [info] events.go:
58
start
thread
1
to generate
redo/
rollback
sql
11[
2021/
02/
26
12:
29:
51] [info] stats_process.go:
166
start
thread
to
analyze
statistics
from
binlog
12[
2021/
02/
26
12:
29:
51] [info] repl.go:
15
start
to
get
binlog
from mysql
13[
2021/
02/
26
12:
29:
51] [info] binlogsyncer.go:
777 rotate
to (mysql3306-
bin
.000004,
154)
14[
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)
15[
2021/
02/
26
12:
29:
51] [info] repl.go:
17
finish getting
binlog
from mysql
16[
2021/
02/
26
12:
29:
51] [info] stats_process.go:
266
exit
thread
to
analyze
statistics
from
binlog
17[
2021/
02/
26
12:
29:
51] [info] events.go:
183
exit
thread
1
to generate
redo/
rollback
sql
18[
2021/
02/
26
12:
29:
51] [info] events.go:
183
exit
thread
2
to generate
redo/
rollback
sql
19[
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
20[
2021/
02/
26
12:
29:
51] [info] rollback_process.go:
15
start
thread
1
to revert
rollback
sql files
21[
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
22[
2021/
02/
26
12:
29:
51] [info] rollback_process.go:
156
finish reverting tmp
file /my2sql/.rollback
.4.sql
into /my2sql/
rollback
.4.sql
23[
2021/
02/
26
12:
29:
51] [info] rollback_process.go:
25
exit
thread
1
to revert
rollback
sql files
24[
2021/
02/
26
12:
29:
51] [info] events.go:
270
finish reverting
content
order
of tmp files
25[
2021/
02/
26
12:
29:
51] [info] events.go:
275
exit
thread
to write
redo/
rollback
sql
into
file
26[root@lhrmy2sql my2sql]
# ll
27total
12
28-rw-r
--r-- 1 root root 107 Feb 26 12:29 biglong_trx.txt
29-rw-r
--r-- 1 root root 291 Feb 26 12:29 binlog_status.txt
30-rw-r
--r-- 1 root root 235 Feb 26 12:29 rollback.4.sql
31[root@lhrmy2sql my2sql]
# more biglong_trx.txt
32
binlog starttime stoptime startpos stoppos
rows
duration
tables
33[root@lhrmy2sql my2sql]
# more binlog_status.txt
34
binlog starttime stoptime startpos stoppos inserts updates deletes
database
table
35mysql3306-
bin
.000004
2021
-02
-26_12:
23:
06
2021
-02
-26_12:
23:
06
780
2100
5
0
0 lhrdb student
36[root@lhrmy2sql my2sql]
# more rollback.4.sql
37
DELETE
FROM
`lhrdb`.
`student`
WHERE
`id`=
1227;
38
DELETE
FROM
`lhrdb`.
`student`
WHERE
`id`=
1229;
39
DELETE
FROM
`lhrdb`.
`student`
WHERE
`id`=
1231;
40
DELETE
FROM
`lhrdb`.
`student`
WHERE
`id`=
1232;
41
DELETE
FROM
`lhrdb`.
`student`
WHERE
`id`=
1233;
可以看到，回滚SQL是DELETE，已经生成。
要回滚该事务，则执行该SQL即可：
1mysql -uroot -plhr -h192.168.66.35 -P3306 < rollback.
4.
sql
8.5 解析binlog 统计DML、长事务与大事务分析
1MySQL [lhrdb]>
flush
logs;
2Query OK, 0 rows affected (0.06 sec)
3
4MySQL [lhrdb]>
show
master
logs;
5+
----------------------+-----------+
6| Log_name | File_size |
7+
----------------------+-----------+
8| mysql3306-bin.000001 | 3071539 |
9| mysql3306-bin.000002 | 154 |
10| mysql3306-bin.000003 | 397 |
11| mysql3306-bin.000004 | 2182 |
12| mysql3306-bin.000005 | 154 |
13+
----------------------+-----------+
145 rows in
set (
0.05 sec)
15
16
-- sysbench创建表并插入数据
17[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
18sysbench
1.0
.17 (
using
system LuaJIT
2.0
.4)
19
20Initializing worker threads...
21
22Creating
table
'sbtest8'...
23Creating
table
'sbtest4'...
24Creating
table
'sbtest2'...
25Creating
table
'sbtest7'...
26Creating
table
'sbtest1'...
27Creating
table
'sbtest5'...
28Creating
table
'sbtest3'...
29Creating
table
'sbtest10'...
30Creating
table
'sbtest6'...
31Creating
table
'sbtest9'...
32Inserting
10000
records
into
'sbtest2'
33Inserting
10000
records
into
'sbtest5'
34Inserting
10000
records
into
'sbtest8'
35Inserting
10000
records
into
'sbtest10'
36Inserting
10000
records
into
'sbtest7'
37Inserting
10000
records
into
'sbtest3'
38Inserting
10000
records
into
'sbtest9'
39Inserting
10000
records
into
'sbtest1'
40Inserting
10000
records
into
'sbtest6'
41Inserting
10000
records
into
'sbtest4'
42Creating a secondary
index
on
'sbtest2'...
43Creating a secondary
index
on
'sbtest5'...
44Creating a secondary
index
on
'sbtest8'...
45Creating a secondary
index
on
'sbtest7'...
46Creating a secondary
index
on
'sbtest10'...
47Creating a secondary
index
on
'sbtest3'...
48Creating a secondary
index
on
'sbtest9'...
49Creating a secondary
index
on
'sbtest4'...
50Creating a secondary
index
on
'sbtest6'...
51Creating a secondary
index
on
'sbtest1'...
使用my2sql进行解析：
1my2sql -user root -password lhr -port 3306 \
2 -host 192.168.66.35 -databases lhrdb \
3 -big-trx-row-limit 500 -long-trx-seconds 300 \
4 -work-type stats -
start-
file mysql3306-
bin
.000005 \
5 -
start-pos
154 -
stop-
file mysql3306-
bin
.000005 -
stop-pos
19097041 \
6 -
output-dir /my2sql/
执行过程：
1[root@lhrmy2sql my2sql]
# my2sql -user root -password lhr -port 3306 \
2> -host 192.168.66.35 -databases lhrdb \
3> -big-trx-row-limit 500 -long-trx-seconds 300 \
4> -work-type stats -
start-
file mysql3306-
bin
.000005 \
5> -
start-pos
154 -
stop-
file mysql3306-
bin
.000005 -
stop-pos
19097041 \
6> -
output-dir /my2sql/
7[
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
false
Local
false
0
0s
0s
0
false
false
0}
8[
2021/
02/
26
12:
44:
51] [info] stats_process.go:
166
start
thread
to
analyze
statistics
from
binlog
9[
2021/
02/
26
12:
44:
51] [info] binlogsyncer.go:
360
begin
to
sync
binlog
from
position (mysql3306-
bin
.000005,
154)
10[
2021/
02/
26
12:
44:
51] [info] repl.go:
15
start
to
get
binlog
from mysql
11[
2021/
02/
26
12:
44:
51] [info] binlogsyncer.go:
777 rotate
to (mysql3306-
bin
.000005,
154)
12[
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)
13[
2021/
02/
26
12:
44:
51] [info] repl.go:
17
finish getting
binlog
from mysql
14[
2021/
02/
26
12:
44:
51] [info] stats_process.go:
266
exit
thread
to
analyze
statistics
from
binlog
15[root@lhrmy2sql my2sql]
#
16[root@lhrmy2sql my2sql]
# ll
17total
12
18-rw-r
--r-- 1 root root 6311 Feb 26 12:44 biglong_trx.txt
19-rw-r
--r-- 1 root root 1614 Feb 26 12:44 binlog_status.txt
20[root@lhrmy2sql my2sql]
# more biglong_trx.txt
21
binlog starttime stoptime startpos stoppos
rows
duration
tables
22mysql3306-
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)]
23mysql3306-
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)]
24mysql3306-
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)]
25mysql3306-
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)]
26mysql3306-
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)]
27mysql3306-
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)]
28mysql3306-
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)]
29mysql3306-
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)]
30mysql3306-
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)]
31mysql3306-
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)]
32mysql3306-
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)]
33mysql3306-
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)]
34mysql3306-
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)]
35mysql3306-
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)]
36mysql3306-
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)]
37mysql3306-
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)]
38mysql3306-
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)]
39mysql3306-
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)]
40mysql3306-
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)]
41mysql3306-
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)]
42mysql3306-
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)]
43mysql3306-
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)]
44mysql3306-
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)]
45mysql3306-
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)]
46mysql3306-
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)]
47mysql3306-
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)]
48mysql3306-
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)]
49mysql3306-
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)]
50mysql3306-
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)]
51mysql3306-
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)]
52mysql3306-
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)]
53mysql3306-
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)]
54mysql3306-
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)]
55mysql3306-
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)]
56mysql3306-
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)]
57mysql3306-
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)]
58mysql3306-
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)]
59mysql3306-
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)]
60mysql3306-
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)]
61mysql3306-
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)]
62[root@lhrmy2sql my2sql]
# more binlog_status.txt
63
binlog starttime stoptime startpos stoppos inserts updates deletes
database
table
64mysql3306-
bin
.000005
2021
-02
-26_12:
33:
44
2021
-02
-26_12:
33:
45
522290
5895978
10000
0
0 lhrdb sbtest5
65mysql3306-
bin
.000005
2021
-02
-26_12:
33:
44
2021
-02
-26_12:
33:
46
4151839
14381222
10000
0
0 lhrdb sbtest7
66mysql3306-
bin
.000005
2021
-02
-26_12:
33:
44
2021
-02
-26_12:
33:
46
5896147
15253548
10000
0
0 lhrdb sbtest10
67mysql3306-
bin
.000005
2021
-02
-26_12:
33:
44
2021
-02
-26_12:
33:
46
6933162
19095982
10000
0
0 lhrdb sbtest1
68mysql3306-
bin
.000005
2021
-02
-26_12:
33:
44
2021
-02
-26_12:
33:
46
7451669
17681394
10000
0
0 lhrdb sbtest3
69mysql3306-
bin
.000005
2021
-02
-26_12:
33:
44
2021
-02
-26_12:
33:
46
9360838
18742335
10000
0
0 lhrdb sbtest6
70mysql3306-
bin
.000005
2021
-02
-26_12:
33:
44
2021
-02
-26_12:
33:
46
9879345
18388688
10000
0
0 lhrdb sbtest4
71mysql3306-
bin
.000005
2021
-02
-26_12:
33:
44
2021
-02
-26_12:
33:
45
3783
5023824
10000
0
0 lhrdb sbtest2
72mysql3306-
bin
.000005
2021
-02
-26_12:
33:
44
2021
-02
-26_12:
33:
45
2077811
8842161
10000
0
0 lhrdb sbtest8
73mysql3306-
bin
.000005
2021
-02
-26_12:
33:
44
2021
-02
-26_12:
33:
46
7970176
18035041
10000
0
0 lhrdb sbtest9
若继续对数据库做压测，继续分析，可以统计到相关的SQL：
1[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
2sysbench 1.0.17 (using system LuaJIT 2.0.4)
3
4Running the test with following options:
5Number of threads: 16
6Report intermediate results every 10 second(s)
7Initializing random number generator from current time
8
9Forcing shutdown in 61 seconds
10
11Initializing worker threads...
12
13Threads started!
14
15[ 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
16[ 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
17[ 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
18[ 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
19[ 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
20[ 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
21SQL statistics:
22 queries performed:
23 read: 284466
24 write: 81276
25 other: 40638
26 total: 406380
27 transactions: 20319 (338.41 per sec.)
28 queries: 406380 (6768.29 per sec.)
29 ignored errors: 0 (0.00 per sec.)
30 reconnects: 0 (0.00 per sec.)
31
32General statistics:
33 total time: 60.0385s
34 total number of events: 20319
35
36Latency (ms):
37 min: 19.25
38 avg: 47.25
39 max: 178.19
40 95th percentile: 74.46
41 sum: 960171.80
42
43Threads fairness:
44 events (avg/stddev): 1269.9375/6.81
45 execution time (avg/stddev): 60.0107/0.01
46
47[root@lhrmy2sql my2sql]
# ll
48total 12
49-rw-r
--r-- 1 root root 6311 Feb 26 12:44 biglong_trx.txt
50-rw-r
--r-- 1 root root 1614 Feb 26 12:44 binlog_status.txt
51[root@lhrmy2sql my2sql]
# rm -rf *
52[root@lhrmy2sql my2sql]
# my2sql -user root -password lhr -port 3306 \
53> -host 192.168.66.35 -databases lhrdb \
54> -big-trx-row-limit 500 -long-trx-seconds 300 \
55> -work-type stats -
start-
file mysql3306-
bin
.000005 \
56> -
start-pos
19097041 -
stop-
file mysql3306-
bin
.000005 -
stop-pos
53302083 \
57> -
output-dir /my2sql/
58[
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
false
Local
false
0
0s
0s
0
false
false
0}
59[
2021/
02/
26
12:
47:
46] [info] binlogsyncer.go:
360
begin
to
sync
binlog
from
position (mysql3306-
bin
.000005,
19097041)
60[
2021/
02/
26
12:
47:
46] [info] stats_process.go:
166
start
thread
to
analyze
statistics
from
binlog
61[
2021/
02/
26
12:
47:
46] [info] repl.go:
15
start
to
get
binlog
from mysql
62[
2021/
02/
26
12:
47:
46] [info] binlogsyncer.go:
777 rotate
to (mysql3306-
bin
.000005,
19097041)
63[
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)
64[
2021/
02/
26
12:
47:
47] [info] repl.go:
17
finish getting
binlog
from mysql
65[
2021/
02/
26
12:
47:
47] [info] stats_process.go:
266
exit
thread
to
analyze
statistics
from
binlog
66[root@lhrmy2sql my2sql]
# ll
67total
12
68-rw-r
--r-- 1 root root 107 Feb 26 12:47 biglong_trx.txt
69-rw-r
--r-- 1 root root 4554 Feb 26 12:47 binlog_status.txt
70[root@lhrmy2sql my2sql]
# more biglong_trx.txt
71
binlog starttime stoptime startpos stoppos
rows
duration
tables
72[root@lhrmy2sql my2sql]
# more binlog_status.txt
73
binlog starttime stoptime startpos stoppos inserts updates deletes
database
table
74mysql3306-
bin
.000005
2021
-02
-26_12:
45:
45
2021
-02
-26_12:
46:
14
19102706
35743680
990
1952
990 lhrdb sbtest1
75mysql3306-
bin
.000005
2021
-02
-26_12:
45:
45
2021
-02
-26_12:
46:
14
19105598
35739197
964
1931
964 lhrdb sbtest9
76mysql3306-
bin
.000005
2021
-02
-26_12:
45:
45
2021
-02
-26_12:
46:
14
19097179
35744246
956
1921
956 lhrdb sbtest4
77mysql3306-
bin
.000005
2021
-02
-26_12:
45:
45
2021
-02
-26_12:
46:
14
19097653
35741997
945
1958
945 lhrdb sbtest6
78mysql3306-
bin
.000005
2021
-02
-26_12:
45:
45
2021
-02
-26_12:
46:
14
19098864
35741523
989
1981
989 lhrdb sbtest2
79mysql3306-
bin
.000005
2021
-02
-26_12:
45:
45
2021
-02
-26_12:
46:
14
19099338
35736472
996
2036
996 lhrdb sbtest5
80mysql3306-
bin
.000005
2021
-02
-26_12:
45:
45
2021
-02
-26_12:
46:
14
19101497
35734145
1019
1994
1019 lhrdb sbtest7
81mysql3306-
bin
.000005
2021
-02
-26_12:
45:
45
2021
-02
-26_12:
46:
14
19098127
35738157
1013
2023
1013 lhrdb sbtest10
82mysql3306-
bin
.000005
2021
-02
-26_12:
45:
45
2021
-02
-26_12:
46:
14
19100549
35743206
993
2008
993 lhrdb sbtest8
83mysql3306-
bin
.000005
2021
-02
-26_12:
45:
45
2021
-02
-26_12:
46:
14
19101023
35740880
1024
1974
1024 lhrdb sbtest3
84mysql3306-
bin
.000005
2021
-02
-26_12:
46:
15
2021
-02
-26_12:
46:
44
35761728
53261637
1017
2046
1017 lhrdb sbtest2
85mysql3306-
bin
.000005
2021
-02
-26_12:
46:
14
2021
-02
-26_12:
46:
44
35744890
53263965
1041
2101
1041 lhrdb sbtest5
86mysql3306-
bin
.000005
2021
-02
-26_12:
46:
14
2021
-02
-26_12:
46:
44
35746573
53262280
1080
2059
1080 lhrdb sbtest6
87mysql3306-
bin
.000005
2021
-02
-26_12:
46:
14
2021
-02
-26_12:
46:
44
35750416
53261071
1012
2098
1012 lhrdb sbtest4
88mysql3306-
bin
.000005
2021
-02
-26_12:
46:
14
2021
-02
-26_12:
46:
44
35751625
53262754
1095
2045
1095 lhrdb sbtest7
89mysql3306-
bin
.000005
2021
-02
-26_12:
46:
15
2021
-02
-26_12:
46:
44
35754518
53256021
1062
2191
1062 lhrdb sbtest8
90mysql3306-
bin
.000005
2021
-02
-26_12:
46:
14
2021
-02
-26_12:
46:
44
35744415
53263322
1001
2093
1001 lhrdb sbtest10
91mysql3306-
bin
.000005
2021
-02
-26_12:
46:
15
2021
-02
-26_12:
46:
45
35745364
53264439
1052
2054
1052 lhrdb sbtest3
92mysql3306-
bin
.000005
2021
-02
-26_12:
46:
14
2021
-02
-26_12:
46:
44
35746099
53254338
1037
2072
1037 lhrdb sbtest1
93mysql3306-
bin
.000005
2021
-02
-26_12:
46:
14
2021
-02
-26_12:
46:
44
35747782
53253220
1010
2057
1010 lhrdb sbtest9
94mysql3306-
bin
.000005
2021
-02
-26_12:
46:
45
2021
-02
-26_12:
46:
45
53270228
53284649
1
3
1 lhrdb sbtest4
95mysql3306-
bin
.000005
2021
-02
-26_12:
46:
45
2021
-02
-26_12:
46:
45
53286333
53291384
1
1
1 lhrdb sbtest1
96mysql3306-
bin
.000005
2021
-02
-26_12:
46:
45
2021
-02
-26_12:
46:
45
53271176
53302052
4
3
4 lhrdb sbtest8
97mysql3306-
bin
.000005
2021
-02
-26_12:
46:
45
2021
-02
-26_12:
46:
45
53272860
53298684
2
1
2 lhrdb sbtest9
98mysql3306-
bin
.000005
2021
-02
-26_12:
46:
45
2021
-02
-26_12:
46:
45
53277911
53299327
2
5
2 lhrdb sbtest7
99mysql3306-
bin
.000005
2021
-02
-26_12:
46:
45
2021
-02
-26_12:
46:
45
53264439
53295317
4
5
4 lhrdb sbtest5
100mysql3306-
bin
.000005
2021
-02
-26_12:
46:
44
2021
-02
-26_12:
46:
45
53265174
53301011
2
4
2 lhrdb sbtest6
101mysql3306-
bin
.000005
2021
-02
-26_12:
46:
44
2021
-02
-26_12:
46:
45
53266122
53295960
3
5
3 lhrdb sbtest2
102mysql3306-
bin
.000005
2021
-02
-26_12:
46:
44
2021
-02
-26_12:
46:
45
53266857
53301486
4
13
4 lhrdb sbtest10
103mysql3306-
bin
.000005
2021
-02
-26_12:
46:
45
2021
-02
-26_12:
46:
45
53270702
53298118
0
4
0 lhrdb sbtest3
104[root@lhrmy2sql my2sql]
#