【MySQL】恢復誤操作的方法

kunlunzhiying發表於2017-08-25
MySql
一 前言
  本週接二連三的出現開發人員在測試環境和生產誤操作導致資料庫誤刪除/更新，對DBA而言，回滾資料著實是一件頭疼的事情，凡涉及到恢復線上資料必然對應用帶來一定的影響。大多數情況是開發誤操作delete資料，update多數行，根據之前的操作經驗，本文介紹常用的恢復方法。
  寫本文的時候 Monogdb 也被曝出有被利用安全漏洞，資料被刪除了，希望各位DBA/安全相關人員及時檢視自己負責的業務資料庫安全相關問題，保護好自己的資料。
二常用的恢復方式
2.1 利用備份恢復
  使用這種方式的前提必須有最近的備份集或者知道出現誤操作起始的binlog 位點或者GTID,利用備份集恢復到中間的機器上，然後利用MySQL的slave 特性
  1. START SLAVE [SQL_THREAD] UNTIL
  2.     MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos;
  3. until_option:
  4.     UNTIL { {SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set
  5.           | MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
  6.           | RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
  7.           | 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 
安裝
  1. git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
  2. pip install -r requirments.txt
用法
  1. usage: binlog2sql.py [-h HOST] [-u USER] [-p PASSWORD] [-P PORT]
  2.                      [--start-file STARTFILE] [--start-position STARTPOS]
  3.                      [--stop-file ENDFILE] [--stop-position ENDPOS]
  4.                      [--start-datetime STARTTIME] [--stop-datetime STOPTIME]
  5.                      [--stop-never] [--help] [-d [DATABASES [DATABASES ...]]]
  6.                      [-t [TABLES [TABLES ...]]] [-K] [-B]
例子
  1. create table flashback(
  2. id int(11) not null auto_increment primary key ,
  3. stat int(11) not null default 1
  4. ) engine=innodb default charset=utf8;
  5. insert into flashback(stat) values (2),(3),(4),(7),(9),(22),(42),(33),(66),(88)
誤操作
  1. update flashback set stat=15
恢復資料的步驟
1 獲取誤操作的dml所在的binlog，不過一般開發可不知道具體binlog，他們只知道什麼時間誤操作了，binlog2sql支援按照時間範圍恢復。
  1. mysql> show master logs;
  2. +------------------+-----------+
  3. | Log_name | File_size |
  4. +------------------+-----------+
  5. | mysql-bin.000009 | 177 |
  6. | mysql-bin.000010 | 464 |
  7. | mysql-bin.000011 | 8209 |
  8. +------------------+-----------+
  9. 3 rows in set (0.00 sec)
本例子中binlog為mysql-bin.000011
2 利用binlog2sql 恢復資料，先解析binlog獲取 update 語句的起始位點，本例中  start 5087 end 5428
  1. python binlog2sql.py -h127.0.0.1 -P3307 -udba -p'dbadmin' -dyang -tflashback --start-file='mysql-bin.000011'

使用binlog2sql -B 引數得到恢復的sql

將獲取到的sql 執行到資料庫，假如生產環境中真的發生了問題，一定要和開發溝通並且確認需要恢復的確切記錄。
  1. mysql> select * from flashback;
  2. +----+------+
  3. | id | stat |
  4. +----+------+
  5. | 1 | 2 |
  6. | 2 | 3 |
  7. | 3 | 4 |
  8. | 4 | 7 |
  9. | 5 | 9 |
  10. | 6 | 22 |
  11. | 7 | 42 |
  12. | 8 | 33 |
  13. | 9 | 66 |
  14. | 10 | 88 |
  15. +----+------+
  16. 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 都能守護好自己的生命線。推薦幾篇關於資料恢復相關的文章
[1] mysqlbinlog flashback 5.6完全使用手冊與原理
[2] 拿走不謝，Flashback for MySQL 5.7
[3] MySQL閃回方案討論及實現
[4] danfengcao, binlog2sql: Parse MySQL binlog to SQL you want  ---本文介紹的工具 
[5] MySQL下實現閃回的設計思路 (MySQL Flashback Feature) 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-2132054/，如需轉載，請註明出處，否則將追究法律責任。

相關文章