MySQL 闪回工具 binlog2sql 安装及使用

发表于 | 分类于 ，
字数统计: 3k | 阅读时长 ≈ 14

一、binlog2sql 概述

从MySQL binlog解析出需要的SQL。根据不同选项，可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。

1.1 用途

  • 数据快速回滚(闪回)
  • 主从切换后新master丢数据的修复
  • 从binlog生成标准SQL，带来的衍生功能

GitHub项目地址：https://github.com/danfengcao/binlog2sql

1.2 所需环境

  • Python 2.7, 3.4+
  • MySQL 5.6, 5.7
  • binlog 模式为 row

1.3 闪回原理

MySQL binlog以event的形式，记录了MySQL server从启用binlog以来所有的变更信息，能够帮助重现这之间的所有变化。MySQL引入binlog主要有两个目的：一是为了主从复制；二是某些备份还原操作后需要重新应用binlog。

有三种可选的binlog格式，各有优缺点：

  • statement：基于SQL语句的模式，binlog数据量小，但是某些语句和函数在复制过程可能导致数据不一致甚至出错；
  • row：基于行的模式，记录的是行的完整变化。很安全，但是binlog会比其他两种模式大很多；
  • mixed：混合模式，根据语句来选用是statement还是row模式；

既然binlog以event形式记录了所有的变更信息，那么我们把需要回滚的event，从后往前回滚回去即可。

1
2
3
对于 delete 操作，从 binlog 提取出 delete 信息，反向生成 insert 回滚语句；
对于 insert 操作，根据 binlog 反向生成 delete 回滚语句；
对于 update 操作，根据 binlog 反向生成 update 回滚语句；

二、binlog2sql 安装

2.1 安装依赖包

1
# yum install -y git wget

2.1 安装python3 pip3

1
2
# yum install -y python3
# pip3 -V （查看pip是否安装成功）

2.2 安装 binlog2sql

1
2
# git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
# pip3 install -r requirements.txt

三、binlog2sql 使用

3.1 使用前配置

MySQL server必须设置以下参数

1
2
3
4
5
6
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

在运行中的mysql中查看参数

1
2
3
4
5
mysql> show variables like 'server_id';
mysql> show variables like 'log_bin%';
mysql> show variables like 'max_binlog_size';
mysql> show variables like 'binlog_format';
mysql> show variables like 'binlog_row_image';

用来闪回数据的user需要的最小权限集合

1
2
3
4
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内容的权限

3.2 基本用法

解析出标准SQL

1
2
3
4
5
6
# 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

1
2
3
4
5
# 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

3.3 参数说明

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。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。

四、binlog2sql 回滚数据

在对数据进行回滚时，一定要弄清楚误执行的sql语句和具体的时间，这对恢复数据可以提供很大的帮助

4.1 误删除数据-恢复

这里有一张工资表

1
2
3
4
5
6
7
8
9
10
11
mysql> select * from payoff;
+----+-----------+-----+-----+------------+----------+
| id | name      | age | sex | department | salary   |
+----+-----------+-----+-----+------------+----------+
|  1 | 张三      |  23 | 男  | 销售       |  8000.00 |
|  2 | 李四      |  25 | 男  | 产品       |  6600.00 |
|  3 | 马五      |  25 | 男  | 技术       | 10000.00 |
|  4 | 贝贝      |  23 | 女  | 运营       |  5000.00 |
|  5 | 张丽丽    |  26 | 女  | 运营       |  6000.00 |
|  6 | 王芳      |  28 | 女  | 人事       |  5600.00 |
+----+-----------+-----+-----+------------+----------+

由于操作失误对数据进行了 inster，delete，update 操作

1
2
3
4
5
6
7
8
9
10
11
# 插入了一条新数据
mysql> insert into payoff(name,age,sex,department,salary) values ('小明',27,'男','技术',8800);
Query OK, 1 row affected (0.00 sec)

# 删除了张三的数据
mysql> delete from payoff where name='张三';
Query OK, 1 row affected (0.00 sec)

# 将张丽丽的工资设置为了10000
mysql> update payoff set salary=10000 where name='张丽丽';
Query OK, 1 row affected (0.01 sec)

此时表中最终的数据是

1
2
3
4
5
6
7
8
9
10
11
mysql> select * from payoff;
+----+-----------+-----+-----+------------+----------+
| id | name      | age | sex | department | salary   |
+----+-----------+-----+-----+------------+----------+
|  2 | 李四      |  25 | 男  | 产品       |  6600.00 |
|  3 | 马五      |  25 | 男  | 技术       | 10000.00 |
|  4 | 贝贝      |  23 | 女  | 运营       |  5000.00 |
|  5 | 张丽丽    |  26 | 女  | 运营       | 10000.00 |
|  6 | 王芳      |  28 | 女  | 人事       |  5600.00 |
|  7 | 小明      |  27 | 男  | 技术       |  8800.00 |
+----+-----------+-----+-----+------------+----------+

