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

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

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

mysqlbinlog [選項(xiàng)] 二進(jìn)制日志文件名

例如，mysqlbinlog --no-defaults mysql-bin.000001 可以解析 mysql-bin.000001 這個(gè)日志文件。常用的選項(xiàng)包括 --start-datetime 和 --stop-datetime 來(lái)指定時(shí)間范圍，--start-position 和 --stop-position 來(lái)指定日志位置范圍。例如，只恢復(fù)某個(gè)時(shí)間段內(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)容應(yīng)用到數(shù)據(jù)庫(kù)：將 mysqlbinlog 解析后的 SQL 語(yǔ)句應(yīng)用到目標(biāo)數(shù)據(jù)庫(kù)中，可以將解析結(jié)果通過(guò)管道直接輸入到 mysql 客戶端來(lái)執(zhí)行。例如：

mysqlbinlog [選項(xiàng)] 二進(jìn)制日志文件名 | mysql -u用戶名 -p密碼

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

在使用 binlog 恢復(fù)數(shù)據(jù)時(shí)，要特別小心，因?yàn)殄e(cuò)誤的操作可能會(huì)導(dǎo)致數(shù)據(jù)進(jìn)一步丟失或損壞。在正式恢復(fù)生產(chǎn)環(huán)境數(shù)據(jù)之前，務(wù)必在測(cè)試環(huán)境中進(jìn)行充分的測(cè)試。

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

1、前提

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

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

2、可選擇的binlog日志配置項(xiàng)

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

[mysqld]
binlog_format = ROW

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

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

注意：在生產(chǎn)環(huán)境中更改這些配置需要謹(jǐn)慎，因?yàn)樗赡軙?huì)影響數(shù)據(jù)庫(kù)的性能和復(fù)制

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

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

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

4、確認(rèn)binlog日志是否開(kāi)啟

確認(rèn)binlog已啟用：
SHOW VARIABLES LIKE 'log_bin';

查看當(dāng)前的日志文件：
SHOW BINARY LOGS;

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

5、使用mysqlbinlog工具查看binlog二進(jìn)制日志文件

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

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

步驟二：備份數(shù)據(jù)(準(zhǔn)確定位到需要恢復(fù)數(shù)據(jù)的時(shí)間點(diǎn))

模擬生產(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ù)，它們各自有不同的作用，以下為你詳細(xì)介紹：

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

• 作用：該參數(shù)用于在執(zhí)行 mysqldump 備份時(shí)，記錄主服務(wù)器的二進(jìn)制日志文件名（File）和位置（Position）信息到備份文件中。這對(duì)于后續(xù)搭建主從復(fù)制環(huán)境非常重要，因?yàn)閺姆?wù)器需要知道從主服務(wù)器的哪個(gè)二進(jìn)制日志位置開(kāi)始復(fù)制數(shù)據(jù)。當(dāng) --master-data 設(shè)置為 2 時(shí)，會(huì)在備份文件中添加一個(gè) CHANGE MASTER TO 語(yǔ)句，其中包含了主服務(wù)器的二進(jìn)制日志文件名和位置信息。
• 示例：假設(shè)執(zhí)行 mysqldump --master-data=2 -u root -p mydatabase > backup.sql 命令來(lái)備份名為 mydatabase 的數(shù)據(jù)庫(kù)。備份完成后，在 backup.sql 文件中會(huì)看到類似以下的內(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 也會(huì)記錄主服務(wù)器的二進(jìn)制日志信息，但它會(huì)在執(zhí)行 mysqldump 時(shí)，對(duì)主服務(wù)器加全局讀鎖（FLUSH TABLES WITH READ LOCK），直到備份完成，這期間主服務(wù)器無(wú)法進(jìn)行寫(xiě)入操作，會(huì)影響數(shù)據(jù)庫(kù)的可用性。而 --master-data=2 不會(huì)加全局讀鎖，它是通過(guò)在事務(wù)中獲取二進(jìn)制日志位置信息來(lái)實(shí)現(xiàn)的，對(duì)數(shù)據(jù)庫(kù)的影響較小。

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

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

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

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

--master-data=2
該選項(xiàng)將二進(jìn)制日志的位置和文件名寫(xiě)入到輸出中。該選項(xiàng)要求有RELOAD權(quán)限，并且必須啟用二進(jìn)制日志。如果該選項(xiàng)值等于1，
位置和文件名被寫(xiě)入CHANGE MASTER語(yǔ)句形式的轉(zhuǎn)儲(chǔ)輸出，如果你使用該SQL轉(zhuǎn)儲(chǔ)主服務(wù)器以設(shè)置從服務(wù)器，從服務(wù)器從主服務(wù)器二進(jìn)制日志的正確位置開(kāi)始。
如果選項(xiàng)值等于2，CHANGE MASTER語(yǔ)句被寫(xiě)成SQL注釋。如果value被省略，這是默認(rèn)動(dòng)作。

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

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

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

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

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

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

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

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