MySQL通過binlog實現(xiàn)恢復數(shù)據(jù)

更新時間：2025年01月26日 09:42:43 作者：魯班班

在MySQL中,如果不小心刪除了數(shù)據(jù),可以利用二進制日志（binlog）來恢復數(shù)據(jù),本文將通過幾個示例為大家介紹一下具體實現(xiàn)方法,希望對大家有所幫助

一、背景

在MySQL中，如果不小心刪除了數(shù)據(jù)，可以利用二進制日志（binlog）來恢復數(shù)據(jù)。

實質就是將binlog記錄中的事件再次執(zhí)行一遍。

二、前提條件

啟用二進制日志：確保 MySQL 啟用了二進制日志功能。

有足夠的權限：確保有權限訪問和讀取二進制日志文件。

三、恢復步驟

1.找到相關的二進制日志文件：

查看是否開啟二進制日志文件

SHOW VARIABLES LIKE 'log_bin%';

查看二進制日志文件位置

SHOW VARIABLES LIKE 'log_bin_basename';

查看二進制日志文件列表

SHOW BINARY LOGS;

2.使用 mysqlbinlog 工具提取日志：事件位置

先使用show binlog events命令查看binlog記錄，確定事件開始位置和結束位置。

查看二進制日志記錄

show binlog events in 'binlog.00001';

再使用 mysqlbinlog 提取開始位置和結束位置的日志：

mysqlbinlog /path/to/binlog.000001 --start-position=13508 --stop-position=14142 | mysql -u username -p database_name

替換 /path/to/binlog.000001 為二進制日志文件路徑

修改stop-position和stop-position

替換 username 為MySQL 用戶名，database_name 為數(shù)據(jù)庫名稱。

3.使用 mysqlbinlog 工具提取日志：時間段

使用 mysqlbinlog 提取特定時間段的日志：

mysqlbinlog /path/to/binlog.000001 --start-datetime="YYYY-MM-DD HH:MM:SS" --stop-datetime="YYYY-MM-DD HH:MM:SS" | mysql -u username -p database_name

替換 /path/to/binlog.000001 為二進制日志文件路徑

修改 start-datetime 和 stop-datetime

替換 username 為MySQL 用戶名，database_name 為數(shù)據(jù)庫名稱。

注意事項

備份當前數(shù)據(jù)：在進行數(shù)據(jù)恢復操作之前，最好先備份當前數(shù)據(jù)庫，以防止進一步的數(shù)據(jù)丟失。

測試恢復腳本：在生產環(huán)境中執(zhí)行恢復腳本之前，可以先在測試環(huán)境中進行測試，確保恢復操作的正確性。

mysqlbinlog命令只用于恢復，不能用于回滾。適用數(shù)據(jù)遷移，數(shù)據(jù)同步的場景。

四、實操

1.插入數(shù)據(jù)

mysql -u root -p 登陸

?  ~ mysql -u root -p                  
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \\g.
Your MySQL connection id is 40
Server version: 8.4.3 MySQL Community Server - GPL

插入兩條數(shù)據(jù)

mysql> use ban;

mysql> INSERT INTO t_user (id, name, phone) VALUES ('1', '小明', '110');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_user (id, name, phone) VALUES ('2', '小紅', '120');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+----+--------+-------+---------------------+---------------------+
| id | name   | phone | create_dt           | update_dt           |
+----+--------+-------+---------------------+---------------------+
| 1  | 小明   | 110   | 2025-01-24 15:19:39 | 2025-01-24 15:19:39 |
| 2  | 小紅   | 120   | 2025-01-24 15:19:46 | 2025-01-24 15:19:46 |
+----+--------+-------+---------------------+---------------------+
2 rows in set (0.00 sec)

2.刪除數(shù)據(jù)

mysql> delete from t_user where id in(1,2);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t_user;
Empty set (0.00 sec)

3.通過binlog恢復刪除的數(shù)據(jù)

找到二進制日志文件：/usr/local/mysql/data/binlog.000002

mysql> SHOW VARIABLES LIKE 'log_bin_basename';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| log_bin_basename | /usr/local/mysql/data/binlog |
+------------------+------------------------------+
1 row in set (0.01 sec)

