1 介紹

binlog（二進制日志）在 MySQL 中具有非常重要的作用。它記錄了數(shù)據(jù)庫的所有更改操作，主要用于數(shù)據(jù)恢復(fù)、復(fù)制和審計等方面。以下是 binlog 的主要作用：

1.數(shù)據(jù)恢復(fù)
binlog 可以用于恢復(fù)數(shù)據(jù)庫中的數(shù)據(jù)。當數(shù)據(jù)庫發(fā)生故障時，可以通過應(yīng)用 binlog 來恢復(fù)數(shù)據(jù)到某個特定的時間點或事務(wù)
2.主從復(fù)制
binlog 是 MySQL 主從復(fù)制的基礎(chǔ)。主服務(wù)器上的所有更改都會記錄在 binlog 中，從服務(wù)器通過讀取 binlog 來同步數(shù)據(jù)。這樣可以實現(xiàn)數(shù)據(jù)的高可用性和負載均衡。
3.審計
binlog 記錄了所有的數(shù)據(jù)更改操作，可以用來進行審計。例如，可以查看哪些用戶在何時進行了哪些更改，這對于安全性和合規(guī)性非常重要。
4.故障診斷
binlog 可以幫助診斷數(shù)據(jù)庫中的問題。通過查看 binlog 文件，可以了解數(shù)據(jù)庫中發(fā)生了哪些具體的更改操作，從而定位問題原因。
5.數(shù)據(jù)遷移
binlog 可以用于數(shù)據(jù)遷移。通過讀取 binlog 文件，可以在新的數(shù)據(jù)庫實例上應(yīng)用相同的更改，從而實現(xiàn)數(shù)據(jù)遷移。
6.數(shù)據(jù)一致性
binlog 可以保證數(shù)據(jù)的一致性。在分布式系統(tǒng)中，通過應(yīng)用 binlog 可以確保多個節(jié)點之間的數(shù)據(jù)一致。
7.事務(wù)回滾
雖然 binlog 主要用于前向應(yīng)用（即恢復(fù)到某個時間點），但在某些情況下也可以用于事務(wù)回滾。例如，在某些故障場景下，可以使用 binlog 來回滾部分事務(wù)。
8.數(shù)據(jù)備份
binlog 可以配合定期的全量備份來實現(xiàn)增量備份。通過結(jié)合全量備份和 binlog 文件，可以恢復(fù)到任意時間點的數(shù)據(jù)狀態(tài)。

本文主要通過demo簡單描述本地mysql（windows系統(tǒng)）如何配置和使用binlog做數(shù)據(jù)恢復(fù)。

---

2 演示

2.1 安裝mysql

具體安裝步驟可以參考我的上一篇文檔。
https://blog.csdn.net/weixin_49702090/article/details/140555877

唯一差異是需要在 my.ini 配置文件中加上 binlog 的一些配置，以下是完整的 my.ini配置文件：

空行是為了給你們看方便，你們使用的時候最好不要有空行

[mysqld]
#設(shè)置3306端口
port=3306
#自定義設(shè)置mysql的安裝目錄，即解壓mysql壓縮包的目錄
basedir=D:\\software\\1_mysql\\mysql-5.7.37-winx64
#自定義設(shè)置mysql數(shù)據(jù)庫的數(shù)據(jù)存放目錄
datadir=D:\\software\\1_mysql\\mysql-5.7.37-winx64\\data
#允許最大連接數(shù)
max_connections=200
#允許連接失敗的次數(shù)，這是為了防止有人從該主機試圖攻擊數(shù)據(jù)庫系統(tǒng)
max_connect_errors=10
#服務(wù)端使用的字符集默認為UTF-8
character-set-server=utf8mb4
#創(chuàng)建新表時將使用的默認存儲引擎
default-storage-engine=INNODB
#創(chuàng)建新表時將使用的默認存儲引擎
default-storage-engine=INNODB#開啟binlog，不開啟的話以下不需要配置
#如果用于復(fù)制環(huán)境，每個服務(wù)器的 server-id 必須唯一
server-id=1
#指定二進制日志的存儲位置和前綴
log-bin=D:\\software\\1_mysql\\binlog
#同步二進制日志到磁盤的頻率，值為 0 表示不強制同步
sync_binlog=1
#設(shè)置二進制日志記錄的格式為行格式
binlog_format=ROW
#單個二進制日志文件的最大大小為1GB
max_binlog_size=1G
#二進制日志文件保存 7 天后自動刪除
expire_logs_days=7[mysql]
#設(shè)置mysql客戶端默認字符集
default-character-set=utf8mb4
[client]
#設(shè)置mysql客戶端連接服務(wù)端時默認使用的端口和默認字符集
port=3306
default-character-set=utf8mb4

