腾讯云

文章/答案/技术大牛

发布

社区首页 >专栏 >my2sql 闪回操作流程

my2sql 闪回操作流程

原创

你要的小米吖

修改于 2021-08-19 06:48:24

93000

代码可运行

文章被收录于专栏：数据库解决方案数据库解决方案

运行总次数：0

代码可运行

简介：

go版MySQL binlog解析工具，通过解析MySQL binlog ，可以生成原始SQL、回滚SQL、去除主键的INSERT SQL等，也可以生成DML统计信息。

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

my2sql有三个主要功能，今天只介绍闪回功能:

#执行闪回操作具体操作流程
#解析binlog生成标准SQL
#解析binlog 统计DML、长事务与大事务分析

1.直接从从GitHub下载源码包

2.传到本地服务器解压

[root@localhost opt]# unzip my2sql-master.zip

3.解压后直接cd进入编译好的路径下：（此处不用编译，作者已经把编译好的包放在releases/centOS_release_7.x

下）

[root@localhost centOS_release_7.x]# pwd
/opt/my2sql-master/releases/centOS_release_7.x

也可以在GitHub上直接下载编译好的，GitHub上有下载链接

参数说明

-U

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

-mode

repl: 伪装成从库解析binlog文件，file: 离线解析binlog文件, 默认repl

-local-binlog-file

当指定-mode=file 参数时，需要指定-local-binlog-file binlog文件相对路径或绝对路径,可以连续解析多个binlog文件，只需要指定起始文件名，程序会自动持续解析下个文件

-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条sql的事务，默认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、事务信息

4.模拟表数据被删除，进行恢复

mysql> show create table sbtest1;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                  |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `c_inx` (`c`),
  KEY `cc` (`pad`)
) ENGINE=InnoDB AUTO_INCREMENT=500001 DEFAULT CHARSET=latin1 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查询一下数据量

mysql> select count(*) from thn.sbtest1;
+----------+
| count(*) |
+----------+
|   500000 |
+----------+
1 row in set (0.05 sec)

#为了模拟方便，重新生成一个binlog
mysql> flush logs;

删除表数据

mysql> delete from sbtest1 where id<400000;
Query OK, 399999 rows affected (7.41 sec)

解析一下binlog，因为是测试，并没有其他热写入数据，我按时间区间去闪回

