Mysql binlog核心配置解析

查看binlog日志核心配置項(xiàng)

輸入以下命令查詢binlog相關(guān)配置信息

mysql> show variables like '%log_bin%';

執(zhí)行結(jié)果：

+---------------------------------+------------------------------------------------+
| Variable_name                   | Value                                          |
+---------------------------------+------------------------------------------------+
| log_bin                         | ON                                             |
| log_bin_basename                | /usr/local/mysql8.0.39/mysql/data/mysql-bin    |
| log_bin_index                   | /usr/local/mysql8.0.39/mysql/data/binlog.index |
| log_bin_trust_function_creators | OFF                                            |
| log_bin_use_v1_row_events       | OFF                                            |
| sql_log_bin                     | ON                                             |
+---------------------------------+------------------------------------------------+

binlog核心配置說明

（1）log_bin 

字段含義：全局控制 MySQL 是否開啟二進(jìn)制日志的總開關(guān)。

• 取值為 ON 時(shí)，MySQL 會(huì)記錄所有對(duì)數(shù)據(jù)的修改操作（如 INSERT/UPDATE/DELETE、表結(jié)構(gòu)變更等）到 binlog 文件，用于 數(shù)據(jù)恢復(fù)（通過 binlog 回滾或重做操作）和 主從復(fù)制（主庫通過 binlog 向從庫同步數(shù)據(jù)）。

• 取值為 OFF 時(shí)，完全關(guān)閉 binlog，不記錄任何修改操作（生產(chǎn)環(huán)境建議開啟，除非是純只讀庫）。

（2）log_bin_basename 

字段含義：指定二進(jìn)制日志文件的 基礎(chǔ)路徑和基礎(chǔ)名稱（即 binlog 文件的 “前綴”）。

• 實(shí)際生成的 binlog 文件會(huì)在基礎(chǔ)名稱后添加 序號(hào)后綴（如 .000001、.000002），形成完整文件名。

• 示例中 log_bin_basename = /usr/local/mysql8.0.39/mysql/data/mysql-bin，表示 binlog 文件會(huì)生成在 /usr/local/mysql8.0.39/mysql/data/ 目錄下，文件名格式為 mysql-bin.000001、mysql-bin.000002 等（文件滿或執(zhí)行 flush logs 時(shí)會(huì)生成新序號(hào)的文件）。

（3）sql_log_bin

字段含義：會(huì)話級(jí) 控制當(dāng)前數(shù)據(jù)庫會(huì)話是否記錄 binlog（優(yōu)先級(jí)高于全局 log_bin）。

• 取值為 ON 時(shí)，當(dāng)前會(huì)話的修改操作會(huì)記錄到 binlog（繼承全局 log_bin=ON 的行為）；取值為 OFF 時(shí)，當(dāng)前會(huì)話的修改操作 不記錄 binlog（臨時(shí)關(guān)閉當(dāng)前會(huì)話的 binlog 記錄，不影響其他會(huì)話）。

• 用途：例如執(zhí)行臨時(shí)數(shù)據(jù)修復(fù)、測試操作時(shí)，可臨時(shí)執(zhí)行 SET sql_log_bin = OFF;，避免這些操作被同步到從庫或記錄到 binlog（執(zhí)行后需注意還原，防止遺漏正常操作）。

查看當(dāng)前所有二進(jìn)制日志（binlog）文件信息

show master logs;

執(zhí)行結(jié)果：

+------------------+------------+-----------+
| Log_name         | File_size  | Encrypted |
+------------------+------------+-----------+
| mysql-bin.000006 | 1073742453 | No        |
| mysql-bin.000007 |  142945256 | No        |
+------------------+------------+-----------+

結(jié)果顯示當(dāng)前有 2 個(gè) binlog 文件，其中較早的 000006 已接近 1GB 上限，后續(xù)會(huì)自動(dòng)生成新文件。

查看當(dāng)前正在活躍的二進(jìn)制日志（binlog）信息

mysql> show master status;

執(zhí)行結(jié)果：

+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 142985195 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+

File ：當(dāng)前正在寫入的binlog文件名。這里為 mysql-bin.000007

Position：當(dāng)前活躍 binlog 文件中的偏移量（字節(jié)位置），表示主庫已經(jīng)往 File 中寫的字節(jié)的位置。

Binlog_Do_DB： 僅記錄指定數(shù)據(jù)庫的 binlog（白名單）為空表示所有數(shù)據(jù)庫的修改都被記錄。

Binlog_Ignore_DB ：不記錄指定數(shù)據(jù)庫的 binlog（黑名單），為空表示不忽略任何庫。

Executed_Gtid_Set： 已執(zhí)行的 GTID（全局事務(wù) ID）集合，為空表示未啟用 GTID 復(fù)制模式（使用傳統(tǒng)的 “基于位置的復(fù)制”）。

以上是數(shù)據(jù)庫中binlog日志相關(guān)配置的解析，下面移步navivate，看一個(gè)小小的數(shù)據(jù)庫誤刪除恢復(fù)案例。