先查看最新的binlog位置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     16781 |
| mysql-bin.000002 |      3541 |
| mysql-bin.000003 |     12984 |
| mysql-bin.000004 |     13832 |
| mysql-bin.000005 |      1334 |
| mysql-bin.000006 |      3320 |
| mysql-bin.000007 |      1050 |
| mysql-bin.000008 |      1071 |
| mysql-bin.000009 |      4289 |
| mysql-bin.000010 |      3894 |
+------------------+-----------+

使用 binlog2sql 前，先创建一个专属用户

1
mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO admin@"%" identified by "abc!@#123ABC";

用 binlog2sql 解析出标准SQL ，就是误操作的sql

1
2
3
4
5
# python3 binlog2sql.py -h192.168.126.133 -P3306 -uadmin -p'abc!@#123ABC' -dschool -t payoff --start-file='mysql-bin.000010'

INSERT INTO `school`.`payoff`(`id`, `name`, `age`, `sex`, `department`, `salary`) VALUES (7, '小明', 27, '男', '技术', 8800); #start 4 end 422 time 2021-09-10 16:21:56
DELETE FROM `school`.`payoff` WHERE `id`=1 AND `name`='张三' AND `age`=23 AND `sex`='男' AND `department`='销售' AND `salary`=8000 LIMIT 1; #start 453 end 721 time 2021-09-10 16:23:12
UPDATE `school`.`payoff` SET `id`=5, `name`='张丽丽', `age`=26, `sex`='女', `department`='运营', `salary`=10000 WHERE `id`=5 AND `name`='张丽丽' AND `age`=26 AND `sex`='女' AND `department`='运营' AND `salary`=6000 LIMIT 1; #start 752 end 1058 time 2021-09-10 16:24:22

如果数据量大还可以根据 --start-datetime 起始时间和 --stop-datetime 终止解析时间缩短范围

用 binlog2sql 解析出回滚SQL ，就多了一个 --flashback 参数

1
2
3
4
5
# python3 binlog2sql.py --flashback -h192.168.126.133 -P3306 -uadmin -p'abc!@#123ABC' -dschool -t payoff --start-file='mysql-bin.000010'

UPDATE `school`.`payoff` SET `id`=5, `name`='张丽丽', `age`=26, `sex`='女', `department`='运营', `salary`=6000 WHERE `id`=5 AND `name`='张丽丽' AND `age`=26 AND `sex`='女' AND `department`='运营' AND `salary`=10000 LIMIT 1; #start 752 end 1058 time 2021-09-10 16:24:22
INSERT INTO `school`.`payoff`(`id`, `name`, `age`, `sex`, `department`, `salary`) VALUES (1, '张三', 23, '男', '销售', 8000); #start 453 end 721 time 2021-09-10 16:23:12
DELETE FROM `school`.`payoff` WHERE `id`=7 AND `name`='小明' AND `age`=27 AND `sex`='男' AND `department`='技术' AND `salary`=8800 LIMIT 1; #start 4 end 422 time 2021-09-10 16:21:56

将需要回滚的反向sql重新执行一遍，就可以恢复数据了

1
2
3
4
5
mysql> UPDATE `school`.`payoff` SET `id`=5, `name`='张丽丽', `age`=26, `sex`='女', `department`='运营', `salary`=6000 WHERE `id`=5 AND `name`='张丽丽' AND `age`=26 AND `sex`='女' AND `department`='运营' AND `salary`=10000 LIMIT 1; 

mysql> INSERT INTO `school`.`payoff`(`id`, `name`, `age`, `sex`, `department`, `salary`) VALUES (1, '张三', 23, '男', '销售', 8000); 

mysql> DELETE FROM `school`.`payoff` WHERE `id`=7 AND `name`='小明' AND `age`=27 AND `sex`='男' AND `department`='技术' AND `salary`=8800 LIMIT 1;

执行完恢复sql后，和之前的数据进行对比

1
2
3
4
5
6
7
8
9
10
11
mysql> select * from payoff;
+----+-----------+-----+-----+------------+----------+
| id | name      | age | sex | department | salary   |
+----+-----------+-----+-----+------------+----------+
|  1 | 张三      |  23 | 男  | 销售       |  8000.00 |
|  2 | 李四      |  25 | 男  | 产品       |  6600.00 |
|  3 | 马五      |  25 | 男  | 技术       | 10000.00 |
|  4 | 贝贝      |  23 | 女  | 运营       |  5000.00 |
|  5 | 张丽丽    |  26 | 女  | 运营       |  6000.00 |
|  6 | 王芳      |  28 | 女  | 人事       |  5600.00 |
+----+-----------+-----+-----+------------+----------+

4.2 误删除表-恢复

如果不小心删除了表，恢复步骤其实和上面是一样的

1
mysql> delete from payoff;

delete from table 是清空表数据，

drop table 是删除表结构和表数据，用binlog2sql无法恢复，只能通过数据库备份 + binlog来手动恢复

在实际使用中最好不要随便给用户授与drop权

先查看最新的binlog位置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     16781 |
| mysql-bin.000002 |      3541 |
| mysql-bin.000003 |     12984 |
| mysql-bin.000004 |     13832 |
| mysql-bin.000005 |      1334 |
| mysql-bin.000006 |      3320 |
| mysql-bin.000007 |      1050 |
| mysql-bin.000008 |      1071 |
| mysql-bin.000009 |      4289 |
| mysql-bin.000010 |      3894 |
| mysql-bin.000011 |       154 |
+------------------+-----------+