[root@localhost data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-binlog.000017 |more

#210818 19:37:08 server id 5  end_log_pos 386 CRC32 0xcf72d3af  Table_map: `thn`.`sbtest1` mapped to number 108
# at 386
#210818 19:37:08 server id 5  end_log_pos 8548 CRC32 0x031211c6     Delete_rows: table id 108

生成回滚SQL

[root@localhost centOS_release_7.x]# pwd
/opt/my2sql-master/releases/centOS_release_7.x
[root@localhost centOS_release_7.x]# ./my2sql  -user root -password mysql  -port 3306 -host 127.0.0.1 -databases thn  -tables sbtest1 -work-type rollback   -start-file mysql-binlog.000017 -start-datetime "2021-08-19 00:00:00" --stop-datetime "2021-09-23 12:00:00" --add-extraInfo  -output-dir /tmp
[2021/08/18 19:41:29] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 127.0.0.1 3306 root   utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2021/08/18 19:41:29] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql-binlog.000017, 4)
[2021/08/18 19:41:29] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/08/18 19:41:29] [info] events.go:210 start thread to write redo/rollback sql into file
[2021/08/18 19:41:29] [info] events.go:60 start thread 1 to generate redo/rollback sql
[2021/08/18 19:41:29] [info] events.go:60 start thread 2 to generate redo/rollback sql
[2021/08/18 19:41:29] [info] repl.go:16 start to get binlog from mysql
[2021/08/18 19:41:29] [info] binlogsyncer.go:777 rotate to (mysql-binlog.000017, 4)
[2021/08/18 19:41:35] [info] repl.go:84 deadline exceeded.
[2021/08/18 19:41:35] [info] repl.go:18 finish getting binlog from mysql
[2021/08/18 19:41:35] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2021/08/18 19:41:35] [info] events.go:185 exit thread 1 to generate redo/rollback sql
[2021/08/18 19:41:35] [info] events.go:185 exit thread 2 to generate redo/rollback sql
[2021/08/18 19:41:35] [info] events.go:259 finish writing rollback sql into tmp files, start to revert content order of tmp files
[2021/08/18 19:41:35] [info] events.go:272 finish reverting content order of tmp files
[2021/08/18 19:41:35] [info] events.go:277 exit thread to write redo/rollback sql into file
[root@localhost centOS_release_7.x]# ./my2sql  -user root -password mysql  -port 3306 -host 127.0.0.1 -databases thn  -tables sbtest1 -work-type rollback   -start-file mysql-binlog.000017 -start-datetime "2021-08-17 00:00:00" --stop-datetime "2021-08-20 12:00:00" --add-extraInfo  -output-dir /tmp
[2021/08/18 19:48:13] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 127.0.0.1 3306 root   utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2021/08/18 19:48:13] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql-binlog.000017, 4)
[2021/08/18 19:48:13] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/08/18 19:48:13] [info] events.go:210 start thread to write redo/rollback sql into file
[2021/08/18 19:48:13] [info] events.go:60 start thread 1 to generate redo/rollback sql
[2021/08/18 19:48:13] [info] events.go:60 start thread 2 to generate redo/rollback sql
[2021/08/18 19:48:13] [info] repl.go:16 start to get binlog from mysql
[2021/08/18 19:48:13] [info] binlogsyncer.go:777 rotate to (mysql-binlog.000017, 4)
[2021/08/18 19:48:14] [info] events.go:244 finish processing mysql-binlog.000017 10488556
[2021/08/18 19:48:15] [info] events.go:244 finish processing mysql-binlog.000017 20976726
[2021/08/18 19:48:15] [info] events.go:244 finish processing mysql-binlog.000017 31464896
[2021/08/18 19:48:15] [info] events.go:244 finish processing mysql-binlog.000017 41953066
[2021/08/18 19:48:16] [info] events.go:244 finish processing mysql-binlog.000017 52441236
[2021/08/18 19:48:16] [info] events.go:244 finish processing mysql-binlog.000017 62929406
[2021/08/18 19:48:16] [info] events.go:244 finish processing mysql-binlog.000017 73417576
[2021/08/18 19:48:21] [info] repl.go:84 deadline exceeded.
[2021/08/18 19:48:21] [info] repl.go:18 finish getting binlog from mysql
[2021/08/18 19:48:21] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2021/08/18 19:48:21] [info] events.go:185 exit thread 2 to generate redo/rollback sql
[2021/08/18 19:48:21] [info] events.go:185 exit thread 1 to generate redo/rollback sql
[2021/08/18 19:48:21] [info] events.go:259 finish writing rollback sql into tmp files, start to revert content order of tmp files
[2021/08/18 19:48:21] [info] rollback_process.go:15 start thread 1 to revert rollback sql files
[2021/08/18 19:48:21] [info] rollback_process.go:41 start to revert tmp file /tmp/.rollback.17.sql into /tmp/rollback.17.sql
[2021/08/18 19:48:22] [info] rollback_process.go:156 finish reverting tmp file /tmp/.rollback.17.sql into /tmp/rollback.17.sql
[2021/08/18 19:48:22] [info] rollback_process.go:25 exit thread 1 to revert rollback sql files
[2021/08/18 19:48:22] [info] events.go:272 finish reverting content order of tmp files
[2021/08/18 19:48:22] [info] events.go:277 exit thread to write redo/rollback sql into file
#看到sql into file这就已经成功搞定了

查看回滚的SQL

[root@localhost data]# cd /tmp/
[root@localhost tmp]# ll
-rw-r--r--. 1 root root       261 8月  18 19:48 biglong_trx.txt
-rw-r--r--. 1 root root       290 8月  18 19:48 binlog_status.txt
-rw-r--r--. 1 root root 103668233 8月  18 19:48 rollback.17.sql

查看DML信息

[root@localhost tmp]# [root@localhost tmp]# cat binlog_status.txt 
binlog            starttime           stoptime            startpos   stoppos    inserts  updates  deletes  database        table               
mysql-binlog.000017 2021-08-18_19:37:08 2021-08-18_19:37:08 330        75925802   0        0        399999   thn             sbtest1   

## 查看生成的binlog_status.txt文件，会统计每个时间段（POS）区间内相关库表所产生的DML次数
## biglong_trx.txt文件记录的是大/长事务，此文件为空，说明没有大/长事务
## 由文件中记录的DML总执行次数可知，确实是删除了399999 条记录

查看回滚SQL形式

