一、概述
binlog2sql是一个开源项目，应用于大众点评线上环境。类似于ORACLE中的闪回功能，binlog2sql可以基于时间点或者位置偏移量进行数据恢复。从MySQL binlog解析出你要的SQL。根据不同选项，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。也就是对于insert操作会生成对应的delete语句，反之delete操作会生出对应的insert语句，update操作会生成相反的语句。
关于binlog2sql的闪回详细介绍可参考闪回原理与实战。我也是偶然间看到一个大神关于这个神器的介绍，猛然心动，决心要动手演练一把。
我的测试环境介绍
l Python 2.6
l MySQL 5.1.73
二、binlog2sql安装
binlog2sql工具可以自己下载 https://github.com/danfengcao/binlog2sql
下面这些包都要装全，否则执行脚本会报错
python-pip
PyMySQL
python-mysql-replication
argparse
Linux机器下载并安装binlog2sql
|
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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
[root@DB binlog2sql-master]
# wget https://codeload.github.com/danfengcao/binlog2sql/zip/master
[root@DB install_page]
# unzip binlog2sql-master.zip
Archive: binlog2sql-master.zip
bb09b8f9079ca4d3cacd0186f35ddf4b3e1cfa7e
creating: binlog2sql-master/
inflating: binlog2sql-master/.gitignore
inflating: binlog2sql-master
/LICENSE
inflating: binlog2sql-master
/README
.md
creating: binlog2sql-master
/binlog2sql/
inflating: binlog2sql-master
/binlog2sql/__init__
.py
inflating: binlog2sql-master
/binlog2sql/binlog2sql
.py
inflating: binlog2sql-master
/binlog2sql/binlog2sql_util
.py
creating: binlog2sql-master
/example/
inflating: binlog2sql-master
/example/mysql-flashback-priciple-and-practice
.md
inflating: binlog2sql-master
/requirements
.txt
creating: binlog2sql-master
/tests/
inflating: binlog2sql-master
/tests/test_binlog2sql_util
.py
[root@DB install_page]
# ls
binlog2sql-master binlog2sql-master.zip
[root@DB binlog2sql-master]
# cd binlog2sql-master #下面脚本执行的时候也要在这么路径下
[root@DB binlog2sql-master]
# pip install -r requirements.txt
Downloading
/unpacking
PyMySQL==0.7.11 (from -r requirements.txt (line 1))
Downloading PyMySQL-0.7.11.
tar
.gz (71kB): 71kB downloaded
Running setup.py egg_info
for
package PyMySQL
Downloading
/unpacking
wheel==0.29.0 (from -r requirements.txt (line 2))
Downloading wheel-0.29.0.
tar
.gz (54kB): 54kB downloaded
Running setup.py egg_info
for
package wheel
no previously-included directories found matching
'wheel/test/*/dist'
no previously-included directories found matching
'wheel/test/*/build'
Downloading
/unpacking
mysql-replication==0.13 (from -r requirements.txt (line 3))
Downloading mysql-replication-0.13.
tar
.gz
Running setup.py egg_info
for
package mysql-replication
Installing collected packages: PyMySQL, wheel, mysql-replication
Running setup.py
install
for
PyMySQL
Running setup.py
install
for
wheel
no previously-included directories found matching
'wheel/test/*/dist'
no previously-included directories found matching
'wheel/test/*/build'
Installing wheel script to
/usr/bin
Running setup.py
install
for
mysql-replication
Successfully installed PyMySQL wheel mysql-replication
Cleaning up...
|
三、Mysql环境要求
|
1
2
3
4
5
|
[mysqld]
server-
id
=160
log-bin=mysql-binlog
max_binlog_size=1G
binlog_format=row
|
2、 创建一个闪回用户
|
1
2
3
4
5
6
7
|
root@localhost test1 19:48:06> create user
test
@
'%'
identified by
'123456'
;
Query OK, 0 rows affected (0.00 sec)
root@localhost test1 19:49:06>grant
select
,replication slave,replication client on *.* to
test
@
'%'
;
Query OK, 0 rows affected (0.00 sec)
root@localhost test1 19:49:50>flush privileges;
Query OK, 0 rows affected (0.00 sec)
|
select, super/replication client, replication slave
权限说明
|
1
2
3
|
select
：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句
super
/replication
client：两个权限都可以，需要执行
'SHOW MASTER STATUS'
, 获取server端的binlog列表
replication slave：通过BINLOG_DUMP协议获取binlog内容的权限
|
3、 模拟一次生产事故，误删数据
test1库tb1表原有数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
root@localhost test1 20:08:52>
select
* from tb1;
+-------+------+
| name | age |
+-------+------+
| kobe | 21 |
| james | 22 |
| jack | 23 |
| mike | 24 |
| bob | 25 |
+-------+------+
5 rows
in
set
(0.01 sec)
root@localhost test1 20:08:59>delete from tb1 where age <23;
Query OK, 2 rows affected (0.00 sec)
root@localhost test1 20:09:03>
select
* from tb1;
+-------+------+
| name | age |
+-------+------+
| jack | 23 |
| mike | 24 |
| bob | 25 |
+-------+------+
3 rows
in
set
(0.01 sec)
|
四、恢复数据步骤
1、登录mysql，查看目前的binlog文件
|
1
2
3
4
5
6
7
|
root@localhost test1 20:09:59>show master status;
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-binlog.000002 | 341 | | |
+---------------------+----------+--------------+------------------+
1 row
in
set
(0.00 sec)
|
最新的binlog文件是mysql-binlog.000002，我们再定位误操作SQL的binlog位置。误操作人只能知道大致的误操作时间，我们根据大致时间过滤数据。
2、 接下来就该这个神器登场了。
先来介绍一下binlog2sql参数
|
1
2
3
4
5
6
7
8
9
10
11
|
--stop-never 持续同步binlog。可选。不加则同步至执行命令时最新的binlog位置。
-K, --no-primary-key 对INSERT语句去除主键。可选。
-B, --flashback 生成回滚语句，可解析大文件，不受内存限制，每打印一千行加一句SELECT SLEEP(1)。可选。与stop-never或no-primary-key不能同时添加。
--start-file 起始解析文件。必须。
--start-position/--start-pos start-file的起始解析位置。可选。默认为start-file的起始位置。
--stop-file/--end-file 末尾解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。
--stop-position/--end-pos stop-file的末尾解析位置。可选。默认为stop-file的最末位置；若解析模式为stop-never，此选项失效。
--start-datetime 从哪个时间点的binlog开始解析，格式必须为datetime，如'2016-11-11 11:11:11'。可选。默认不过滤。
--stop-datetime 到哪个时间点的binlog停止解析，格式必须为datetime，如'2016-11-11 11:11:11'。可选。默认不过滤。
-d, --databases 只输出目标db的sql。可选。默认为空。
-t, --tables 只输出目标tables的sql。可选。默认为空。
|
3、 根据预估时间，执行下面命令找出对应的position
|
1
2
3
|
[root@DB binlog2sql]
# python binlog2sql.py -h 192.168.221.160 -utest -p123456 -dtest1 -ttb1 --start-file='mysql-binlog.000002' --start-datetime='2017-12-04 20:00:00' --stop-datetime='2017-12-04 20:10:00'
DELETE FROM `test1`.`tb1` WHERE `age`=21 AND `name`=
'kobe'
LIMIT 1;
#start 4 end 271 time 2017-12-04 20:08:59
DELETE FROM `test1`.`tb1` WHERE `age`=22 AND `name`=
'james'
LIMIT 1;
#start 4 end 271 time 2017-12-04 20:08:59
|
我们得到了误操作sql的准确位置在4-271之间，再根据位置进一步过滤，使用flashback模式生成回滚sql，检查回滚sql是否正确(注：真实环境下，此步经常会进一步筛选出需要的sql。结合grep、编辑器等)
4、 使用flashback模式生成回滚sql
|
1
|
[root@DB binlog2sql]
# python binlog2sql.py -h 192.168.221.160 -utest -p123456 -dtest1 -ttb1 --start-file='mysql-binlog.000002' --start-position=4 --stop-position=271 -B > tb1_rollback.sql
|
查看闪回导出的文件
|
1
2
3
|
[root@DB binlog2sql]
# cat tb1_rollback.sql
INSERT INTO `test1`.`tb1`(`age`, `name`) VALUES (22,
'james'
);
#start 4 end 271 time 2017-12-04 20:08:59
INSERT INTO `test1`.`tb1`(`age`, `name`) VALUES (21,
'kobe'
);
#start 4 end 271 time 2017-12-04 20:08:59
|
5、 确认回滚sql正确，执行回滚语句。登录mysql确认，数据回滚成功。
|
1
2
|
[root@DB binlog2sql]
# mysql -uroot test1 -p123456 <tb1_rollback.sql
Enter password:
|
6、登录数据库检验数据完整性
|
1
2
3
4
5
6
7
8
9
10
11
|
root@localhost test1 20:18:04>
select
* from tb1;
+-------+------+
| name | age |
+-------+------+
| kobe | 21 |
| james | 22 |
| jack | 23 |
| mike | 24 |
| bob | 25 |
+-------+------+
5 rows
in
set
(0.00 sec)
|
可以看到，之前删除的两条数据又回来了
五、结语
binlog2sql是属于纯Python开发，安装与使用都很简单，易于上手，可操作性强，解析为标准SQL，方便理解、调试。但仍存在一些缺点，比如只能在mysql开启的状态下运行，不能离线操作，数据量较大时会暴露出解析速度慢的问题。总体来说，仍不失成为一个很NICE的工具。
本文转自 青苗飞扬 51CTO博客，原文链接:http://blog.51cto.com/qingmiao/2047363