用 binlog2sql 解析出标准SQL ，就是误操作的sql

1
2
3
4
5
6
7
8
# python3 binlog2sql.py -h192.168.126.133 -P3306 -uadmin -p'abc!@#123ABC' -dschool -t payoff --start-file='mysql-bin.000011'

DELETE FROM `school`.`payoff` WHERE `id`=1 AND `name`='张三' AND `age`=23 AND `sex`='男' AND `department`='销售' AND `salary`=8000 LIMIT 1; #start 4 end 580 time 2021-09-10 16:54:28
DELETE FROM `school`.`payoff` WHERE `id`=2 AND `name`='李四' AND `age`=25 AND `sex`='男' AND `department`='产品' AND `salary`=6600 LIMIT 1; #start 4 end 580 time 2021-09-10 16:54:28
DELETE FROM `school`.`payoff` WHERE `id`=3 AND `name`='马五' AND `age`=25 AND `sex`='男' AND `department`='技术' AND `salary`=10000 LIMIT 1; #start 4 end 580 time 2021-09-10 16:54:28
DELETE FROM `school`.`payoff` WHERE `id`=4 AND `name`='贝贝' AND `age`=23 AND `sex`='女' AND `department`='运营' AND `salary`=5000 LIMIT 1; #start 4 end 580 time 2021-09-10 16:54:28
DELETE FROM `school`.`payoff` WHERE `id`=5 AND `name`='张丽丽' AND `age`=26 AND `sex`='女' AND `department`='运营' AND `salary`=6000 LIMIT 1; #start 4 end 580 time 2021-09-10 16:54:28
DELETE FROM `school`.`payoff` WHERE `id`=6 AND `name`='王芳' AND `age`=28 AND `sex`='女' AND `department`='人事' AND `salary`=5600 LIMIT 1; #start 4 end 580 time 2021-09-10 16:54:28

用 binlog2sql 解析出回滚SQL ，就多了一个 --flashback 参数

1
2
3
4
5
6
7
8
# python3 binlog2sql.py --flashback -h192.168.126.133 -P3306 -uadmin -p'abc!@#123ABC' -dschool -t payoff --start-file='mysql-bin.000011'

INSERT INTO `school`.`payoff`(`id`, `name`, `age`, `sex`, `department`, `salary`) VALUES (6, '王芳', 28, '女', '人事', 5600); #start 4 end 580 time 2021-09-10 16:54:28
INSERT INTO `school`.`payoff`(`id`, `name`, `age`, `sex`, `department`, `salary`) VALUES (5, '张丽丽', 26, '女', '运营', 6000); #start 4 end 580 time 2021-09-10 16:54:28
INSERT INTO `school`.`payoff`(`id`, `name`, `age`, `sex`, `department`, `salary`) VALUES (4, '贝贝', 23, '女', '运营', 5000); #start 4 end 580 time 2021-09-10 16:54:28
INSERT INTO `school`.`payoff`(`id`, `name`, `age`, `sex`, `department`, `salary`) VALUES (3, '马五', 25, '男', '技术', 10000); #start4 end 580 time 2021-09-10 16:54:28
INSERT INTO `school`.`payoff`(`id`, `name`, `age`, `sex`, `department`, `salary`) VALUES (2, '李四', 25, '男', '产品', 6600); #start 4 end 580 time 2021-09-10 16:54:28
INSERT INTO `school`.`payoff`(`id`, `name`, `age`, `sex`, `department`, `salary`) VALUES (1, '张三', 23, '男', '销售', 8000); #start 4 end 580 time 2021-09-10 16:54:28

将需要回滚的反向sql重新执行一遍，就可以恢复数据了

1
2
3
4
5
6
mysql> INSERT INTO `school`.`payoff`(`id`, `name`, `age`, `sex`, `department`, `salary`) VALUES (6, '王芳', 28, '女', '人事', 5600);
mysql> INSERT INTO `school`.`payoff`(`id`, `name`, `age`, `sex`, `department`, `salary`) VALUES (5, '张丽丽', 26, '女', '运营', 6000);
mysql> INSERT INTO `school`.`payoff`(`id`, `name`, `age`, `sex`, `department`, `salary`) VALUES (4, '贝贝', 23, '女', '运营', 5000);
mysql> INSERT INTO `school`.`payoff`(`id`, `name`, `age`, `sex`, `department`, `salary`) VALUES (3, '马五', 25, '男', '技术', 10000);
mysql> INSERT INTO `school`.`payoff`(`id`, `name`, `age`, `sex`, `department`, `salary`) VALUES (2, '李四', 25, '男', '产品', 6600);
mysql> INSERT INTO `school`.`payoff`(`id`, `name`, `age`, `sex`, `department`, `salary`) VALUES (1, '张三', 23, '男', '销售', 8000);
-------------本文结束感谢您的阅读-------------
分享到： 收藏夹 复制网址 邮件 微信 QQ空间 腾讯微博 豆瓣 一键分享 更多