一、binlog日志恢復數(shù)據(jù)簡介

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

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

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

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

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

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

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

二、使用binlog日志恢復數(shù)據(jù)的步驟

1、前提

在數(shù)據(jù)庫的配置文件中一定要開啟binlog日志，否則不會有binlog日志產(chǎn)生。

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

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

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

[mysqld]
binlog_format = ROW

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

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

注意：在生產(chǎn)環(huán)境中更改這些配置需要謹慎，因為它可能會影響數(shù)據(jù)庫的性能和復制

3、使用命令行在系統(tǒng)中進行操作

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

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

4、確認binlog日志是否開啟

確認binlog已啟用：
SHOW VARIABLES LIKE 'log_bin';

查看當前的日志文件：
SHOW BINARY LOGS;

查看binlog的格式（可選）：
SHOW VARIABLES LIKE 'binlog_format';

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

三、數(shù)據(jù)備份和恢復步驟

 步驟一：在sql中插入數(shù)據(jù)

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

模擬生產(chǎn)每天數(shù)據(jù)備份的的數(shù)據(jù)

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 命令常用的參數(shù)，它們各自有不同的作用，以下為你詳細介紹：

--master-data=2 參數(shù)詳解：

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

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

--single-transaction 參數(shù)詳解：

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

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

--single-transaction
                      Creates a consistent snapshot by dumping all tables in a
                      single transaction. Works ONLY for tables stored in
                      storage engines which support multiversioning (currently
                      only InnoDB does); the dump is NOT guaranteed to be
                      consistent for other storage engines. While a
                      --single-transaction dump is in process, to ensure a
                      valid dump file (correct table contents and binary log
                      position), no other connection should use the following
                      statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
                      TRUNCATE TABLE, as consistent snapshot is not isolated
                      from them. Option automatically turns off --lock-tables.

--single-transaction選項在執(zhí)行mysqldump命令時，會將隔離級別設置為
REPEATABLE READ，并開啟一個事務。這樣，在備份過程中讀取的數(shù)據(jù)是一個邏輯一致的快照，即使在備份過程中有其他會話對數(shù)據(jù)進行修改，
也不會影響到備份的數(shù)據(jù)。這種方式避免了在備份大型數(shù)據(jù)庫時出現(xiàn)長時間的鎖定或阻塞現(xiàn)象，對生產(chǎn)環(huán)境的業(yè)務操作影響較小?。

--master-data=2
該選項將二進制日志的位置和文件名寫入到輸出中。該選項要求有RELOAD權限，并且必須啟用二進制日志。如果該選項值等于1，
位置和文件名被寫入CHANGE MASTER語句形式的轉儲輸出，如果你使用該SQL轉儲主服務器以設置從服務器，從服務器從主服務器二進制日志的正確位置開始。
如果選項值等于2，CHANGE MASTER語句被寫成SQL注釋。如果value被省略，這是默認動作。

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

步驟四：假設不小心刪除了數(shù)據(jù)

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

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

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

步驟七：數(shù)據(jù)恢復

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

到此這篇關于MySQL使用binlog日志恢復數(shù)據(jù)的方法步驟的文章就介紹到這了,更多相關MySQL binlog日志恢復內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論