MySQL闪回-binlog2sql

dyok

　　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 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位置。误操作人只能知道大致的误操作时间，我们根据大致时间过滤数据。
　　[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'>　　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里面
　　[root@node2 binlog2sql]# 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)