實(shí)戰(zhàn)案例

案例1：恢復(fù)誤刪除的數(shù)據(jù)庫

這里首先創(chuàng)建了一個(gè)名為binlog_demo_1的數(shù)據(jù)庫,其中包含了一張user表，插入了一條信息。但是忘截圖了，等恢復(fù)后再看哈。

接著將數(shù)據(jù)庫不小心刪掉，下面進(jìn)行數(shù)據(jù)庫及其中數(shù)據(jù)的恢復(fù)實(shí)戰(zhàn)。

（1）獲取mysql正在寫入的binlog文件

查詢當(dāng)前數(shù)據(jù)庫正在寫入的binlog文件，結(jié)果顯示文件名為mysql-bin.000007，其中已經(jīng)寫了
143233008字節(jié)的信息。

（2）查看binlog文件具體日志信息。

命令： SHOW BINLOG EVENTS IN 'your binlog filename';

BEGIN代表事務(wù)開啟，COMMIT或ROLLBACK表示事務(wù)結(jié)束。

（3）查看binlog日志，確定恢復(fù)數(shù)據(jù)庫的記錄。

這里可以找到創(chuàng)建數(shù)據(jù)庫的日志記錄。

可以找到刪除數(shù)據(jù)庫的日志記錄，這兩條記錄之間包含了該數(shù)據(jù)庫中所有表的DML(數(shù)據(jù)操作)和DDL（表結(jié)構(gòu)操作）記錄，重新執(zhí)行這兩條記錄之間的所有SQL（不包括刪除數(shù)據(jù)庫的sql）便可恢復(fù)該數(shù)據(jù)庫。

（4）確定position范圍

Pos指起始位置，End_log_pos指結(jié)束位置?？梢钥吹交謴?fù)binlog_demo_1的pos起點(diǎn)為 143117334 終點(diǎn)為 143164854（143164854 - 143164987這條不能再執(zhí)行，不然數(shù)據(jù)庫又被刪了）

（5）利用mysql官方工具 mysqlbinlog進(jìn)行數(shù)據(jù)恢復(fù)

進(jìn)入mysql的bin目錄，可以看到mysqlbinlog。但是想要恢復(fù)，就必須知道binlog日志文件的位置，還記得怎么去找到嗎？

log_bin_basename 含義是日志存儲(chǔ)的基礎(chǔ)位置和基礎(chǔ)前綴，使用命令：

mysql> show variables like 'log_bin_basename';

可以看到文件基礎(chǔ)位置/usr/local/mysql8.0.39/mysql/data，基礎(chǔ)前綴為mysql-bin，結(jié)合上面的binlog文件名，便可定位到我們需要的binlog日志文件。

在恢復(fù)之前，登錄mysql客戶端，關(guān)閉當(dāng)前會(huì)話的binlog日志記錄，可以避免恢復(fù)操作產(chǎn)生的記錄對(duì)原binlog日志造成污染，也防止從節(jié)點(diǎn)同步數(shù)據(jù)時(shí)重復(fù)執(zhí)行sql導(dǎo)致的異常。執(zhí)行以下命令。

mysql> SET sql_log_bin = OFF;

關(guān)閉當(dāng)前會(huì)話后，緊接著就可以進(jìn)行數(shù)據(jù)恢復(fù)了，因?yàn)榍懊鎯H針對(duì)當(dāng)前會(huì)話關(guān)閉binlog記錄。如果退出就失效了，所以重新開一個(gè)終端，進(jìn)入到mysql的bin目錄下，執(zhí)行恢復(fù)命令：

mysqlbinlog --no-defaults --start-position=143117334 --stop-position=143164854 --database=binlog_demo_1 /usr/local/mysql8.0.39/mysql/data/mysql-bin.000007 | mysql -uroot -p

mysqlbinlog 
  --no-defaults                                  # 1. 不加載默認(rèn)配置文件
  --start-position=143117334             # 2. 起始位置：從binlog的該字節(jié)位置開始解析
  --stop-position=143164854              # 3. 結(jié)束位置：解析到binlog的該字節(jié)位置停止
  --database=binlog_demo_1               # 4. 篩選數(shù)據(jù)庫：只解析該數(shù)據(jù)庫的操作
  /usr/local/mysql8.0.39/mysql/data/mysql-bin.000007          # 5. 目標(biāo)binlog文件路徑
  | mysql -u<username>  -p<password>                       # 6. 管道：將解析出的SQL傳遞給mysql客戶端執(zhí)行

最后記得執(zhí)行下行命令，重新開啟會(huì)話binlog日志。

mysql> SET sql_log_bin = ON;

（6）檢驗(yàn)恢復(fù)結(jié)果

可以看到binlog_demo_1數(shù)據(jù)庫已被恢復(fù)。

數(shù)據(jù)庫中的數(shù)據(jù)也都被一并恢復(fù)。

案例2：恢復(fù)表中誤刪除的記錄。

在user表中新增一條記錄（2,xiaoming）