[root@localhost tmp]# more rollback.17.sql 
INSERT INTO `thn`.`sbtest1` (`id`,`k`,`c`,`pad`) VALUES (399999,48268,'95132265201-45028665901-44037695346-99430732771-
94049966285-01025661979-46614439842-38764489961-91197548642-34427705307','54658966786-08986899978-98420410571-388688724
36-65095829410');
INSERT INTO `thn`.`sbtest1` (`id`,`k`,`c`,`pad`) VALUES (399998,295137,'13049311991-28212069454-50756206952-46824748230
-09868329157-60277837353-92033860701-98090420440-28261455360-36687743868','97210253317-61299060306-09164181955-97529490
798-90188641840');

恢复数据：

[root@localhost tmp]# mysql -u root -pmysql -P3306 -h127.0.0.1  thn < /tmp/rollback.17.sql

恢复成功后查询一下数据总量，数据已经完全恢复回来了

mysql> select count(*) from thn.sbtest1;
+----------+
| count(*) |
+----------+
|   500000 |
+----------+
1 row in set (0.05 sec)

根据POS点解析binlog，生成回滚日志

也可以根据binlog的pos点解析，这里不再展示

命令：

./my2sql  -user root -password mysql  -port 3306 -host 127.0.0.1 -databases thn  -tables student -work-type rollback   -start-file mysql-binlog.000017 -start-pos  100 -stop-file  mysql-binlog.000017 -stop-pos  1000 -output-dir /tmp

限制

使用回滚/闪回功能时，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权限

案例

解析出回滚SQL

根据时间点解析出回滚SQL

#伪装成从库解析binlog
./my2sql  -user root -password mysql -host 127.0.0.1   -port 3306 -mode repl -work-type rollback  -start-file mysql-binlog.000017  -start-datetime "2021-08-19 00:00:00" -stop-datetime "2021-08-20 12:00:00" -output-dir /tmp
#直接读取binlog文件解析
./my2sql  -user root -password mysql -host 127.0.0.1   -port 3306  -mode file -local-binlog-file /data/mysql/data/mysql-binlog.000017 -work-type rollback  -start-file mysql-binlog.000017  -start-datetime "2021-08-19 00:00:00" -stop-datetime "2021-08-20 12:00:00" -output-dir /tmp

根据pos点解析出回滚SQL

#伪装成从库解析binlog
./my2sql  -user root -password mysql -host 127.0.0.1   -port 3306 -mode repl -work-type rollback  -start-file mysql-binlog.000017  -start-pos 4 -stop-file mysql-binlog.000017 -stop-pos 583918266  -output-dir /tmp
#直接读取binlog文件解析
./my2sql  -user root -password mysql -host 127.0.0.1   -port 3306   -mode file -local-binlog-file ./mysql-binlog.000017  -work-type rollback  -start-file mysql-binlog.000017  -start-pos 4 -stop-file mysql-binlog.000017 -stop-pos 583918266  -output-dir /tmp

统计DML以及大事务

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

#伪装成从库解析binlog
./my2sql  -user root -password mysql -host 127.0.0.1   -port 3306  -mode repl -work-type stats  -start-file mysql-binlog.000017  -start-datetime "2021-08-19 00:00:00" -stop-datetime "2021-08-20 12:00:00"  -big-trx-row-limit 500 -long-trx-seconds 300   -output-dir /tmp
#直接读取binlog文件解析
./my2sql  -user root -password mysql -host 127.0.0.1   -port 3306 -mode file -local-binlog-file ./mysql-binlog.000017   -work-type stats  -start-file mysql-binlog.000017  -start-datetime "2021-08-19 00:00:00" -stop-datetime "2021-08-20 12:00:00"  -big-trx-row-limit 500 -long-trx-seconds 300   -output-dir /tmp

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

#伪装成从库解析binlog
./my2sql  -user root -password mysql -host 127.0.0.1   -port 3306  -mode repl -work-type stats  -start-file mysql-binlog.000017  -start-pos 4 -stop-file mysql-binlog.000017 -stop-pos 583918266  -big-trx-row-limit 500 -long-trx-seconds 300   -output-dir /tmp
#直接读取binlog文件解析
./my2sql  -user root -password mysql -host 127.0.0.1   -port 3306 -mode file -local-binlog-file ./mysql-binlog.000017  -work-type stats  -start-file mysql-binlog.000017  -start-pos 4 -stop-file mysql-binlog.000017 -stop-pos 583918266  -big-trx-row-limit 500 -long-trx-seconds 300   -output-dir /tmp

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

#伪装成从库解析binlog
./my2sql  -user root -password mysql -host 127.0.0.1   -port 3306 -mode repl  -work-type 2sql  -start-file mysql-binlog.000017  -start-pos 4   -output-toScreen