Binlog（二進制日誌）

數據庫管理中不可或缺的重要工具，記錄了數據庫中的所有變更操作，為數據恢復、主從複製和審計等關鍵功能提供了基礎

• 是MySQL 服務器層維護的一種二進制格式的日誌文件，記錄了所有DDL（數據定義語句）和DML（數據操作語句），但不包括 select 和 show 等

  作用：

• 數據恢復：某些場景下的數據恢復工作需要藉助 Bin log 完成
• 主從複製：在 master 端開啓 Binlog ，Slave 端通過讀取 Master 端的Binlog 實現數據同步

• 審計： 用户可以通過 Binlog 中的信息來進行審計，判斷是否有對數據庫進行注入攻擊

  Binlog 在數據恢復中的應用

1.確定恢復點
2.提取相關事件
3.應用Binlog
4.驗證恢復結果

開啓Binlog

- 在 MySQL 配置文件中添加 log-bin
- log-bin 是日誌文件的路徑

關閉Binlog

 - 註釋掉 log-bin ，或刪除 log-bin 這一行
 - 然後添加一行 skip_log_bin

查看Binlog狀態

 - 使用 show master status; 和 show binary logs; 命令查看Binlog狀態和Binlog文件列表

查看是否開啓 bin_log

• log-bin 的值為ON即為開啓狀態，OFF即為關閉狀態

 show golbal variables like 'log_bin';

查看Binlog 日誌

 show binary logs;

常用配置

#啓用二進制日誌
#log_bin 指定了二進制日誌文件的前綴名稱。日誌文件將以這個前綴開始，後跟一個數字後綴
[mysqld]
log_bin = mysql-bin

#設置日誌格式
#ROW每行變化都記錄在日誌中。
#statement 記錄執行的SQL語句
#MIXED 結合ROW和STATEMENT，根據情況選擇最合適的模式
[mysqld]
binlog_format = ROW | STATEMENT | MIXED

#指定日誌文件大小，max_binlog_size指定了單個binlog 文件的最大尺寸。當達到這個大小時，MySQL將創建一個新的日誌文件
[mysqld]
max_binlog_size = 100M

#二進制日誌緩存大小，binlog_cache_size指定了事務日誌的大小，用於ROW 格式的binlog
[mysqld]
binlog_cache_size = 32K

#二進制索引文件
#log_bin_index 指定了二進制日誌索引文件的名稱，該文件記錄了所有當前活躍和舊的binlog文件的位置
[mysqld]
log_bin_index = mysql-bin.index

#同步二進制日誌到磁盤
#binlog_sync 控制何時將事務日誌從緩存同步到磁盤。1表示每次事務後都同步，這會降低性能但確保數據安全。
[mysqld]
binlog_sync = 1

#二進制日誌過期天數
#expire_logs_days 指定了binlog文件在被自動刪除前可以保留的天數。
[mysqld]
expire_logs_days = 7

#最大二進制日誌文件數量
#max_binlog_files 指定了服務器將保留的最大binlog文件數量。當超過這個數量時，最舊的文件將被刪除
[mysqld]
max_binlog_files = 100

#啓用GTID模式
#gtid_mode 啓用全局事務標識符（GTID），用於複製和恢復
[mysqld]
gtid_mode = ON

#啓用自動位置同步
#log_slave_updates 允許從服務器將複製的更新記錄到自己的binlog中。
[mysqld]
log_slave_updates = 1

設置Binlog大小和保留時間

• 在MySQL配置文件中使用 max_binlog_size 和 expire_logs_days 參數控制Binlog 文件大小和保留時間

[mysqld]
#設置binlog文件最大為500M
max_binlog_size = 500M

#設置binlog最大保存天數為14天
expire_logs_days = 14

Binlog 工具使用：mysqlbinlog

基本語法： mysqlbinlog [options] log_fle
常用選項：

• -start-datetime, stop-datetime ：指定時間範圍
• -start-position -stop-position ：指定位置範圍
• -database ：指定數據庫

命令行選項：

-h 或 --host: 指定連接的 MySQL 主機。
-P 或 --port: 指定 MySQL 服務器的端口號。
-u 或 --user: 指定連接 MySQL 服務器的用户名。
-p 或 --password: 提示用户輸入連接 MySQL 服務器的密碼。
--base64-output=[DECODED | ENCODED]: 控制輸出是否以 Base64 編碼格式。
--start-datetime: 指定開始解析的日期時間。
--stop-datetime: 指定結束解析的日期時間。
--start-position: 指定開始解析的日誌位置。
--stop-position: 指定結束解析的日誌位置。
--database: 指定要解析的數據庫名。
--table: 指定要解析的表名。
--result-file: 將輸出寫入指定的文件。
--verbose: 顯示詳細的解析過程信息。
--skip-gtids: 跳過 GTID (Global Transaction ID) 信息的解析。

