做了多年oracle dba，对oralce的闪回是一往情深.如今搞起了mysql,找了个不错的闪回工具，分享给大家吧。
1.下载
https://github.com/danfengcao/binlog2sql
Search: python-pip
pip 是一个Python包管理工具，主要是用于安装 PyPI 上的软件包。
2. 安装
|
1
2
3
4
5
6
7
|
# yum install python-setuptools -y
# rpm -ivh python-pip-7.1.0-1.el6.noarch.rpm --rpmfind.net 下载上传安装
# pip install PyMySQL
# pip install -r requirments.txt
# pwd
/usr/local/binlog2sql-master/binlog2sql-master
# less README.md --这个必须要看个十遍，八遍的才行
|
3. 测试用例
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
(root@localhost) [kk]> create database roidba;
Query OK, 1 row affected (0.00 sec)
(root@localhost) [kk]> use roidba
Database changed
(root@localhost) [roidba]> create table roidba (
id
int,name varchar(10));
Query OK, 0 rows affected (0.15 sec)
(root@localhost) [roidba]> insert into roidba values(1,
'mysql'
);
Query OK, 1 row affected (0.00 sec)
(root@localhost) [roidba]> insert into roidba values(2,
'oracle'
);
Query OK, 1 row affected (0.00 sec)
(root@localhost) [roidba]> insert into roidba values(3,
'mariadb'
);
Query OK, 1 row affected (0.00 sec)
(root@localhost) [roidba]> delete from roidba;
Query OK, 3 rows affected (0.01 sec)
(root@localhost) [roidba]>
select
* from roidba;
Empty
set
(0.00 sec)
|
4. 创建闪回用户
|
1
2
3
4
5
|
(root@localhost) [roidba]> GRANT SELECT,REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO flashback@
'%'
identified by
'flashback'
;
Query OK, 0 rows affected (0.04 sec)
(root@localhost) [roidba]> flush privileges;
Query OK, 0 rows affected (0.05 sec)
|
5. 闪回的要求
|
1
2
3
4
5
6
7
8
9
10
|
1)知道大概的时间点2017-01-12 18:00 ~ 2017-01-12 18:00
2)知道binlog信息
(root@localhost) [roidba]> show binary logs;
+------------------+----------------+
| Log_name | File_size |
+------------------+----------------+
| mysql-bin.000001 |3828 |
| mysql-bin.000002 | 238 |
| mysql-bin.000003 |1231 |
+------------------+---------------+
|
6. 解析
|
1
2
3
4
5
6
7
8
9
10
11
12
|
--binlog中解析的sql
[root@mysql-121 binlog2sql-master]
# python binlog2sql/binlog2sql.py -h192.168.80.132 -P3306 -uflashback -pflashback -droidba -troidba --start-file='mysql-bin.000003' --start-datetime='2017-01-12 18:00:00' --stop-datetime='2017-01-12 18:30:00'
INSERT INTO `roidba`.`roidba`(`
id
`, `name`) VALUES (1,
'mysql'
);
#start 239 end 413 time 2017-01-12 18:10:54
INSERT INTO `roidba`.`roidba`(`
id
`, `name`) VALUES (2,
'oracle'
);
#start 492 end 667 time 2017-01-12 18:11:00
INSERT INTO `roidba`.`roidba`(`
id
`, `name`) VALUES (3,
'mariadb'
);
#start 746 end 922 time 2017-01-12 18:11:08
DELETE FROM `roidba`.`roidba` WHERE `
id
`=1 AND `name`=
'mysql'
LIMIT 1;
#start 1001 end 1200 time 2017-01-12 18:11:14
DELETE FROM `roidba`.`roidba` WHERE `
id
`=2 AND `name`=
'oracle'
LIMIT 1;
#start 1001 end 1200 time 2017-01-12 18:11:14
DELETE FROM `roidba`.`roidba` WHERE `
id
`=3 AND `name`=
'mariadb'
LIMIT 1;
#start 1001 end 1200 time 2017-01-12 18:11:14
USE roidba;
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO
'flashback'
@
'%'
IDENTIFIED BY PASSWORD
'*6DE874C277DDA35061BD526FB38B6A3C8B3A9254'
;
USE roidba;
flush privileges;
|
7. 闪回
|
1
2
3
4
5
6
7
8
|
--闪回sql
# python binlog2sql/binlog2sql.py -h192.168.80.132 -P3306 -uflashback -p'flashback' -droidba -troidba --start-file='mysql-bin.000003' --start-position=1001 --stop-position=1200 -B > rollback.sql
[root@mysql-121 binlog2sql-master]
# less rollback.sql
INSERT INTO `roidba`.`roidba`(`
id
`, `name`) VALUES (3,
'mariadb'
);
#start 1001 end 1200 time 2017-01-12 18:11:14
INSERT INTO `roidba`.`roidba`(`
id
`, `name`) VALUES (2,
'oracle'
);
#start 1001 end 1200 time 2017-01-12 18:11:14
INSERT INTO `roidba`.`roidba`(`
id
`, `name`) VALUES (1,
'mysql'
);
#start 1001 end 1200 time 2017-01-12 18:11:14
--开始回滚
[root@mysql-121 binlog2sql-master]
# mysql -ubackup -pbackup -h192.168.80.132 <./rollback.sql --具有插入权限才可以
|
8.验证
自己多测试，多动手，看别人做很简单，其实真的自己上手就不一定了，少说多做，少扯淡。
本文转自 roidba 51CTO博客，原文链接：http://blog.51cto.com/roidba/1891595，如需转载请自行联系原作者