2.2 重啟mysql，通過命令驗證binlog是否配置生效

SHOW VARIABLES LIKE ‘log_bin’;

值 ON 表示生效。

生效后會生成一些binlog文件在本地，這些文件策略就在my.ini配置文件中，比如存放在哪里，保留的時間策略等。我使用binlog比較久，下面是我本地生成的一些binlog文件：

2.3 模擬數(shù)據(jù)

模擬建庫建表，并分批次插入一些數(shù)據(jù)，到時候用來測試恢復(fù)插入的某一批數(shù)據(jù)。
最終刪除所有數(shù)據(jù) delete from 表名

---

以下過程開始恢復(fù)數(shù)據(jù)！！！

2.4. 確定上面插入數(shù)據(jù)過程 生成的binlog文件是哪個

通過生成的binlog文件的修改時間可以看到我在模擬數(shù)據(jù)的過程中，一些事務(wù)操作存在了 binlog.000009 這個文件下，以下操作我都基于binlog.000009 這個文件！！！ 第一次啟用 binlog 應(yīng)該只有一個 binlog 文件，所以比較好確定。

2.5 分析binlog文件，找到要恢復(fù)的那一次insert的起始位置和結(jié)束位置

這里有兩種方式 ：

2.5.1 方式1，通過mysql命令直接查看起始&結(jié)束位置

mysql> show binlog events in ‘binlog.000009’ ;

可以通過 BEGIN 、COMMIT 看到，這一次操作在binlog中的起始位置和結(jié)束位置分別為： 1637 和 2385，記住這兩個索引，恢復(fù)數(shù)據(jù)的時候我們僅恢復(fù)這一次的插入操作 。

2.5.2 方式2，通過閱讀本地生成的binlog文件查找起始&結(jié)束位置

binlog文件（binlog.000009）是二進制文件，不能直接查看，所以我們得將二進制文件轉(zhuǎn)換為可閱讀的文本文件（.txt） 。

一、生成 txt 文件

重新打開一個DOS窗口，不要在mysql中執(zhí)行。通過以下命令：
這里的路徑根據(jù)你實際路徑為準。

mysqlbinlog --no-defaults D:\software\1_mysql\binlog.000009 > D:\software\1_mysql\binlog000009.txt

可以檢查下生成的txt文件是否存在。

二、分析 txt 文件

這里我就簡單說一下關(guān)鍵的信息，太多了其實我也不懂，可以網(wǎng)上多搜搜。

這兩種方式看起來，我們要恢復(fù)的那一批數(shù)據(jù)的起始索引和結(jié)束索引都是：1637 和 2385

2.6 根據(jù)binlog生成sql備份文件

說直白點就是在 binlog000009 把我們要恢復(fù)的那一批數(shù)據(jù)根據(jù) 起始索引 和 結(jié)束索引 摘出來，單獨輸出到一個文件中（D:\software\1_mysql\output.sql） ，lg_test 是我這次的測試數(shù)據(jù)庫庫名，你們改一下不能寫錯了。

mysqlbinlog --no-defaults D:\software\1_mysql\binlog.000009 --start-position=1637 --stop-position=2385 -d lg_test > D:\software\1_mysql\output.sql

生成的output.sql文件如下：

如果我們通過第二種方式解析binlog文件的話，可以完全打開兩個文件對比下，看到我們要回滾的那部分操作已經(jīng)被摘出來了！

2.7 回滾

需要在mysql中操作，注意mysql中的斜杠是 / 不要寫錯了.

mysql> source D:/software/1_mysql/output.sql ;

最后執(zhí)行查詢操作，看到數(shù)據(jù)已經(jīng)恢復(fù)了。

2.8 恭喜完成！