傳統的高可用架構不能預防誤刪數據的，因為主庫的一個drop table命令，會通過binlog傳給所有從庫和級聯從庫，進而導致整個集群的實例都會執行這個命令。

MySQL相關誤刪數據主要有以下幾種可能：

• 使用delete語句誤刪數據行；

• 使用drop table或truncate table語句誤刪數據表；

• 使用drop database誤刪數據庫；

• 使用rm誤刪整個MySQL實例。

誤刪行

如果使用delete語句誤刪數據行，可以用Flashback工具通過閃回將數據恢復。原理是修改binlog的內容，拿回原庫重放。能使用該方案的前提是需要確保binlog_format=row和binlog_row_image=FULL。

具體而言：

• 對于insert，對應的binlog event類型是Write_rows event，改成Delete_rows event即可；

• 對于delete，將Delete_rows event改為Write_rows event；

• 對于update，對調binlog里修改前后的兩行位置即可。

如果誤操作不止一個，比如是三個事務：

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

若要恢復，用Flashback解析binlog后，寫回主庫的命令是：

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

即誤操作涉及多事務時，需要將事務順序反過來執行。

恢復數據比較安全的做法，是找一個從庫作為臨時庫，在臨時庫上執行這些操作，然后再將確認過的臨時庫的數據，恢復回主庫。因為一個執行線上邏輯的主庫，數據狀態的變更往往是有關聯的，可能由于誤操作導致后續邏輯修改了其他關聯數據，這時如果單獨恢復誤操作的數據，可能會出現對數據的二次破壞。

當然，更重要的是做到事前預防，有兩個建議：

• 設置sql_safe_updates=on，這樣如果delete或update中沒寫where，或where條件里沒有包含索引字段，該語句的執行就會報錯；

• 代碼上線前，必須經過SQL審計。

那么當設置sql_safe_updates=on，想要刪除一個小表的全部數據，該怎么辦呢？

• 可以在delete語句加上where id>=0。該方法很慢，性能不好；

• 使用truncate table/drop table，缺點是無法通過Flashback恢復，因為binlog里只有一個truncate/drop語句，恢復不出數據。

誤刪庫/表

此時想恢復數據，需要使用全量備份+增量日志的方式，因此方案要求線上有定期的全量備份，并且實時備份binlog。

假設中午12點誤刪了一個庫，那么恢復流程如下：

• 取最近一次全量備份，假設該庫一天一備，上次備份為當前0點；

• 用備份恢復出一個臨時庫；

• 從日志備份里取出凌晨0點后的日志；

• 將這些日志，除了誤刪數據的語句，全應用到臨時庫。

該過程有幾個需要說明的地方：

• 為加速數據恢復，若臨時庫上有多個數據庫，可以在使用mysqlbinlog命令時加上-database參數指定誤刪表所在的庫，避免在恢復數據時還要應用其他庫日志的情況；

• 應用日志需要跳過誤操作語句的binlog：

  • 如果原實例沒有使用GTID模式，只能在應用到包含12點的binlog文件時，先用-stop-position參數執行到誤操作前的日志，再用-start-position從誤操作后的日志繼續執行；

  • 如果實例使用GTID模式，假設誤操作命令的GTID是gtid1，只需執行set gtid_next=gtid1;begin;commit;，先將這個GTID加到臨時實例的GTID集合，之后按順序執行binlog時就會自動跳過誤操作的語句。

但該方法恢復數據還是不夠快，主要兩個原因：

• 如果是誤刪表，最好是只恢復這張表，但mysqlbinlog并不能指定只解析一個表的日志；

• 應用日志的過程只能是單線程。

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

• 在start slave之前，先通過執行change replication filter replicate_do_table=(tbl_name)，可以讓臨時庫只同步誤操作的表；

• 這樣做可以用上并行復制。

不論是把mysqlbinlog工具解析出的binlog文件應用到臨時庫還是把臨時庫接到備庫，兩個方案的共同點是：誤刪表/庫后，恢復的思路主要是通過備份，再加上應用binlog的方式。即都要求備份系統定期備份全量日志，且需確保binlog在從本地刪除前已經做了備份。

但一個系統不可能備份無限的日志，還需要根據成本和磁盤空間資源設定一個日志保留的天數。

延遲復制備庫

雖然可以利用并行復制來加速恢復數據的過程，但該方案仍存在恢復時間不可控的問題。如果一個庫的備份特別大，或誤操作時間距離上一個全量備份的時間較長，比如一周一備的實例，在備份后的第6天發生誤操作，那就需要恢復6天的日志，該恢復時間可能會按天計算。

如果有非常核心的業務，不允許太長的恢復時間，可以考慮搭建延遲復制的備庫。

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

延遲復制的備庫是一種特殊備庫，通過CHANGE MASTER TO MASTER_DELAY=N，可以指定這個備庫持續保持跟主庫有N秒延遲。比如設置N=3600，表示如果主庫上有數據被誤刪，且在1小時內發現了該誤操作命令，這個命令就還沒在延遲復制的備庫執行，此時到備庫上執行stop slave，再通過之前介紹的方法，跳過誤操作命令，就可以恢復出需要的數據。

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

預防誤刪庫/表的方法

這里給出兩條建議：

• 賬號分離，目的是避免寫錯命令，如：

  • 只給業務開發DML權限而不給truncate/drop權限，如果業務開發有DDL需求，也可以通過開發管理系統得到 支持；

  • 即使是DBA團隊成員，日常也只使用只讀賬戶，必要時才使用有更新權限的賬戶。

• 制定操作規范，目的是避免寫錯要刪除的表名，如：

  • 刪除數據表前，必須先對表做改名，然后觀察一段時間，確保對業務無影響后再進行刪除；

  • 改表名時，要求給表名加固定后綴如_to_be_deleted，然后刪除表的動作必須通過管理系統執行，且刪除表只能刪除固定后綴的表。

rm刪除數據

只要不是惡意刪除整個集群，只是刪除其中某一個節點的數據的話，HA系統會選出一個新主庫，從而保證整個集群的正常工作。

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

如果出現批量下線機器的操作，導致整個MySQL集群的所有節點都全軍覆沒。這種情況，建議只能說盡量將備份跨機房，或最好是跨城市保存。