參數：

binlog-file: 指定要解析的二進制日誌文件名。
其他參數，如數據庫名、表名等，用於過濾解析的內容。

解析Binlog 內容

- mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000001 | grep -i "update"
--base64-output=DECODE-ROWS: 此選項會將基於行的事件（row-based events）從Base64編碼格式解碼並轉化為偽SQL形式的輸出。
這意味着如果日誌中存儲的是基於行的變化數據，則這些變化會被翻譯成類似於 INSERT、UPDATE 或 DELETE 的 SQL 語句。

-v (verbose): 提高輸出的詳細程度。當設置此標誌時，除了顯示實際發生的更改外，還會提供更多的上下文信息，比如表結構定義等

Binlog 日誌格式

statement

• 基於SQL語句的複製，記錄執行的SQL 語句，文件較小，但在某些情況下可能導致主從不一致
• statement-based replication SBR

row

• 基於行的複製（RBR）。記錄實際的數據行變化，文件較大，但能確保主從一致性
• row-based replication RBR

mixed

• 混合模式複製（MBR）。默認使用 statement 格式，在特定情況下自動切換到 ROW 格式
• mixed-based replication MBR 日誌格式修改 在配置文件中修改，新增配置 binlog_format = row

修改當前會話日誌格式

set session binlog_format = 'row';

修改全局日誌格式

set global binlog_format = 'row';

解析二進制日誌

通過偏移量定位

show master status;    #查詢偏移量，position參數的值

mysqlbinlog --start-position=196 mysql-bin.000007 -vv >/data/01.sql

指定時間的解析

mysqlbinlog --start-datetime="2025-02-20 00:00:00" mysql-bin.000007 > /data/02.sql

GTID 解析

mysqlbinlog --include-gtids 'e7d171f1-f04c-11ef-8afe-000c297262ee:1-43' mysql-bin.000008 -vv > /data/03.sql

解析單獨一個庫

mysqlbinlog --start-position=572 --stop-position=965 -d test1mysql-bin.000008 -vv > /data/04.sql

解析加密的Binlog

mysqlbinlog --read-from-remote-server -uroot -padmin --start-position=196 mysql-bin.000009 -vv >/data/05.sql

Binlog清除

• 配置自動清除

set global expire_logs_days=0;
set global binlog_expire_logs_seconds = 604800;
flush logs;
#expire_logs_days和binlog_expire_logs_seconds不能同時設置
#expire_logs_days是自動清除天數
#binlog_expire_logs_seconds自動清除秒數

• 刪除指定Binlog 之前的文件

show binary logs;
purge binary logs to 'mysql-bin.000002';
show binary logs;

注意事項

• 儘量採用自動清除的方式
• 確保要清除的Binlog日誌所有從庫都沒有在使用
• 監控磁盤使用率

Binlog 落盤

• 過程

 1. 創建Binlog 緩衝區
    - MySQL 啓動時會為 每個客户端線程 分配一個線程級（Thread-Level）的 Binlog 緩存，用於臨時存儲事務中的變更操作。
    - **關鍵點**
            - 每個線程獨立管理自己的 Binlog 緩存（避免併發寫入衝突）。
            - 此外，還有一個全局 Binlog 緩衝區（內存隊列），用於合併所有線程緩存的數據。

 2. 有變更操作，先寫Binlog 緩衝區
    - **當有數據變更操作（如 INSERT/UPDATE/DELETE）時：**
        - 變更會先寫入當前線程的 線程級 Binlog 緩存。
        - 事務提交時，該線程的 Binlog 緩存會被複制到 全局 Binlog 緩衝區。
    - **關鍵點：**
        - 未提交的事務不會進入全局緩衝區（保證原子性）。
        - 全局緩衝區是所有線程共享的，用於後續批量寫入磁盤。

 3. 將Binlog 緩衝區的數據寫入到磁盤
    - 主線程（Main Thread） 負責將全局 Binlog 緩衝區的數據寫入磁盤文件。
    - 寫入時機由參數 sync_binlog 控制

落盤頻率

#查詢配置
show global variables like "sync_binlog";

參數取值：

sync_binlog=0 禁用mysql服務將binlog同步到磁盤的功能，由操作系統控制binlog的刷盤，這種情況下性能會比較好，但是如果操作系統崩潰是會丟是部分事務，導致丟數據問題。
sync_binlog=1 表示每有一個事務都會同步一次到磁盤，這個是最安全的，但是如果隨着事務增加，會導致寫入磁盤頻率增高，導致性能下降。
sync_binlog=N 表示有N個事務會同步一次到磁盤。

參數作用：

• sync_binlog 是 MySQL 的一個重要參數，主要用於控制二進制日誌（Binary Log,Binlog）與存儲設備之間的同步頻率。它的設置直接影響到 MySQL 系統的性能以及數據的安全性和一致性