快捷導(dǎo)航

MYSQL?Binlog恢復(fù)誤刪數(shù)據(jù)庫詳解

更新時(shí)間：2022年11月18日 11:40:08 作者：Mingvvv

MySQL一旦誤刪數(shù)據(jù)庫之后恢復(fù)數(shù)據(jù)很麻煩,這里記錄一下艱辛的恢復(fù)過程,這篇文章主要給大家介紹了關(guān)于如何利用MySQL的binlog恢復(fù)誤刪數(shù)據(jù)庫的相關(guān)資料,需要的朋友可以參考下

對(duì)表誤刪或執(zhí)行缺少條件的修改 SQL 導(dǎo)致修改了表內(nèi)其他數(shù)據(jù)時(shí)，我們需要想辦法將數(shù)據(jù)恢復(fù)回來。

先創(chuàng)建兩個(gè)測(cè)試表 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日志

-- 查看日志狀態(tài)
show variables like '%log_bin%';

之前沒有開啟 Binlog 日志的話下面的方法就不能繼續(xù)操作了，開啟日志的方法網(wǎng)上有很多，修改一下配置文件并重啟 MYSQL 服務(wù)。

模擬刪除

正常情況下，我們需要從線上正在使用的日志中查找信息，要考慮的因素會(huì)很多。

為了方便測(cè)試，這里重啟了一個(gè)新的日志，后面的所有操作都會(huì)記錄到新的日志文件中。

flush logs

-- 插入table1一些數(shù)據(jù)
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的內(nèi)容
update table_1 set name= Concat(name,'1') ;

修復(fù)步驟

我們需要?jiǎng)h除的信息，并保留刪除之后修改過的信息

-- 查看日志內(nèi)容
show master status;
show binlog events in 'binlog.000023';

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

如圖，我們?cè)?993 的位置執(zhí)行了刪除表的操作，我們要把數(shù)據(jù)還原到這個(gè)位置之前

每一個(gè)事務(wù)從 Gtid 開始，Xid 結(jié)束。

有的時(shí)候，我們不確定這個(gè)位置在什么地方，但是我們有時(shí)間范圍，可以根據(jù)時(shí)間范圍縮小所有區(qū)間，這里需要用到 binlog 命令

以下是 Windows CMD 的命令實(shí)例：

mysqlbinlog.exe F:\Mysql\mysql-8.0.27-winx64\data\binlog.000021 --start-datetime="2022-11-17 13:00:00" --stop-datetime="2022-11-17 14:00:00"

控制臺(tái)打印出來的結(jié)果和SQL查詢出來的結(jié)果大致上是一樣的。

找到我們要還原的位置之后，執(zhí)行還原命令。

以下是 Windows CMD 的命令實(shí)例：

mysqlbinlog.exe F:\Mysql\mysql-8.0.27-winx64\data\binlog.000023 --stop-position=993 --database=dgcat | F:\Mysql\mysql-8.0.27-winx64\bin\mysql -uroot -p*****

執(zhí)行后發(fā)現(xiàn)報(bào)錯(cuò)：

反饋 table_1表中已經(jīng)有了主鍵 1 ，不能插入。是因?yàn)槲覀冎恢付藬?shù)據(jù)恢復(fù)的結(jié)束位置，而當(dāng)前日志又包含了 table_1 的所有行插入記錄，當(dāng)恢復(fù)表時(shí)，他會(huì)重新插入這些行，導(dǎo)致和表內(nèi)已存在的信息主鍵沖突。

因?yàn)槲业?binlog 日志記錄了 table_1 的所有操作記錄。所以這里我選擇將 table_1 清空，用日志恢復(fù)所有數(shù)據(jù)。

truncate table table_1

正常情況下，我們最好能確認(rèn)下來數(shù)據(jù)恢復(fù)的開始位置。

再次執(zhí)行恢復(fù)命令后，表內(nèi)信息如下：

現(xiàn)在已經(jīng)恢復(fù)了我們刪除之前的狀態(tài)，但是我們?cè)趧h除之后還做了修改操作，我們還需要把修改操作也還原一下：

相當(dāng)于跳過 delete 操作，1100 位置即是我們修改操作的開始位置，直至 1478 整個(gè)操作結(jié)束。

mysqlbinlog.exe F:\Mysql\mysql-8.0.27-winx64\data\binlog.000023 --start-position=1100 --stop-position=1478 --database=dgcat | F:\Mysql\mysql-8.0.27-winx64\bin\mysql -uroot -pzdm3713261995!!!

執(zhí)行結(jié)束后我們?cè)诓樵儽韮?nèi)信息，刪除的數(shù)據(jù)恢復(fù)了，而且我們修改過的記錄也在。

到此這篇關(guān)于MYSQL Binlog恢復(fù)誤刪數(shù)據(jù)庫詳解的文章就介紹到這了,更多相關(guān)MYSQL Binlog恢復(fù)數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: