专栏首页mysql-dba使用my2sql来恢复mysql数据
原创

使用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]# 

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • my2sql 闪回操作流程

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

    你要的小米吖
  • 技术分享 | binlog 实用解析工具 my2sql

    爱可生 MySQL DBA 团队成员,Oracle 10g OCM,MySQL 5.7 OCP,擅长数据库性能问题诊断、事务与锁问题的分析等,负责处理客户 My...

    爱可生开源社区
  • 【DB宝43】MySQL误操作闪回恢复利器之my2sql

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

    小麦苗DBA宝典
  • 【DB宝43】MySQL误操作闪回恢复利器之my2sql

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

    小麦苗DBA宝典
  • my2sql

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

    shynodes
  • MySQL 数据恢复

    前两天因为没注意的误操作, 直接把某个数据表清掉了, 心慌慌. 怪自己学艺不精, 当时整了一下午也没把数据找回来. 当晚回来闭关研究, 终于在凌晨1点多整出来了...

    烟草的香味
  • 使用binlog2sql针对mysql进行数据恢复

    DBA或开发人员,有时会误删或者误更新数据,如果是线上环境并且影响较大,就需要能快速回滚。传统恢复方法是利用备份重搭实例,再应用去除错误sql后的binlog来...

    大道七哥
  • mysql数据恢复 转

    binlog 基本认识     MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,...

    wuweixiang
  • 恢复mysql数据库

    show binlog events in 'mysql-bin.000002' 查看指定文件

    用户1437675
  • 使用binlog2sql恢复数据

    在MySQL里面,如果我们有延迟从库的话,也可以找回之前的数据,但是有时候不太好使(因为追数据到误操作前的准确的时间点有时候也不太好把握)。

    二狗不要跑
  • MySQL中使用undrop来恢复drop的表(上)

    MySQL中可以使用编程语言(比如Python)来解析binlog中DML的逆操作来达到闪回的效果,如果数据不多,手工解析也可以。这也是现在大家碰到的很多DML...

    jeanron100
  • mysql 恢复单表数据

    版权声明:本文为木偶人shaon原创文章,转载请注明原文地址,非常感谢。 https://b...

    shaonbean
  • mysql数据备份与恢复

    MySQL数据备份与恢复 #1. 物理备份: 直接复制数据库文件,适用于大型数据库环境。但不能恢复到异构系统中如Windows。 #2. 逻辑备份: 备份的是建...

    用户1214487
  • MySQL数据备份与恢复

    常用source命令,用use进入到某个数据库,mysql>source D:\sys.sql,后面的参数为脚本文件

    Linux
  • mysql binlog恢复数据实战

    数据库恢复的先决条件是,定时备份数据库,缩小binlog恢复范围.首先我们备份测试数据库数据:

    仙士可
  • 使用多数据中心部署来应对Kafka灾难恢复(一)使用多数据中心部署来应对灾难恢复

    数据中心宕机和数据丢失能导致企业损失很多收入或者完全停摆。为了将由于事故导致的宕机和数据丢失带来的损失最小化,企业需要制定业务可持续性计划和灾难恢复策略。

    扫帚的影子
  • CentOS下利用mysqlbinlog恢复MySQL数据库

    老七Linux
  • MySQL数据库备份和恢复

    linxinzhe
  • myloader恢复mysql数据库示例

        mydumper是针对mysql数据库备份的一个轻量级第三方的开源工具,备份方式为逻辑备份。它支持多线程,备份速度远高于原生态的mysqldump以及众...

    Leshami

扫码关注云+社区

领取腾讯云代金券