之前我們聊過(guò)備份怎么做、怎么避坑，也把 Binlog 的原理拆了個(gè)底朝天?？扇绻麛?shù)據(jù)真被刪了，從發(fā)現(xiàn)到恢復(fù)完，具體每一步該干什么？

這個(gè)問(wèn)題不是我瞎想的。上個(gè)月隔壁組一個(gè)同事執(zhí)行 DELETE 忘了加 WHERE，一張用戶表兩千多行直接清空了。當(dāng)時(shí)辦公室那個(gè)氣氛，我到現(xiàn)在都記得。最后折騰了三個(gè)多小時(shí)才恢復(fù)，中間還差點(diǎn)因?yàn)檎`操作把事情搞得更糟。

后來(lái)我把整個(gè)恢復(fù)過(guò)程復(fù)盤(pán)了一遍，又翻了不少資料，整理出一套從止血到恢復(fù)的 SOP。今天按誤刪的嚴(yán)重程度分三種場(chǎng)景講，每種給出具體操作步驟。

一、誤刪的三種場(chǎng)景

先搞清楚你面對(duì)的是哪種情況，不同級(jí)別對(duì)應(yīng)不同的恢復(fù)方案。

三個(gè)場(chǎng)景的恢復(fù)復(fù)雜度遞增，但核心思路就一句話：全量備份定基調(diào)，Binlog 回放補(bǔ)增量。 前面講過(guò)，Binlog 記錄了所有變更的邏輯日志，這就是數(shù)據(jù)恢復(fù)的底氣。

二、黃金第一步：止血

不管哪種場(chǎng)景，發(fā)現(xiàn)誤刪后的第一反應(yīng)都不是恢復(fù)，而是止損。

我見(jiàn)過(guò)有人發(fā)現(xiàn)誤刪之后慌了，直接重啟 MySQL，結(jié)果 redo log 被刷掉，少了一層保障。還有人下意識(shí)執(zhí)行了 FLUSH LOGS，導(dǎo)致 Binlog 被輪轉(zhuǎn)到新文件，定位誤刪位置變得更麻煩。

發(fā)現(xiàn)誤刪后，按順序做三件事：

1. 停止寫(xiě)入。 把相關(guān)的應(yīng)用會(huì)話 kill 掉，或者把數(shù)據(jù)庫(kù)設(shè)成只讀模式：

SET GLOBAL read_only = ON;

2. 保護(hù)現(xiàn)場(chǎng)。 不要重啟 MySQL，不要執(zhí)行 FLUSH LOGS，不要?jiǎng)尤魏稳罩疚募?/p>

3. 確認(rèn) Binlog 狀態(tài)。 看看 Binlog 是否完整，當(dāng)前在哪個(gè)文件：

SHOW BINARY LOGS;
SHOW MASTER STATUS;

如果 Binlog 還在，恭喜，恢復(fù)的概率很大。如果 Binlog 已經(jīng)被清理掉了，那只能靠全量備份了。所以之前我反復(fù)強(qiáng)調(diào)，expire_logs_days 別設(shè)太短，就是這個(gè)原因。

三、方案 A：DELETE 誤刪幾行數(shù)據(jù)

這是最常見(jiàn)的場(chǎng)景，也是最好恢復(fù)的。

假設(shè)你執(zhí)行了這么一條語(yǔ)句：

DELETE FROM orders WHERE create_time < '2025-01-01';

然后發(fā)現(xiàn)忘了加其他條件，把不該刪的也刪了。

Step 1：定位誤刪的時(shí)間點(diǎn)

用 mysqlbinlog 找到 DELETE 操作在 Binlog 中的位置：

mysqlbinlog --start-datetime="2026-06-03 10:00:00" \
            --stop-datetime="2026-06-03 10:05:00" \
            --base64-output=DECODE-ROWS -v \
            binlog.000003 | grep -B5 "DELETE"

找到 DELETE 語(yǔ)句對(duì)應(yīng)的 end_log_pos，記下來(lái)。如果你知道大概的時(shí)間范圍，用 --start-datetime 和 --stop-datetime 縮小范圍；如果不知道，可能得翻好幾個(gè) Binlog 文件。

Step 2：解析 Binlog 生成反向 SQL

找到位置之后，把那段 Binlog 解析成人能看懂的 SQL：

mysqlbinlog --start-position=1234 --stop-position=5678 \
            --base64-output=DECODE-ROWS -v \
            binlog.000003 > recovery.sql

打開(kāi) recovery.sql，找到 DELETE 操作。ROW 模式下，Binlog 會(huì)記錄被刪行的完整數(shù)據(jù)（### DELETE FROM 后面的內(nèi)容）。你需要手動(dòng)把這些數(shù)據(jù)拼成 INSERT 語(yǔ)句。

說(shuō)實(shí)話這個(gè)過(guò)程挺痛苦的，字段多的時(shí)候一個(gè)一個(gè)對(duì)很累。

更省事的辦法：binlog2sql

有個(gè)開(kāi)源工具叫 binlog2sql，能自動(dòng)把 Binlog 里的 DELETE 轉(zhuǎn)成 INSERT、UPDATE 轉(zhuǎn)成反向 UPDATE，省得你手動(dòng)拼：

python binlog2sql.py -h 127.0.0.1 -P 3306 -u root -p'password' \
    -d mydb -t orders \
    --start-datetime="2026-06-03 10:00:00" \
    --stop-datetime="2026-06-03 10:05:00" \
    --type DELETE > flashback.sql

生成的 SQL 直接執(zhí)行就能把數(shù)據(jù)恢復(fù)回去。我在測(cè)試環(huán)境試過(guò)，確實(shí)比手動(dòng)解析快很多。

四、方案 B：DROP TABLE 恢復(fù)

整張表被刪了，靠解析 Binlog 里的單行數(shù)據(jù)已經(jīng)不現(xiàn)實(shí)了。這時(shí)候需要全量備份 + Binlog 增量回放。

Step 1：找到最近的全量備份

翻你的備份目錄，找到離 DROP TABLE 時(shí)間最近的一次全量備份。假設(shè)你用的是 mysqldump：

ls -lt /backup/full_*.sql
# 找到 full_20260602.sql

Step 2：在臨時(shí)實(shí)例上恢復(fù)

別直接往生產(chǎn)庫(kù)灌！先起一個(gè)臨時(shí) MySQL 實(shí)例，在上面恢復(fù)全量備份：

# 臨時(shí)實(shí)例上恢復(fù)全量
mysql -h 127.0.0.1 -P 3307 -u root -p < /backup/full_20260602.sql

Step 3：回放增量 Binlog 到誤刪前

從全量備份的時(shí)間點(diǎn)開(kāi)始，把到 DROP TABLE 之前的 Binlog 全部回放：

mysqlbinlog --start-datetime="2026-06-02 02:00:00" \
            --stop-datetime="2026-06-03 14:30:00" \
            binlog.000002 binlog.000003 \
    | mysql -h 127.0.0.1 -P 3307 -u root -p

--stop-datetime 要設(shè)在 DROP TABLE 之前，不然回放過(guò)去又把表刪了。

Step 4：把數(shù)據(jù)導(dǎo)回生產(chǎn)庫(kù)

臨時(shí)實(shí)例上確認(rèn)數(shù)據(jù)沒(méi)問(wèn)題后，單獨(dú)導(dǎo)出被刪的那張表，再導(dǎo)入回生產(chǎn)庫(kù)：

# 從臨時(shí)實(shí)例導(dǎo)出
mysqldump -h 127.0.0.1 -P 3307 -u root -p mydb orders > orders_recovery.sql
# 導(dǎo)回生產(chǎn)庫(kù)
mysql -h 生產(chǎn)庫(kù)IP -u root -p mydb < orders_recovery.sql

恢復(fù)完之后記得把 read_only 關(guān)掉，恢復(fù)正常業(yè)務(wù)。

五、方案 C：從延遲從庫(kù)恢復(fù)

前面兩種方案都依賴(lài)備份，如果你的備份恰好不完整（別笑，之前就講過(guò)這種事不少見(jiàn)），還有最后一道保險(xiǎn)：延遲從庫(kù)。

什么是延遲從庫(kù)？

就是在搭建從庫(kù)的時(shí)候，讓它故意比主庫(kù)慢一段時(shí)間：

-- MySQL 8.0
CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 3600;
-- MySQL 5.7
CHANGE MASTER TO MASTER_DELAY = 3600;

SOURCE_DELAY = 3600 意味著從庫(kù)會(huì)延遲 1 小時(shí)回放主庫(kù)的 Binlog。這一小時(shí)就是你的"后悔窗口"。

怎么用它恢復(fù)？

假設(shè)主庫(kù)在 14:30 執(zhí)行了 DROP TABLE，延遲從庫(kù)還沒(méi)回放到這條語(yǔ)句：

-- 在延遲從庫(kù)上檢查
SHOW SLAVE STATUS\G
-- SQL_Delay: 3600
-- 說(shuō)明從庫(kù)還在回放 13:30 之前的 Binlog，DROP TABLE 的語(yǔ)句還沒(méi)執(zhí)行到

直接從延遲從庫(kù)把表導(dǎo)出來(lái)就行，連 Binlog 解析都不用。然后導(dǎo)入回主庫(kù)。

為什么建議重要業(yè)務(wù)都配一個(gè)？

延遲從庫(kù)不占太多資源（就多一個(gè) MySQL 實(shí)例 + 1小時(shí)的磁盤(pán)空間），但它給你的安全感是實(shí)打?qū)嵉?。我之前覺(jué)得"應(yīng)該用不上吧"，直到隔壁組那次事故之后，我們組默默配了一個(gè)。

六、面試怎么答

如果面試官問(wèn)：“MySQL 誤刪數(shù)據(jù)怎么恢復(fù)？”

我的回答思路：

先分場(chǎng)景。DELETE 誤刪少量數(shù)據(jù)，用 mysqlbinlog 解析 Binlog 找到被刪行，生成反向 INSERT 語(yǔ)句恢復(fù)。也可以用 binlog2sql 工具自動(dòng)化這個(gè)過(guò)程。

DROP TABLE 或 DROP DATABASE，用全量備份 + Binlog 增量回放，也就是 Point-in-Time Recovery。先在臨時(shí)實(shí)例上恢復(fù)全量備份，再回放從備份點(diǎn)到誤刪前的 Binlog，最后把數(shù)據(jù)導(dǎo)回生產(chǎn)庫(kù)。

如果有延遲從庫(kù)就更簡(jiǎn)單了，直接從延遲從庫(kù)取數(shù)據(jù)，不用解析 Binlog。

不管哪種方案，第一步都是止血：停止寫(xiě)入、保護(hù)現(xiàn)場(chǎng)、確認(rèn) Binlog 完整性。我見(jiàn)過(guò)有人發(fā)現(xiàn)誤刪后直接重啟 MySQL，反而導(dǎo)致 redo log 丟失，增加了恢復(fù)難度。

預(yù)防層面，從庫(kù)設(shè) super_read_only 防止誤寫(xiě)，SQL 審核平臺(tái)攔截?zé)o WHERE 的 DELETE/UPDATE，關(guān)鍵表可以建觸發(fā)器自動(dòng)備份被刪數(shù)據(jù)到審計(jì)表。

面試官追問(wèn)：“Point-in-Time Recovery 的原理是什么？”

就是全量備份 + Binlog 增量回放。全量備份給你一個(gè)基線，Binlog 記錄了從備份點(diǎn)之后的所有數(shù)據(jù)變更。通過(guò)指定 --stop-datetime 或 --stop-position，可以把數(shù)據(jù)恢復(fù)到任意時(shí)間點(diǎn)。前提是你得有完整的 Binlog 文件，所以 Binlog 的保留策略很重要。

七、預(yù)防：讓誤刪無(wú)法發(fā)生

恢復(fù)再好也不如不誤刪。幾個(gè)預(yù)防措施：

從庫(kù)寫(xiě)保護(hù)。 所有從庫(kù)開(kāi)啟 super_read_only，防止有人手滑在從庫(kù)上寫(xiě)數(shù)據(jù)：

SET GLOBAL super_read_only = ON;

SQL 審核攔截。 如果公司有 SQL 審核平臺(tái)（比如 Yearning、Archery），配置規(guī)則攔截沒(méi)有 WHERE 條件的 DELETE 和 UPDATE。這一條規(guī)則能攔住 80% 的誤刪操作。

關(guān)鍵表審計(jì)觸發(fā)器。 對(duì)于特別重要的表（比如用戶表、訂單表），可以建一個(gè)審計(jì)觸發(fā)器，每次 DELETE 的時(shí)候自動(dòng)把被刪數(shù)據(jù)備份到審計(jì)表：

CREATE TABLE orders_audit LIKE orders;
ALTER TABLE orders_audit ADD COLUMN deleted_at DATETIME DEFAULT CURRENT_TIMESTAMP;

CREATE TRIGGER trg_orders_backup
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO orders_audit SELECT OLD.*, NOW();
END;

這個(gè)方案有個(gè)缺點(diǎn)：每次 DELETE 都多一次寫(xiě)入，會(huì)影響性能。只建議用在核心表上。

定期恢復(fù)演練。 光備份不驗(yàn)證等于沒(méi)備份。至少每月一次，在測(cè)試環(huán)境把備份恢復(fù)出來(lái)，確認(rèn)數(shù)據(jù)完整、恢復(fù)流程走得通。

生產(chǎn)避坑清單

恢復(fù)過(guò)程中我踩過(guò)的和見(jiàn)過(guò)的坑，列出來(lái)避免你們重蹈覆轍：

發(fā)現(xiàn)誤刪不要重啟 MySQL。redo log 和 binlog 都在內(nèi)存/文件里，重啟可能觸發(fā)刷盤(pán)或輪轉(zhuǎn)，讓恢復(fù)變得更復(fù)雜。

執(zhí)行 FLUSH LOGS 之前想清楚。它會(huì)把當(dāng)前 Binlog 輪轉(zhuǎn)到新文件，不影響已有數(shù)據(jù)，但如果你正在定位誤刪位置，突然多一個(gè)新文件容易搞混。

--stop-datetime 千萬(wàn)別設(shè)到 DROP TABLE 之后。我同事當(dāng)時(shí)手抖把時(shí)間設(shè)晚了一秒，回放過(guò)去又把表刪了，白忙活半小時(shí)。

恢復(fù)前先備份當(dāng)前狀態(tài)。就算數(shù)據(jù)已經(jīng)被刪了，也要把現(xiàn)有的 ibdata、ib_logfile、binlog 文件先拷一份出來(lái)。萬(wàn)一恢復(fù)操作出了問(wèn)題，還有退路。

不要在生產(chǎn)庫(kù)上直接做恢復(fù)操作。先在臨時(shí)實(shí)例上驗(yàn)證，確認(rèn)沒(méi)問(wèn)題再導(dǎo)回生產(chǎn)。在生產(chǎn)庫(kù)上直接灌備份，灌錯(cuò)了就是二次事故。

Binlog 保留時(shí)間別太短。之前說(shuō)過(guò) expire_logs_days 建議 7 到 15 天。如果誤刪后才發(fā)現(xiàn) Binlog 已經(jīng)被清理了，那 Binlog 回放這條路就走不通了。

學(xué)習(xí)心得

之前我一直覺(jué)得"誤刪恢復(fù)"是個(gè)離自己很遠(yuǎn)的事情，直到真看到同事出事才意識(shí)到，這種事情不是"會(huì)不會(huì)發(fā)生"，而是"什么時(shí)候發(fā)生"。

讓我收獲最大的是理解了恢復(fù)的核心邏輯：全量備份是基線，Binlog 是增量，兩者配合才能恢復(fù)到任意時(shí)間點(diǎn)。之前學(xué) mysqldump和Binlog 原理的時(shí)候，這兩塊知識(shí)是分開(kāi)的。寫(xiě)這篇的時(shí)候它們終于串起來(lái)了，感覺(jué)像拼圖的最后一塊扣上了。

延遲從庫(kù)那部分是我之前沒(méi)怎么關(guān)注的。之前總覺(jué)得"多一個(gè)從庫(kù)就夠了，干嘛還要故意延遲"，現(xiàn)在想想，那一小時(shí)的窗口就是給你后悔用的。成本不高，關(guān)鍵時(shí)候能救命。

binlog2sql 這個(gè)工具我測(cè)試環(huán)境試了一下，確實(shí)比手動(dòng)解析 Binlog 方便太多。手動(dòng)解析那種 ### DELETE FROM 一堆字段對(duì)來(lái)對(duì)去的過(guò)程，經(jīng)歷過(guò)一次就夠了。

防誤刪那塊，SQL 審核平臺(tái)攔截?zé)o WHERE 的 DELETE，這個(gè)規(guī)則看起來(lái)簡(jiǎn)單，但真的能攔住大部分手滑操作。如果你的公司還沒(méi)有這個(gè)流程，值得推一下。

以上就是MySQL誤刪數(shù)據(jù)恢復(fù)之Binlog回放+全量備份+延遲從庫(kù)的三種方案實(shí)戰(zhàn)的詳細(xì)內(nèi)容，更多關(guān)于MySQL誤刪數(shù)據(jù)恢復(fù)方案的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論