備份策略原則

• 定期自動化：建立排程（cron/systemd timer），嚴格執行。
• 啟用二進制日誌：log-bin=mysql-bin，支援時間點恢復（PITR）。
• 命名規範：dbname_YYYY-MM-DD_HHMM.sql.gz，易於識別版本。
• 壓縮儲存：gzip 節省空間。
• 異地備份：備份檔勿與資料目錄同磁碟，建議同步至遠端/雲端。
• 定期演練還原：確保備份檔可用、流程熟悉。

方法一：mysqldump 邏輯備份（跨平台、可遷移）

備份單一資料庫

mysqldump -u root -p --single-transaction --routines --triggers --events db_name > /backup/db_name_$(date +%F_%H%M).sql

備份所有資料庫

mysqldump -u root -p --single-transaction --routines --triggers --events --all-databases > /backup/all_dbs_$(date +%F_%H%M).sql

壓縮同步

mysqldump -u root -p --single-transaction db_name | gzip > /backup/db_name_$(date +%F_%H%M).sql.gz

參數說明：
--single-transaction：InnoDB 熱備份不鎖表；
--routines --triggers --events：包含預存程序、觸發器、事件；
--master-data=2：在輸出檔頭部記錄 binlog 位置，利於 PITR。

方法二：實體檔案複製（極速、需停服務或鎖表）

1. 鎖定所有表：FLUSH TABLES WITH READ LOCK;
2. 複製資料目錄：cp -a /var/lib/mysql /backup/mysql_$(date +%F) 或使用 tar/rsync。
3. 解鎖：UNLOCK TABLES;

適用 MyISAM 或已停止服務的 InnoDB；InnoDB 熱備份建議改用 Percona XtraBackup（開源、非阻塞、支援增量）。

還原操作

邏輯備份還原

mysql -u root -p db_name < /backup/db_name_2024-01-15_0300.sql
# 或壓縮檔
zcat /backup/db_name_2024-01-15_0300.sql.gz | mysql -u root -p db_name

時間點恢復（PITR）

1. 還原最近一次完整備份。
2. 應用二進制日誌：
   mysqlbinlog --start-datetime="2024-01-15 03:00:00" --stop-datetime="2024-01-15 14:30:00" /var/lib/mysql/mysql-bin.000001 | mysql -u root -p

自動化腳本範例

#!/bin/bash
# /usr/local/bin/mysql_backup.sh
BACKUP_DIR="/backup/mysql"
DB_USER="backup_user"
DB_PASS="strong_password"
RETENTION_DAYS=7

mkdir -p "$BACKUP_DIR"
databases=$(mysql -u"$DB_USER" -p"$DB_PASS" -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)")

for db in $databases; do
  mysqldump -u"$DB_USER" -p"$DB_PASS" --single-transaction --routines --triggers --events "$db" | gzip > "$BACKUP_DIR/${db}_$(date +%F_%H%M).sql.gz"
done

find "$BACKUP_DIR" -type f -mtime +$RETENTION_DAYS -name "*.gz" -delete

加入 crontab：0 3 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

總結

方式	優點	缺點	適用場景
mysqldump	跨版本/架構遷移、可編輯 SQL、支援 PITR	大資料庫較慢、需鎖表或單一交易	中小型資料庫、異構遷移、開發測試
XtraBackup	非阻塞、支援增量、恢復快	僅限同主版本 InnoDB、檔案較大	生產大型 InnoDB、高可用環境
檔案複製	極速	需停服務/鎖表、不支援 PITR	唯讀副本、MyISAM、離線維護窗口