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

152次阅读

共计 4563 个字符，预计需要花费 12 分钟才能阅读完成。

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 表已回滚。

mysql_rollback.py 脚本 可以到 Linux 公社资源站下载：

—————————————— 分割线 ——————————————

免费下载地址在 http://linux.linuxidc.com/

用户名与密码都是www.linuxidc.com

具体下载目录在 /2018 年资料 / 5 月 / 3 日 / 使用 Python 脚本实现 MySQL 误操作的快速回滚 /

下载方法见 http://www.linuxidc.com/Linux/2013-07/87684.htm

—————————————— 分割线 ——————————————

正文完

星哥说事-微信公众号