程序員自救指南:一不小心刪庫刪表怎么辦?

? 作者丨林曉斌

策劃丨小智

寫在前面

雖然我們之前遇到的大多數(shù)的數(shù)據(jù)被刪，都是運維同學(xué)或者 DBA 背鍋的。但實際上，只要有數(shù)據(jù)操作權(quán)限的同學(xué)，都有可能踩到誤刪數(shù)據(jù)這條線。

今天我們就來聊聊誤刪數(shù)據(jù)前后，我們可以做些什么，減少誤刪數(shù)據(jù)的風(fēng)險，和由誤刪數(shù)據(jù)帶來的損失。

以 MySQL 為例，為了找到解決誤刪數(shù)據(jù)的更高效的方法，我們需要先對和 MySQL 相關(guān)的誤刪數(shù)據(jù)，做下分類：

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

誤刪行如果是使用 delete 語句誤刪了數(shù)據(jù)行，可以用 Flashback 工具通過閃回把數(shù)據(jù)恢復(fù)回來。

Flashback 恢復(fù)數(shù)據(jù)的原理，是修改 binlog 的內(nèi)容，拿回原庫重放。而能夠使用這個方案的前提是，需要確保 binlog_format=row 和 binlog_row_image=FULL。

具體恢復(fù)數(shù)據(jù)時，對單個事務(wù)做如下處理：

1. 對于 insert 語句，對應(yīng)的 binlog event 類型是 Write_rows event，把它改成 Delete_rows event 即可；
2. 同理，對于 delete 語句，也是將 Delete_rows event 改為 Write_rows event；
3. 而如果是 Update_rows 的話，binlog 里面記錄了數(shù)據(jù)行修改前和修改后的值，對調(diào)這兩行的位置即可。

如果誤操作不是一個，而是多個，會怎么樣呢？比如下面三個事務(wù)：

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

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

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

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

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

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

為什么要這么做呢？

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

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

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

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

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

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

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

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

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

??

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

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

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

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

圖 1 數(shù)據(jù)恢復(fù)流程 -mysqlbinlog 方法

關(guān)于這個過程，我需要和你說明如下幾點：

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

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

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

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

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

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

圖 2 數(shù)據(jù)恢復(fù)流程 -master-slave 方法

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

假設(shè)，我們發(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 文件，在文件開頭加入兩行，內(nèi)容分別是 “./master.000005”和“./master.000006”;
3. 重啟備庫，目的是要讓備庫重新識別這兩個日志文件；
4. 現(xiàn)在這個備庫上就有了臨時庫需要的所有 binlog 了，建立主備關(guān)系，就可以正常同步了。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

有道無術(shù)，術(shù)可成；有術(shù)無道，止于術(shù)

歡迎大家關(guān)注Java之道公眾號

好文章，我在看??