Linux环境通过my2sql误删MySQL数据binlog恢复

my2sql介绍

github地址
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生成

限制

• 使用回滚/闪回功能时，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权限
• MySQL8.0版本需要在配置文件中加入default_authentication_plugin =mysql_native_password，用户密码认证必须是mysql_native_password才能解析

产品性能对比

	my2sql	binlog2sql
1.1G binlog生成回滚SQL	1分40秒	65分钟
1.1G binlog生成原始SQL	1分30秒	50分钟
1.1G binlog生成表DML统计信息、以及事务统计信息	40秒	不支持

安装

yum install -y golang
但是安装过程中可能会出现 没有可用软件包 golang 的情况，这是因为缺少 EPEL源 ，所以软件仓库中找不到安装的软件包，只要先安装一下erel源即可。
yum install epel-release
go version
go env | grep GOPATH

mkdir -p /root/go/src
cd /root/go/src
git clone https://github.com/liuhr/my2sql.git
如果克隆报错改成 git clone git://github.com/liuhr/my2sql.git
cd my2sql/
go build .

参数

参数	说明
-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	找出满足n条sql的事务，默认500条
-databases 、 -tables	库及表条件过滤, 以逗号分隔
-sql	要解析的sql类型，可选参数insert、update、delete，默认全部解析
-doNotAddPrifixDb	默认生成insert into db1.tb1 (x1, x1) values (y1, y1)类sql，也可以生成不带库名的sql
-file-per-table	为每个表生成一个sql文件
-full-columns	生成的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 -user root -password 123456 -port 3306
-host 127.0.0.1 -databases testdb -tables student
-work-type rollback -start-file mysql-bin.000045
-start-datetime “2020-07-18 11:40:00” --stop-datetime “2020-07-18 12:00:00”
-output-dir tmpdir/

解析binlog生成标准SQL

./my2sql -user root -password 123456 -port 3306
-host 127.0.0.1 -databases testdb -tables student
-work-type 2sql -start-file mysql-bin.000047
-start-datetime “2020-07-18 12:35:00” --stop-datetime “2020-07-18 12:43:00”
-output-dir tmpdir/

解析binlog 统计DML、长事务与大事务分析

./my2sql -user root -password 123456 -port 3306
-databases testdb -tables student
-big-trx-row-limit 500 -long-trx-seconds 300
-work-type stats -start-file mysql-bin.000045
-start-datetime “2020-07-18 11:40:00” --stop-datetime “2020-07-18 12:00:00”
-output-dir tmpdir/

实际操作

准备测试数据

-- --------------------------------------------------------
-- 主机:                           192.168.10.101
-- 服务器版本:                        8.0.11 - MySQL Community Server - GPL
-- 服务器操作系统:                      linux-glibc2.12
-- HeidiSQL 版本:                  11.2.0.6213
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- 导出 test 的数据库结构
CREATE DATABASE IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test`;

-- 导出  表 test.user 结构
CREATE TABLE IF NOT EXISTS `user` (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  `manager_id` bigint(20) DEFAULT NULL COMMENT '直属上级id',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `version` int(11) DEFAULT '1' COMMENT '版本',
  `deleted` int(11) DEFAULT '0' COMMENT '逻辑删除标识(0.未删除,1.已删除)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 正在导出表  test.user 的数据：~0 rows (大约)
DELETE FROM `user`;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` (`id`, `name`, `age`, `email`, `manager_id`, `create_time`, `update_time`, `version`, `deleted`) VALUES
	(1087982257332887553, '大boss', 40, 'boss@baomidou.com', 1088248166370832385, '2019-01-11 14:20:20', NULL, 1, 0),
	(1088248166370832385, '王天风', 26, 'wtf@baomidou.com', 1087982257332887553, '2019-02-05 11:12:22', '2020-05-17 23:12:45', 1, 0),
	(1088250446457389058, '李艺伟', 28, 'lyw@baomidou.com', 1088248166370832385, '2019-02-14 08:31:16', NULL, 1, 0),
	(1094590409767661570, '张雨琪', 31, 'zjq@baomidou.com', 1088248166370832385, '2019-01-14 09:15:15', NULL, 1, 0),
	(1094592041087729666, '刘红雨', 32, 'lhm@baomidou.com', 1088248166370832385, '2019-01-14 09:48:16', NULL, 1, 1),
	(1261938439893303297, '刘明', 30, 'lm@baoimidou.com', 1094590409767661570, '2021-09-01 09:06:50', NULL, 1, 0),
	(1261940605047615490, '刘xi', 30, '456@baomidou.com', 1094590409767661570, '2021-09-01 09:06:52', NULL, 6, 0);
/*!40000 ALTER TABLE `user` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;

检查是否开启binlog， log_bin ON为开启

show variables like ‘log_bin%’;

Variable_name	Value
log_bin	ON
log_bin_basename	/usr/local/mysql/data/binlog
log_bin_index	/usr/local/mysql/data/binlog.index
log_bin_trust_function_creators	OFF
log_bin_use_v1_row_events	OFF

方便演示刷新下日志,查看当前使用的binlog,在删除表中数据

flush logs;
show master status;
DELETE FROM `test`.`user`;

闪回

cd /root/src/my2sql/
mkdir tmpdir
./my2sql -user root -password 123456 -port 3306
-host 127.0.0.1 -databases test -tables user
-work-type rollback -start-file /usr/local/mysql/data/binlog.000003
-start-datetime “2022-01-27 13:58:59” --stop-datetime “2022-01-27 13:59:59”
-output-dir tmpdir/

进入 tmpdir,发现有个 rollback.3.sql文件,就是我们要找的数据,最后将找回的数据插入到数据库中.

最后在说下这个 start-datetime 和 stop-datetime ,上面这个例子相当于回滚到 2022-01-27 13:58:59,后面的 DML都失效了 (2022-01-27 13:58:59 - 2022-01-27 13:59:59)