【MySQL运维】使用my2sql闪回工具实现MySQL数据回滚

tanglu MySQL 2022-09-27 144 0

一、my2sql介绍

my2sql项目地址为https://github.com/liuhr/my2sql。my2sql是一款基于GO语言、参考my2fback工具所二次开发的MySQL回滚工具。其原理和binlog2sql一样都是通过解析binlog生成原始SQL与回滚SQL以数据数据的回滚或者主从切换后的数据补偿等功能,其核心特点如下:

· 解析效率上my2sql更为快速

· 支持对DML进行统计,当服务器出现IO高、TPS高的情况时,更方便DBA统计出更新频繁的库表
· 方便查找某个时间点数据库是否有大事务或者长事务


二、my2sql常用选项

· -mode:默认为repl,代表伪装成从库获取主库的binlog文件,如果为file则是从本地文件系统获取 binlog 文件
· -local-binlog-file:当-mode=file时所指定的binlog文件路径
· -sql:要解析的SQL类型,可选参数有insert 、update、delete,默认全部解析
· -file-per-table:为每个表生成一个 sql 文件
· -output-dir: 将生成的结果存放到指定目录
· -threads:线程数,默认2个,支持并发
· -work-type:如果为2sql则是生成原始sql,如果为rollback则生成回滚SQL,如果为stats则只统计DML信息

· -databases 、 -tables:指定库表


三、my2sql使用教程

1、注意事项

· my2sql需要伪装成从库获取主库 binlog进行解析,因此执行操作的数据库用户需要具有 SELECT,REPLICATION SALVE,REPLICATION CLIENT 的权限

· 使用回滚/闪回功能时,binlog 格式必须为 row ,且 binlog_row_image=full ,DML 统计以及大事务分析不受影响

· 使用rollback功能时,要解析的binlog段表结构要保持一致(例如:解析mysql-bin.000001文件,此binlog文件的的表有add column或drop column操作,则执行rollback可能会执行异常)
· MySQL8.0版本需要在配置文件中加入default_authentication_plugin =mysql_native_password,用户密码认证必须是mysql_native_password才能解析


2、下载my2sql

wget https://raw.githubusercontent.com/liuhr/my2sql/master/releases/centOS_release_7.x/my2sql


3、my2sql使用说明

· 根据时间点解析并生成回滚SQL

#伪装成从库方式
./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
./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 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 -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

#伪装成从库方式
./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
./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 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 -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操作数量

#统计一个事务大于500条、时间大于300秒的事务

#直接读取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 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


4、my2sql实际案例

· 故意删除employees库里的数据,产生binlog

DELETE FROM `departments`


· 解析binlog,因为我们明确删除操作所在的库,所以这里只解析了employees库的binlog

/usr/local/src/my2sql -user dba -password 123456 -host 172.20.1.190 -port 3306 -databases employees -mode file -work-type 2sql -local-binlog-file /data/mysql3306/log/binlog/mysql-bin.000210 -start-file /data/mysql3306/log/binlog/mysql-bin.000210  -output-dir /tmp/2sql

cat forward.210.sql                                                                                                            
DELETE FROM `employees`.`departments` WHERE `dept_no`='d001';                                                                                                             
DELETE FROM `employees`.`departments` WHERE `dept_no`='d002';                                                                                                             
DELETE FROM `employees`.`departments` WHERE `dept_no`='d003';                                                                                                             
DELETE FROM `employees`.`departments` WHERE `dept_no`='d004';                                                                                                             
DELETE FROM `employees`.`departments` WHERE `dept_no`='d005';                                                                                                             
DELETE FROM `employees`.`departments` WHERE `dept_no`='d006';                                                                                                             
DELETE FROM `employees`.`departments` WHERE `dept_no`='d007';                                                                                                             
DELETE FROM `employees`.`departments` WHERE `dept_no`='d008';                                                                                                             
DELETE FROM `employees`.`departments` WHERE `dept_no`='d009';


· 生成回滚SQL

/usr/local/src/my2sql -user dba -password 123456 -host 172.20.1.190 -port 3306 -databases employees -mode file -work-type rollback -local-binlog-file /data/mysql3306/log/binlog/mysql-bin.000210 -start-file /data/mysql3306/log/binlog/mysql-bin.000210  -output-dir /tmp/2sql

cat rollback.210.sql                                                                                                           
INSERT INTO `employees`.`departments` (`dept_no`,`dept_name`) VALUES ('d009','Customer Service');                                                                         
INSERT INTO `employees`.`departments` (`dept_no`,`dept_name`) VALUES ('d008','Research');                                                                                 
INSERT INTO `employees`.`departments` (`dept_no`,`dept_name`) VALUES ('d007','Sales');                                                                                    
INSERT INTO `employees`.`departments` (`dept_no`,`dept_name`) VALUES ('d006','Quality Management');                                                                       
INSERT INTO `employees`.`departments` (`dept_no`,`dept_name`) VALUES ('d005','Development');                                                                              
INSERT INTO `employees`.`departments` (`dept_no`,`dept_name`) VALUES ('d004','Production');                                                                               
INSERT INTO `employees`.`departments` (`dept_no`,`dept_name`) VALUES ('d003','Human Resources');                                                                          
INSERT INTO `employees`.`departments` (`dept_no`,`dept_name`) VALUES ('d002','Finance');                                                                                  
INSERT INTO `employees`.`departments` (`dept_no`,`dept_name`) VALUES ('d001','Marketing');



· my2sql运行结果解析

命令结束后会根据指定的参数在output-dir目录生成不同的文件

Snipaste_2022-09-27_15-05-17.png

版权声明
本站所有文章均为原创,转载请注明出处!小站维护不易,如果对您有所帮助,希望能点击一下站内广告,谢谢!

评论