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

關(guān)注?歡少的成之路??回復(fù)算法，MySQL，8888，6666 領(lǐng)取海量學(xué)習(xí)資料。有機(jī)會參與領(lǐng)書活動！

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

思路

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

誤刪行

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

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

binlog_format=row

這個(gè)參數(shù)我們在前面介紹binlog日志的時(shí)候介紹過。主要分row，?statement，mixed

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

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)容，拿回主庫重新加載。要使用當(dāng)前方法同時(shí)也要對事物進(jìn)行修改操作如下。

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

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

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

預(yù)防

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

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

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

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

如果真刪了呢？

誤刪表/庫

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

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

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

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

擴(kuò)展

• 上述在做數(shù)據(jù)恢復(fù)的時(shí)候，如果這個(gè)臨時(shí)庫有多個(gè)數(shù)據(jù)庫。在使用mysqlbinlog命令時(shí)加一個(gè)-database參數(shù)。指定表所在的庫避免恢復(fù)數(shù)據(jù)時(shí)還要查找其他庫的日志情況。
• 如果使用了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è)庫，一個(gè)實(shí)例。如果恢復(fù)的是一個(gè)表的話就多此一舉了。也不是多此一舉，只是mysql并不能指定只解析一個(gè)表的日志。

加速方法

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

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

日志遺失

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

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

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

延遲復(fù)制備庫

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

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

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

預(yù)防表/庫方法

1. 賬號分離，不同的業(yè)務(wù)人員擁有不同的操作權(quán)限。避免寫錯(cuò)命令。
2. 制定操作規(guī)范。這樣做的目的，是避免寫錯(cuò)要刪除的表名

rm 刪除數(shù)據(jù)

這個(gè)風(fēng)險(xiǎn)還是比較高的，一般出現(xiàn)這種情況，只能采用集群的方式恢復(fù)了，如果沒有集群的話只能嗝屁了。

如果只是刪除一個(gè)節(jié)點(diǎn)的話，HA系統(tǒng)就會開始工作，先選出一個(gè)新的主庫，然后就是在這個(gè)節(jié)點(diǎn)上把數(shù)據(jù)恢復(fù)然后接入整個(gè)集群。這樣就可以解決了。

為了保險(xiǎn)起見，一般rm命令危害比較大，建議分機(jī)房，跨城市保存數(shù)據(jù)

總結(jié)

今天介紹了數(shù)據(jù)被刪后，除了跑路我們還可以有哪些處理方式以及數(shù)據(jù)被刪后的應(yīng)對方案和應(yīng)急方案。

投完票之后，加博主好友，一起參與抽書活動吧