Mysql 誤刪資料進行恢復

HuDu發表於2022-02-14
MySql

前言

由於使用 Navicat 連線伺服器的 MySQL，經常需要新增和刪除一些測試資料，但是由於不仔細，不小心刪除了不需要刪除的資料，具體恢復流程如下

操作流暢

1、第一步

SHOW VARIABLES LIKE '%log_bin%';

如果 log_bin 為 ON 說明可以恢復，如果為 OFF 則沒有開啟 binlog，也沒有預先生成回滾 SQL，那可能就無法快速回滾了，不過好像可以通過 ibd 恢復，具體可以參考 blog.csdn.net/hanjun0612/article/d...

Mysql 誤刪資料進行恢復

2、檢視資料檔案存放路徑

SHOW VARIABLES LIKE '%datadir%';

Mysql 誤刪資料進行恢復

開啟資料庫所在路徑檢視有mysql-bin.****這樣的檔案，注意DELETE刪除的時間,對比mysql-bin檔案的修改時間

3、找到 mysql 安裝路徑

SHOW VARIABLES LIKE '%basedir%';

Mysql 誤刪資料進行恢復

mysqlbinlog.exe --no-defaults  --start-datetime="2021-11-10 15:30:00" --stop-datetime="2019-11-26 18:10:00" -vv D:\mysql-8.0.26-winx64\data\binlog.000009 > D:\update\mysqllog.sql
mysqlbinlog 命令的引數說明
--base64-output=decode-rows //資料轉換正常的字元，如果不設定這個引數將顯示base64的資料
-vv 將二進位制轉換為可閱讀文字
--database=DBName  //資料庫名（一個mysql資料庫比較多，指定方便恢復）
--start-datetime="2019-11-26 18:00:00"  //恢復起始時間
--stop-datetime="2019-11-26 18:10:00"  //恢復結束時間
D:\MySQL\Data\mysql-bin_copy.000028  //為資料恢復的日誌檔案
mysqllog.sql    //恢復以後我們需要的檔名

使用mysqlbinlog將binlog檔案轉換成可讀的內容時可能會報錯：

mysqlbinlog mysql-bin.000004 mysqlbinlog: unknown variable 'default-character-set=utf8'
原因是mysqlbinlog這個工具無法識別binlog中的配置中的default-character-set=utf8這個指令。

兩個方法可以解決這個問題

一是在MySQL的配置/etc/my.cnf中將default-character-set=utf8 修改為 character-set-server = utf8，但是這需要重啟MySQL服務，如果你的MySQL服務正在忙，那這樣的代價會比較大。

二是用mysqlbinlog –no-defaults mysql-bin.000004 命令開啟

4、恢復 sql 語句

執行

$ cat mysqllog.sql | LC_CTYPE=C sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@6.*),/\1;/g' | sed 's/@[1-9]=//g' | sed 's/@[1-9][0-9]=//g' > result.sql

結論

一定要Mysql開啟binlog功能，開啟binLog功能方法(windows),開啟my.ini檔案，新增如下配置,重啟mysql即可開啟

# log-bin
log-bin=mysql-bin 
binlog_format = ROW

還有就是在操作前一定要開啟事務，等操作結果達到預期後再進行事務提交，一旦發生問題還可以進行回滾。在執行 DML 操作之前，使用begin 或者 START TRANSACTION 開始一個事務，如果需要回滾事務使用 rollback，確認提交事務使用 commit

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章