mysqldump 邏輯備份最佳實踐：一致性、增量、主從搭建、恢復實戰

mysqldump 邏輯備份最佳實踐：一致性、增量、主從搭建、恢復實戰

mysqldump 是最通用的邏輯備份工具，但默認參數存在鎖表時間長、非一致性、誤刪風險等坑點。本文梳理生產級參數組合、增量備份策略、主從搭建標準流程、全量+二進制日誌恢復實戰。

默認參數陷阱

生產級全量備份命令

# 核心業務庫（InnoDB 為主）
mysqldump -u$USER -p$PASS \
  --single-transaction \
  --master-data=2 \
  --routines --triggers --events \
  --flush-logs \
  --databases db1 db2 db3 \
  > /backup/full_$(date +%F_%H%M).sql

# 系統庫（MyISAM，需鎖表獲取一致性）
mysqldump -u$USER -p$PASS \
  --flush-privileges \
  --lock-all-tables \
  --databases mysql \
  > /backup/mysql_sys_$(date +%F_%H%M).sql

參數解析：

• --single-transaction：啟動事務快照（RR 隔離級別），無鎖一致性備份，**僅適用 InnoDB**
• --master-data=2：輸出 CHANGE MASTER TO MASTER_LOG_FILE='binlog.xxx', MASTER_LOG_POS=yyy; 作為註釋，不加鎖（配合 single-transaction）
• --flush-logs：備份瞬間滾動 Binlog，便於增量備份切分點對齊
• --routines --triggers --events：完整備份可編程對象

增量備份策略：Binlog 歸檔

# 1. 定時滾動 Binlog（每小時/每日）
mysqladmin -u$USER -p$PASS flush-logs

# 2. 歸檔滾動前的 Binlog 文件
# 從 master-data=2 記錄的位點開始，收集所有後續 Binlog
# 建議：rsync 到異地存儲，保留 7-30 天

# 3. 定期清理（PURGE BINARY LOGS TO 'binlog.xxx'）
# 僅在確認全量備份+後續 Binlog 完整可用後執行

主從搭建標準流程（mysqldump 版）

1. 主庫配置：server-id=1, log_bin=mysql-bin, binlog_format=ROW, sync_binlog=1, innodb_flush_log_at_trx_commit=1
2. 從庫配置：server-id=101, relay_log=relay-bin, read_only=1, skip_slave_start=1
3. 建立復制賬號：

   GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.%' IDENTIFIED BY 'StrongPass';

4. 主庫導出一致性備份：

   mysqldump -uroot -p \
     --single-transaction --master-data=2 --routines \
     --databases db1 db2 > /backup/repl_base.sql

5. 傳輸並導入從庫：

   scp /backup/repl_base.sql slave_host:/tmp/
   mysql -uroot -p < /tmp/repl_base.sql

6. 獲取 Binlog 位點並啟動復制：

   head -50 /tmp/repl_base.sql | grep "CHANGE MASTER TO"
   # 輸出：-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=809;
   
   mysql -uroot -p -e "
   CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl', MASTER_PASSWORD='StrongPass',
     MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=809;
   START SLAVE;
   SHOW SLAVE STATUS\G"
   

   驗證 Slave_IO_Running: Yes, Slave_SQL_Running: Yes

全量+Binlog 時間點恢復（PITR）實戰

場景：誤執行 DELETE FROM users WHERE id > 100，需恢復到誤操作前。

1. 準備：最近一次全量備份 full_20240115_0200.sql + 後續所有 Binlog
2. 恢復全量：

   # 停服務、清空 datadir、重新初始化
   systemctl stop mysqld
   rm -rf /var/lib/mysql/*
   /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/var/lib/mysql
   systemctl start mysqld
   mysql_secure_installation  # 設置密碼
   
   # 導入全量
   mysql -uroot -p < full_20240115_0200.sql

3. 應用 Binlog 到誤操作前：

   # 1. 解析 Binlog 定位誤操作位置
   mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000027 | grep -n -A5 -B5 "DELETE FROM users"
   # 找到 position=12345
   
   # 2. 應用 Binlog 到指定位置（排除誤操作事務）
   mysqlbinlog --stop-position=12345 mysql-bin.000027 | mysql -uroot -p
   mysqlbinlog mysql-bin.000028 | mysql -uroot -p  # 後續完整 Binlog

4. 驗證數據：SELECT COUNT(*) FROM users; 恢復正確

mysqldump 避坑清單

• 🔴 大表導出 OOM：加 --quick（默認開啟）流式讀取，避免緩存整個結果集
• 🔴 導入極慢：導入前 SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;，導入後 COMMIT; 恢復
• 🟡 字符集不匹配：指定 --default-character-set=utf8mb4，或確保 my.cnf client 區塊已配置
• 🟡 GTID 複製場景：加 --set-gtid-purged=OFF 避免 GTID 集合衝突（5.6+）
• 🟢 並行導入：將備份按庫/表拆分文件，多線程並行 mysql -uroot -p < shard_x.sql &

工具選型建議