MySQL學習從零開始--第六部分

Binlog是什么？有哪幾種格式？推薦使用哪種，為什么

Binlog是什么

Binlog二進制日志是MySQL Server層記錄所有更改數據庫內容的操作日志的二進制文件，如操作UPDATE,DELETE,INSERT
Binlog不記錄SELECT，SHOW等查詢操作
使主從復制，數據恢復，審計追蹤的核心
通常位于數據目錄，文件名類似：mysql-bin.000001

通過參數bonlog_format設置格式

STATEMENT 記錄原始SQL語句（默認早期版本使用）
ROW 記錄每一行被修改的具體值（行級變更）
MINED 自動選擇使用STATEMENT 或 ROW，復雜操作使用 ROW，簡單操作用 STATEMENT

推薦使用ROW格式

	描述
精確	記錄數據變更后的具體值，不受環境因素影響
一致性高	主從復制不會出現因 SQL 非確定性引起的數據不一致問題
恢復準確	更容易基于 binlog 做精準恢復

可通過修改MySQL配置文件或使用SQL語句設置Binlog格式

修改配置文件

[mysqld]
binlog_format = ROW  # 可選值：STATEMENT、ROW、MIXED

使用SQL語句動態修改

SET GLOBAL binlog_format = 'ROW';  # 可選值：STATEMENT、ROW、MIXED

對數據庫的操作是先執行，還是先寫入binlog中？為什么
總結：

事務性操作（如 InnoDB）：
執行操作 → 記錄 redo log → 寫入 binlog → 提交事務通過兩階段提交確保 binlog 與數據變更的一致性。

非事務性操作（如 MyISAM）：執行操作 → 寫入 binlog，存在崩潰導致主從不一致的風險
- 事務性存儲引擎（如 InnoDB）的執行流程
  
  對于支持事務的存儲引擎，MySQL 采用 兩階段提交 確保 binlog 與數據變更的一致性
  1. 執行 SQL 并記錄 redo log：
    - 執行 SQL 語句，修改內存中的數據頁（Buffer Pool）。
    - 將變更記錄到redo log（物理日志），確保崩潰恢復時數據不丟失。
  2. 準備階段（Prepare）：
    - 存儲引擎將事務標記為PREPARED狀態。
    - 強制將 redo log 寫入磁盤（確保持久性）。
  3. 寫入 binlog：將事務的變更寫入binlog（邏輯日志）。
  4. 提交階段（Commit）：
    - 存儲引擎將事務標記為COMMITTED狀態。
    - 寫入commit 標記到 redo log，事務完成。
  關鍵點：
  - binlog 在事務提交前寫入，但在存儲引擎真正提交前。
  - 通過 redo log 和兩階段提交，保證 binlog 與數據變更的順序一致，避免主從復制不一致。
  二、非事務性存儲引擎（如 MyISAM）的執行流程
  
  對于不支持事務的存儲引擎：
  1. 執行 SQL 并修改數據文件：直接執行 SQL，修改數據文件和索引。
  2. 寫入 binlog：操作完成后，將變更記錄到 binlog。
  風險：
  如果在執行 SQL 后、binlog 寫入前發生崩潰，可能導致主從數據不一致（主庫已執行但從庫未復制）。
如果剛寫入binlog，數據庫出現異常，沒有寫入就重啟了，會發生什么事情
- 在 MySQL 中，若binlog 已寫入但數據未提交時發生異常重啟，InnoDB 存儲引擎會通過 崩潰恢復和兩階段提交（2PC） 機制確保數據一致性
- binlog 寫入后數據庫異常重啟，不會導致數據不一致，可能需要通過 redo log 完成未完成的提交操作。是 MySQL 主從復制可靠性的核心保障之一
  
  兩階段提交
  1. 準備階段（Prepare）：
  - 執行 SQL 并記錄 redo log。
  - InnoDB 將事務標記為PREPARED，并寫入 redo log。
  1. 提交階段（Commit）：
  - MySQL Server 寫入 binlog。
  - InnoDB 寫入COMMIT標記到 redo log，事務完成
- 發生異常的三種場景及處理
- 場景 1：binlog 寫入后，InnoDB 未收到提交指令
  - 現象：binlog 已寫入磁盤，但 InnoDB 未執行COMMIT。
  - 恢復流程
    1. 重啟后，InnoDB 通過 redo log 發現事務處于PREPARED狀態。
    2. 檢查 binlog：
      - 若 binlog 存在該事務：InnoDB 自動提交事務（與 binlog 保持一致）。
      - 若 binlog 不存在：InnoDB 回滾事務。
  場景 2：binlog 寫入失敗（如磁盤已滿）
  - 現象：binlog 寫入中斷，InnoDB 未收到提交指令。
  - 恢復流程
    1. binlog 寫入失敗時，MySQL Server 會回滾事務（協調者決定）。
    2. 重啟后，InnoDB 通過 redo log 發現事務未完成，直接回滾。
  場景 3：binlog 和 redo log 部分丟失
  - 現象：binlog 或 redo log 文件損壞。
  - 恢復流程
    1. 若 binlog 完整但 redo log 丟失，事務會被重新應用（通過 binlog）。
    2. 若兩者均損壞，可能需要從備份恢復或手動修復（極端情況
隨著時間的推移，binglog越來越大怎么操作
- 可手動清理或自動清理來管理Binlog
  方法：定期清理舊日志，手動 PURGE BINARY LOGS
  配置自動過期時間
如何強制創建新的binlog文件，這個操作有什么實際用途
- 強制切換
```
FLUSH BINARY LOGS;
```
  用途說明
  日志歸檔便于日志管理、切分日志
  主從同步初始化主庫切換 binlog 文件，讓從庫能從新起點同步
  手動備份配合 binlog 做增量控制增量恢復的起點
