MySQL—使用binlog日志恢復數據

一、binlog日志恢復數據簡介

在 MySQL 中，使用二進制日志（binlog）恢復數據是一種常見的用于故障恢復或數據找回的方法。以下是詳細的使用步驟：

確認 binlog 已啟用：首先需要確認 MySQL 服務器已經啟用了二進制日志功能。可以通過查看 MySQL 的配置文件（通常是?my.cnf?或?my.ini），檢查是否存在?log-bin?配置項。如果配置文件中存在類似?log-bin=mysql-bin?的配置，就表示已經啟用了二進制日志。也可以在 MySQL 命令行中執行?SHOW VARIABLES LIKE 'log_bin';?命令，若?Value?為?ON，則說明已啟用。
找到需要的 binlog 文件：二進制日志文件默認會以?mysql-bin.xxxxxx?的形式命名，xxxxxx?是一個數字編號。可以通過?SHOW BINARY LOGS;?命令查看所有的二進制日志文件列表，確定需要用于恢復數據的日志文件范圍。如果知道數據丟失或誤操作的大致時間點，可以使用?SHOW BINLOG EVENTS IN '日志文件名';?命令查看指定日志文件中的事件，找到對應的操作記錄。
準備恢復環境：為了恢復數據，最好在一個與原生產環境相同或相似的測試環境中進行操作。可以使用備份的數據文件先恢復到一個時間點，然后再通過 binlog 來補充后續的操作。
使用 mysqlbinlog 工具解析 binlog：mysqlbinlog?是 MySQL 提供的用于解析二進制日志的工具。可以使用以下命令來解析指定的二進制日志文件：

mysqlbinlog [選項] 二進制日志文件名

例如，mysqlbinlog --no-defaults mysql-bin.000001?可以解析?mysql-bin.000001?這個日志文件。常用的選項包括?--start-datetime?和?--stop-datetime?來指定時間范圍，--start-position?和?--stop-position?來指定日志位置范圍。例如，只恢復某個時間段內的操作，可以使用?mysqlbinlog --start-datetime='2024-01-01 00:00:00' --stop-datetime='2024-01-02 00:00:00' mysql-bin.000001?。
5.?將解析后的內容應用到數據庫：將?mysqlbinlog?解析后的 SQL 語句應用到目標數據庫中，可以將解析結果通過管道直接輸入到?mysql?客戶端來執行。例如：

mysqlbinlog [選項] 二進制日志文件名 | mysql -u用戶名 -p密碼

假設用戶名是?root，密碼是?123456，要恢復?mysql-bin.000001?這個日志文件中的數據，可以執行?mysqlbinlog --no-defaults mysql-bin.000001 | mysql -uroot -p123456?。

在使用 binlog 恢復數據時，要特別小心，因為錯誤的操作可能會導致數據進一步丟失或損壞。在正式恢復生產環境數據之前，務必在測試環境中進行充分的測試。

二、使用binlog日志恢復數據的步驟

1、前提

在數據庫的配置文件中一定要開啟binlog日志，否則不會有binlog日志產生。

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
server-id = 1

2、可選擇的binlog日志配置項

添加配置項：在[mysqld]部分添加或修改以下配置內容。
- server-id=1：每個 MySQL 服務器必須有一個唯一的 ID，一般設置為正整數。
- log_bin=mysql-bin：指定開啟 binlog 日志，并設置日志文件的基礎名，默認存儲在 MySQL 的數據目錄下，也可指定絕對路徑，如log_bin=/data/mysql/mysql-bin。
- binlog_format=ROW：設置 binlog 的格式，可選項有ROW（記錄每一行數據的修改細節）、STATEMENT（記錄 SQL 語句本身）、MIXED（混合模式），推薦使用ROW格式。
- expire_logs_days=7：設置 binlog 日志自動過期的天數，到期后會自動刪除。

[mysqld]
binlog_format = ROW

STATEMENT格式記錄了語句的原文，RO格式記錄了每行數據的變化，MIXED格式在某些情況下會記錄為STATEMENT，在其他情況下會記錄為ROW。

確保配置后重啟MySQL服務以使更改生效。

注意：在生產環境中更改這些配置需要謹慎，因為它可能會影響數據庫的性能和復制

3、使用命令行在系統中進行操作

登錄 MySQL：使用命令mysql -u root -p，輸入密碼登錄到 MySQL 數據庫3。
執行命令啟用 binlog3
- SET GLOBAL binlog_format=ROW;：設置 binlog 格式為ROW，也可根據需求設置為STATEMENT或MIXED。
- SET GLOBAL binlog-do-db=<要記錄更改的數據庫>;：指定要記錄更改的數據庫，如果要記錄多個數據庫，數據庫之間用逗號分隔。
- SET GLOBAL binlog-ignore-db=<要忽略的數據庫>;：指定要忽略的數據庫，多個數據庫之間用逗號分隔。
保存設置：執行COMMIT;保存設置3。

配置完成后，可以使用show variables like 'log_bin%';命令查看 binlog 是否已啟用。如果Value為ON，則表示 binlog 已經成功開啟。

4、確認binlog日志是否開啟

確認binlog已啟用：
SHOW VARIABLES LIKE 'log_bin';查看當前的日志文件：
SHOW BINARY LOGS;查看binlog的格式（可選）：
SHOW VARIABLES LIKE 'binlog_format';

5、使用mysqlbinlog工具查看binlog二進制日志文件

