MySQL 二進制日誌備份與主從復制實戰指南

MySQL 二進制日誌 (Binlog) 是時間點恢復 (PITR) 與主從復制的核心。本文涵蓋 Binlog 開啟與管理、基於時間/位置的恢復、定時全備+增備腳本、主從復制 (A/B 機) 搭建全流程。

1. 開啟 Binlog

[mysqld]
log-bin = mysql-bin          -- 指定前綴，生成 mysql-bin.000001...
server-id = 1                -- 唯一整數，主從必須不同
expire_logs_days = 7         -- 自動清理 7 天前舊日誌
max_binlog_size = 100M       -- 單文件上限，超過自動輪轉
binlog_format = ROW          -- 推薦 ROW 格式，兼容性最好

重啟後生效，或動態執行 SET GLOBAL log_bin = ON; (需重啟持久化)。

2. Binlog 查看與恢復

查看工具

mysqlbinlog mysql-bin.000001                    -- 全量查看
mysqlbinlog --start-datetime="2024-01-01 10:00:00" --stop-datetime="2024-01-01 11:00:00" mysql-bin.000001
mysqlbinlog --start-position=357 --stop-position=539 mysql-bin.000001

恢復實戰

-- 按位置恢復 (推薦，精確)
mysqlbinlog --start-position=4 --stop-position=1000 mysql-bin.000001 | mysql -u root -p

-- 按時間恢復
mysqlbinlog --start-datetime="2024-01-01 10:00:00" --stop-datetime="2024-01-01 11:00:00" mysql-bin.000001 | mysql -u root -p

注意：--start-position 必須從 4 開始 (Format Description Event)，不可從事件起始位置直接開始，否則解析失敗。

3. 定時備份策略：全備 + 增備

每週日全備 (刷新日誌、刪除舊 Binlog、生成新基準點)

#!/bin/bash
# full_backup.sh
mysqldump --delete-master-logs --all-databases -u root -p'pwd' > /backup/full_$(date +%F).sql

每日增備 (Flush Logs 產生新 Binlog，移動舊 Binlog 到備份目錄)

#!/bin/bash
# incr_backup.sh
mysqladmin flush-logs -u root -p'pwd'
if [ $? -eq 0 ]; then
    # 取最舊的 binlog 文件 (非 .index)
    oldest=$(ls /var/lib/mysql/mysql-bin.0* | head -1)
    mv "$oldest" /backup/incr/
fi

4. 主從復制 (A/B 機熱備) 搭建

主庫 (Master) 配置

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = biz_db          -- 可選：只同步指定庫
# binlog-ignore-db = test      -- 可選：忽略庫

-- 建立同步帳號
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%' IDENTIFIED BY 'repl_pwd';
FLUSH PRIVILEGES;

-- 鎖表備份基準點
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  -- 記下 File (如 mysql-bin.000003) 和 Position (如 73)
-- 此處用 tar 或 mysqldump --master-data=2 備份數據目錄
UNLOCK TABLES;

從庫 (Slave) 配置

[mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1              -- 防止誤寫入

-- 導入主庫基準備份
mysql -u root -p < full_backup.sql

-- 配置同步源
CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_pwd',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=73;

START SLAVE;
SHOW SLAVE STATUS\G
-- 關鍵指標: Slave_IO_Running=Yes, Slave_SQL_Running=Yes, Seconds_Behind_Master=0

5. 常見故障與處理

• IO 線程掛起：檢查網路、防火牆、帳號密碼、MASTER_HOST。
• SQL 線程報錯：SHOW SLAVE STATUS\G 看 Last_SQL_Error，常見主鍵衝突、字符集不一致。可 STOP SLAVE; SET GLOBAL sql_slave_skip_counter=1; START SLAVE; 跳過單條錯誤 (謹慎使用)。
• 延遲過大：檢查從庫硬體、sync_binlog=1、innodb_flush_log_at_trx_commit=1、並行復制 (slave_parallel_workers MySQL 5.6+)。
• Binlog 損壞/丟失：重新從主庫全量同步，或用 mysqlbinlog 修復單文件。

6. GTID 復制 (MySQL 5.6+ 推薦)

啟用 gtid_mode=ON、enforce_gtid_consistency=ON，CHANGE MASTER TO MASTER_AUTO_POSITION=1 無需手動指定 File/Position，故障切換、多級復制更簡單。