???????mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       668 | No        |
| binlog.000002 |     14142 | No        |
| binlog.000003 |       181 | No        |
| binlog.000004 |       181 | No        |
| binlog.000005 |       181 | No        |
+---------------+-----------+-----------+
5 rows in set (0.01 sec)

查看二進制日志記錄，確認事件開始位置（13508）和結束位置（14142）

mysql> show binlog events in 'binlog.00002';

| Log_name      | Pos   | Event_type     | Server_id | End_log_pos | Info  
| binlog.00002 | 13508 | Anonymous_Gtid |         1 |       13587 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
| binlog.00002 | 13587 | Query          |         1 |       13669 | BEGIN                               
| binlog.00002 | 13669 | Table_map      |         1 |       13734 | table_id: 109 (ban.t_user)          
| binlog.00002 | 13734 | Write_rows     |         1 |       13794 | table_id: 109 flags: STMT_END_F     
| binlog.00002 | 13794 | Xid            |         1 |       13825 | COMMIT /* xid=1058 */               
| binlog.00002 | 13825 | Anonymous_Gtid |         1 |       13904 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
| binlog.00002 | 13904 | Query          |         1 |       13986 | BEGIN                               
| binlog.00002 | 13986 | Table_map      |         1 |       14051 | table_id: 109 (ban.t_user)          
| binlog.00002 | 14051 | Write_rows     |         1 |       14111 | table_id: 109 flags: STMT_END_F     
| binlog.00002 | 14111 | Xid            |         1 |       14142 | COMMIT /* xid=1059 */

使用 mysqlbinlog 恢復數(shù)據(jù)

?  ~ mysqlbinlog /usr/local/mysql/data/binlog.00002 --start-position=13508 --stop-position=14142 | mysql -u root -p ban

4.完整示例

--登陸--
?  ~ mysql -u root -p                  
Enter password: 

--插入數(shù)據(jù)----
mysql> use ban;
mysql> INSERT INTO t_user (id, name, phone) VALUES ('1', '小明', '110');
mysql> INSERT INTO t_user (id, name, phone) VALUES ('2', '小紅', '120');
mysql> select * from t_user;
+----+--------+-------+---------------------+---------------------+
| id | name   | phone | create_dt           | update_dt           |
+----+--------+-------+---------------------+---------------------+
| 1  | 小明   | 110   | 2025-01-24 15:19:39 | 2025-01-24 15:19:39 |
| 2  | 小紅   | 120   | 2025-01-24 15:19:46 | 2025-01-24 15:19:46 |
+----+--------+-------+---------------------+---------------------+
2 rows in set (0.00 sec)

--刪除數(shù)據(jù)--
mysql> delete from t_user where id in(1,2);
mysql> select * from t_user;
Empty set (0.00 sec)

--通過binlog恢復刪除的數(shù)據(jù)--

--找到二進制日志文件：/usr/local/mysql/data/binlog.000002
mysql> SHOW VARIABLES LIKE 'log_bin_basename';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| log_bin_basename | /usr/local/mysql/data/binlog |
+------------------+------------------------------+
1 row in set (0.01 sec)

mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       668 | No        |
| binlog.000002 |     14142 | No        |
| binlog.000003 |       181 | No        |
| binlog.000004 |       181 | No        |
| binlog.000005 |       181 | No        |
+---------------+-----------+-----------+
5 rows in set (0.01 sec)

--查看二進制日志記錄，確認事件開始位置（13508）和結束位置（14142）--
mysql> show binlog events in 'binlog.00002';