三、數據備份和恢復步驟

?步驟一：在sql中插入數據

步驟二：備份數據(準確定位到需要恢復數據的時間點)

模擬生產每天數據備份的的數據

mysqldump -ustc -pppp --master-data=2 --single-transaction -S /opt/sumscope/mysql/mysql.sock test stc > stc.sql

備份命令要帶上 --master-data=2 --single-transaction

在 MySQL 中，--master-data=2?和?--single-transaction?是?mysqldump?命令常用的參數，它們各自有不同的作用，以下為你詳細介紹：

`--master-data=2`?參數詳解：

作用：該參數用于在執行?mysqldump?備份時，記錄主服務器的二進制日志文件名（File）和位置（Position）信息到備份文件中。這對于后續搭建主從復制環境非常重要，因為從服務器需要知道從主服務器的哪個二進制日志位置開始復制數據。當?--master-data?設置為?2?時，會在備份文件中添加一個?CHANGE MASTER TO?語句，其中包含了主服務器的二進制日志文件名和位置信息。
示例：假設執行?mysqldump --master-data=2 -u root -p mydatabase > backup.sql?命令來備份名為?mydatabase?的數據庫。備份完成后，在?backup.sql?文件中會看到類似以下的內容（部分示例）：

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=459;
--
-- Current Database: `mydatabase`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydatabase` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `mydatabase`;
--
-- Table structure for table `users`
--
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) NOT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
--
-- Dumping data for table `users`
--
LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` (`id`, `name`) VALUES (1,'John');
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

與?--master-data=1?的區別：--master-data=1?也會記錄主服務器的二進制日志信息，但它會在執行?mysqldump?時，對主服務器加全局讀鎖（FLUSH TABLES WITH READ LOCK），直到備份完成，這期間主服務器無法進行寫入操作，會影響數據庫的可用性。而?--master-data=2?不會加全局讀鎖，它是通過在事務中獲取二進制日志位置信息來實現的，對數據庫的影響較小。

`--single-transaction`?參數詳解：

作用：該參數主要用于在 InnoDB 存儲引擎的數據庫上進行一致性備份。它會在備份開始時開啟一個事務，然后在這個事務中執行?SELECT?語句來獲取數據，由于 InnoDB 的 MVCC（多版本并發控制）機制，在事務執行期間，其他事務對數據的修改不會影響到本次備份的數據讀取，從而保證了備份數據的一致性。在備份過程中，不會對表加鎖（除了在獲取二進制日志位置時可能會有短暫的鎖），所以可以在數據庫正常運行時進行備份，不影響業務的寫入操作。
適用場景：適用于需要在不影響數據庫正常運行的情況下進行在線備份的場景，特別是對于寫入頻繁的 InnoDB 數據庫。例如，在一個電商網站的數據庫中，使用?--single-transaction?參數可以在不中斷訂單處理等寫入操作的同時，獲取到一個一致的數據庫備份。
注意事項：--single-transaction?只對 InnoDB 存儲引擎有效，對于其他存儲引擎（如 MyISAM）不起作用。因為 MyISAM 表不支持事務，所以在備份 MyISAM 表時，可能會出現數據不一致的情況。

--master-data=2?主要用于記錄主服務器的二進制日志信息以便后續搭建主從復制，--single-transaction?則用于在不影響數據庫正常寫入的情況下實現 InnoDB 數據庫的一致性備份。

--single-transactionCreates a consistent snapshot by dumping all tables in asingle transaction. Works ONLY for tables stored instorage engines which support multiversioning (currentlyonly InnoDB does); the dump is NOT guaranteed to beconsistent for other storage engines. While a--single-transaction dump is in process, to ensure avalid dump file (correct table contents and binary logposition), no other connection should use the followingstatements: ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE, as consistent snapshot is not isolatedfrom them. Option automatically turns off --lock-tables.--single-transaction選項在執行mysqldump命令時，會將隔離級別設置為
REPEATABLE READ，并開啟一個事務。這樣，在備份過程中讀取的數據是一個邏輯一致的快照，即使在備份過程中有其他會話對數據進行修改，
也不會影響到備份的數據。這種方式避免了在備份大型數據庫時出現長時間的鎖定或阻塞現象，對生產環境的業務操作影響較小?。--master-data=2
該選項將二進制日志的位置和文件名寫入到輸出中。該選項要求有RELOAD權限，并且必須啟用二進制日志。如果該選項值等于1，
位置和文件名被寫入CHANGE MASTER語句形式的轉儲輸出，如果你使用該SQL轉儲主服務器以設置從服務器，從服務器從主服務器二進制日志的正確位置開始。
如果選項值等于2，CHANGE MASTER語句被寫成SQL注釋。如果value被省略，這是默認動作。

步驟三：在向數據庫中插入數據模擬備份到誤刪除中間的時間段還有其他數據入庫?

步驟四：假設不小心刪除了數據

步驟五：使用mysqlbinlog命令查看binlog日志明文確定刪除前的POS的點好截取相關的日志文件

步驟六：查看誤刪時間段的日志信息

/opt/sumscope/mysql/bin/mysqlbinlog binlog.000002  --start-position=備份數據的POS --stop-position=刪除數據的POS -vv > redo.biglog

步驟七：數據恢復

 --先導入備份的數據source /opt/sumscope/mysql/logs/stc.sql--再導入binlog中的日志source /opt/sumscope/mysql/logs/redo.biglog