快捷導(dǎo)航

MySQL誤刪后使用binlog恢復(fù)數(shù)據(jù)的實(shí)現(xiàn)方法

更新時(shí)間：2024年05月23日 11:27:53 作者：紀(jì)佰倫

這篇文章主要介紹了MySQL誤刪后使用binlog恢復(fù)數(shù)據(jù)的實(shí)現(xiàn)方法,使用 binlog 恢復(fù)數(shù)據(jù)的預(yù)期效果是將誤刪的數(shù)據(jù)還原到誤刪之前的狀態(tài),以減少或消除數(shù)據(jù)丟失的影響,文中有相關(guān)的代碼示例和圖文介紹,需要的朋友可以參考下

1 預(yù)期效果

使用 binlog 恢復(fù)數(shù)據(jù)的預(yù)期效果是將誤刪的數(shù)據(jù)還原到誤刪之前的狀態(tài)，以減少或消除數(shù)據(jù)丟失的影響。通過(guò)正確解析和執(zhí)行 binlog 中的操作記錄，可以重新執(zhí)行誤刪操作之后的插入、更新或刪除操作，從而恢復(fù)被誤刪的數(shù)據(jù)。

數(shù)據(jù)恢復(fù)：通過(guò)恢復(fù)誤刪操作之后的操作記錄，可以將誤刪的數(shù)據(jù)重新插入到數(shù)據(jù)庫(kù)中，還原到誤刪之前的狀態(tài)。這意味著恢復(fù)后的數(shù)據(jù)庫(kù)將包含被誤刪的數(shù)據(jù)，以及誤刪之后的其他操作。
數(shù)據(jù)一致性：如果只選擇了誤刪操作之后的操作記錄進(jìn)行恢復(fù)，而忽略了其他更改操作，可以確保恢復(fù)后的數(shù)據(jù)保持一致性。這意味著只有被誤刪的數(shù)據(jù)會(huì)恢復(fù)，而其他更改操作不會(huì)被重新執(zhí)行。
最小化數(shù)據(jù)丟失：使用 binlog 恢復(fù)數(shù)據(jù)可以最小化數(shù)據(jù)丟失的影響。通過(guò)恢復(fù)誤刪操作之后的操作記錄，可以盡可能地還原被誤刪的數(shù)據(jù)，而無(wú)需依賴數(shù)據(jù)庫(kù)備份或其他手段。

需要注意的是，預(yù)期效果可能受到以下因素的影響：

其他更改操作：如果誤刪操作之后進(jìn)行了其他更改操作，恢復(fù)過(guò)程可能會(huì)導(dǎo)致這些操作被重新執(zhí)行，可能會(huì)引起數(shù)據(jù)不一致或沖突。因此，在執(zhí)行恢復(fù)操作之前，應(yīng)仔細(xì)分析 binlog 文件，并選擇適當(dāng)?shù)牟僮饔涗涍M(jìn)行恢復(fù)。
數(shù)據(jù)庫(kù)狀態(tài)和依賴項(xiàng)：誤刪操作可能依賴于特定的數(shù)據(jù)庫(kù)狀態(tài)或外部數(shù)據(jù)。在執(zhí)行恢復(fù)操作之前，應(yīng)確保數(shù)據(jù)庫(kù)的環(huán)境和依賴項(xiàng)與誤刪操作發(fā)生時(shí)相同，以確?；謴?fù)的數(shù)據(jù)能夠正確關(guān)聯(lián)和使用。
恢復(fù)操作的正確性：正確解析和執(zhí)行 binlog 中的操作記錄是關(guān)鍵。在執(zhí)行恢復(fù)操作之前，應(yīng)仔細(xì)驗(yàn)證和測(cè)試恢復(fù)過(guò)程，確保操作記錄的準(zhǔn)確性和正確性。

2 實(shí)現(xiàn)原理

binlog記錄了數(shù)據(jù)庫(kù)中的所有更改操作，以便在需要時(shí)進(jìn)行數(shù)據(jù)恢復(fù)、主從復(fù)制和數(shù)據(jù)審計(jì)等操作。通過(guò)解析和分析binlog，可以還原數(shù)據(jù)庫(kù)中的數(shù)據(jù)更改歷史，并進(jìn)行相應(yīng)的操作，例如數(shù)據(jù)恢復(fù)或主從復(fù)制等

下面是使用 binlog 恢復(fù)數(shù)據(jù)的一般原理：