| Log_name      | Pos   | Event_type     | Server_id | End_log_pos | Info  
| binlog.00002 | 13508 | Anonymous_Gtid |         1 |       13587 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
| binlog.00002 | 13587 | Query          |         1 |       13669 | BEGIN                               
| binlog.00002 | 13669 | Table_map      |         1 |       13734 | table_id: 109 (ban.t_user)          
| binlog.00002 | 13734 | Write_rows     |         1 |       13794 | table_id: 109 flags: STMT_END_F     
| binlog.00002 | 13794 | Xid            |         1 |       13825 | COMMIT /* xid=1058 */               
| binlog.00002 | 13825 | Anonymous_Gtid |         1 |       13904 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
| binlog.00002 | 13904 | Query          |         1 |       13986 | BEGIN                               
| binlog.00002 | 13986 | Table_map      |         1 |       14051 | table_id: 109 (ban.t_user)          
| binlog.00002 | 14051 | Write_rows     |         1 |       14111 | table_id: 109 flags: STMT_END_F     
| binlog.00002 | 14111 | Xid            |         1 |       14142 | COMMIT /* xid=1059 */            

--使用 mysqlbinlog 恢復數(shù)據(jù)--
?  ~ mysqlbinlog /usr/local/mysql/data/binlog.00002 --start-position=13508 --stop-position=14142 | mysql -u root -p ban

到此這篇關于MySQL通過binlog實現(xiàn)恢復數(shù)據(jù)的文章就介紹到這了,更多相關MySQL binlog恢復數(shù)據(jù)內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

MySQL
binlog

MySQL9.1.0實現(xiàn)GTID模式的項目實踐
本文主要介紹了在MySQL 9.1.0中實現(xiàn)GTID模式的主從復制,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
2025-02-02
詳解MySQL如何交換兩列值數(shù)據(jù)
最近遇到了需要將表里的兩列值進行互換,原因是存庫時值存放反了,在編碼語言中,我們一般可以通過定義一個臨時變量c,將b賦值給c,a賦值給b,c賦值給a就能實現(xiàn)a和b的值互換,那么,在mysql中,非儲存過程的sql語句,沒有定義變量的過程,如何實現(xiàn)呢,接下來由小編給大家介紹一下
2024-01-01
MySQL免密登錄的幾種配置方式小結
在日常運維中,為了簡化登錄操作或提高自動化腳本執(zhí)行的效率,我們經常需要配置 MySQL 免密登錄,MySQL 支持通過多種方式實現(xiàn)免密登錄,這樣我們就可以避免每次輸入密碼,本文給大家介紹了MySQL免密登錄的幾種配置方式,需要的朋友可以參考下
2024-10-10
MySQL分支和循環(huán)結構方式
在MySQL中,IF函數(shù)用于根據(jù)條件返回不同的值,類似于Java的三目運算符,CASE語句則提供了兩種形式：簡單CASE函數(shù)和搜索CASE函數(shù),分別類似于Java中的switch-case結構和多重if判斷,這些控制流函數(shù)在數(shù)據(jù)庫查詢和數(shù)據(jù)處理中非常有用,可以實現(xiàn)復雜的邏輯判斷
2024-10-10
mysql 8.0.18 安裝配置方法圖文教程(linux)
這篇文章主要介紹了linux下mysql 8.0.18 安裝配置方法圖文教程，文中安裝步驟介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2019-11-11
寶塔安裝的MySQL無法連接的情況及解決方案
寶塔面板是一款流行的服務器管理工具,其中集成的 MySQL 數(shù)據(jù)庫有時會出現(xiàn)連接問題,本文詳細介紹兩種最常見的 MySQL 連接錯誤：“1130 - Host is not allowed to connect” 和 “1045 - Access denied”,以及它們的解決方案,需要的朋友可以參考下
2025-05-05
mysql安裝配置詳解教程（一）
這篇文章主要為大家詳細介紹了mysql安裝配置教程，以圖文結合的方式為大家分享了mysql安裝步驟，感興趣的小伙伴們可以參考一下
2016-06-06
MySQL中主從復制重復鍵問題修復方法
這篇文章主要介紹了MySQL中主從復制重復鍵問題修復,需要的朋友可以參考下
2014-12-12
MySQL中count()查詢的性能梳理
這篇文章主要為大家詳細介紹了MySQL中count()查詢的性能對比與優(yōu)化，文中的示例代碼講解詳細，具有一定的參考價值，需要的可以了解一下
2023-07-07
關于B+樹的使用及說明
這篇文章主要介紹了關于B+樹的使用及說明,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
2025-06-06