MySQL数据恢复，你会吗？-51CTO.COM

今天分享一下binlog2sql，它是一款比较常用的数据恢复工具，可以通过它从MySQL binlog解析出你要的SQL，并根据不同选项，可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。主要用途如下：

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

1.系统环境准备

（1）安装Git

该项目分享与git上，可以直接从git上获取，因此可以先安装git。

yum install  -y  pip1.

（2）安装Python

因binlog2sql依赖于python2.7或python3.4+版本，如果本机python版本较低，则无法使用。安装或升级python的方法可以参考《python安装及升级》

（3）安装pip

如果系统中没有安装pip，则需要先安装pip,因为后续需要用pip安装python所需的包。

（4）binlog2sql下载及依赖包安装

binlog2sql可以部署在其他机器上，而不是必须部署在mysql服务端上。

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt1.
2.

2.MySQL环境准备

（1）参数设置

MySQL server必须设置以下参数。

[mysqld]
server_id = 128
log_bin = /data/mysql/mysql3306/logs/mysql-bin
max_binlog_size = 512M
binlog_format = row
binlog_row_image = full   # 默认值，可以不显式设置1.
2.
3.
4.
5.
6.

（2）创建恢复账号

因binlog2sql是通过模拟从库的方式获取binlog，所以，数据库账号权限至少需设置为从库所需的权限。

--  创建用户
mysql> create  user  data_rec@'192.168.56.%'  identified by 'xxxxxxxx';
Query OK, 0 rows affected (0.01 sec)
--  授权
mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO data_rec@'192.168.56.%';
Query OK, 0 rows affected (0.00 sec)1.
2.
3.
4.
5.
6.

权限说明：

select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句。
super/replication client：两个权限都可以，需要执行'SHOW MASTER STATUS', 获取server端的binlog列表。
replication slave：通过BINLOG_DUMP协议获取binlog内容的权限。

（3）创建测试表及数据

mysql> use testdb;
Database changed
mysql> create table t_test1 (id int primary key  auto_increment ,c_name  varchar(20), c_num  int );
Query OK, 0 rows affected (0.02 sec)


mysql> insert into  t_test1(c_name,c_num) values('aaaa',10),('abcc',15),('bacess',9),('andd',10);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql> insert into  t_test1(c_name,c_num) values('bbbaa',1),('dc',5),('vgcess',29),('hdgd',0);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 01.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

（4）模拟误删除数据

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-09-18 16:29:08 |
+---------------------+
1 row in set (0.00 sec)


mysql> delete from  t_test1;
Query OK, 8 rows affected (0.00 sec)


mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-09-18 16:29:26 |
+---------------------+
1 row in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

3.binlog2sql恢复数据

（1）生成恢复SQL

因知道大概误删除的时间，因此通过解析对应时间的binlog恢复出指定库表的数据，生成的结果是用于恢复的sql.

python binlog2sql.py  --flashback  -h 192.168.56.128 -udata_rec -p'xxxxxxxx' -d testdb  -t t_test1  --start-file='mysql-bin.000003' --start-datetime='2020-09-18 16:29:08' --stop-datetime='2020-09-18 16:30:00'  >/tmp/rec.sql1.

结果内容如下：

（2）恢复数据

mysql> select  * from t_test1;
Empty set (0.00 sec)
mysql> source /tmp/rec.sql;
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql> select  * from t_test1;
+----+--------+-------+
| id | c_name | c_num |
+----+--------+-------+
|  1 | aaaa   |    10 |
|  2 | abcc   |    15 |
|  3 | bacess |     9 |
|  4 | andd   |    10 |
|  5 | bbbaa  |     1 |
|  6 | dc     |     5 |
|  7 | vgcess |    29 |
|  8 | hdgd   |     0 |
+----+--------+-------+
8 rows in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.

数据已恢复。

4.使用说明

参考原文链接：https://github.com/danfengcao/binlog2sql。

（1）解析模式

stop-never 持续解析binlog。可选。默认False，同步至执行命令时最新的binlog位置。一般情况下解析单个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。

（2）解析范围控制

start-file 起始解析文件，只需文件名，无需全路径，必选参数。即指定对应的binlog起始文件名。
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'。可选。默认不过滤。

（3）对象过滤

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