一、背景

在日常開發運維中，我們可能會出現一些操作失誤的情況，比如使用了錯誤的 sql 語句對 mysql 數據進行了 update、delete 等操作，失誤操作導致數據出現問題，又或者架構設計上沒有使用軟刪除機制，用戶誤刪除了數據需要恢復。

那么如果出現這種情況，應該如何恢復失誤/mysql 誤刪數據時，本文將使用幾種常用方法一步一步帶你回滾誤刪/誤操的數據

二、準備測試數據

1. 創建測試表

create table jxy_pms.test_model
(create_time       bigint unsigned default 0     null,update_time       bigint unsigned default 0     null,delete_time       bigint unsigned default 0     null,test_model_id     varchar(255)                  not nullprimary key,name              varchar(255)                  null,remark            varchar(255)                  null
)collate = utf8mb4_unicode_ci;

2. 創建測試數據

insert into test_model (test_model_id, name, remark)
values ('1','test1','test-1'),('2','test2','test-2'),('3','test3','test-3');select * from test_modeltest_model_id  name   remark
1                 test1    test-1
2              test2    test-2
3                 test3    test-3

三、模擬誤操作

delete from test_model

四、數據回滾

（一）方案一：云數據庫恢復

如果你的 mysql 使用的時云數據庫，比如阿里云、騰訊云，那么你可以到云數據庫的控制臺操作面板進行一鍵數據回滾，一般都可以選擇對應的恢復區間，具體可以咨詢對應的云數據庫提供商，一般都會提供技術支持

優點：

• 低風險
• 操作簡單
• 有云供應商提供技術支持

缺點：

• 恢復精度較低，沒辦法恢復指定的數據
• 有可能恢復不全，云數據庫是定期備份，可能還沒來得及備份就被刪了

（二）方案二：手動恢復

通過解讀 binlog 日志內容，進行解析然后回放數據

binlog 日志：大白話：記錄數據庫的每個修改操作 sql

所以我們只需要找到對應的 binlog 日志中我們誤操作的 sql 數據，然后編寫對應的回放sql，就可以回滾數據

實操如下：

1. 查詢 binlog 日志

show binary logs;  或  SHOW MASTER STATUS;mysql-bin.000014    80019706    No
mysql-bin.000015    1326884    No
mysql-bin.000016    3650781    No
mysql-bin.000017    81424072    No
mysql-bin.000018    46681992    No
mysql-bin.000019    1075    No
mysql-bin.000020    207322979    No

可以看到最新的日志為：mysql-bin.000020

2. 找到刪除語句，手動還原為插入語句

mysqlbinlog --no-defaults --verbose --base64-output=DECODE-ROWS --start-datetime='2025-01-16 15:12:00' --stop-datetime='2025-01-16 15:15:00' /var/lib/mysql/mysql-bin.000027 > /home/DataVolume/rec.sql

• –base64-output=DECODE-ROWS 生成不加密的 sql 文件
• –start-datetime 、stop-datetime 數據操作的時間區間

從 rec.sql 中找到對應的表 test_model 的刪除操作，如果找不到，調整下–start-datetime 和 stop-datetime

找到刪除的 sql 語句如下：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
........
/*!*/;
# at 207319108
#240919 10:58:30 server id 1  end_log_pos 207319202 CRC32 0xe7b80345     Table_map: `jxy_pms`.`asynccron_cron_task` mapped to number 306#240919 10:59:04 server id 1  end_log_pos 207321626 CRC32 0x6b3323a9     Table_map: `jxy_pms`.`test_model` mapped to number 315
# at 207321626
#240919 10:59:04 server id 1  end_log_pos 207321718 CRC32 0xb004dad7     Delete_rows: table id 315 flags: STMT_END_F
### DELETE FROM `jxy_pms`.`test_model`
### WHERE
###   @1='1'
###   @2='test1'
###   @3='test-1'
### DELETE FROM `jxy_pms`.`test_model`
### WHERE
###   @1='2'
###   @2='test2'
###   @3='test-2'
### DELETE FROM `jxy_pms`.`test_model`
### WHERE
###   @1='3'
###   @2='test3'
###   @3='test-3'
# at 207321718
#240919 10:59:04 server id 1  end_log_pos 207321749 CRC32 0x90b858f7     Xid = 7486642
COMMIT/*!*/;

然后寫個腳本，解析這塊 sql，重新翻譯為 insert 語句即可

優點：

• 適合一些少量數據的還原
• 能準確還原指定的數據，操作簡單
• 重新執行的 sql，不會對已有的數據造成其他損壞

缺點：

• 不適合一些大量數據的還原
• 比較復雜，需要解讀 binlog 日志，并且需要編寫腳本

（三）方案三: 導入還原的 sql（未測試，慎用）

如果你的數據庫不是用的云數據庫，沒有一鍵恢復功能，然后你又不想用方案二，覺得太復雜，你可以直接將 binlog 日志直接全部還原

• 先導出 binlog 日志

  mysqlbinlog --no-defaults --verbose --base64-output=DECODE-ROWS --start-datetime='2025-01-16 15:12:00' --stop-datetime='2025-01-16 15:15:00' /var/lib/mysql/mysql-bin.000027 > /home/DataVolume/rec.sql
  

  這里會導出 rec.sql 文件

• 導入 sql

  mysql -u root -p test < rec.sql
  

優點：

• 相比較方案二簡單一些
• 適合范圍恢復

缺點：

• 恢復精度較低，比如你可能只需要恢復 2025-01-16 15:12:00 這個點的某條錯誤sql，但是這個點可能還有其他操作sql也會被一起恢復，
• 恢復期間的操作數據可能存在丟失

??????：本方案沒有實際驗證過，請自行測試

總結

如果你用的是云數據庫，并且恢復的數據量比較大，推薦【方案一】使用云數據庫提供的恢復功能（非常適用那么刪庫跑路的恢復，哈哈哈哈）

如果你只是想要恢復某個個點、某些錯誤sql，那么可以使用【方案二】

原文地址

Mysql 如何使用 binlog 日志回滾操作失誤的數據