? 作者丨林曉斌

策劃丨小智

寫在前面

雖然我們之前遇到的大多數的數據被刪，都是運維同學或者 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?...

現(xiàn)在要把數據庫恢復回這三個事務操作之前的狀態(tài)，用 Flashback 工具解析 binlog 后，寫回主庫的命令是：

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

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

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

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

為什么要這么做呢？

這是因為，一個在執(zhí)行線上邏輯的主庫，數據狀態(tài)的變更往往是有關聯(lián)的?？赡苡捎诎l(fā)現(xiàn)數據問題的時間晚了一點兒，就導致已經在之前誤操作的基礎上，業(yè)務代碼邏輯又繼續(xù)修改了其他數據。所以，如果這時候單獨恢復這幾行數據，而又未經確認的話，就可能會出現(xiàn)對數據的二次破壞。

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

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

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

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

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

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

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

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

??

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

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

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

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

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

關于這個過程，我需要和你說明如下幾點：

1. 為了加速數據恢復，如果這個臨時庫上有多個數據庫，你可以在使用 mysqlbinlog 命令時，加上一個 --database 參數，用來指定誤刪表所在的庫。這樣，就避免了在恢復數據時還要應用其他庫日志的情況。
2. 在應用日志的時候，需要跳過 12 點誤操作的那個語句的 binlog：如果原實例沒有使用 GTID 模式，只能在應用到包含 12 點的 binlog 文件的時候，先用 --stop-position 參數執(zhí)行到誤操作之前的日志，然后再用 --start-position 從誤操作之后的日志繼續(xù)執(zhí)行；如果實例使用了 GTID 模式，就方便多了。假設誤操作命令的 GTID 是 gtid1，那么只需要執(zhí)行 set gtid_next=gtid1;begin;commit; 先把這個 GTID 加到臨時實例的 GTID 集合，之后按順序執(zhí)行 binlog 的時候，就會自動跳過誤操作的語句。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

這里的原因，主要包括兩個方面：

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

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

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

那么，我們有什么方法可以縮短恢復數據需要的時間呢？

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

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

延遲復制的備庫是一種特殊的備庫，通過 CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定這個備庫持續(xù)保持跟主庫有 N 秒的延遲。

比如你把 N 設置為 3600，這就代表了如果主庫上有數據被誤刪了，并且在 1 小時內發(fā)現(xiàn)了這個誤操作命令，這個命令就還沒有在這個延遲復制的備庫執(zhí)行。這時候到這個備庫上執(zhí)行 stop slave，再通過之前介紹的方法，跳過誤操作命令，就可以恢復出需要的數據。

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

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

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

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

第二條建議是，制定操作規(guī)范。這樣做的目的，是避免寫錯要刪除的表名。比如：

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

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

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

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

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

有道無術，術可成；有術無道，止于術

歡迎大家關注Java之道公眾號

好文章，我在看??