MySQL 數據庫備份策略與實戰：mysqldump、文件拷貝、二進制日誌完整體系

備份必要性

• 硬件故障、實例崩潰、人為誤操作（DROP TABLE、UPDATE 無 WHERE）
• 管理員直接用文本編輯器修改表文件導致損壞

兩大核心備份方式對比

mysqldump 核心參數速查

# 全庫備份（含結構+數據，單事務一致性，適合 InnoDB）
mysqldump -u root -p --single-transaction --quick --hex-blob --routines --triggers --events db_name > dump.sql

# 僅結構
mysqldump -u root -p -d db_name > schema.sql

# 僅數據
mysqldump -u root -p -t db_name > data.sql

# 指定表
mysqldump -u root -p db_name tbl1 tbl2 > tables.sql

# WHERE 條件導出子集
mysqldump -u root -p --where="created_at > '2023-01-01'" db_name tbl > subset.sql

# 壓縮輸出
mysqldump ... | gzip > dump.sql.gz

物理備份操作規範

1. FLUSH TABLES WITH READ LOCK;（全局讀鎖）
2. cp -r /var/lib/mysql /backup/mysql_$(date +%F) 或 tar/rsync
3. UNLOCK TABLES;
4. 備份 ibdata*、ib_logfile*、.frm、.ibd、my.cnf

注意： InnoDB 物理備份需配合 innodb_file_per_table=ON 且無活躍事務，否則建議用 Percona XtraBackup 熱備份。

二進制日誌：增量備份與時間點恢復 (PITR)

• 啟用：log_bin = /var/log/mysql/binlog、binlog_format = ROW、expire_logs_days = 7
• 備份策略：全量備份（日/週）+ Binlog 增量（實時/小時）
• 恢復流程：
  1. 恢復全量備份：mysql < full_dump.sql
  2. 重放 Binlog：mysqlbinlog --start-datetime="..." --stop-datetime="..." binlog.xxx | mysql

備份文件管理規範

• 命名：dbname_YYYYMMDD_HHMMSS.sql.gz（含庫名、時間、壓縮標識）
• 存放：不同磁盤/機器/機房，避免單點故障
• 保留：日備留 7 天、週備留 4 週、月備留 12 月，自動清理腳本
• 驗證：定期演練恢復（月度/季度），記錄 RTO/RPO

異地容災與遷移

• 同步備份文件至異地存儲（S3/OSS/NAS）
• 從庫 mysqldump --master-data=2 獲取 Binlog 位點，構建新從庫
• 跨版本遷移：邏輯備份導入目標版本，運行 mysql_upgrade

總結

生產級備份體系 = 邏輯全量（mysqldump/XtraBackup）+ 二進制日誌增量 + 異地存儲 + 定期恢復演練。根據 RPO/RTO 選擇工具：RPO=0 需同步複制/半同步；RPO<1h 需 Binlog 實時歸檔；RPO≥1h 定時 myslqdump 即可。