数据库误删除数据库恢复工具binlog2sql,my2sql ,MyFlash,my2fback及工具使用
1、工具binlog2sql使用：
已测试环境（此工具使用时字段多时有时候存在字段和字段值不配的情况，如是生产环境，建议测试环境先查看后再导入生产）
* Python 2.7, 3.4+
* MySQL 5.6, 5.7
安装
==============
shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
shell> pip install -r requirements.txt
git与pip的安装问题请自行搜索解决
### MySQL server必须设置以下参数:
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
### user需要的最小权限集合：
select, super/replication client, replication slave
建议授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO
**权限说明**
* select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句
* super/replication client：两个权限都可以，需要执行'SHOW MASTER STATUS', 获取server端的binlog列表
* replication slave：通过BINLOG_DUMP协议获取binlog内容的权限
### 基本用法
**解析出标准SQL**
```bash
shell> python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t test3 test4 --start-file='mysql-bin.000002'
输出：
INSERT INTO `test`.`test3`(`addtime`, `data`, `id`) VALUES ('2016-12-10 13:03:38', 'english', 4); #start 570 end 736
UPDATE `test`.`test3` SET `addtime`='2016-12-10 12:00:00', `data`='中文', `id`=3 WHERE `addtime`='2016-12-10 13:03:22' AND `data`='中文' AND `id`=3 LIMIT 1; #start 763 end 954
DELETE FROM `test`.`test3` WHERE `addtime`='2016-12-10 13:03:38' AND `data`='english' AND `id`=4 LIMIT 1; #start 981 end 1147
```
**解析出回滚SQL**
```bash
shell> python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttest3 --start-file='mysql-bin.000002' --start-position=763 --stop-position=1147
输出：
INSERT INTO `test`.`test3`(`addtime`, `data`, `id`) VALUES ('2016-12-10 13:03:38', 'english', 4); #start 981 end 1147
UPDATE `test`.`test3` SET `addtime`='2016-12-10 13:03:22', `data`='中文', `id`=3 WHERE `addtime`='2016-12-10 12:00:00' AND `data`='中文' AND `id`=3 LIMIT 1; #start 763 end 954
```
### 选项
**mysql连接配置**
-h host; -P port; -u user; -p password
**解析模式**
--stop-never 持续解析binlog。可选。默认False，同步至执行命令时最新的binlog位置。
-K, --no-primary-key 对INSERT语句去除主键。可选。默认False
-B, --flashback 生成回滚SQL，可解析大文件，不受内存限制。可选。默认False。与stop-never或no-primary-key不能同时添加。
--back-interval -B模式下，每打印一千行回滚SQL，加一句SLEEP多少秒，如不想加SLEEP，请设为0。可选。默认1.0。
**解析范围控制**
--start-file 起始解析文件，只需文件名，无需全路径 。必须。
--start-position/--start-pos 起始解析位置。可选。默认为start-file的起始位置。
--stop-file/--end-file 终止解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。
--stop-position/--end-pos 终止解析位置。可选。默认为stop-file的最末位置；若解析模式为stop-never，此选项失效。
--start-datetime 起始解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。
--stop-datetime 终止解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。
**对象过滤**
-d, --databases 只解析目标db的sql，多个库用空格隔开，如-d db1 db2。可选。默认为空。
-t, --tables 只解析目标table的sql，多张表用空格隔开，如-t tbl1 tbl2。可选。默认为空。
--only-dml 只解析dml，忽略ddl。可选。默认False。
--sql-type 只解析指定类型，支持INSERT, UPDATE, DELETE。多个类型用空格隔开，如--sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。
### 应用案例
#### **误删整张表数据，需要紧急回滚**
闪回详细介绍可参见example目录下《闪回原理与实战》[example/mysql-flashback-priciple-and-practice.md](./example/mysql-flashback-priciple-and-practice.md)
```bash
test库tbl表原有数据
mysql> select * from tbl;
+----+--------+---------------------+
| id | name | addtime |
+----+--------+---------------------+
| 1 | 小赵 | 2016-12-10 00:04:33 |
| 2 | 小钱 | 2016-12-10 00:04:48 |
| 3 | 小孙 | 2016-12-13 20:25:00 |
| 4 | 小李 | 2016-12-12 00:00:00 |
+----+--------+---------------------+
4 rows in set (0.00 sec)
mysql> delete from tbl;
Query OK, 4 rows affected (0.00 sec)
20:28时，tbl表误操作被清空
mysql> select * from tbl;
Empty set (0.00 sec)
```
**恢复数据步骤**：
1. 登录mysql，查看目前的binlog文件
```bash
mysql> show master status;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000051 | 967 |
| mysql-bin.000052 | 965 |
+------------------+-----------+
```
2. 最新的binlog文件是mysql-bin.000052，我们再定位误操作SQL的binlog位置。误操作人只能知道大致的误操作时间，我们根据大致时间过滤数据。
```bash
shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-datetime='2016-12-13 20:25:00' --stop-datetime='2016-12-13 20:30:00'
输出：
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:26:00', 4, '小李'); #start 317 end 487 time 2016-12-13 20:26:26
UPDATE `test`.`tbl` SET `addtime`='2016-12-12 00:00:00', `id`=4, `name`='小李' WHERE `addtime`='2016-12-13 20:26:00' AND `id`=4 AND `name`='小李' LIMIT 1; #start 514 end 701 time 2016-12-13 20:27:07
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:33' AND `id`=1 AND `name`='小赵' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:48' AND `id`=2 AND `name`='小钱' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-13 20:25:00' AND `id`=3 AND `name`='小孙' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-12 00:00:00' AND `id`=4 AND `name`='小李' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
```
3. 我们得到了误操作sql的准确位置在728-938之间，再根据位置进一步过滤，使用flashback模式生成回滚sql，检查回滚sql是否正确(注：真实环境下，此步经常会进一步筛选出需要的sql。结合grep、编辑器等)
```bash
shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-position=3346 --stop-position=3556 -B > rollback.sql | cat
输出：
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-12 00:00:00', 4, '小李'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:25:00', 3, '小孙'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:48', 2, '小钱'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:33', 1, '小赵'); #start 728 end 938 time 2016-12-13 20:28:05
```
4. 确认回滚sql正确，执行回滚语句。登录mysql确认，数据回滚成功。
```bash
shell> mysql -h127.0.0.1 -P3306 -uadmin -p'admin' < rollback.sql
mysql> select * from tbl;
+----+--------+---------------------+
| id | name | addtime |
+----+--------+---------------------+
| 1 | 小赵 | 2016-12-10 00:04:33 |
| 2 | 小钱 | 2016-12-10 00:04:48 |
| 3 | 小孙 | 2016-12-13 20:25:00 |
| 4 | 小李 | 2016-12-12 00:00:00 |
+----+--------+---------------------+
```
### 限制（对比mysqlbinlog）
* mysql server必须开启，离线模式下不能解析
* 参数 _binlog\_row\_image_ 必须为FULL，暂不支持MINIMAL
* 解析速度不如mysqlbinlog
### 优点（对比mysqlbinlog）
* 纯Python开发，安装与使用都很简单
* 自带flashback、no-primary-key解析模式，无需再装补丁
* flashback模式下，更适合[闪回实战](./example/mysql-flashback-priciple-and-practice.md)
* 解析为标准SQL，方便理解、筛选
* 代码容易改造，可以支持更多个性化解析
MySQL误操作闪回恢复利器之my2sql （转载于：https://bbs.huaweicloud.com/blogs/344706）
一、my2sql简介
可以用于MySQL误操作闪回的工具包括my2sql、binlog2sql和MyFlash等工具，其中，个人感觉my2sql最好用。
my2sql是使用go语言开发的MySQL binlog解析工具，通过解析MySQL binlog ，可以生成原始SQL、回滚SQL、去除主键的INSERT SQL等，也可以生成DML统计信息。类似工具有binlog2sql、MyFlash、my2fback等，本工具基于my2fback、binlog_rollback工具二次开发而来。
my2sql的GitHub地址：https://github.com/liuhr/my2sql
优点：
- 功能丰富，不仅支持回滚操作，还有其他实用功能。
- 基于golang实现，速度快，全量解析1.1Gbinlog只需要1分30秒左右，当前其他类似开源工具一般要几十分钟。
二、my2sql用途
- 数据快速回滚(闪回)
- 主从切换后新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秒 | 不支持 |
四、安装
-- 申请一台全新的主机
docker rm -f lhrmy2sql
docker run -d --name lhrmy2sql -h lhrmy2sql \
--privileged=true lhrbest/lhrcentos76:8.0 \
/usr/sbin/init
docker exec -it lhrmy2sql bash
-- 方法1：编译安装
yum install -y golang
go version
go env | grep GOPATH
mkdir -p /root/go/src
cd /root/go/src
git clone https://github.com/liuhr/my2sql.git
cd my2sql/
go build .
-- 方法2：直接下载编译好的二进制文件。
-- https://github.com/liuhr/my2sql/tree/master/releases
wget https://raw.githubusercontent.com/liuhr/my2sql/master/releases/centOS_release_6.x/my2sql
wget https://raw.githubusercontent.com/liuhr/my2sql/master/releases/centOS_release_7.x/my2sql
mv my2sql /usr/local/bin/my2sql
chmod +x /usr/local/bin/my2sql
-- 若不能下载，请添加以下解析：
echo "
13.229.188.59 github.com
199.232.4.133 raw.githubusercontent.com
" >> /etc/hosts
五、重要参数说明
-U 优先使用unique key作为where条件，默认false
-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行数据的事务，默认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、事务信息
六、使用案例
6.1 解析出标准SQL
根据时间点解析出标准SQL
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -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
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -work-type 2sql \
-start-file mysql-bin.011259 -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266 \
-output-dir ./tmpdir
6.2 解析出回滚SQL
根据时间点解析出回滚SQL
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -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 -work-type rollback \
-start-file mysql-bin.011259 -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266 \
-output-dir ./tmpdir
6.3 统计DML以及大事务
统计时间范围各个表的DML操作数量，统计一个事务大于500条、时间大于300秒的事务
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -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秒的事务
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -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
6.4 从某一个pos点解析出标准SQL，并且持续打印到屏幕
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -work-type 2sql \
-start-file mysql-bin.011259 -start-pos 4 -output-toScreen
七、限制
- 使用回滚/闪回功能时，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才能解析
八、案例演示
8.1 准备MySQL环境
docker rm -f mysql3306
docker run -d --name mysql3306 -h mysql3306 -p 3306:3306 \
-v /etc/mysql/mysql3306/conf:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=lhr -e TZ=Asia/Shanghai \
mysql:5.7.30
docker exec -it mysql3306 bash
docker logs -f mysql3306
cat > /etc/mysql/mysql3306/conf/my.cnf <<"EOF"
[mysqld]
default-time-zone = '+8:00'
log_timestamps = SYSTEM
skip-name-resolve
log-bin
server_id=573306
character_set_server=utf8mb4
EOF
docker restart mysql3306
mysql -uroot -plhr -h192.168.66.35 -P3306
create database lhrdb default character set utf8mb4;
8.2 执行DML操作
show master logs;
flush logs;
select now();
use lhrdb;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`number` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`add_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加的时间',
`content` json DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_name` (`number`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1233,26,'ranran','2020-07-15 19:06:03',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1232,134,'asdf','2020-07-12 11:08:41',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1231,21,'chenxi','2020-07-12 10:12:45',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1229,20,'chenxi','2020-07-11 16:20:50',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`