使用Python脚本实现MySQL误操作的快速回滚

1.简介 在Oracle数据库中，当一个误操作被提交后，我们可以通过Oracle提供的闪回功能将表闪回至误操作之前的状态。mysql中没有原生的flushback功能，DBA误操作时，传统的恢复方式是利用全备+二进制日志前滚进行恢复。

今天给大家介绍一种使用Python脚本在MySQL中实现类似Oracle中flushback table的闪回功能，相比于传统的全备+增备，本方法更为快速、简单。

2.闪回原理 原理：调用mysql_rollback.py（下载见本文最后）对rows格式的binlog进行逆向操作，delete反向生成insert、update生成反向的update、insert反向生成delete。

3.说明 0、需安装python及MySQLdb模块 1、binlog的格式必须为row 2、要恢复的表操作前后表结构没有发生变更，否则脚本无法解析 3、只生成DML(insert/update/delete)的rollback语句，DDL语句不可回滚 4、最终生成的SQL是逆序的，所以最新的DML会生成在输入文件的最前面，并且带上了时间戳和偏移点，方便查找目标 5、需要提供一个连接MySQL的只读用户，主要是为了获取表结构 6、如果binlog过大，建议带上时间范围，也可以指定只恢复某个库的SQL 7、SQL生成后，请务必在测试环境上测试恢复后再应用到线上

4.实战 step1.登陆mysql查看表信息

mysql> use db1 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed mysql> select * from test; +------+-------+------+---------+----------+ | id  | name  | age  | country | city    | +------+-------+------+---------+----------+ |    1 | alex  |  26 | china  | shanghai | |    2 | bob  |  25 | britain | london  | |    3 | simon |  24 | france  | paris    | +------+-------+------+---------+----------+ 3 rows in set (0.00 sec)

step2.模拟误操作（update）

mysql> update test set country='europe' where name='bob';  --bob的国家被改为europe Query OK, 1 row affected (0.01 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> select * from test; +------+-------+------+---------+----------+ | id  | name  | age  | country | city    | +------+-------+------+---------+----------+ |    1 | alex  |  26 | china  | shanghai | |    2 | bob  |  25 | europe  | london  | |    3 | simon |  24 | france  | paris    | +------+-------+------+---------+----------+ 3 rows in set (0.00 sec)

mysql> exit; Bye

step3.分析binlog并生成反向语句 找到最新的binlog

SZD-L0087668:gzz3306:Master > ll -rw-rw---- 1 mysql mysql      167 May  2 14:30 mysql-bin.000001 -rw-rw---- 1 mysql mysql 11400402 May  2 19:28 mysql-bin.000002 -rw-rw---- 1 mysql mysql    1807 May  2 19:49 mysql-bin.000003 -rw-rw---- 1 mysql mysql      660 May  2 20:10 mysql-bin.000004 -rw-rw---- 1 mysql mysql      403 May  2 20:10 mysql-bin.000005 -rw-rw---- 1 mysql mysql      584 May  3 10:45 mysql-bin.000006 -rw-rw---- 1 mysql mysql      417 May  3 10:53 mysql-bin.000007 -rw-rw---- 1 mysql mysql    1973 May  3 13:28 mysql-bin.000008 -rw-rw---- 1 mysql mysql    2604 May  3 14:13 **mysql-bin.000009** -rw-rw---- 1 mysql mysql      369 May  3 13:28 mysql-bin.index -rw-r--r-- 1 root  root    12222 Apr 13  2017 mysql_rollback.py

根据关键词europe查找binlog中的误操作sql，并输出europe前后30行（行数视具体情况而定，一定要输出语句对应的BEGIN和COMMIT部分）

SZD-L0087668:gzz3306:Master > mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS --set-charset=utf8 mysql-bin.000009 | grep -C 30 'europe' ... BEGIN /*!*/; # at 2426 #180503 14:13:36 server id 1  end_log_pos 2482 CRC32 0xe79b9612        Table_map: `db1`.`test` mapped to number 76 # at 2482 #180503 14:13:36 server id 1  end_log_pos 2573 CRC32 0xacd94a0b        Update_rows: table id 76 flags: STMT_END_F ### UPDATE `db1`.`test` ### WHERE ###  @1=2 /* INT meta=0 nullable=1 is_null=0 */ ###  @2='bob' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ ###  @3=25 /* INT meta=0 nullable=1 is_null=0 */ ###  @4='britain' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ ###  @5='london' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ ### SET ###  @1=2 /* INT meta=0 nullable=1 is_null=0 */ ###  @2='bob' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ ###  @3=25 /* INT meta=0 nullable=1 is_null=0 */ ###  @4='europe' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ ###  @5='london' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ # at 2573 #180503 14:13:36 server id 1  end_log_pos 2604 CRC32 0x63b3d5fa        Xid = 118 COMMIT/*!*/; ...

选取2426和2604作为分析binlog的起始位置

SZD-L0087668:gzz3306:Master > python2.7 mysql_rollback.py -f mysql-bin.000009 -o rollback.sql -h127.0.0.1 -P3306 -uroot -p123456 --start-position='2426' --stop-position='2604' -d db1 正在获取参数..... 正在解析binlog..... 正在初始化列名..... 正在开始拼凑sql..... done!

查看rollback.sql中误操作的逆向语句

SZD-L0087668:gzz3306:Master > cat rollback.sql ## at 2482 ##180503 14:13:36 server id 1  end_log_pos 2573 CRC32 0xacd94a0b        Update_rows: table id 76 flags: STMT_END_F UPDATE `db1`.`test` SET   id=2   ,name='bob'   ,age=25   ,country='britain'   ,city='london' WHERE   id=2   AND name='bob'   AND age=25   AND country='europe'   AND city='london';

step4.回滚

SZD-L0087668:gzz3306:Master > mysql -uroot -p <rollback.sql Enter password: SZD-L0087668:gzz3306:Master > mysql -uroot -p -e 'select * from db1.test'; Enter password: +------+-------+------+---------+----------+ | id  | name  | age  | country | city    | +------+-------+------+---------+----------+ |    1 | alex  |  26 | china  | shanghai | |    2 | bob  |  25 | britain | london  | |    3 | simon |  24 | france  | paris    | +------+-------+------+---------+----------+

test表已回滚。