確認(rèn)誤刪的時(shí)間點(diǎn)：首先，需要確定誤刪操作發(fā)生的時(shí)間點(diǎn)。這將幫助你確定要恢復(fù)的數(shù)據(jù)范圍，以便從 binlog 中提取相應(yīng)的操作記錄。
導(dǎo)出 binlog 文件：找到包含誤刪操作的 binlog 文件。這通常是通過(guò)查看 MySQL 數(shù)據(jù)庫(kù)的配置文件（如 my.cnf 或 my.ini）中的 binlog 相關(guān)配置參數(shù)來(lái)確定。將該 binlog 文件復(fù)制到安全的位置，以便進(jìn)行恢復(fù)操作。
解析 binlog 文件：使用 mysqlbinlog 工具來(lái)解析 binlog 文件，并將其轉(zhuǎn)換為可讀的 SQL 語(yǔ)句。例如，可以執(zhí)行以下命令：mysqlbinlog binlog-file > output.sq 其中 binlog-file 是實(shí)際的 binlog 文件名，output.sql 是輸出的 SQL 文件，包含了所有的操作記錄。
過(guò)濾和恢復(fù)操作：在生成的 SQL 文件中，可以根據(jù)誤刪操作發(fā)生的時(shí)間點(diǎn)，選擇需要恢復(fù)的操作記錄?？梢允謩?dòng)編輯 SQL 文件，刪除不需要的操作記錄，只保留誤刪操作之后的操作語(yǔ)句。確保只包含了需要恢復(fù)數(shù)據(jù)的操作。
執(zhí)行恢復(fù)操作：使用數(shù)據(jù)庫(kù)客戶端連接到 MySQL 數(shù)據(jù)庫(kù)，并執(zhí)行編輯后的 SQL 文件，將其中的操作語(yǔ)句逐個(gè)重新執(zhí)行。這將重新執(zhí)行誤刪操作之后的操作，從而還原到誤刪前的數(shù)據(jù)狀態(tài)。

需要注意的是，使用 binlog 恢復(fù)數(shù)據(jù)存在一些限制和風(fēng)險(xiǎn)，包括：

誤刪操作之后的其他修改：如果誤刪操作之后的時(shí)間段內(nèi)進(jìn)行了其他更改操作，這些操作也將被重新執(zhí)行，可能會(huì)導(dǎo)致數(shù)據(jù)不一致或沖突。在恢復(fù)數(shù)據(jù)之前，應(yīng)仔細(xì)分析 binlog 文件，確保只恢復(fù)必要的操作。
依賴外部數(shù)據(jù)和狀態(tài)：如果誤刪操作涉及到外部數(shù)據(jù)或依賴于特定的數(shù)據(jù)庫(kù)狀態(tài)，恢復(fù)過(guò)程可能會(huì)受到影響。在執(zhí)行恢復(fù)操作之前，確保數(shù)據(jù)庫(kù)的環(huán)境和依賴項(xiàng)與誤刪操作發(fā)生時(shí)相同。
數(shù)據(jù)庫(kù)備份和恢復(fù)策略：為避免數(shù)據(jù)丟失和誤刪除的影響，建議實(shí)施定期的數(shù)據(jù)庫(kù)備份和恢復(fù)策略，并測(cè)試和驗(yàn)證備份的可用性和完整性。

3 實(shí)際操作

3.1 查看自己的binlog日志是否打開

在黑窗口中輸入命令查看show variables like 'log_bin%' ; ，一般都是默認(rèn)打開的

log_bin 變量被設(shè)置為 ON，表示二進(jìn)制日志功能已經(jīng)啟用。
log_bin_basename 顯示二進(jìn)制日志文件的路徑和文件名前綴。
log_bin_index 顯示二進(jìn)制日志索引文件的路徑。
其他一些與二進(jìn)制日志相關(guān)的配置項(xiàng)的值。

sql_log_bin 是 MySQL 中一個(gè)非常有用的系統(tǒng)變量,它控制當(dāng)前會(huì)話是否將執(zhí)行的 SQL 語(yǔ)句記錄到二進(jìn)制日志中。

可以通過(guò)SET sql_log_bin = 1;修改成ON

3.2 查看binlog文件

通過(guò)上一步查詢的log_bin_basename得到的路徑打開存儲(chǔ)binlog文件的文件夾

可以看到已經(jīng)有很多l(xiāng)og文件了

