mysql誤刪資料恢復

1. Binlog 日誌介紹
Binlog 是記錄所有資料庫表結構變更以及表資料修改的二進位制日誌，不會記錄 SELECT 和 SHOW 這類操作。Binlog 日誌是以事件形式記錄，還包含語句所執行的消耗時間。開啟 Binlog 日誌有以下兩個最重要的使用場景。

主從複製：在主庫中開啟 Binlog 功能，這樣主庫就可以把 Binlog 傳遞給從庫，從庫拿到 Binlog 後實現資料恢復達到主從資料一致性。

資料恢復：透過 mysqlbinlog 工具來恢復資料。

Binlog 檔名預設為“主機名_binlog-序列號”格式，例如 oak_binlog-000001，也可以在配置檔案中指定名稱。檔案記錄模式有 STATEMENT、ROW 和 MIXED 三種，具體含義如下。

ROW（row-based replication, RBR）：日誌中會記錄每一行資料被修改的情況，然後在 slave 端對相同的資料進行修改。

優點：能清楚記錄每一個行資料的修改細節，能完全實現主從資料同步和資料的恢復。

缺點：批次操作，會產生大量的日誌，尤其是 alter table 會讓日誌暴漲。

STATMENT（statement-based replication, SBR）：每一條被修改資料的 SQL 都會記錄到 master 的 Binlog 中，slave 在複製的時候 SQL 程序會解析成和原來 master 端執行過的相同的 SQL 再次執行。簡稱 SQL 語句複製。

優點：日誌量小，減少磁碟 IO，提升儲存和恢復速度

缺點：在某些情況下會導致主從資料不一致，比如 last_insert_id()、now()等函式。

MIXED（mixed-based replication, MBR）：以上兩種模式的混合使用，一般會使用 STATEMENT 模式儲存 binlog，對於 STATEMENT 模式無法複製的操作使用 ROW 模式儲存 binlog，MySQL 會根據執行的 SQL 語句選擇寫入模式。

理念
首先使用binlog恢復資料，指的不是透過回滾binlog日誌實現恢復的，而是指的是將刪除的資料，透過一定的方式找回後，重新匯入到該資料庫，進行恢復的。

3、實戰
3.1 Binlog 狀態檢視
show variables like 'log_bin';
我這裡是已經開啟的，如果未開啟，請按照下面方法開啟

3.2 開啟 Binlog 功能
修改 /etc/my.cnf 或 my.ini 配置檔案，在[mysqld]下面增加 log_bin=mysql-bin，重啟 MySQL 服務。
log-bin=mysql-bin
binlog_format=ROW

3.3 再次檢視
show variables like 'log_bin';

常用命令
show binary logs; //等價於show master logs;
show master status;
show binlog events;
show binlog events in 'mysql-bin.000001';

在伺服器中也可以直接使用如下命令：
mysqlbinlog （引數選項）logfilename

引數選項：
-d :指定資料庫名稱，只列出指定的資料庫相關操作。
-o : 忽略掉日志中的前 n 行命令。
-v :將事件重構成sql語句
-vv ：將事件重構成sql語句，並且輸出註釋資訊

4、方案一

4.1、前提條件：
a:開啟了binlog日誌功能
b:之前的binlog日誌未清除
c:此種方案可以實現binlog_format 為MIXED 和ROW的資料恢復

4.2、開始恢復
檢視日誌，大致確定下需要恢復的資料位於哪個日誌檔案中
show binary logs;

檢視 binlog 日誌事件並確定資料的開始和結束的offset

注意：我們恢復資料，這種方式不是透過刪除的事件恢復的，是透過之前建立事件恢復的，所以我們要看清楚offset的位置

執行恢復：在 mysql 資料所在的檔案下，也可以不在，如果不在請補全檔案路徑

//按指定時間恢復 
mysqlbinlog --no-defaults --start-datetime="2021-07-09 12:10:49" --stopdatetime="2021-07-09 12:17:37" mysql-binlog.000008 | mysql -uroot -proot
//按事件位置號恢復 
mysqlbinlog --no-defaults --start-position=234 --stop-position=1373 mysql-binlog.000008 | mysql -uroot -proot
#如果出現警告是密碼直接輸入不安全，可以先不輸入密碼，回車之後輸入

--start-datetime：從二進位制日誌中讀取指定等於時間戳或者晚於本地伺服器的時間
--stop-datetime：從二進位制日誌中讀取指定小於時間戳或者等於本地伺服器的時間取值和上述一樣
--start-position：從二進位制日誌中讀取指定 position 事件位置作為開始。
--stop-position：從二進位制日誌中讀取指定 position 事件位置作為事件截至
--database=lanebin 指定只恢復 lanebin 資料庫(一臺主機上往往有多個資料庫，只限本地 log 日誌)

不常用選項：
-u --user=name 連線到遠端主機的使用者名稱
-p --password[=name] 連線到遠端主機的密碼
-h --host=name 從遠端主機上獲取 binlog 日誌
--read-from-remote-server 從某個 MySQL 伺服器上讀取 binlog 日誌

5.方案二
針對於方案一的情況，如果資料量非常大的情況，恢復速度會很慢，過程有些困難。第二種方案就比較簡潔，效率更快一些。
前置條件：
a.使用的binlog_format為ROW級別
開始操作：
檢視日誌，大致確定下需要恢復的資料位於哪個日誌檔案中
show binary logs;
檢視日誌並確定刪除範圍
show binlog events in 'binlog.000011';

使用命令匯出日誌記錄
按照offset匯出
mysqlbinlog --no-defaults --start-position=236 --stop-position=747 --base64-output=decode-rows --skip-gtids=true -v /var/lib/mysql/binlog.000011 | sed -n '/### DELETE FROM lanebin.user/,/COMMIT/p' > /root/mysql/resultsql-new.sql
按照時間匯出
mysqlbinlog --no-defaults --start-datetime="2021-07-09 12:10:49" --stopdatetime="2021-07-09 12:17:37" --base64-output=decode-rows --skip-gtids=true -v /var/lib/mysql/binlog.000011 | sed -n '/### DELETE FROM lanebin.user/,/COMMIT/p' > /root/mysql/resultsql-new.sql
匯出全部
mysqlbinlog --no-defaults --base64-output=decode-rows --skip-gtids=true -v /var/lib/mysql/binlog.000011 | sed -n '/### DELETE FROM lanebin.user/,/COMMIT/p' > /root/mysql/resultsql-new.sql

正則匹配，清除無效資料
cat resultsql-new.sql | sed -n '/###/p' | sed 's/### //g;s//*./,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@4.),/\1;/g' | sed 's/@[1-30]=//g' > t1.sql
也可以自己在編輯軟體中，直接編輯resultsql-new.sql 將它修改成sql後，放到資料庫中進行恢復資料
最終效果：

mysql誤刪資料恢復

相關文章