Categories
程式開發

誤刪庫刪表後的挽救措施


寫在前面

雖然我們之前遇到的大多數的數據被刪,都是運維同學或者 DBA 背鍋的。但實際上,只要有數據操作權限的同學,都有可能踩到誤刪數據這條線。

今天我們就來聊聊誤刪數據前後,我們可以做些什麼,減少誤刪數據的風險,和由誤刪數據帶來的損失。

MySQL 為例,為了找到解決誤刪數據的更高效的方法,我們需要先對和 MySQL 相關的誤刪數據,做下分類:

  1. 使用 delete 語句誤刪數據行;
  2. 使用 drop table 或者 truncate table 語句誤刪數據表;
  3. 使用 drop database 語句誤刪數據庫;
  4. 使用 rm 命令誤刪整個 MySQL 實例。

誤刪行如果是使用 delete 語句誤刪了數據行,可以用 Flashback 工具通過閃回把數據恢復回來。

Flashback 恢復數據的原理,是修改 binlog 的內容,拿回原庫重放。而能夠使用這個方案的前提是,需要確保 binlog_format=row 和 binlog_row_image=FULL。

具體恢復數據時,對單個事務做如下處理:

  1. 對於 insert 語句,對應的 binlog event 類型是 Write_rows event,把它改成 Delete_rows event 即可;
  2. 同理,對於 delete 語句,也是將 Delete_rows event 改為 Write_rows event;
  3. 而如果是 Update_rows 的話,binlog 裡面記錄了數據行修改前和修改後的值,對調這兩行的位置即可。

如果誤操作不是一個,而是多個,會怎麼樣呢?比如下面三個事務:

(A)delete ...
(B)insert ...
(C)update ...

現在要把數據庫恢復回這三個事務操作之前的狀態,用 Flashback 工具解析 binlog 後,寫回主庫的命令是:

(reverse C)update ...
(reverse B)delete ...
(reverse A)insert ...

也就是說,如果誤刪數據涉及到了多個事務的話,需要將事務的順序調過來再執行。

需要說明的是,我不建議你直接在主庫上執行這些操作。

恢復數據比較安全的做法,是恢復出一個備份,或者找一個從庫作為臨時庫,在這個臨時庫上執行這些操作,然後再將確認過的臨時庫的數據,恢復回主庫。

為什麼要這麼做呢?

這是因為,一個在執行線上邏輯的主庫,數據狀態的變更往往是有關聯的。可能由於發現數據問題的時間晚了一點兒,就導致已經在之前誤操作的基礎上,業務代碼邏輯又繼續修改了其他數據。所以,如果這時候單獨恢復這幾行數據,而又未經確認的話,就可能會出現對數據的二次破壞。

當然,我們不止要說誤刪數據的事後處理辦法,更重要是要做到事前預防。我有以下兩個建議:

  1. 把 sql_safe_updates 參數設置為 on。這樣一來,如果我們忘記在 delete 或者 update 語句中寫 where 條件,或者 where 條件裡面沒有包含索引字段的話,這條語句的執行就會報錯。
  2. 代碼上線前,必須經過 SQL 審計。

你可能會說,設置了 sql_safe_updates=on,如果我真的要把一個小表的數據全部刪掉,應該怎麼辦呢?

如果你確定這個刪除操作沒問題的話,可以在 delete 語句中加上 where 條件,比如 where id>=0。

但是,delete 全表是很慢的,需要生成回滾日誌、寫 redo、寫 binlog。所以,從性能角度考慮,你應該優先考慮使用 truncate table 或者 drop table 命令。

使用 delete 命令刪除的數據,你還可以用 Flashback 來恢復。而使用 truncate /drop table 和 drop database 命令刪除的數據,就沒辦法通過 Flashback 來恢復了。為什麼呢?

這是因為,即使我們配置了 binlog_format=row,執行這三個命令時,記錄的 binlog 還是 statement 格式。 binlog 裡面就只有一個 truncate/drop 語句,這些信息是恢復不出數據的。

那麼,如果我們真的是使用這幾條命令誤刪數據了,又該怎麼辦呢?

誤刪庫 / 表