（這里我們是要測(cè)試binlog恢復(fù)數(shù)據(jù)的使用，所以就日志文件都放到一個(gè)全新binlog文件中方便查詢使用，如果是實(shí)際恢復(fù)數(shù)據(jù)的話，就要一個(gè)一個(gè)的在這些binlog文件中找自己要的那部分文件了。）

3.3 模擬數(shù)據(jù)庫(kù)

在數(shù)據(jù)庫(kù)中進(jìn)行 flush logs 命令可以新創(chuàng)一個(gè)binlog文件，接下來(lái)的操作也就會(huì)放到新的文件中了。此時(shí)再進(jìn)入到上面這個(gè)文件夾中就會(huì)看到又多了一個(gè)文件叫做LAPTOP-595LBSCH-bin.000092

假設(shè)我們的數(shù)據(jù)庫(kù)是7天一備份，然后binlog的過(guò)期時(shí)間是大于7天的，那么通過(guò)備份的數(shù)據(jù)庫(kù)+binlog文件就能夠恢復(fù)數(shù)據(jù)庫(kù)到達(dá)7天內(nèi)的任意一個(gè)時(shí)間點(diǎn)的狀態(tài)。，下面是一個(gè)模擬備份的行為

之后我們進(jìn)行一些操作，模擬正常數(shù)據(jù)庫(kù)操作

添加一條數(shù)據(jù)：
INSERT INTO `user` (`id`, `name`) VALUES (6, '老六');
將小二改成張三豐：
UPDATE `user` SET `name` = '張三豐' WHERE `id` = 1;
將王五改成王偉：
UPDATE `user` SET `name` = '王偉' WHERE `name` = '王五';
刪除整個(gè)表：
DROP TABLE `user`;
?

經(jīng)過(guò)這些操作之后！

3.4 恢復(fù)操作實(shí)戰(zhàn)

現(xiàn)在的處境就是整個(gè)表都被刪除了，我們想要實(shí)現(xiàn)將數(shù)據(jù)庫(kù)改成王五剛被改成王偉的數(shù)據(jù)庫(kù)的模樣

我們要做的就是將上次備份的數(shù)據(jù)庫(kù)恢復(fù)，然后從上次備份的時(shí)間點(diǎn) - > 到王五剛被改成王偉的時(shí)間點(diǎn) 中的binlog操作都找到

1、我們?cè)赽inlog所在的文件夾位置打開黑窗口，然后運(yùn)行，(注意LAPTOP-595LBSCH-bin.000092是因?yàn)闇y(cè)試時(shí)候知道剛才的操作一定就在這個(gè)文件中，如果不知道就需要逐個(gè)打開多個(gè)binlog文件然后自己找你想要的那個(gè)時(shí)間點(diǎn)，)

mysqlbinlog -v --set-charset=utf8mb4 LAPTOP-595LBSCH-bin.000092 > output.txt

之后通過(guò)打開這個(gè)output.txt文件可能有部分亂碼（亂碼自己解決，如果實(shí)在解決不了只能猜了。），比如找到這一部分，意思就是將王五改成王五的操作，他們的執(zhí)行行數(shù)在1109

另一種辦法就是在mysql中使用show binlog events in 'LAPTOP-595LBSCH-bin.000092';來(lái)查看binlog中的日志，

我們可以看到有4個(gè)數(shù)據(jù)，有寫入數(shù)據(jù)，刪除更新數(shù)據(jù)等，還有最后一個(gè)是drop table。

經(jīng)過(guò)這些我們已經(jīng)得到了想要的信息，數(shù)據(jù)庫(kù)上次備份后的binlog開始時(shí)間應(yīng)該是317也就是備份后的第一條ddl語(yǔ)句的begin時(shí)間，然后我們想要恢復(fù)到的時(shí)間是1109，日志文件的名字叫做LAPTOP-595LBSCH-bin.000092也就是更新王五那步操作的commit行，之后就是將這個(gè)時(shí)間段內(nèi)binlog記錄的操作都輸入到備份的數(shù)據(jù)庫(kù)中

下面這部操作是在不登陸mysql的黑窗口運(yùn)行的， | mysql -uroot -p<數(shù)據(jù)庫(kù)密碼>的意思就是將前面步驟操作的結(jié)果輸入到后面的命令中

mysqlbinlog --no-defaults --start-position=317 --stop-position=1109 LAPTOP-595LBSCH-bin.000092 | mysql -uroot -p<數(shù)據(jù)庫(kù)密碼>