一 前言

前一段時間接二連三的出現開發人員在測試環境和生產誤操作導致數據庫誤刪除/更新，對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 恢復數據的資料請關注腳本之家其它相關文章！