這種情況下,要想恢復數據,就需要使用全量備份,加增量日誌的方式了。這個方案要求線上有定期的全量備份,並且實時備份 binlog。

在這兩個條件都具備的情況下,假如有人中午 12 點誤刪了一個庫,恢復數據的流程如下:

  1. 取最近一次全量備份,假設這個庫是一天一備,上次備份是當天 0 點;
  2. 用備份恢復出一個臨時庫;
  3. 從日誌備份裡面,取出凌晨 0 點之後的日誌;
  4. 把這些日誌,除了誤刪除數據的語句外,全部應用到臨時庫。

這個流程的示意圖如下所示:

誤刪庫刪表後的挽救措施 1

圖 1 數據恢復流程 -mysqlbinlog 方法

關於這個過程,我需要和你說明如下幾點:

  1. 為了加速數據恢復,如果這個臨時庫上有多個數據庫,你可以在使用 mysqlbinlog 命令時,加上一個 –database 參數,用來指定誤刪表所在的庫。這樣,就避免了在恢復數據時還要應用其他庫日誌的情況。

  2. 在應用日誌的時候,需要跳過 12 點誤操作的那個語句的 binlog:

    如果原實例沒有使用GTID 模式,只能在應用到包含12 點的binlog 文件的時候,先用–stop-position 參數執行到誤操作之前的日誌,然後再用–start-position 從誤操作之後的日誌繼續執行;

    如果實例使用了 GTID 模式,就方便多了。假設誤操作命令的GTID 是gtid1,那麼只需要執行set gtid_next=gtid1;begin;commit; 先把這個GTID 加到臨時實例的GTID 集合,之後按順序執行binlog 的時候,就會自動跳過誤操作的語句。

不過,即使這樣,使用 mysqlbinlog 方法恢復數據還是不夠快,主要原因有兩個:

  1. 如果是誤刪表,最好就是只恢復出這張表,也就是只重放這張表的操作,但是 mysqlbinlog 工具並不能指定只解析一個表的日誌;
  2. 用 mysqlbinlog 解析出日誌應用,應用日誌的過程就只能是單線程。我們在第 26 篇文章中介紹的那些並行複制的方法,在這裡都用不上。

一種加速的方法是,在用備份恢復出臨時實例之後,將這個臨時實例設置成線上備庫的從庫,這樣:

  1. 在 start slave 之前,先通過執行 change replication filter replicate_do_table = (tbl_name) 命令,就可以讓臨時庫只同步誤操作的表;
  2. 這樣做也可以用上並行複制技術,來加速整個數據恢復過程。

這個過程的示意圖如下所示:

誤刪庫刪表後的挽救措施 2

圖 2 數據恢復流程 -master-slave 方法

可以看到,圖中binlog 備份系統到線上備庫有一條虛線,是指如果由於時間太久,備庫上已經刪除了臨時實例需要的binlog 的話,我們可以從binlog 備份系統中找到需要的binlog ,再放回備庫中。

假設,我們發現當前臨時實例需要的 binlog 是從 master.000005 開始的,但是在備庫上執行 show binlogs 顯示的最小的 binlog 文件是 master.000007,意味著少了兩個 binlog 文件。這時,我們就需要去 binlog 備份系統中找到這兩個文件。

把之前刪掉的 binlog 放回備庫的操作步驟,是這樣的:

  1. 從備份系統下載 master.000005 和 master.000006 這兩個文件,放到備庫的日誌目錄下;
  2. 打開日誌目錄下的 master.index 文件,在文件開頭加入兩行,內容分別是 “./master.000005”和“./master.000006”;
  3. 重啟備庫,目的是要讓備庫重新識別這兩個日誌文件;
  4. 現在這個備庫上就有了臨時庫需要的所有 binlog 了,建立主備關係,就可以正常同步了。

不論是把mysqlbinlog 工具解析出的binlog 文件應用到臨時庫,還是把臨時庫接到備庫上,這兩個方案的共同點是:誤刪庫或者表後,恢復數據的思路主要就是通過備份,再加上應用binlog 的方式。

也就是說,這兩個方案都要求備份系統定期備份全量日誌,而且需要確保 binlog 在被從本地刪除之前已經做了備份。

