MySQL 两种恢复数据的方法

MySQL 两种恢复数据的方法

一 前言

前一段时间接二连三的出现开发人员在测试环境和生产误 *** 作导致数据库误删除/更新，对DBA而言，回滚数据着实是一件头疼的事情，凡涉及到恢复线上数据必然对应用带来一定的影响。大多数情况是开发误 *** 作delete数据，update多数行，根据之前的 *** 作经验，本文介绍常用的恢复方法。

二 常用的恢复方式

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 requirements.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] 
           [-d [DATABASES [DATABASES ...]]]
           [-t [TABLES [TABLES ...]]] 
           [-K] [-B]
           [--help] 

例子

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'

使用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 都能守护好自己的生命线。

以上就是MySQL 两种恢复数据的方法的详细内容，更多关于MySQL 恢复数据的资料请关注脚本之家其它相关文章！