Mysql闪回技术之 binlog2sql
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.验证
自己多测试,多动手,看别人做很简单,其实真的自己上手就不一定了,少说多做,少扯淡。