MYSQL Binlog恢復誤刪資料庫詳解

2022-11-21 14:02:39

對錶誤刪或執行缺少條件的修改 SQL 導致修改了表內其他資料時，我們需要想辦法將資料恢復回來。

先建立兩個測試表 table_1

CREATE TABLE `table_1`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

首先要確保開啟了BINLOG紀錄檔

-- 檢視紀錄檔狀態
show variables like '%log_bin%';

之前沒有開啟 Binlog 紀錄檔的話下面的方法就不能繼續操作了，開啟紀錄檔的方法網上有很多，修改一下組態檔並重啟 MYSQL 服務。

模擬刪除

正常情況下，我們需要從線上正在使用的紀錄檔中查詢資訊，要考慮的因素會很多。

為了方便測試，這裡重啟了一個新的紀錄檔，後面的所有操作都會記錄到新的紀錄檔檔案中。

flush logs

-- 插入table1一些資料
insert into table_1(name) values  ('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('10')

-- 模擬刪除表資訊table1
delete from table_1 where id >5;
--然後修改table1的內容
update table_1 set name= Concat(name,'1') ;

修復步驟

我們需要刪除的資訊，並保留刪除之後修改過的資訊

-- 檢視紀錄檔內容
show master status;
show binlog events in 'binlog.000023';

從上面的查詢語句可以查詢 binlog.000023 中記錄的所有操作。

如圖，我們在 993 的位置執行了刪除表的操作，我們要把資料還原到這個位置之前

每一個事務從 Gtid 開始，Xid 結束。

有的時候，我們不確定這個位置在什麼地方，但是我們有時間範圍，可以根據時間範圍縮小所有區間，這裡需要用到 binlog 命令

以下是 Windows CMD 的命令範例：

mysqlbinlog.exe F:Mysqlmysql-8.0.27-winx64databinlog.000021 --start-datetime="2022-11-17 13:00:00" --stop-datetime="2022-11-17 14:00:00"

控制檯列印出來的結果和SQL查詢出來的結果大致上是一樣的。

找到我們要還原的位置之後，執行還原命令。

以下是 Windows CMD 的命令範例：

mysqlbinlog.exe F:Mysqlmysql-8.0.27-winx64databinlog.000023 --stop-position=993 --database=dgcat | F:Mysqlmysql-8.0.27-winx64binmysql -uroot -p*****

執行後發現報錯：

反饋 table_1表中已經有了主鍵 1 ，不能插入。是因為我們只指定了資料恢復的結束位置，而當前紀錄檔又包含了 table_1 的所有行插入記錄，當恢復表時，他會重新插入這些行，導致和表內已存在的資訊主鍵衝突。

因為我的 binlog 紀錄檔記錄了 table_1 的所有操作記錄。所以這裡我選擇將 table_1 清空，用紀錄檔恢復所有資料。

truncate table table_1

正常情況下，我們最好能確認下來資料恢復的開始位置。

再次執行恢復命令後，表內資訊如下：

現在已經恢復了我們刪除之前的狀態，但是我們在刪除之後還做了修改操作，我們還需要把修改操作也還原一下：

相當於跳過 delete 操作，1100 位置即是我們修改操作的開始位置，直至 1478 整個操作結束。

mysqlbinlog.exe F:Mysqlmysql-8.0.27-winx64databinlog.000023 --start-position=1100 --stop-position=1478 --database=dgcat | F:Mysqlmysql-8.0.27-winx64binmysql -uroot -pzdm3713261995!!!

執行結束後我們在查詢表內資訊，刪除的資料恢復了，而且我們修改過的記錄也在。

到此這篇關於MYSQL Binlog恢復誤刪資料庫詳解的文章就介紹到這了,更多相關MYSQL Binlog恢復資料內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MYSQL Binlog恢復誤刪資料庫詳解

熱門文章