mysql誤刪后使用binlog恢復數據

1 預期效果

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

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

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

其他更改操作：如果誤刪操作之后進行了其他更改操作，恢復過程可能會導致這些操作被重新執行，可能會引起數據不一致或沖突。因此，在執行恢復操作之前，應仔細分析 binlog 文件，并選擇適當的操作記錄進行恢復。
數據庫狀態和依賴項：誤刪操作可能依賴于特定的數據庫狀態或外部數據。在執行恢復操作之前，應確保數據庫的環境和依賴項與誤刪操作發生時相同，以確保恢復的數據能夠正確關聯和使用。
恢復操作的正確性：正確解析和執行 binlog 中的操作記錄是關鍵。在執行恢復操作之前，應仔細驗證和測試恢復過程，確保操作記錄的準確性和正確性。

2實現原理

binlog記錄了數據庫中的所有更改操作，以便在需要時進行數據恢復、主從復制和數據審計等操作。通過解析和分析binlog，可以還原數據庫中的數據更改歷史，并進行相應的操作，例如數據恢復或主從復制等

下面是使用 binlog 恢復數據的一般原理：

確認誤刪的時間點：首先，需要確定誤刪操作發生的時間點。這將幫助你確定要恢復的數據范圍，以便從 binlog 中提取相應的操作記錄。
導出 binlog 文件：找到包含誤刪操作的 binlog 文件。這通常是通過查看 MySQL 數據庫的配置文件（如 my.cnf 或 my.ini）中的 binlog 相關配置參數來確定。將該 binlog 文件復制到安全的位置，以便進行恢復操作。
解析 binlog 文件：使用?mysqlbinlog?工具來解析 binlog 文件，并將其轉換為可讀的 SQL 語句。例如，可以執行以下命令：mysqlbinlog binlog-file > output.sq? ? ? ? ? ? ? 其中?binlog-file?是實際的 binlog 文件名，output.sql?是輸出的 SQL 文件，包含了所有的操作記錄。
過濾和恢復操作：在生成的 SQL 文件中，可以根據誤刪操作發生的時間點，選擇需要恢復的操作記錄。可以手動編輯 SQL 文件，刪除不需要的操作記錄，只保留誤刪操作之后的操作語句。確保只包含了需要恢復數據的操作。
執行恢復操作：使用數據庫客戶端連接到 MySQL 數據庫，并執行編輯后的 SQL 文件，將其中的操作語句逐個重新執行。這將重新執行誤刪操作之后的操作，從而還原到誤刪前的數據狀態。

需要注意的是，使用 binlog 恢復數據存在一些限制和風險，包括：

誤刪操作之后的其他修改：如果誤刪操作之后的時間段內進行了其他更改操作，這些操作也將被重新執行，可能會導致數據不一致或沖突。在恢復數據之前，應仔細分析 binlog 文件，確保只恢復必要的操作。
依賴外部數據和狀態：如果誤刪操作涉及到外部數據或依賴于特定的數據庫狀態，恢復過程可能會受到影響。在執行恢復操作之前，確保數據庫的環境和依賴項與誤刪操作發生時相同。
數據庫備份和恢復策略：為避免數據丟失和誤刪除的影響，建議實施定期的數據庫備份和恢復策略，并測試和驗證備份的可用性和完整性。

3實際操作

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

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

log_bin 變量被設置為 ON，表示二進制日志功能已經啟用。
log_bin_basename 顯示二進制日志文件的路徑和文件名前綴。
log_bin_index 顯示二進制日志索引文件的路徑。
其他一些與二進制日志相關的配置項的值。

sql_log_bin 是 MySQL 中一個非常有用的系統變量,它控制當前會話是否將執行的 SQL 語句記錄到二進制日志中。

可以通過SET sql_log_bin = 1;修改成ON

3.2 查看binlog文件

通過上一步查詢的log_bin_basename得到的路徑打開存儲binlog文件的文件夾

可以看到已經有很多log文件了

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

3.3 模擬數據庫

在數據庫中進行 flush logs?命令可以新創一個binlog文件，接下來的操作也就會放到新的文件中了。此時再進入到上面這個文件夾中就會看到又多了一個文件叫做LAPTOP-595LBSCH-bin.000092

假設我們的數據庫是7天一備份，然后binlog的過期時間是大于7天的，那么通過備份的數據庫+binlog文件就能夠恢復數據庫到達7天內的任意一個時間點的狀態。，下面是一個模擬備份的行為

之后我們進行一些操作，模擬正常數據庫操作

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

經過這些操作之后！

3.4 恢復操作實戰

現在的處境就是整個表都被刪除了，我們想要實現將數據庫改成王五剛被改成王偉的數據庫的模樣

我們要做的就是將上次備份的數據庫恢復，然后從上次備份的時間點 - > 到王五剛被改成王偉的時間點? ?中的binlog操作都找到

3.41、我們在binlog所在的文件夾位置打開黑窗口，然后運行，(注意LAPTOP-595LBSCH-bin.000092是因為測試時候知道剛才的操作一定就在這個文件中，如果不知道就需要逐個打開多個binlog文件然后自己找你想要的那個時間點，)

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

之后通過打開這個output.txt文件可能有部分亂碼（亂碼自己解決，如果實在解決不了只能猜了。），比如找到這一部分，意思就是將王五改成王五的操作，他們的執行行數在1109另一種辦法就是在mysql中使用show binlog events in 'LAPTOP-595LBSCH-bin.000092';來查看binlog中的日志，

我們可以看到有4個數據，有寫入數據，刪除更新數據等，還有最后一個是drop table。

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

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

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