1. 简介
在 oracle 数据库中, 当一个误操作被提交后, 我们可以通过 oracle 提供的闪回功能将表闪回至误操作之前的状态. mysql 中没有原生的 flushback 功能, DBA 误操作时, 传统的恢复方式是利用全备 + 二进制日志前滚进行恢复.
今天给大家介绍一种使用 python 脚本在 mysql 中实现类似 oracle 中 flushback table 的闪回功能, 相比于传统的全备 + 增备, 本方法更为快速, 简单.
2. 闪回原理
原理: 调用 mysql_rollback.py(脚本在我的另一篇 blog 闪回脚本: 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 表已回滚.
来源: http://blog.51cto.com/13476134/2112181