加班到2點(diǎn)，一不小心我把MySQL刪了

作者：歡少的成長(zhǎng)之路 2021-10-07 16:45:06

誤刪行這種情況應(yīng)該是比較常見的，有些時(shí)候?yàn)榱私鉀Q數(shù)據(jù)問題，我們直接刪了這一行。刪完之后才反應(yīng)過來刪錯(cuò)了。接下來我們介紹一下，我們應(yīng)該如何處理!

大家好，我是Leo。目前在常州從事Java后端。上一篇文章我們介紹了線上數(shù)據(jù)庫(kù)掛了一個(gè)節(jié)點(diǎn)之后，應(yīng)該如何排查節(jié)點(diǎn)宕機(jī)問題。從select 1 ，外部統(tǒng)計(jì)，內(nèi)部統(tǒng)計(jì)等一系列流程方案的介紹。這一篇我們介紹一下線上數(shù)據(jù)庫(kù)誤刪數(shù)據(jù)后，到底是跑路還是該如何解決!

思路

本篇文章的介紹思路以下圖的思維導(dǎo)圖為大綱。也有利于讀者更好的分辨可讀性!

誤刪行

提到誤刪行，就必須涉及到兩個(gè)參數(shù) binlog_format=row binlog_row_image=FULL

binlog_format=row

這個(gè)參數(shù)我們?cè)谇懊娼榻Bbinlog日志的時(shí)候介紹過。主要分row， statement，mixed

這里為什么必須設(shè)置為row呢，因?yàn)橹挥杏涗浽敿?xì)的日志信息，作恢復(fù)數(shù)據(jù)的時(shí)候才好操作。statement肯定是不夠的。mixed也是不符合的，因?yàn)橥耆恍枰袛?

binlog_row_image=FULL

這個(gè)是由上列參數(shù)同時(shí)引入的一個(gè)新的參數(shù)。當(dāng)前有兩個(gè)選擇項(xiàng)，F(xiàn)ULL記錄每一行的變更，minimal只記錄影響后的行。默認(rèn)使用FULL。

步入正題了。。。。。。

可以通過Flashback 工具通過閃回把數(shù)據(jù)恢復(fù)回來。數(shù)據(jù)恢復(fù)的原理就是修改binlog內(nèi)容，拿回主庫(kù)重新加載。要使用當(dāng)前方法同時(shí)也要對(duì)事物進(jìn)行修改操作如下。

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

如果執(zhí)行的是多個(gè)事務(wù)，比如原本是A,B,C。想要數(shù)據(jù)恢復(fù)的話那就直接順序反過來即可，也就是C,B,A

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

預(yù)防

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

如果要?jiǎng)h除表的數(shù)據(jù)量比較大，并且確認(rèn)數(shù)據(jù)是無用的，不建議使用delete。這樣會(huì)生成并寫入redo log，binlog，回滾日志等。采用truncate table 或者 drop table 命令可以節(jié)省性能

為什么采用truncate table 或者 drop table可以節(jié)省性能?

上文我們說到，必須設(shè)置 binlog_format=row 。這里我們要說明一下，雖然我們配置的是沒問題的，但是內(nèi)部機(jī)制的問題。使用這兩個(gè)命令會(huì)自動(dòng)設(shè)置成statement 所以這兩個(gè)命令保存的日志比較簡(jiǎn)單。恢復(fù)不了數(shù)據(jù)。性能比較好。

如果真刪了呢?

誤刪表/庫(kù)

如果真刪了還是有辦法的。不過稍微比較費(fèi)事。這也是最低的底牌了。全量備份+增量備份。這種方案要求線上有定期的全量備份，并且實(shí)時(shí)備份。

這個(gè)方案類似于Redis的AOF和RDB。那么他們是如何操作的呢?

假如有人中午12點(diǎn)誤刪了一個(gè)庫(kù)

取最近的一次全量備份，假如備份時(shí)間是凌晨3點(diǎn)，一天一備。

