使用 mysqldump 實現 MySQL 邏輯備份與二進制日誌增量恢復

本文演示基於 mysqldump 的完整備份與 mysqlbinlog 增量恢復實戰流程，採雙節點架構（Node1 備份、Node2 恢復），涵蓋 MyISAM/InnoDB 不同引擎的備份策略、主從日誌座標記錄與點對點恢復驗證。

架構與前置條件

• Node1 (192.168.1.113)：主庫，啟用二進制日誌，資料目錄掛載於獨立 LVM /data/mysql。
• Node2 (192.168.1.114)：備用恢復節點，同樣啟用二進制日誌。
• 測試資料庫：hellodb，含 students 表。

Node1：配置二進制日誌

mkdir -p /data/mysql
chown mysql:mysql /data/mysql
# 複製現有 binlog 至新目錄
cp /var/lib/mysql/mysql-bin.* /data/mysql/

# 修改配置
vim /etc/my.cnf.d/server.cnf
[server]
log_bin=/data/mysql/mysql-bin

systemctl restart mariadb

Node1：全量備份（區分引擎）

# 查看表引擎
mysql -e "USE hellodb; SHOW TABLE STATUS\G;"

# MyISAM：需鎖表、記錄 binlog 座標、刷新日誌
mysqldump -uroot --lock-tables --master-data=2 --flush-logs --databases hellodb > /root/hellodb_myis.sql

# InnoDB：單一交易熱備、記錄 binlog 座標、刷新日誌
mysqldump -uroot --single-transaction --master-data=2 --flush-logs --databases hellodb > /root/hellodb_inno.sql

參數說明：
--single-transaction：InnoDB 熱備核心，不鎖表。
--master-data=2：在輸出檔以註釋形式記錄 CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...;。
--flush-logs：備份瞬間滾動產生新 binlog，便於後續增量切點。

Node1：模擬增量變更

mysql -e "USE hellodb; INSERT INTO students (Name,Age,Gender,ClassID,TeacherID) VALUES ('caocao',99,'M',6,8); DELETE FROM students WHERE StuID=3;"

Node2：環境準備與全量恢復

# 配置 binlog 目錄
mkdir -p /data/mysql
vim /etc/my.cnf
[mysqld]
log_bin=/data/mysql/mysql-bin
chown -R mysql:mysql /data
systemctl start mariadb

# 恢復全量備份
mysql < /root/hellodb_inno.sql

# 檢查備份檔中的 binlog 座標
grep "CHANGE MASTER" /root/hellodb_inno.sql
# 輸出範例：-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=245;

Node1：導出增量 binlog (從 POS 245 開始)

mysqlbinlog --start-position=245 /var/lib/mysql/mysql-bin.000002 > /root/binlog.sql
scp /root/binlog.sql 192.168.1.114:/root/

Node2：應用增量日誌

mysql < /root/binlog.sql

驗證恢復結果

mysql -e "USE hellodb; SELECT * FROM students;"
# 預期包含新增的 caocao (StuID=26) ，且 StuID=3 已刪除

關鍵點總結

進階建議

• 生產環境建議採用 **GTID 複制** 替代基於檔案/位置的傳統複制，簡化故障轉移。
• 備份檔與 binlog **不可置於同一物理磁碟** 與資料目錄，防單點故障。
• 定期演練「全量 + 增量」恢復，驗證 RPO/RTO 指標。