mysqldump 邏輯備份最佳實踐：從參數優化到搭建 Slave 與數據恢復全流程

mysqldump 邏輯備份最佳實踐：從參數優化到搭建 Slave 與數據恢復全流程

mysqldump 是 MySQL 官方邏輯備份工具，參數繁多且默認行為有坑。本文基於實戰總結「最佳姿勢」：最小化鎖定、獲取一致性備份、保留 Binlog 位點、分離系統庫備份、增量策略、Slave 搭建優化、還原實戰演練。

一、默認參數陷阱與優化方向

二、生產級全量備份命令 (推薦模板)

# 1. 業務庫備份 (InnoDB 為主，單事務一致性，記錄 Binlog 位點)
mysqldump -u$USER -p$PASS \
  --single-transaction \
  --master-data=2 \
  --routines \
  --flush-logs \
  --databases db1 db2 db3 \
  > /backup/full_$(date +%F_%H-%M-%S).sql

# 2. 系統庫 mysql 獨立備份 (MyISAM 表需刷新權限)
mysqldump -u$USER -p$PASS \
  --flush-privileges \
  --databases mysql \
  > /backup/mysql_$(date +%F_%H-%M-%S).sql

# 3. 合併備份 (若堅持單文件)
mysqldump -u$USER -p$PASS \
  --single-transaction \
  --master-data=2 \
  --routines \
  --flush-logs \
  --flush-privileges \
  --all-databases \
  > /backup/alldb_$(date +%F_%H-%M-%S).sql

關鍵點：--single-transaction 僅對 InnoDB 有效；--master-data=2 輸出註釋形式 CHANGE MASTER TO ...，不加鎖；--flush-logs 產生新 Binlog，便於增量切割。

三、增量備份策略：Binlog 歸檔

# 每小時/按業務頻率執行
mysqladmin -u$USER -p$PASS flush-logs

# 將產生的新 Binlog 文件 (如 mysql-bin.000027) 複製/同步到異地存儲
# 保留策略：全備保留 7 天，Binlog 保留 30 天或配合 expire_logs_days

恢復流程：全量 SQL 還原 → 依序應用增量 Binlog → 應用最新 Binlog 至故障點。

四、使用 mysqldump 搭建 Slave (最佳參數)

# Master 端執行 (不需 --flush-logs，避免多產生一個 Binlog)
mysqldump -u$USER -p$PASS \
  --single-transaction \
  --master-data=2 \
  --routines \
  --databases db1 db2 \
  > /backup/for_slave_$(date +%F).sql

# 腳本傳輸到 Slave
scp /backup/for_slave_*.sql slave_host:/tmp/

# Slave 端導入
mysql -u$USER -p$PASS < /tmp/for_slave_*.sql

# 從備份文件頭部提取 CHANGE MASTER 信息
head -50 /tmp/for_slave_*.sql
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=809;

# Slave 配置並啟動復制
CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='repl_pass',
  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 精確恢復

1. 全量備份：mysqldump --single-transaction --master-data=2 --routines --flush-logs --databases gs ngx_lua > gs_ngx_lua_backup.sql
2. 備份後 Binlog 歸檔：head -50 backup.sql 獲取 MASTER_LOG_FILE='mysql-bin.000027'，將 000027 之前的 Binlog 移至安全區，執行 PURGE BINARY LOGS TO 'mysql-bin.000027' 更新索引 (實際文件已移走)。
3. 模擬業務寫入：DELETE、FLUSH LOGS (產生 000027)、再 DELETE、FLUSH LOGS (產生 000028)。
4. 災難模擬：刪除 datadir，killall mysqld，重新初始化 mysql_install_db，mysql_secure_installation 設置密碼。
5. 還原全量：mysql -uroot -p < gs_ngx_lua_backup.sql
6. 應用增量 Binlog：

   mysqlbinlog mysql-bin.000027 > 27.sql && mysql -uroot -p < 27.sql
   mysqlbinlog mysql-bin.000028 > 28.sql && mysql -uroot -p < 28.sql

7. 驗證數據：確認恢復至刪除 datadir 前的最新狀態。

六、mysqldump 核心坑總結

1. --add-drop-table 默認開啟：導入腳本會先 DROP TABLE，若誤執行導致數據丟失。建議常態加 --skip-add-drop-table。
2. 字符集不匹配：備份與還原端 --default-character-set 不一致導致亂碼。顯式指定或統一配置 [mysqldump] default-character-set=utf8mb4。
3. 大表備份緩衝區溢出：加 --quick (默認開啟) 逐行讀取，避免一次性緩存整張表。
4. 觸發器默認備份，事件不備份：需 --events 額外備份 Event。

七、總結：三大場景標準動作

大規模庫 (>500G) 推薦改用 xtrabackup 物理備份，速度快數倍、不阻塞、支持增量備份、恢復時間可控。