【MySQL运维】使用my2sql闪回工具实现MySQL数据回滚
一、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目录生成不同的文件
评论