MySQL闪回-binlog2sql

 参考：  https://github.com/danfengcao/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必须设置以下参数: [mysqld] 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
[root@node2 binlog2sql]# 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 statusG *************************** 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位置。误操作人只能知道大致的误操作时间，我们根据大致时间过滤数据。   [root@node2 binlog2sql]# 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

[root@node2 binlog2sql]#  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、编辑器等)
[root@node2 binlog2sql]# 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" identified with "mysql_native_password" as "*6bb4837eb74329105ee4568dda7dc67ed2ca2ad9"; flush privileges; use db1; create database db1; use db1; create table t1( id int not null, name varchar(20), primary key (id) ); 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里面

[root@node2 binlog2sql]# mysql -S /tmp/mysql3307.sock -uroot -p < backup.sql  Enter password: 

mysql> select * from t1; +----+------+ | id | name | +----+------+ |  1 | aaa  | |  2 | bbb  | |  3 | ccc  | |  4 | ddd  | |  5 | java | |  6 | php  | +----+------+ 6 rows in set (0.00 sec)