一、my2sql简介
二、my2sql用途
三、产品性能对比
四、安装
五、重要参数说明
六、使用案例
6.1 解析出标准SQL
6.2 解析出回滚SQL
6.3 统计DML以及大事务
6.4 从某一个pos点解析出标准SQL,并且持续打印到屏幕
七、限制
八、案例演示
8.1 准备MySQL环境
8.2 执行DML操作
8.3 解析binlog生成标准SQL
8.4 执行闪回操作
8.5 解析binlog 统计DML、长事务与大事务分析
可以用于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
优点:
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
1datetime=2020-07-16_10:44:09 database=orchestrator table=cluster_domain_name binlog=mysql-bin.011519 startpos=15552 stoppos=15773
2
3UPDATE `orchestrator`.`cluster_domain_name` SET `last_registered`='2020-07-16 10:44:09' WHERE `cluster_name`='192.168.1.1:3306'
./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
./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
./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
./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
./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
./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
./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
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;
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)
可以根据时间点解析出标准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插入语句已经被解析出来了。
根据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
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]#
本文结束。