前奏
DBA/开发 工作过程中误删数据、误改数据是常有的事，作为 DBA 如何快速填坑呢
（1）利用最近的全量备份+增量binlog备份，恢复到误操作之前的状态，但是随着数据量的增大，binlog的增多，恢复起来很费时。
（2）如果binlog的格式为row，那么就可以将binlog解析出来生成反向的原始SQL
当然还有其他的一些操作方法，这里暂不展开来讲，我们今天主要介绍binlog2sql
大众点评开源的一个 MySQL 闪回工具 -- binlog2sql
闪回原理
binlog 概述：
MySQL binlog 以event 的形式，记录了 MySQL server 从启用 binlog 以来的所有变更信息，能够帮实现这之间的所有变化。
MySQL 引用 binglog 的主要目的：一、主从复制；二、某些备份还原操作需要重新应用 binlog
既然 binlog 以 event 形式记录了所有的变更信息，那么我们把需要回滚的event，从后往前回滚回去即可。
闪回前提：log_bin 为 ON；binlog_row_image 为full；binlog_format 为 row；
| log_bin | ON | | binlog_row_image | full | | binlog_format | ROW |
回滚操作：
- 对于 delete 操作，我们从 binlog 提取出 delete 信息，反向生成 insert 回滚语句；
- 对于 insert 操作，反向生成 delete 回滚语句；
- 对于update操作，回滚sql应该交换SET和WHERE的值。
闪回实战
(一) 安装binlog2sql
(root@localhost) [employees]> select * from titles where emp_no <= 10007 ; +--------+-----------------+------------+---------+ | emp_no | title | from_date | to_date | +--------+-----------------+------------+---------+ | 10001 | Senior | 1986-06-26 | NULL | | 10002 | Staff | 1996-08-03 | NULL | | 10003 | Senior Engineer | 1995-12-03 | NULL | | 10004 | Engineer | 1986-12-01 | NULL | | 10004 | Senior Engineer | 1995-12-01 | NULL | | 10005 | Senior Staff | 1996-09-12 | NULL | | 10005 | Staff | 1989-09-12 | NULL | | 10006 | Senior Engineer | 1990-08-05 | NULL | | 10007 | Senior Staff | 1996-02-11 | NULL | | 10007 | Staff | 1989-02-10 | NULL | +--------+-----------------+------------+---------+ 10 rows in set (0.00 sec) (root@localhost) [employees]> delete from titles where emp_no <= 10007 ; Query OK, 10 rows affected (0.00 sec) (root@localhost) [employees]> select * from titles where emp_no <= 10007 ; Empty set (0.00 sec)
(root@localhost) [employees]> show master statusG
*************************** 1. row ***************************
File: mysql-bin.000015
Position: 364596
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
(root@localhost) [employees]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-01-23 16:26:43 |
+---------------------+
1 row in set (0.00 sec)
[root@05 binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p123 -demployees -t titles --start-file=mysql-bin.000015 --start-datetime='2019-01-23 16:20:04'
[root@05 binlog2sql]# python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uroot -p123 -demployees -t titles --start-file=mysql-bin.000015 --start-datetime='2019-01-23 16:20:04' >tit.sql
[root@05 binlog2sql]# mysql -uroot -p123 --database employees < tit.sql
[root@05 binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p123 -demployees -t titles --start-file=mysql-bin.000015 --start-datetime='2019-01-23 16:20:04'
DELETE FROM `employees`.`titles` WHERE `emp_no`=10001 AND `to_date` IS NULL AND `from_date`='1986-06-26' AND `title`='Senior' LIMIT 1; #start 364141 end 364565 time 2019-01-23 16:23:59
DELETE FROM `employees`.`titles` WHERE `emp_no`=10002 AND `to_date` IS NULL AND `from_date`='1996-08-03' AND `title`='Staff' LIMIT 1; #start 364141 end 364565 time 2019-01-23 16:23:59
DELETE FROM `employees`.`titles` WHERE `emp_no`=10003 AND `to_date` IS NULL AND `from_date`='1995-12-03' AND `title`='Senior Engineer' LIMIT 1; #start 364141 end 364565 time 2019-01-23 16:23:59
DELETE FROM `employees`.`titles` WHERE `emp_no`=10004 AND `to_date` IS NULL AND `from_date`='1986-12-01' AND `title`='Engineer' LIMIT 1; #start 364141 end 364565 time 2019-01-23 16:23:59
DELETE FROM `employees`.`titles` WHERE `emp_no`=10004 AND `to_date` IS NULL AND `from_date`='1995-12-01' AND `title`='Senior Engineer' LIMIT 1; #start 364141 end 364565 time 2019-01-23 16:23:59
DELETE FROM `employees`.`titles` WHERE `emp_no`=10005 AND `to_date` IS NULL AND `from_date`='1996-09-12' AND `title`='Senior Staff' LIMIT 1; #start 364141 end 364565 time 2019-01-23 16:23:59
DELETE FROM `employees`.`titles` WHERE `emp_no`=10005 AND `to_date` IS NULL AND `from_date`='1989-09-12' AND `title`='Staff' LIMIT 1; #start 364141 end 364565 time 2019-01-23 16:23:59
DELETE FROM `employees`.`titles` WHERE `emp_no`=10006 AND `to_date` IS NULL AND `from_date`='1990-08-05' AND `title`='Senior Engineer' LIMIT 1; #start 364141 end 364565 time 2019-01-23 16:23:59
DELETE FROM `employees`.`titles` WHERE `emp_no`=10007 AND `to_date` IS NULL AND `from_date`='1996-02-11' AND `title`='Senior Staff' LIMIT 1; #start 364141 end 364565 time 2019-01-23 16:23:59
DELETE FROM `employees`.`titles` WHERE `emp_no`=10007 AND `to_date` IS NULL AND `from_date`='1989-02-10' AND `title`='Staff' LIMIT 1; #start 364141 end 364565 time 2019-01-23 16:23:59
You have mail in /var/mail/root
[root@05 binlog2sql]# python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uroot -p123 -demployees -t titles --start-file=mysql-bin.000015 --start-datetime='2019-01-23 16:20:04' >tit.sql
[root@05 binlog2sql]# mysql -uroot -p123 --database employees < tit.sql
(root@localhost) [employees]> select * from titles where emp_no <= 10007 ;
+--------+-----------------+------------+---------+
| emp_no | title | from_date | to_date |
+--------+-----------------+------------+---------+
| 10001 | Senior | 1986-06-26 | NULL |
| 10002 | Staff | 1996-08-03 | NULL |
| 10003 | Senior Engineer | 1995-12-03 | NULL |
| 10004 | Engineer | 1986-12-01 | NULL |
| 10004 | Senior Engineer | 1995-12-01 | NULL |
| 10005 | Senior Staff | 1996-09-12 | NULL |
| 10005 | Staff | 1989-09-12 | NULL |
| 10006 | Senior Engineer | 1990-08-05 | NULL |
| 10007 | Senior Staff | 1996-02-11 | NULL |
| 10007 | Staff | 1989-02-10 | NULL |
+--------+-----------------+------------+---------+
10 rows in set (0.01 sec
TIPS
- 闪回的目标：快速筛选出真正需要回滚的数据。
- 先根据库、表、时间做一次过滤，再根据位置做更准确的过滤。
- 由于数据一直在写入，要确保回滚sql中不包含其他数据。可根据是否是同一事务、误操作行数、字段值的特征等等来帮助判断。
- 执行回滚sql时如有报错，需要查实具体原因，一般是因为对应的数据已发生变化。由于是严格的行模式，只要有唯一键(包括主键)存在，就只会报某条数据不存在的错，不必担心会更新不该操作的数据。业务如果有特殊逻辑，数据回滚可能会带来影响。
- 如果只回滚某张表，并且该表有关联表，关联表并不会被回滚，需与业务方沟通清楚
哪些数据需要回滚，让业务方来判断！
MySQL binlog2sql的更多内容可参考：
https://github.com/danfengcao/binlog2sql/blob/master/example/mysql-flashback-priciple-and-practice.md