但是,一個系統不可能備份無限的日誌,你還需要根據成本和磁盤空間資源,設定一個日誌保留的天數。如果你的 DBA 團隊告訴你,可以保證把某個實例恢復到半個月內的任意時間點,這就表示備份系統保留的日誌時間就至少是半個月。

另外,我建議你不論使用上述哪種方式,都要把這個數據恢復功能做成自動化工具,並且經常拿出來演練。為什麼這麼說呢?

這裡的原因,主要包括兩個方面:

  1. 雖然“發生這種事,大家都不想的”,但是萬一出現了誤刪事件,能夠快速恢復數據,將損失降到最小,也應該不用跑路了。
  2. 而如果臨時再手忙腳亂地手動操作,最後又誤操作了,對業務造成了二次傷害,那就說不過去了。

延遲複製備庫

雖然我們可以通過利用並行複制來加速恢復數據的過程,但是這個方案仍然存在“恢復時間不可控”的問題。

如果一個庫的備份特別大,或者誤操作的時間距離上一個全量備份的時間較長,比如一周一備的實例,在備份之後的第6 天發生誤操作,那就需要恢復6 天的日誌,這個恢復時間可能是要按天來計算的。

那麼,我們 有什麼方法可以縮短恢復數據需要的時間呢?

如果有非常核心的業務,不允許太長的恢復時間,我們可以考慮搭建延遲複製的備庫。這個功能是 MySQL 5.6 版本引入的。

一般的主備複製結構存在的問題是,如果主庫上有個表被誤刪了,這個命令很快也會被發給所有從庫,進而導致所有從庫的數據表也都一起被誤刪了。

延遲複製的備庫是一種特殊的備庫,通過 CHANGE MASTER TO MASTER_DELAY = N 命令,可以指定這個備庫持續保持跟主庫有 N 秒的延遲。

比如你把 N 設置為 3600,這就代表瞭如果主庫上有數據被誤刪了,並且在 1 小時內發現了這個誤操作命令,這個命令就還沒有在這個延遲複製的備庫執行。這時候到這個備庫上執行 stop slave,再通過之前介紹的方法,跳過誤操作命令,就可以恢復出需要的數據。

這樣的話,你就隨時可以得到一個,只需要最多再追 1 小時,就可以恢復出數據的臨時實例,也就縮短了整個數據恢復需要的時間。

預防誤刪庫 / 表的方法

雖然常在河邊走,很難不濕鞋,但終究還是可以找到一些方法來避免的。所以這裡,我也會給你一些減少誤刪操作風險的建議。

第一條建議是,賬號分離。這樣做的目的是,避免寫錯命令。比如:

  • 我們只給業務開發同學 DML 權限,而不給 truncate/drop 權限。而如果業務開發人員有 DDL 需求的話,也可以通過開發管理系統得到支持。
  • 即使是 DBA 團隊成員,日常也都規定只使用只讀賬號,必要的時候才使用有更新權限的賬號。

第二條建議是,制定操作規範。這樣做的目的,是避免寫錯要刪除的表名。比如:

  • 在刪除數據表之前,必須先對錶做改名操作。然後,觀察一段時間,確保對業務無影響以後再刪除這張表。
  • 改表名的時候,要求給表名加固定的後綴(比如加 _to_be_deleted),然後刪除表的動作必須通過管理系統執行。並且,管理系刪除表的時候,只能刪除固定後綴的表。

rm 刪除數據

其實,對於一個有高可用機制的 MySQL 集群來說,最不怕的就是 rm 刪除數據了。只要不是惡意地把整個集群刪除,而只是刪掉了其中某一個節點的數據的話,HA 系統就會開始工作,選出一個新的主庫,從而保證整個集群的正常工作。

這時,你要做的就是在這個節點上把數據恢復回來,再接入整個集群。

當然了,現在不止是 DBA 有自動化系統,SA(系統管理員)也有自動化系統,所以也許一個批量下線機器的操作,會讓你整個 MySQL 集群的所有節點都全軍覆沒。

應對這種情況,我的建議只能是說盡量把你的備份跨機房,或者最好是跨城市保存。

作者介紹

林曉彬,網名丁奇,前阿里資深技術專家,極客時間MySQL實戰45講專欄作者。