Mysql误操作后使用binlog2sql快速回滚

Mysql误操作后使用binlog2sql快速回滚
一、binlog2sql安装
从mysql binlog解析出你要的sql。根据不同选项，你可以得到原始sql、回滚sql、去除主键的insert sql等。
1.1、用途
数据快速回滚(闪回)
主从切换后数据不一致的修复
从binlog生成标准SQL，带来的衍生功能
1.2、安装
[root@foidndb ~]# cd /usr/local/
[root@foidndb local]# git clone git://github.com/danfengcao/binlog2sql.git

[root@foidndb binlog2sql]# pip install -r requirements.txt

MySQL的配置要开启以下选项：

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full



2.3 user需要的最小权限集合
select, super/replication client, replication slave权限
建议授权：
mysql > grant select,replication slave,replication client on *.* to flashback@'localhost' identified by 'Shiyu!520';
mysql > grant select,replication slave,replication client on *.* to flashback@'127.0.0.1' identified by 'Shiyu!520';

2.4基本用法
解析出标准SQL

shell> python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -ddatabase -t table1 table2 --start-file='mysql-bin.000040' --start-datetime='2018-07-09 18:30:00' --stop-datetime='2018-07-09 18:40:00' --start-pos=1240

解析出回滚SQL

shell> python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttest3 --start-file='mysql-bin.000040' --start-position=763 --stop-position=1147

三、测试
3.1 新建表test
mysql> create table yuhuashi(id int,name varchar(10));
Query OK, 0 rows affected (0.26 sec)

mysql> insert into yuhuashi values(1,'s');
Query OK, 1 row affected (0.06 sec)

mysql> insert into yuhuashi values(2,'h');
Query OK, 1 row affected (0.06 sec)

mysql> insert into yuhuashi values(3,'i');
Query OK, 1 row affected (0.05 sec)

mysql> insert into yuhuashi values(4,'y');
Query OK, 1 row affected (0.07 sec)

mysql> insert into yuhuashi values(5,'u');
Query OK, 1 row affected (0.06 sec)

mysql> select * from yuhuashi;
+------+------+
| id   | name |
+------+------+
|    1 | s    |
|    2 | h    |
|    3 | i    |
|    4 | y    |
|    5 | u    |
+------+------+
5 rows in set (0.00 sec)

停个5分钟以后执行一下命令：

mysql> delete from yuhuashi where id>=3;
Query OK, 3 rows affected (0.05 sec)


3.2 解析标准sql
[root@foidndb ~]# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py -uflashback -pShiyu'!'520 -dmonitor -tyuhuashi --start-file='mysql-bin.000040' --start-datetime='2018-07-09 17:16:00' --stop-datetime='2018-07-09 17:19:00' > yuhuashi.sql
[root@foidndb ~]# cat yuhuashi.sql
DELETE FROM `monitor`.`yuhuashi` WHERE `id`=3 AND `name`='i' LIMIT 1; #start 25735066 end 25735319 time 2018-07-09 17:18:14
DELETE FROM `monitor`.`yuhuashi` WHERE `id`=4 AND `name`='y' LIMIT 1; #start 25735066 end 25735319 time 2018-07-09 17:18:14
DELETE FROM `monitor`.`yuhuashi` WHERE `id`=5 AND `name`='u' LIMIT 1; #start 25735066 end 25735319 time 2018-07-09 17:18:14

3.3 解析出回滚SQL
[root@foidndb ~]# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py -uflashback -pShiyu'!'520 -dmonitor -tyuhuashi --start-file='mysql-bin.000040' --start-datetime='2018-07-09 17:16:00' --stop-datetime='2018-07-09 17:19:00' -B >rollback.sql
[root@foidndb ~]# cat rollback.sql
INSERT INTO `monitor`.`yuhuashi`(`id`, `name`) VALUES (5, 'u'); #start 25735066 end 25735319 time 2018-07-09 17:18:14
INSERT INTO `monitor`.`yuhuashi`(`id`, `name`) VALUES (4, 'y'); #start 25735066 end 25735319 time 2018-07-09 17:18:14
INSERT INTO `monitor`.`yuhuashi`(`id`, `name`) VALUES (3, 'i'); #start 25735066 end 25735319 time 2018-07-09 17:18:14

mysql连接配置
   -h host; -P port; -u user; -p password
解析模式
  --realtime 持续同步binlog。可选。不加则同步至执行命令时最新的binlog位置。
  --popPk 对INSERT语句去除主键。可选。
  -B, --flashback 生成回滚语句。可选。与realtime或popPk不能同时添加。
解析范围控制
  --start-file 起始解析文件。必须。
  --start-pos start-file的起始解析位置。可选。默认为start-file的起始位置；
  --end-file 末尾解析文件。可选。默认为start-file同一个文件。若解析模式为realtime，此选项失效。
  --end-pos end-file的末尾解析位置。可选。默认为end-file的最末位置；若解析模式为realtime，此选项失效。
对象过滤
   -d, --databases 只输出目标db的sql。可选。默认为空。
   -t, --tables 只输出目标tables的sql。可选。默认为空。
   
   
3.4、开始回滚
[root@foidndb ~]# mysql -u root -p < rollback.sql
Enter password:
3.5登录数据库验证
 mysql> select * from yuhuashi;
+------+------+
| id   | name |
+------+------+
|    1 | s    |
|    2 | h    |
|    5 | u    |
|    4 | y    |
|    3 | i    |
+------+------+
5 rows in set (0.00 sec)
四 、注意事项

4.1、在配置文件中设置了以下参数：
server_id = 1
log_bin = /data/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full # 默认

4.2、在闪回的时候必须启动 MySQL 服务

因为它是通过 BINLOG_DUMP 协议来获取 binlog 内容，需要读取server端 information_schema.COLUMNS 表，来获取表结构的元信息，才能拼接成 SQL 语句。因此需要给用户提供的最小权限如下：
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user'@'%';
源码中，主要是使用 python-mysql-replication 作为实时解析 MySQL binlog 来获取各个 EVENT。
python-mysql-replication 实现了 MySQL 复制协议，客户端伪装成 slave 来获取主的 binlog 和 EVENT。

4.3、insert、update、delete大部分时候可以解析出来标准sql和回滚sql

一种情况例外:insert、updete、delete操作之后，drop/truncate table。 此时虽然在binlog中记录了所有的event，但是使用binlog2sql生成标准sql、回滚sql的时候已经找不到了dml操作的相应的表

4.4、DDL无法使用binlog2sql闪回数据。