現(xiàn)在再次不小心把該條記錄刪掉。開始進(jìn)行恢復(fù)操作演示。

（1）確定binlog_format模式

MySQL 的 binlog_format 有三種模式：STATEMENT（語句模式）、ROW（行模式）、MIXED（混合模式）。不同模式的 binlog 記錄內(nèi)容差異極大，因此使用 mysqlbinlog 解析時(shí)需要匹配不同的策略。以下是詳細(xì)說明：

查看mysql配置文件，通常名為 my.cnf 或 my.ini，確定 binlog_format模式。

這里演示的是ROW模式，各位小伙伴一定要確認(rèn)好自己的mysql binlog_format模式。

（2）確定對(duì)應(yīng)mysqlbinlog解析策略

1）TATEMENT 模式 解析

特點(diǎn)：binlog 中直接存儲(chǔ)明文 SQL，無需額外解碼。

解析命令（基礎(chǔ)參數(shù)即可）：

mysqlbinlog \
  --no-defaults \  # 避免配置文件沖突
  --start-datetime='2025-10-20 10:52:00' \  # 時(shí)間過濾
  --stop-datetime='2025-10-20 10:54:00' \
  --database=目標(biāo)庫名 \  # 庫名過濾
  原始binlog文件路徑 \
  > 解析結(jié)果.sql

解析結(jié)果可直接看到完整SQL語句，例如：

DELETE FROM `test`.`user` WHERE id = 123;
UPDATE `test`.`order` SET status = 1 WHERE create_time < '2025-10-20';

2）ROW 模式解析（最復(fù)雜，需解碼）

特點(diǎn)：binlog 中以 base64 編碼存儲(chǔ)行數(shù)據(jù)變化，不顯示 SQL 語句，需解碼才能看到具體字段值。

解析命令（必須加解碼參數(shù)）:

mysqlbinlog \
  --no-defaults \
  --base64-output=decode-rows \  # 核心：解碼 base64 編碼的行數(shù)據(jù)
  --verbose \  # 可選：顯示字段類型注釋（如 `@1=123 /* INT */`）
  --start-datetime='2025-10-20 10:52:00' \
  --stop-datetime='2025-10-20 10:54:00' \
  --database=目標(biāo)庫名 \
  原始binlog文件路徑 \
  > 解析結(jié)果.sql

解析結(jié)果：顯示每行數(shù)據(jù)的變化，例如刪除操作：

### DELETE FROM `test`.`user`
### WHERE
###   @1=123  /* INT meta=0 nullable=0 is_null=0 */  -- 字段1（id）
###   @2='張三'  /* VARCHAR(20) meta=65535 nullable=0 is_null=0 */  -- 字段2（name）
###   @3='2025-10-20 10:53:00'  /* DATETIME meta=0 nullable=0 is_null=0 */  -- 字段3（create_time）

3) MIXED 模式解析（兼容兩種策略）

特點(diǎn)：部分操作是 STATEMENT 格式（明文 SQL），部分是 ROW 格式（編碼行數(shù)據(jù)）

解析命令：按 ROW 模式的命令解析（兼容 STATEMENT 格式）：

mysqlbinlog \
  --no-defaults \
  --base64-output=decode-rows \  # 解碼 ROW 部分，不影響 STATEMENT 部分
  --verbose \
  --start-datetime='時(shí)間范圍' \
  原始binlog文件路徑 \
  > 解析結(jié)果.sql

解析結(jié)果：同時(shí)包含明文 SQL（STATEMENT 部分）和行數(shù)據(jù)（ROW 部分），例如：

-- STATEMENT 格式的操作
INSERT INTO `test`.`log` (content) VALUES ('system start');

-- ROW 格式的操作
### UPDATE `test`.`user`
### WHERE
###   @1=456  /* INT */
###   @2='李四'  /* VARCHAR */
### SET
###   @1=456
###   @2='李四_updated'  /* VARCHAR */

關(guān)鍵：同時(shí)處理兩種格式的內(nèi)容 ——SQL 語句直接查看，行數(shù)據(jù)按 ROW 模式的方法解析

記住：只要 binlog 中可能包含 ROW 格式內(nèi)容（如 rbr_only=yes），就必須加 --base64-output=decode-rows，否則無法看到具體數(shù)據(jù)變化。

（3） 使用mysqlbinlog策略進(jìn)行日志解析

博主的mysql binlog_formate=ROW，各位小伙伴確定好自己mysql的日志模式，選擇相應(yīng)的策略。

將解析結(jié)果保存至recovery_detail.sql

（4）定位誤刪除的記錄

根據(jù)關(guān)鍵字（DELETE FROM `database_name`）定位操作

（5） 數(shù)據(jù)轉(zhuǎn)換并恢復(fù)數(shù)據(jù)

將DELETE轉(zhuǎn)為INSERT

執(zhí)行sql，恢復(fù)數(shù)據(jù)。

數(shù)據(jù)已被恢復(fù)。

總結(jié)

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

最新評(píng)論