如何手動清理binlog（只保留當前使用的，刪除其他的），自動清理需要如何配置。
- 清除某個 binlog 之前的所有日志
```
PURGE BINARY LOGS TO 'mysql-bin.000010';
```
- 或清除指定時間之前的日志
```
PURGE BINARY LOGS BEFORE '2025-07-10 00:00:00';
```
  不能清除正在被從庫讀取的日志
- 自動清理配置
  
  在my.cnf中添加：
```
expire_logs_days = 7           # 保留7天
# 或新版：
binlog_expire_logs_seconds = 604800
```
  運行代碼重啟生效
binlog 的生命周期和清理機制是什么？如何設置自動過期時間
- 生命周期由參數 binlog_expire_logs_seconds 決定。
- 清理方式分為手動（PURGE）和自動（expire）。
- 自動清理是 MySQL 后臺線程定期執行的，保證磁盤空間控制。

用途	說明
日志歸檔	便于日志管理、切分日志
主從同步初始化	主庫切換 binlog 文件，讓從庫能從新起點同步
手動備份配合 binlog 做增量	控制增量恢復的起點

如何通過 mysqlbinlog 工具解析 binlog 內容？如何只解析某一個時間段的 binlog

基本用法

mysqlbinlog /var/lib/mysql/mysql-bin.000001

只解析某一時間段：

mysqlbinlog --start-datetime="2025-07-10 12:00:00" \--stop-datetime="2025-07-10 13:00:00" \/var/lib/mysql/mysql-bin.000001

可以重定向為SQL文件

mysqlbinlog ... > recover.sql

配置文件中確保開啟了binlog，并且使用ROW格式。
- 在my.cnf中配置：
```
[mysqld]
log-bin=mysql-bin
binlog_format=ROW
server-id=1
```
  然后重啟MySQL服務

對數據庫進行全量備份，使用之前創建的任意表格新增3條記錄。

全量備份 mysqldump

mysqldump -u root -p --all-databases --single-transaction --master-data=2 > full_backup.sql

插入記錄

insert into student values (1001,'張三',18);
insert into student values (1002,'李四',20);
insert into student values (1003,'王五',22);

drop整個表，之后使用binlog還原表，包括新增的3條記錄。
1. 查找 drop 前的 binlog 位置或時間
2. 使用 mysqlbinlog 導出 binlog SQL
```
mysqlbinlog --start-datetime="2025-07-10 10:00:00" \--stop-datetime="2025-07-10 12:00:00" \/var/lib/mysql/mysql-bin.000003 > recover.sql
```
3. 手動編輯文件：只保留建表和插入語句
4. 執行SQL恢復數據：
```
mysql -u root -p < recover.sql
```
主從同步中的binlog與relaylog有什么關系
- 從庫 I/O 線程將主庫 binlog 拉過來，寫入 relay log，再由 SQL 線程執行
- 日志類型主庫 or 從庫作用
  Binlog 主庫記錄主庫的所有更改操作
  Relay log 從庫是從庫從主庫拉過來的 binlog 拷貝
mysql主從同步時是如何保障數據一致性的
- 日志驅動：主庫寫操作記錄到 binlog，從庫 IO 線程拉取 binlog 存為 relay log，SQL 線程順序執行 relay log 語句，復刻主庫變更。
- 順序執行：從庫 SQL 線程串行應用 relay log，嚴格遵循主庫事務提交順序，避免亂序導致的數據沖突。
- GTID（可選）：為事務分配全局唯一 ID，精準標記已同步事務，防重復執行，簡化故障切換時的斷點定位。
- 故障校驗：從庫通過心跳檢測主從連接，結合Seconds_Behind_Master監控延遲，異常時觸發重連 / 修復，維持同步鏈路。
  本質是靠日志復制 + 串行重演 + 狀態校驗，讓從庫精準復刻主庫數據流轉，保障最終一致。
按照時間順序，描述主從同步的所有步驟
1. 主庫記錄 binlog
  - 所有 DML 操作被寫入 binlog。
    主庫對 DML（增刪改）、DDL（建表等）操作，會按binlog_format（STATEMENT/ROW/MIXED）格式寫入 binlog，是復制的基礎，
2. 從庫 I/O 線程連接主庫
  - 發起 COM_BINLOG_DUMP 請求。
    從庫啟動后，IO線程通過CHANGE MASTER TO配置的主庫信息，發起COM_BINLOG_DUMP協議請求，拉取 binlog
3. 主庫傳送 binlog 內容
  - 主庫的 dump 線程發送 binlog 給從庫。
    主庫響應請求后，會創建dump線程，持續將 binlog 事件推送給從庫
4. 從庫寫入 relay log
  - I/O 線程把 binlog 內容保存為中繼日志（relay log）。
    從庫IO線程接收的 binlog，會先落地為中繼日志（relay log），避免直接應用時中斷丟失
5. SQL 線程讀取 relay log
  - 從庫 SQL 線程按順序執行這些操作，實現數據同步。
    從庫SQL線程串行解析 relay log，重演 SQL 操作，保證主從數據最終一致
6. 完成復制
- binlog 與事務的關聯：主庫寫 binlog 時，會通過兩階段提交（2PC） 保證與 InnoDB redo log 一致，避免主從數據分裂。
- relay log 的管理：從庫會自動清理過期 relay log（可通過relay_log_purge等參數控制），防止日志膨脹。
- 異常場景處理：若主從網絡中斷，IO線程會自動重連；若 SQL 線程執行報錯（如主鍵沖突），需人工介入修復（如跳過事務、修正數據