MySQL恢复误操作的方法

120次阅读

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

一前言
本周接二连三的出现开发人员在测试环境和生产误操作导致数据库误删除 / 更新，对 DBA 而言，回滚数据着实是一件头疼的事情，凡涉及到恢复线上数据必然对应用带来一定的影响。大多数情况是开发误操作 delete 数据，update 多数行，根据之前的操作经验，本文介绍常用的恢复方法。
写本文的时候 Monogdb 也被曝出有被利用安全漏洞，数据被删除了，希望各位 DBA/ 安全相关人员及时查看自己负责的业务数据库安全相关问题，保护好自己的数据。
二常用的恢复方式
2.1 利用备份恢复
使用这种方式的前提必须有最近的备份集或者知道出现误操作起始的 binlog 位点或者 GTID, 利用备份集恢复到中间的机器上，然后利用 MySQL 的 slave 特性

START SLAVE [SQL_THREAD] UNTIL
MASTER_LOG_FILE = ‘log_name’, MASTER_LOG_POS = log_pos;
until_option:
UNTIL {{SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set
| MASTER_LOG_FILE = ‘log_name’, MASTER_LOG_POS = log_pos
| RELAY_LOG_FILE = ‘log_name’, RELAY_LOG_POS = log_pos
| SQL_AFTER_MTS_GAPS }

恢复出到一个临时的实例，将误删除, 更新的数据 dump 出来并恢复到老的实例里面。恢复数据期间的受影响的表最好不可写，否则将难以达到最想要的结果。例如
a=2，被误更新为 a=4，恢复的期间有被更新为 a =7，结果恢复后又恢复为 a =2。
此种恢复方式不适合恢复大量数据库，且需要临时实例。
2.2 利用开源工具 binlog2sql 恢复。
binlog2sql 是大众点评公司的 DBA 开发的一款基于通过解析 binlog 将 delete 恢复为 insert,update 的值 set 字段和 where 条件做对调的原理来恢复数据的。
使用限制 MySQL 的 binlog format 必须是 row
安装

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install –r requirments.txt

用法

usage: binlog2sql.py [–h HOST] [–u USER] [–p PASSWORD] [–P PORT]
[––start–file STARTFILE] [––start–position STARTPOS]
[––stop–file ENDFILE] [––stop–position ENDPOS]
[––start–datetime STARTTIME] [––stop–datetime STOPTIME]
[––stop–never] [––help] [–d [DATABASES [DATABASES ...]]]
[–t [TABLES [TABLES ...]]] [–K] [–B]

例子

create table flashback(
id int(11) not null auto_increment primary key ,
stat int(11) not null default 1
) engine=innodb default charset=utf8;
insert into flashback(stat) values (2),(3),(4),(7),(9),(22),(42),(33),(66),(88)

误操作

update flashback set stat=15

恢复数据的步骤
1 获取误操作的 dml 所在的 binlog，不过一般开发可不知道具体 binlog，他们只知道什么时间误操作了，binlog2sql 支持按照时间范围恢复。

mysql> show master logs;
+––––––––––––––––––+–––––––––––+
| Log_name | File_size |
+––––––––––––––––––+–––––––––––+
| mysql–bin.000009 | 177 |
| mysql–bin.000010 | 464 |
| mysql–bin.000011 | 8209 |
+––––––––––––––––––+–––––––––––+
3 rows in set (0.00 sec)

本例子中 binlog 为 mysql-bin.000011

2 利用 binlog2sql 恢复数据，先解析 binlog 获取 update 语句的起始位点，本例中 start 5087 end 5428

Python binlog2sql.py –h127.0.0.1 –P3307 –udba –p‘dbadmin’ –dyang –tflashback ––start–file=‘mysql-bin.000011’

MySQL 恢复误操作的方法
使用 binlog2sql -B 参数得到恢复的 sql

将获取到的 sql 执行到数据库，假如生产环境中真的发生了问题，一定要和开发沟通并且确认需要恢复的确切记录。

mysql> select * from flashback;
+––––+––––––+
| id | stat |
+––––+––––––+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 7 |
| 5 | 9 |
| 6 | 22 |
| 7 | 42 |
| 8 | 33 |
| 9 | 66 |
| 10 | 88 |
+––––+––––––+
10 rows in set (0.00 sec)

binlog2sql 的限制
mysql server 必须开启，离线模式下不能解析
优点（对比 mysqlbinlog）
纯 Python 开发，安装与使用都很简单
自带 flashback、no-primary-key 解析模式，无需再装补丁
flashback 模式下，更适合闪回实战
解析为标准 SQL，方便理解、调试
代码容易改造，可以支持更多个性化解析
其实 MySQL 还提供了一个参数 sql_safe_updates，该参数将禁止不带 where 条件的 delete 和 update 语句。具体用法和介绍还请参考MySQL 官方介绍

三总结
本文简单介绍了两种恢复误操作数据的方法，其实还有其他的方式比如使用 mysqlbinlog 编写脚本来恢复数据，利用闪回的 patch 或者去哪儿的 inception 等等，大家可以继续去研究。保护数据安全乃 DBA 的基本职责，每年都有各种
因为数据被误删除导致的惨案。希望每个 DBA 都能守护好自己的生命线。

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-01/139390.htm

正文完

星哥说事-微信公众号