MySQL闪回-binlog2sql
binlog2sql从mysql binlog解析出你要的sql。根据不同选项，你可以得到原始sql、回滚sql、去除主键的insert sql等。
用途
[*] 数据快速回滚(闪回)
[*] 主从切换后数据不一致的修复
[*] 从binlog生成标准SQL，带来的衍生功能
项目状态
已测试环境 Python 2.6, 2.7
MySQL 5.6，5.7
工具安装
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt
需要先安装 git 和 pip
yum -y install git
pip 下载：wget "https://pypi.python.org/packages/source/p/pip/pip-1.5.4.tar.gz#md5=834b2904f92d46aaa333267fb1c922bb" --no-check-certificate
# tar -xzvf pip-1.5.4.tar.gz
# cd pip-1.5.4
# python setup.py install
安装 PyMySQL
pip install PyMySQL
安装setuptools
$wget --no-check-certificate https://pypi.python.org/packages/source/s/setuptools/setuptools-12.0.3.tar.gz#md5=f07e4b0f4c1c9368fcd980d888b29a65
$ tar -zxvf setuptools-12.0.3.tar.gz
$ cd setuptools=12.0.3
$ python setup.py install
mysql server必须设置以下参数:
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1g
binlog_format = row
binlog_row_image = full
user需要的最小权限集合：
select, super/replication client, replication slave
建议授权
权限说明
select：需要读取server端information_schema.columns表，获取表结构的元信息，拼接成可视化的sql语句
super/replication client：两个权限都可以，需要执行'show master status', 获取server端的binlog列表
replication slave：通过binlog_dump协议获取binlog内容的权限
解析出标准sql
# python binlog2sql.py -h127.0.0.1 -P3307 -uroot -p'123456' -ddb1 -t t1 --start-file='mysql-bin.000006';
insert into `db1`.`t1`(`id`, `name`) values (5, 'java'); #start 1543 end 1691 time 2017-03-20 14:26:42
insert into `db1`.`t1`(`id`, `name`) values (6, 'php'); #start 1779 end 1934 time 2017-03-20 14:28:10
#选项 mysql连接配置
-h host; -P port; -u user; -p password
解析模式
--stop-never 持续同步binlog。可选。不加则同步至执行命令时最新的binlog位置。
-K, --no-primary-key 对INSERT语句去除主键。可选。
-B, --flashback 生成回滚语句，可解析大文件，不受内存限制，每打印一千行加一句SLEEP SELECT(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。可选。默认为空。
删除t1表所有的数据,紧急恢复
mysql> delete from t1;
Query OK, 6 rows affected (0.15 sec)
mysql> select * from t1;
Empty set (0.00 sec)
登陆mysql
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000006
Position: 2832
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 3db33b36-0e51-409f-a61d-c99756e90155:1-25:1000022,
8772e43a-0ae2-11e7-aa23-005056aaa05e:1-4,
cad45731-0aea-11e7-92ce-005056aa8034:1-4
1 row in set (0.00 sec)
最新的binlog文件是mysql-bin.000006，我们再定位误操作SQL的binlog位置。误操作人只能知道大致的误操作时间，我们根据大致时间过滤数据。
# python binlog2sql.py -h127.0.0.1 -P3307 -uroot -p'123456' -ddb1 -t t1 --start-file='mysql-bin.000006'
insert into `db1`.`t1`(`id`, `name`) values (5, 'java'); #start 1543 end 1691 time 2017-03-20 14:26:42
insert into `db1`.`t1`(`id`, `name`) values (6, 'php'); #start 1779 end 1934 time 2017-03-20 14:28:10
delete from `db1`.`t1` where `id`=1 and `name`='aaa' limit 1; #start 2604 end 2805 time 2017-03-27 15:19:29
delete from `db1`.`t1` where `id`=2 and `name`='bbb' limit 1; #start 2604 end 2805 time 2017-03-27 15:19:29
delete from `db1`.`t1` where `id`=3 and `name`='ccc' limit 1; #start 2604 end 2805 time 2017-03-27 15:19:29
delete from `db1`.`t1` where `id`=4 and `name`='ddd' limit 1; #start 2604 end 2805 time 2017-03-27 15:19:29
delete from `db1`.`t1` where `id`=5 and `name`='java' limit 1; #start 2604 end 2805 time 2017-03-27 15:19:29
delete from `db1`.`t1` where `id`=6 and `name`='php' limit 1; #start 2604 end 2805 time 2017-03-27 15:19:29
#python binlog2sql.py --flashback -h127.0.0.1 -P3307 -uroot -p'123456' -ddb1 -t t1 --start-file='mysql-bin.000006'
insert into `db1`.`t1`(`id`, `name`) values (6, 'php'); #start 347 end 548 time 2017-03-27 15:45:31
insert into `db1`.`t1`(`id`, `name`) values (5, 'java'); #start 347 end 548 time 2017-03-27 15:45:31
insert into `db1`.`t1`(`id`, `name`) values (4, 'ddd'); #start 347 end 548 time 2017-03-27 15:45:31
insert into `db1`.`t1`(`id`, `name`) values (3, 'ccc'); #start 347 end 548 time 2017-03-27 15:45:31
insert into `db1`.`t1`(`id`, `name`) values (2, 'bbb'); #start 347 end 548 time 2017-03-27 15:45:31
insert into `db1`.`t1`(`id`, `name`) values (1, 'aaa'); #start 347 end 548 time 2017-03-27 15:45:31
全部将 delete 转换成了insert
我们得到了误操作sql的准确位置在 2604 - 2805 之间，再根据位置进一步过滤，使用flashback模式生成回滚sql，检查回滚sql是否正确
(注：真实环境下，此步经常会进一步筛选出需要的sql。结合grep、编辑器等)
# python binlog2sql.py -h127.0.0.1 -P3307 -uroot -p'123456' -ddb1 -t t1 --start-file='mysql-bin.000005'
use mysql;
flush privileges;
alter user 'root'@'localhost'> flush privileges;
use db1;
create database db1;
use db1;
create table t1(> insert into `db1`.`t1`(`id`, `name`) values (1, 'aaa'); #start 2129 end 2271 time 2017-03-17 15:55:34
insert into `db1`.`t1`(`id`, `name`) values (2, 'bbb'); #start 2359 end 2501 time 2017-03-17 15:55:41
insert into `db1`.`t1`(`id`, `name`) values (3, 'ccc'); #start 2589 end 2731 time 2017-03-17 15:55:54
insert into `db1`.`t1`(`id`, `name`) values (4, 'ddd'); #start 2819 end 2961 time 2017-03-17 15:56:01
可以把 mysql-bin.000005 和 mysql-bin.000006 里面的关于t1表的操作sql合并一下合并到 backup.sql里面
# mysql -S /tmp/mysql3307.sock -uroot -p < backup.sql
Enter password:
mysql> select * from t1;
+----+------+
|> +----+------+
|1 | aaa|
|2 | bbb|
|3 | ccc|
|4 | ddd|
|5 | java |
|6 | php|
+----+------+
6 rows in set (0.00 sec)
页:
[1]