Mysql binlog核心配置解析

查看binlog日志核心配置項

輸入以下命令查詢binlog相關配置信息

mysql> show variables like '%log_bin%';

執(zhí)行結果：

+---------------------------------+------------------------------------------------+
| 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 是否開啟二進制日志的總開關。

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

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

（2）log_bin_basename 

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

• 實際生成的 binlog 文件會在基礎名稱后添加 序號后綴（如 .000001、.000002），形成完整文件名。

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

（3）sql_log_bin

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

• 取值為 ON 時，當前會話的修改操作會記錄到 binlog（繼承全局 log_bin=ON 的行為）；取值為 OFF 時，當前會話的修改操作 不記錄 binlog（臨時關閉當前會話的 binlog 記錄，不影響其他會話）。

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

查看當前所有二進制日志（binlog）文件信息

show master logs;

執(zhí)行結果：

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

結果顯示當前有 2 個 binlog 文件，其中較早的 000006 已接近 1GB 上限，后續(xù)會自動生成新文件。

查看當前正在活躍的二進制日志（binlog）信息

mysql> show master status;

執(zhí)行結果：

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

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

Position：當前活躍 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（全局事務 ID）集合，為空表示未啟用 GTID 復制模式（使用傳統(tǒng)的 “基于位置的復制”）。

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

實戰(zhàn)案例

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

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

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

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

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

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

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

BEGIN代表事務開啟，COMMIT或ROLLBACK表示事務結束。

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

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

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

（4）確定position范圍

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

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

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

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

mysql> show variables like 'log_bin_basename';

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

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

mysql> SET sql_log_bin = OFF;

關閉當前會話后，緊接著就可以進行數(shù)據(jù)恢復了，因為前面僅針對當前會話關閉binlog記錄。如果退出就失效了，所以重新開一個終端，進入到mysql的bin目錄下，執(zhí)行恢復命令：

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

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

mysql> SET sql_log_bin = ON;

（6）檢驗恢復結果

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

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

案例2：恢復表中誤刪除的記錄。

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

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

（1）確定binlog_format模式

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

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

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

（2）確定對應mysqlbinlog解析策略

1）TATEMENT 模式 解析

特點：binlog 中直接存儲明文 SQL，無需額外解碼。

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

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

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

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

2）ROW 模式解析（最復雜，需解碼）

特點：binlog 中以 base64 編碼存儲行數(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=目標庫名 \
  原始binlog文件路徑 \
  > 解析結果.sql

解析結果：顯示每行數(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 模式解析（兼容兩種策略）

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

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

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

解析結果：同時包含明文 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 */

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

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

（3） 使用mysqlbinlog策略進行日志解析

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

將解析結果保存至recovery_detail.sql

（4）定位誤刪除的記錄

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

（5） 數(shù)據(jù)轉換并恢復數(shù)據(jù)

將DELETE轉為INSERT

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

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

總結

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

最新評論