用備份恢復(fù)出一個(gè)臨時(shí)庫(kù);

從日志備份里面，取出凌晨 3 點(diǎn)之后的日志;

把這些日志，除了誤刪除數(shù)據(jù)的語(yǔ)句外，全部應(yīng)用到臨時(shí)庫(kù)。

擴(kuò)展

上述在做數(shù)據(jù)恢復(fù)的時(shí)候，如果這個(gè)臨時(shí)庫(kù)有多個(gè)數(shù)據(jù)庫(kù)。在使用mysqlbinlog命令時(shí)加一個(gè)-database參數(shù)。指定表所在的庫(kù)避免恢復(fù)數(shù)據(jù)時(shí)還要查找其他庫(kù)的日志情況。
如果使用了GTID模式，就省事多了，只需要將未執(zhí)行的gtid1加到臨時(shí)實(shí)例的GTID集合中，之后按順序執(zhí)行binlog就可以了。
如果沒有使用GTID模式，還是比較麻煩的。只能在應(yīng)用到包含 12 點(diǎn)的 binlog 文件的時(shí)候，先用–stop-position 參數(shù)執(zhí)行到誤操作之前的日志，然后再用–start-position 從誤操作之后的日志繼續(xù)執(zhí)行;

性能優(yōu)化

這樣的流程從性能上考慮還是比較慢的，因?yàn)椴僮鞯脑捦且粋€(gè)庫(kù)，一個(gè)實(shí)例。如果恢復(fù)的是一個(gè)表的話就多此一舉了。也不是多此一舉，只是mysql并不能指定只解析一個(gè)表的日志。

加速方法

用備份恢復(fù)臨時(shí)實(shí)例之后，將這個(gè)臨時(shí)實(shí)例設(shè)置成線上備庫(kù)的從庫(kù)。在保存主從配置之前，先通過執(zhí)行change replication filter replicate_do_table = (tbl_name)

命令，就可以讓臨時(shí)庫(kù)只同步誤操作的表。這樣做也可以用之前介紹的并行復(fù)制技術(shù)，來加速整個(gè)數(shù)據(jù)恢復(fù)過程。

日志遺失

如果在尋找日志恢復(fù)實(shí)例時(shí)，備庫(kù)上已經(jīng)刪除了臨時(shí)實(shí)例需要的binlog的話，我們可以從binlog備份系統(tǒng)中找到需要的binlog，再放回備庫(kù)中。具體操作如下

先下載兩個(gè)遺失的日志，放到備庫(kù)的日志目錄下
打開日志目錄下的 master.index 文件，在文件開頭加入兩行，內(nèi)容分別是 ./master.丟失001和 ./master.丟失002
重啟備庫(kù)，重新加載這兩個(gè)日志。這個(gè)時(shí)候建立主從關(guān)系就可以正常同步了。

必須要求備份系統(tǒng)定期備份全量日志，考慮磁盤硬件需求。可以適當(dāng)?shù)谋４婀潭ǖ奶鞌?shù)

延遲復(fù)制備庫(kù)

這個(gè)方案是屬于一個(gè)日志延遲方案。比如在從庫(kù)寫入一個(gè)數(shù)據(jù)，這個(gè)數(shù)據(jù)不會(huì)立即同步到備庫(kù)上。然后采用延遲的手法同步到備庫(kù)。

比如我們延遲1個(gè)小時(shí)。主庫(kù)寫入數(shù)據(jù)之后，1個(gè)小時(shí)之后會(huì)同步到從庫(kù)。那么如果1個(gè)小時(shí)內(nèi)發(fā)現(xiàn)了數(shù)據(jù)有誤，就可以使用stop slave 命令把這個(gè)寫入的數(shù)據(jù)停止。

可以通過 CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定這個(gè)備庫(kù)持續(xù)保持跟主庫(kù)有 N 秒的延遲。