使用my2sql来恢复mysql数据
原创
my2sql
使用说明来源原作者：my2sql的GitHub地址：https://github.com/liuhr/my2sql
go版MySQL binlog解析工具，通过解析MySQL binlog ，可以生成原始SQL、回滚SQL、去除主键的INSERT SQL等，也可以生成DML统计信息。类似工具有binlog2sql、MyFlash、my2fback等，本工具基于my2fback、binlog_rollback工具二次开发而来。
用途
- 数据快速回滚(闪回)
- 主从切换后新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秒 | 不支持 |
重要参数说明
-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、事务信息使用案例
解析出标准SQL
根据时间点解析出标准SQL
#伪装成从库解析binlog
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -mode repl -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
#直接读取binlog文件解析
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -mode file -local-binlog-file ./mysql-bin.011259 -work-type 2sql -start-file mysql-bin.011259 -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" -output-dir ./tmpdir根据pos点解析出标准SQL
#伪装成从库解析binlog
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -mode repl -work-type 2sql -start-file mysql-bin.011259 -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266 -output-dir ./tmpdir
#直接读取binlog文件解析
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -mode file -local-binlog-file ./mysql-bin.011259 -work-type 2sql -start-file mysql-bin.011259 -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266 -output-dir ./tmpdir解析出回滚SQL
根据时间点解析出回滚SQL
#伪装成从库解析binlog
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -mode repl -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
#直接读取binlog文件解析
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -mode file -local-binlog-file ./mysql-bin.011259 -work-type rollback -start-file mysql-bin.011259 -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" -output-dir ./tmpdir根据pos点解析出回滚SQL
#伪装成从库解析binlog
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -mode repl -work-type rollback -start-file mysql-bin.011259 -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266 -output-dir ./tmpdir
#直接读取binlog文件解析
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -mode file -local-binlog-file ./mysql-bin.011259 -work-type rollback -start-file mysql-bin.011259 -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266 -output-dir ./tmpdir统计DML以及大事务
统计时间范围各个表的DML操作数量，统计一个事务大于500条、时间大于300秒的事务
#伪装成从库解析binlog
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -mode repl -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
#直接读取binlog文件解析
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -mode file -local-binlog-file ./mysql-bin.011259 -work-type stats -start-file mysql-bin.011259 -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" -big-trx-row-limit 500 -long-trx-seconds 300 -output-dir ./tmpdir统计一段pos点范围各个表的DML操作数量，统计一个事务大于500条、时间大于300秒的事务
#伪装成从库解析binlog
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -mode repl -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
#直接读取binlog文件解析
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -mode file -local-binlog-file ./mysql-bin.011259 -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从某一个pos点解析出标准SQL，并且持续打印到屏幕
#伪装成从库解析binlog
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -mode repl -work-type 2sql -start-file mysql-bin.011259 -start-pos 4 -output-toScreen 安装
编译好的my2sql
使用验证：
环境mysql8.0.18
创建库表：
create database db_backup;
CREATE TABLE `db_archive_log` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '表id',
`server_source` varchar(640) DEFAULT NULL COMMENT '源服务器',
`db_source` varchar(64) DEFAULT NULL COMMENT '源数据库schema',
`table_source` varchar(128) DEFAULT NULL COMMENT '源数据库表',
`server_dest` varchar(640) DEFAULT NULL COMMENT '目标服务器',
`db_dest` varchar(64) DEFAULT NULL COMMENT '目标数据库schema',
`table_dest` varchar(128) DEFAULT NULL COMMENT '目标数据库表',
`archive_qty` int(10) DEFAULT NULL COMMENT '归档数量',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='数据库归档执行日志表';
插入数据：
INSERT INTO `db_backup`.`db_archive_log` (`id`, `server_source`, `db_source`, `table_source`, `server_dest`, `db_dest`, `table_dest`, `archive_qty`) VALUES ('130298', '172.16.37.219', 'guiyu_oss', 't_oss_plan_call_phone_36', '172.16.169.40', 'guiyu_oss', 't_oss_plan_call_phone_36', '642');
INSERT INTO `db_backup`.`db_archive_log` (`id`, `server_source`, `db_source`, `table_source`, `server_dest`, `db_dest`, `table_dest`, `archive_qty`) VALUES ('130299', '172.16.37.219', 'guiyu_oss', 't_oss_plan_call_phone_37', '172.16.169.40', 'guiyu_oss', 't_oss_plan_call_phone_37', '5831');
INSERT INTO `db_backup`.`db_archive_log` (`id`, `server_source`, `db_source`, `table_source`, `server_dest`, `db_dest`, `table_dest`, `archive_qty`) VALUES ('130300', '172.16.37.219', 'guiyu_oss', 't_oss_plan_call_phone_38', '172.16.169.40', 'guiyu_oss', 't_oss_plan_call_phone_38', '1964');
INSERT INTO `db_backup`.`db_archive_log` (`id`, `server_source`, `db_source`, `table_source`, `server_dest`, `db_dest`, `table_dest`, `archive_qty`) VALUES ('130301', '172.16.37.219', 'guiyu_oss', 't_oss_plan_call_phone_39', '172.16.169.40', 'guiyu_oss', 't_oss_plan_call_phone_39', '1146');
INSERT INTO `db_backup`.`db_archive_log` (`id`, `server_source`, `db_source`, `table_source`, `server_dest`, `db_dest`, `table_dest`, `archive_qty`) VALUES ('130302', '172.16.37.219', 'guiyu_oss', 't_oss_plan_call_phone_4', '172.16.169.40', 'guiyu_oss', 't_oss_plan_call_phone_4', '3371');
mysql> select * from db_archive_log;
+--------+---------------+-----------+--------------------------+---------------+-----------+--------------------------+-------------+
| id | server_source | db_source | table_source | server_dest | db_dest | table_dest | archive_qty |
+--------+---------------+-----------+--------------------------+---------------+-----------+--------------------------+-------------+
| 130298 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_36 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_36 | 642 |
| 130299 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_37 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_37 | 5831 |
| 130300 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_38 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_38 | 1964 |
| 130301 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_39 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_39 | 1146 |
| 130302 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_4 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_4 | 3371 |
+--------+---------------+-----------+--------------------------+---------------+-----------+--------------------------+-------------+
update数据：
mysql> update db_archive_log set db_source='guiyu_ossxxx' where id in (130298,130301);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql>
mysql> select * from db_archive_log;
+--------+---------------+--------------+--------------------------+---------------+-----------+--------------------------+-------------+
| id | server_source | db_source | table_source | server_dest | db_dest | table_dest | archive_qty |
+--------+---------------+--------------+--------------------------+---------------+-----------+--------------------------+-------------+
| 130298 | 172.16.37.219 | guiyu_ossxxx | t_oss_plan_call_phone_36 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_36 | 642 |
| 130299 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_37 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_37 | 5831 |
| 130300 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_38 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_38 | 1964 |
| 130301 | 172.16.37.219 | guiyu_ossxxx | t_oss_plan_call_phone_39 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_39 | 1146 |
| 130302 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_4 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_4 | 3371 |
+--------+---------------+--------------+--------------------------+---------------+-----------+--------------------------+-------------+
5 rows in set (0.00 sec)
删除数据：
mysql> delete from db_archive_log where id=130302;
Query OK, 1 row affected (0.00 sec)
mysql> select * from db_archive_log;
+--------+---------------+--------------+--------------------------+---------------+-----------+--------------------------+-------------+
| id | server_source | db_source | table_source | server_dest | db_dest | table_dest | archive_qty |
+--------+---------------+--------------+--------------------------+---------------+-----------+--------------------------+-------------+
| 130298 | 172.16.37.219 | guiyu_ossxxx | t_oss_plan_call_phone_36 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_36 | 642 |
| 130299 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_37 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_37 | 5831 |
| 130300 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_38 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_38 | 1964 |
| 130301 | 172.16.37.219 | guiyu_ossxxx | t_oss_plan_call_phone_39 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_39 | 1146 |
+--------+---------------+--------------+--------------------------+---------------+-----------+--------------------------+-------------+
4 rows in set (0.00 sec)
解析binlog里的原始sql语句：
[root@localhost home]# ./my2sql -user root -password Guijidba@2021 -port 3308 \
> -host 127.0.0.1 -databases db_backup -tables db_archive_log \
> -work-type 2sql -start-file mysql-bin.000002 \
> -start-datetime "2021-05-20 13:35:00" --stop-datetime "2021-05-20 14:35:00" \
> -output-dir /home/tmpsql
[2021/05/20 14:09:32] [info] events.go:210 start thread to write redo/rollback sql into file
[2021/05/20 14:09:32] [info] events.go:60 start thread 1 to generate redo/rollback sql
[2021/05/20 14:09:32] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 127.0.0.1 3308 root utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2021/05/20 14:09:32] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql-bin.000002, 4)
[2021/05/20 14:09:32] [info] events.go:60 start thread 2 to generate redo/rollback sql
[2021/05/20 14:09:32] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/05/20 14:09:32] [info] repl.go:16 start to get binlog from mysql
[2021/05/20 14:09:32] [info] binlogsyncer.go:777 rotate to (mysql-bin.000002, 4)
[2021/05/20 14:09:37] [info] repl.go:84 deadline exceeded.
[2021/05/20 14:09:37] [info] repl.go:18 finish getting binlog from mysql
[2021/05/20 14:09:37] [info] events.go:185 exit thread 1 to generate redo/rollback sql
[2021/05/20 14:09:37] [info] events.go:185 exit thread 2 to generate redo/rollback sql
[2021/05/20 14:09:37] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2021/05/20 14:09:37] [info] events.go:274 finish writing redo/forward sql into file
[2021/05/20 14:09:37] [info] events.go:277 exit thread to write redo/rollback sql into file
[root@localhost tmpsql]# ll
total 12
-rw-r--r--. 1 root root 107 May 20 14:09 biglong_trx.txt
-rw-r--r--. 1 root root 576 May 20 14:09 binlog_status.txt
-rw-r--r--. 1 root root 1599 May 20 14:09 forward.2.sql
[root@localhost tmpsql]# cat biglong_trx.txt
binlog starttime stoptime startpos stoppos rows duration tables
[root@localhost tmpsql]#
[root@localhost tmpsql]# cat binlog_status.txt -- binlog 信息
binlog starttime stoptime startpos stoppos inserts updates deletes database table
mysql-bin.000002 2021-05-20_14:01:38 2021-05-20_14:01:39 29863 33040 5 0 0 db_backup db_archive_log
mysql-bin.000002 2021-05-20_14:04:04 2021-05-20_14:04:04 33341 33914 0 2 0 db_backup db_archive_log
mysql-bin.000002 2021-05-20_14:05:01 2021-05-20_14:05:01 34170 34401 0 0 1 db_backup db_archive_log
[root@localhost tmpsql]# cat forward.2.sql --解析的原始sql语句
INSERT INTO `db_backup`.`db_archive_log` (`id`,`server_source`,`db_source`,`table_source`,`server_dest`,`db_dest`,`table_dest`,`archive_qty`) VALUES (130298,'172.16.37.219','guiyu_oss','t_oss_plan_call_phone_36','172.16.169.40','guiyu_oss','t_oss_plan_call_phone_36',642);
INSERT INTO `db_backup`.`db_archive_log` (`id`,`server_source`,`db_source`,`table_source`,`server_dest`,`db_dest`,`table_dest`,`archive_qty`) VALUES (130299,'172.16.37.219','guiyu_oss','t_oss_plan_call_phone_37','172.16.169.40','guiyu_oss','t_oss_plan_call_phone_37',5831);
INSERT INTO `db_backup`.`db_archive_log` (`id`,`server_source`,`db_source`,`table_source`,`server_dest`,`db_dest`,`table_dest`,`archive_qty`) VALUES (130300,'172.16.37.219','guiyu_oss','t_oss_plan_call_phone_38','172.16.169.40','guiyu_oss','t_oss_plan_call_phone_38',1964);
INSERT INTO `db_backup`.`db_archive_log` (`id`,`server_source`,`db_source`,`table_source`,`server_dest`,`db_dest`,`table_dest`,`archive_qty`) VALUES (130301,'172.16.37.219','guiyu_oss','t_oss_plan_call_phone_39','172.16.169.40','guiyu_oss','t_oss_plan_call_phone_39',1146);
INSERT INTO `db_backup`.`db_archive_log` (`id`,`server_source`,`db_source`,`table_source`,`server_dest`,`db_dest`,`table_dest`,`archive_qty`) VALUES (130302,'172.16.37.219','guiyu_oss','t_oss_plan_call_phone_4','172.16.169.40','guiyu_oss','t_oss_plan_call_phone_4',3371);
UPDATE `db_backup`.`db_archive_log` SET `db_source`='guiyu_ossxxx' WHERE `id`=130298;
UPDATE `db_backup`.`db_archive_log` SET `db_source`='guiyu_ossxxx' WHERE `id`=130301;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130302;
[root@localhost tmpsql]#
解析binlog生产回滚语句：
[root@localhost home]# ./my2sql -user root -password Guijidba@2021 -port 3308 -mode repl \
> -host 127.0.0.1 -databases db_backup -tables db_archive_log \
> -work-type rollback -start-file mysql-bin.000002 \
> -start-datetime "2021-05-20 13:35:00" --stop-datetime "2021-05-20 14:35:00" \
> -output-dir /home/rollbacktmp
[2021/05/20 14:14:53] [info] events.go:60 start thread 1 to generate redo/rollback sql
[2021/05/20 14:14:53] [info] events.go:210 start thread to write redo/rollback sql into file
[2021/05/20 14:14:53] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 127.0.0.1 3308 root utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2021/05/20 14:14:53] [info] events.go:60 start thread 2 to generate redo/rollback sql
[2021/05/20 14:14:53] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql-bin.000002, 4)
[2021/05/20 14:14:53] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/05/20 14:14:53] [info] repl.go:16 start to get binlog from mysql
[2021/05/20 14:14:53] [info] binlogsyncer.go:777 rotate to (mysql-bin.000002, 4)
[2021/05/20 14:14:58] [info] repl.go:84 deadline exceeded.
[2021/05/20 14:14:58] [info] repl.go:18 finish getting binlog from mysql
[2021/05/20 14:14:58] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2021/05/20 14:14:58] [info] events.go:185 exit thread 1 to generate redo/rollback sql
[2021/05/20 14:14:58] [info] events.go:185 exit thread 2 to generate redo/rollback sql
[2021/05/20 14:14:58] [info] events.go:259 finish writing rollback sql into tmp files, start to revert content order of tmp files
[2021/05/20 14:14:58] [info] rollback_process.go:15 start thread 1 to revert rollback sql files
[2021/05/20 14:14:58] [info] rollback_process.go:41 start to revert tmp file /home/rollbacktmp/.rollback.2.sql into /home/rollbacktmp/rollback.2.sql
[2021/05/20 14:14:58] [info] rollback_process.go:156 finish reverting tmp file /home/rollbacktmp/.rollback.2.sql into /home/rollbacktmp/rollback.2.sql
[2021/05/20 14:14:58] [info] rollback_process.go:25 exit thread 1 to revert rollback sql files
[2021/05/20 14:14:58] [info] events.go:272 finish reverting content order of tmp files
[2021/05/20 14:14:58] [info] events.go:277 exit thread to write redo/rollback sql into file
[root@localhost home]#
[root@localhost home]#
[root@localhost home]# cd rollbacktmp/
[root@localhost rollbacktmp]# ll
total 12
-rw-r--r--. 1 root root 107 May 20 14:14 biglong_trx.txt
-rw-r--r--. 1 root root 576 May 20 14:14 binlog_status.txt
-rw-r--r--. 1 root root 738 May 20 14:14 rollback.2.sql
[root@localhost rollbacktmp]# cat biglong_trx.txt
binlog starttime stoptime startpos stoppos rows duration tables
[root@localhost rollbacktmp]# cat binlog_status.txt
binlog starttime stoptime startpos stoppos inserts updates deletes database table
mysql-bin.000002 2021-05-20_14:01:38 2021-05-20_14:01:39 29863 33040 5 0 0 db_backup db_archive_log
mysql-bin.000002 2021-05-20_14:04:04 2021-05-20_14:04:04 33341 33914 0 2 0 db_backup db_archive_log
mysql-bin.000002 2021-05-20_14:05:01 2021-05-20_14:05:01 34170 34401 0 0 1 db_backup db_archive_log
[root@localhost rollbacktmp]# cat rollback.2.sql
INSERT INTO `db_backup`.`db_archive_log` (`id`,`server_source`,`db_source`,`table_source`,`server_dest`,`db_dest`,`table_dest`,`archive_qty`) VALUES (130302,'172.16.37.219','guiyu_oss','t_oss_plan_call_phone_4','172.16.169.40','guiyu_oss','t_oss_plan_call_phone_4',3371);
UPDATE `db_backup`.`db_archive_log` SET `db_source`='guiyu_oss' WHERE `id`=130301;
UPDATE `db_backup`.`db_archive_log` SET `db_source`='guiyu_oss' WHERE `id`=130298;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130302;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130301;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130300;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130299;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130298;
[root@localhost rollbacktmp]#统计DML以及事务信息：
[root@localhost home]# ./my2sql -user root -password Guijidba@2021 -port 3308 -mode repl \
> -host 127.0.0.1 -databases db_backup -tables db_archive_log \
> -work-type stats -start-file mysql-bin.000002 \
> -start-datetime "2021-05-20 13:35:00" --stop-datetime "2021-05-20 14:35:00" \
> -output-dir /home/tmpstats
[2021/05/20 14:19:37] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 127.0.0.1 3308 root utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2021/05/20 14:19:37] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql-bin.000002, 4)
[2021/05/20 14:19:37] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/05/20 14:19:37] [info] repl.go:16 start to get binlog from mysql
[2021/05/20 14:19:37] [info] binlogsyncer.go:777 rotate to (mysql-bin.000002, 4)
[2021/05/20 14:19:42] [info] repl.go:84 deadline exceeded.
[2021/05/20 14:19:42] [info] repl.go:18 finish getting binlog from mysql
[2021/05/20 14:19:42] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[root@localhost home]#
[root@localhost home]# cd tmpstats/
[root@localhost tmpstats]# ll
total 8
-rw-r--r--. 1 root root 107 May 20 14:19 biglong_trx.txt
-rw-r--r--. 1 root root 576 May 20 14:19 binlog_status.txt
[root@localhost tmpstats]# cat biglong_trx.txt
binlog starttime stoptime startpos stoppos rows duration tables
[root@localhost tmpstats]# cat binlog_status.txt
binlog starttime stoptime startpos stoppos inserts updates deletes database table
mysql-bin.000002 2021-05-20_14:01:38 2021-05-20_14:01:39 29863 33040 5 0 0 db_backup db_archive_log
mysql-bin.000002 2021-05-20_14:04:04 2021-05-20_14:04:04 33341 33914 0 2 0 db_backup db_archive_log
mysql-bin.000002 2021-05-20_14:05:01 2021-05-20_14:05:01 34170 34401 0 0 1 db_backup db_archive_log
[root@localhost tmpstats]#
上面的原始sql和回滚sql中已经包括了dml事务的统计信息注意当表被删除了之后是无法被解析的（如果表被删除，可以建立一个空表然后再解析binlog生成语句）
[root@localhost home]# ./my2sql -user root -password Guijidba@2021 -port 3308 -mode repl \
> -host 127.0.0.1 -databases db_backup -tables db_archive_log \
> -work-type rollback -start-file mysql-bin.000002 \
> -start-datetime "2021-05-20 13:35:00" --stop-datetime "2021-05-20 14:35:00" \
> -output-dir /home/rollbacktmp
[2021/05/20 14:22:53] [info] events.go:210 start thread to write redo/rollback sql into file
[2021/05/20 14:22:53] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 127.0.0.1 3308 root utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2021/05/20 14:22:53] [info] events.go:60 start thread 2 to generate redo/rollback sql
[2021/05/20 14:22:53] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql-bin.000002, 4)
[2021/05/20 14:22:53] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/05/20 14:22:53] [info] events.go:60 start thread 1 to generate redo/rollback sql
[2021/05/20 14:22:53] [info] repl.go:16 start to get binlog from mysql
[2021/05/20 14:22:53] [info] binlogsyncer.go:777 rotate to (mysql-bin.000002, 4)
[2021/05/20 14:22:53] [error] mysqlFuncs.go:256 Error 1146: Table 'db_backup.db_archive_log' doesn't exist fail to query mysql: SHOW COLUMNS FROM `db_backup`.`db_archive_log`
[2021/05/20 14:22:53] [error] mysqlFuncs.go:134 Error 1146: Table 'db_backup.db_archive_log' doesn't exist fail to query mysql: SHOW INDEX FROM `db_backup`.`db_archive_log`
[2021/05/20 14:22:53] [fatal] repl.go:138 no table struct found for db_backup.db_archive_log, it maybe dropped, skip it. RowsEvent position:(mysql-bin.000002, 30096)
[root@localhost home]#
离线直接读取binlog文件解析：（-mode file -local-binlog-file）
离线库上必须要创建需要恢复或者解析的库和表，不然解析会报错找不到表的错误
[root@localhost fileroolback]# cd ..
[root@localhost home]# ./my2sql -user root -password Guijidba@2021 -port 3308 -mode file -local-binlog-file ./mysql-bin.000002 \
> -host 127.0.0.1 -databases db_backup -tables db_archive_log \
> -work-type rollback -start-file mysql-bin.000002 \
> -start-datetime "2021-05-20 13:35:00" --stop-datetime "2021-05-20 14:35:00" \
> -output-dir /home/fileroolback
[2021/05/20 14:31:34] [info] events.go:210 start thread to write redo/rollback sql into file
[2021/05/20 14:31:34] [info] file.go:32 start to parse binlog from local files
[2021/05/20 14:31:34] [info] events.go:60 start thread 2 to generate redo/rollback sql
[2021/05/20 14:31:34] [info] file.go:35 start to parse mysql-bin.000002 4
[2021/05/20 14:31:34] [info] file.go:44 start to parse mysql-bin.000002 4
[2021/05/20 14:31:34] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/05/20 14:31:34] [info] events.go:60 start thread 1 to generate redo/rollback sql
[2021/05/20 14:31:34] [info] file.go:60 mysql-bin.000003 not exists nor a file
[2021/05/20 14:31:34] [info] file.go:71 finish parsing binlog from local files
[2021/05/20 14:31:34] [info] events.go:185 exit thread 2 to generate redo/rollback sql
[2021/05/20 14:31:34] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2021/05/20 14:31:34] [info] events.go:185 exit thread 1 to generate redo/rollback sql
[2021/05/20 14:31:34] [info] events.go:259 finish writing rollback sql into tmp files, start to revert content order of tmp files
[2021/05/20 14:31:34] [info] rollback_process.go:15 start thread 1 to revert rollback sql files
[2021/05/20 14:31:34] [info] rollback_process.go:41 start to revert tmp file /home/fileroolback/.rollback.2.sql into /home/fileroolback/rollback.2.sql
[2021/05/20 14:31:34] [info] rollback_process.go:156 finish reverting tmp file /home/fileroolback/.rollback.2.sql into /home/fileroolback/rollback.2.sql
[2021/05/20 14:31:34] [info] rollback_process.go:25 exit thread 1 to revert rollback sql files
[2021/05/20 14:31:34] [info] events.go:272 finish reverting content order of tmp files
[2021/05/20 14:31:34] [info] events.go:277 exit thread to write redo/rollback sql into file
[root@localhost home]#
[root@localhost home]# cd fileroolback/
[root@localhost fileroolback]# ls
biglong_trx.txt binlog_status.txt rollback.2.sql
[root@localhost fileroolback]# cat biglong_trx.txt
binlog starttime stoptime startpos stoppos rows duration tables
[root@localhost fileroolback]# cat binlog_status.txt
binlog starttime stoptime startpos stoppos inserts updates deletes database table
mysql-bin.000002 2021-05-20_14:01:38 2021-05-20_14:01:39 29863 33040 5 0 0 db_backup db_archive_log
mysql-bin.000002 2021-05-20_14:04:04 2021-05-20_14:04:04 33341 33914 0 2 0 db_backup db_archive_log
mysql-bin.000002 2021-05-20_14:05:01 2021-05-20_14:05:01 34170 34401 0 0 1 db_backup db_archive_log
[root@localhost fileroolback]#
[root@localhost fileroolback]# cat rollback.2.sql
INSERT INTO `db_backup`.`db_archive_log` (`id`,`server_source`,`db_source`,`table_source`,`server_dest`,`db_dest`,`table_dest`,`archive_qty`) VALUES (130302,'172.16.37.219','guiyu_oss','t_oss_plan_call_phone_4','172.16.169.40','guiyu_oss','t_oss_plan_call_phone_4',3371);
UPDATE `db_backup`.`db_archive_log` SET `db_source`='guiyu_oss' WHERE `id`=130301;
UPDATE `db_backup`.`db_archive_log` SET `db_source`='guiyu_oss' WHERE `id`=130298;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130302;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130301;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130300;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130299;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130298;
[root@localhost fileroolback]# 原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
腾讯云开发者
