資料庫備份與災難復原策略 — 從小專案到正式環境

前言

「你有備份嗎？」——這句話通常在最不該聽到的時候才會被問出來。

資料庫備份是那種「沒事的時候覺得多餘，出事的時候恨自己沒做」的事情。我見過太多慘痛的案例：有人不小心在正式環境跑了 DROP TABLE、有人的硬碟突然壞了、有人被勒索軟體攻擊……如果沒有可靠的備份，這些都是致命的。

這篇文章我會從備份的基本概念開始，涵蓋邏輯備份、物理備份、WAL archiving、PITR（Point-in-Time Recovery），再到自動化腳本和復原演練。不管你是管理自己的 side project 還是公司的正式環境，都能找到適合的方案。

備份的基本觀念

備份的類型

| 類型 | 說明 | 優點 | 缺點 |
|——|——|——|——|
| 邏輯備份 | 匯出 SQL 語句 | 跨版本相容、可讀 | 慢、還原慢 |
| 物理備份 | 複製資料檔案 | 快、還原快 | 版本相依 |
| 增量備份 | 只備份變更 | 節省空間和時間 | 還原較複雜 |
| 連續歸檔 | 持續記錄 WAL | 可做 PITR | 需要額外儲存 |

3-2-1 備份法則

• 3 份副本：原始資料 + 2 份備份
• 2 種媒介：例如本地硬碟 + 雲端儲存
• 1 份異地：至少一份備份在不同的地理位置

MySQL / MariaDB 備份

邏輯備份：mysqldump

# 備份單一資料庫
mysqldump -u root -p --single-transaction --routines --triggers \
    mydb > mydb_$(date +%Y%m%d_%H%M%S).sql

# 備份所有資料庫
mysqldump -u root -p --all-databases --single-transaction \
    --routines --triggers > all_databases_$(date +%Y%m%d).sql

# 壓縮備份（大幅減少檔案大小）
mysqldump -u root -p --single-transaction mydb | gzip > mydb_$(date +%Y%m%d).sql.gz

# 只備份表結構（不含資料）
mysqldump -u root -p --no-data mydb > mydb_schema.sql

# 備份特定的表
mysqldump -u root -p --single-transaction mydb users orders > mydb_partial.sql

--single-transaction 很重要：它讓 InnoDB 表在備份時不會被鎖定，可以持續接受寫入。

還原

# 從 SQL 檔案還原
mysql -u root -p mydb < mydb_20240215.sql

# 從壓縮檔還原
gunzip < mydb_20240215.sql.gz | mysql -u root -p mydb

# 還原前先建立空資料庫
mysql -u root -p -e "CREATE DATABASE mydb_restored"
mysql -u root -p mydb_restored < mydb_20240215.sql

物理備份：Percona XtraBackup

對於大型資料庫，mysqldump 太慢了。Percona XtraBackup 可以做熱備份：

# 安裝
apt-get install percona-xtrabackup-80

# 完整備份
xtrabackup --backup --target-dir=/backup/full \
    --user=root --password=secret

# 準備備份（讓它可以被還原）
xtrabackup --prepare --target-dir=/backup/full

# 還原
systemctl stop mysql
xtrabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

# 增量備份
xtrabackup --backup --target-dir=/backup/inc1 \
    --incremental-basedir=/backup/full \
    --user=root --password=secret

PostgreSQL 備份

邏輯備份：pg_dump

# 備份單一資料庫（SQL 格式）
pg_dump -U postgres -d mydb > mydb_$(date +%Y%m%d).sql

# 自訂格式（推薦！支援平行還原和選擇性還原）
pg_dump -U postgres -d mydb -Fc > mydb_$(date +%Y%m%d).dump

# 目錄格式（支援平行備份）
pg_dump -U postgres -d mydb -Fd -j 4 -f /backup/mydb_$(date +%Y%m%d)

# 壓縮備份
pg_dump -U postgres -d mydb | gzip > mydb_$(date +%Y%m%d).sql.gz

# 備份所有資料庫
pg_dumpall -U postgres > all_databases_$(date +%Y%m%d).sql

# 只備份特定 schema
pg_dump -U postgres -d mydb -n public > mydb_public.sql

還原

# 從 SQL 檔案還原
psql -U postgres -d mydb_restored < mydb_20240215.sql

# 從自訂格式還原（支援平行作業）
pg_restore -U postgres -d mydb_restored -j 4 mydb_20240215.dump

# 只還原某張表
pg_restore -U postgres -d mydb_restored -t users mydb_20240215.dump

# 列出備份中的內容
pg_restore --list mydb_20240215.dump

物理備份：pg_basebackup

# 完整的物理備份
pg_basebackup -U replicator -h localhost \
    -D /backup/base_$(date +%Y%m%d) \
    -Ft -z -P

# -Ft: tar 格式
# -z: 壓縮
# -P: 顯示進度

WAL Archiving 和 PITR

這是 PostgreSQL 最強大的備份功能——可以把資料庫還原到任意時間點。

設定 WAL 歸檔

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/wal/%f'
# 或者用更安全的方式
# archive_command = 'test ! -f /archive/wal/%f && cp %p /archive/wal/%f'

# 建立歸檔目錄
mkdir -p /archive/wal
chown postgres:postgres /archive/wal

# 重啟 PostgreSQL
systemctl restart postgresql

做基礎備份

# 做一次基礎備份
pg_basebackup -U postgres -D /backup/base -Ft -z -P

PITR 還原

假設在 2024-02-15 14:30:00 有人不小心刪了資料，你要還原到 14:29:59：

# 1. 停止 PostgreSQL
systemctl stop postgresql

# 2. 備份目前的資料目錄（以防萬一）
mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main_broken

# 3. 還原基礎備份
mkdir /var/lib/postgresql/16/main
tar xzf /backup/base/base.tar.gz -C /var/lib/postgresql/16/main

# 4. 設定還原參數
cat > /var/lib/postgresql/16/main/postgresql.auto.conf << 'EOF'
restore_command = 'cp /archive/wal/%f %p'
recovery_target_time = '2024-02-15 14:29:59+08'
recovery_target_action = 'promote'
EOF

# 5. 建立恢復信號檔案
touch /var/lib/postgresql/16/main/recovery.signal

# 6. 修正權限
chown -R postgres:postgres /var/lib/postgresql/16/main

# 7. 啟動 PostgreSQL（它會自動回放 WAL 到指定時間）
systemctl start postgresql

# 8. 檢查狀態
psql -U postgres -c "SELECT pg_is_in_recovery();"
# 應該回傳 false（表示恢復完成）

自動化備份腳本

MySQL 自動備份

#!/bin/bash
# mysql_backup.sh — MySQL 每日自動備份腳本

set -euo pipefail

# 設定
DB_USER="backup_user"
DB_PASS="backup_password"
DB_NAMES="mydb wordpress"
BACKUP_DIR="/backup/mysql"
RETAIN_DAYS=30
S3_BUCKET="s3://my-backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/mysql_backup.log"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

# 建立今天的備份目錄
mkdir -p "${BACKUP_DIR}/${DATE}"

# 備份每個資料庫
for DB in $DB_NAMES; do
    BACKUP_FILE="${BACKUP_DIR}/${DATE}/${DB}.sql.gz"
    log "Starting backup of ${DB}..."

mysqldump -u "$DB_USER" -p"$DB_PASS" \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        "$DB" | gzip > "$BACKUP_FILE"

SIZE=$(du -sh "$BACKUP_FILE" | cut -f1)
    log "Completed ${DB} backup: ${SIZE}"
done

# 上傳到 S3
if command -v aws &> /dev/null; then
    log "Uploading to S3..."
    aws s3 sync "${BACKUP_DIR}/${DATE}" "${S3_BUCKET}/${DATE}/"
    log "S3 upload completed"
fi

# 清理舊備份
log "Cleaning backups older than ${RETAIN_DAYS} days..."
find "$BACKUP_DIR" -type d -mtime "+${RETAIN_DAYS}" -exec rm -rf {} + 2>/dev/null || true

# 驗證備份完整性（檢查 gzip 檔案是否正常）
for f in "${BACKUP_DIR}/${DATE}"/*.sql.gz; do
    if gzip -t "$f" 2>/dev/null; then
        log "Verified: $(basename $f) - OK"
    else
        log "ERROR: $(basename $f) is corrupted!"
        # 發送告警
        # curl -X POST "https://hooks.slack.com/..." -d '{"text":"Backup corrupted!"}'
    fi
done

log "Backup job completed"

PostgreSQL 自動備份

#!/bin/bash
# pg_backup.sh — PostgreSQL 每日自動備份腳本

set -euo pipefail

# 設定
export PGHOST="localhost"
export PGUSER="postgres"
export PGPASSFILE="/root/.pgpass"
BACKUP_DIR="/backup/postgresql"
RETAIN_DAYS=30
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/pg_backup.log"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

mkdir -p "${BACKUP_DIR}/${DATE}"

# 備份全部資料庫（自訂格式，支援平行還原）
DATABASES=$(psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres'")

for DB in $DATABASES; do
    DB=$(echo "$DB" | xargs)  # 去掉空白
    BACKUP_FILE="${BACKUP_DIR}/${DATE}/${DB}.dump"
    log "Starting backup of ${DB}..."

pg_dump -d "$DB" -Fc -Z 6 -f "$BACKUP_FILE"

SIZE=$(du -sh "$BACKUP_FILE" | cut -f1)
    log "Completed ${DB} backup: ${SIZE}"

# 驗證備份
    if pg_restore --list "$BACKUP_FILE" > /dev/null 2>&1; then
        log "Verified: ${DB} - OK"
    else
        log "ERROR: ${DB} backup verification failed!"
    fi
done

# 備份全域物件（角色、表空間）
log "Backing up global objects..."
pg_dumpall --globals-only -f "${BACKUP_DIR}/${DATE}/globals.sql"

# 清理舊備份
find "$BACKUP_DIR" -type d -mtime "+${RETAIN_DAYS}" -exec rm -rf {} + 2>/dev/null || true

log "Backup job completed"

設定 Cron

# 編輯 crontab
crontab -e

# MySQL：每天凌晨 2 點備份
0 2   * /opt/scripts/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

# PostgreSQL：每天凌晨 3 點備份
0 3   * /opt/scripts/pg_backup.sh >> /var/log/pg_backup.log 2>&1

# 每週日做一次完整的物理備份
0 4   0 pg_basebackup -U postgres -D /backup/base_$(date +\%Y\%m\%d) -Ft -z -P

Docker 環境的備份

很多人用 Docker 跑資料庫，備份方式稍有不同：

# docker-compose.yml
version: '3.8'
services:
  db:
    image: postgres:16
    volumes:
      - pg_data:/var/lib/postgresql/data
      - ./backups:/backups
    environment:
      POSTGRES_PASSWORD: secret

volumes:
  pg_data:

# 從 Docker 容器備份 PostgreSQL
docker exec my_postgres pg_dump -U postgres mydb | gzip > backup_$(date +%Y%m%d).sql.gz

# 從 Docker 容器備份 MySQL
docker exec my_mysql mysqldump -u root -psecret mydb | gzip > backup_$(date +%Y%m%d).sql.gz

# 還原到 Docker 容器
gunzip < backup_20240215.sql.gz | docker exec -i my_postgres psql -U postgres mydb

# 備份 Docker volume（物理備份）
docker run --rm \
    -v pg_data:/data \
    -v $(pwd)/backups:/backup \
    alpine tar czf /backup/pg_volume_$(date +%Y%m%d).tar.gz -C /data .

Docker 備份腳本

#!/bin/bash
# docker_db_backup.sh

CONTAINER_NAME="my-blog-db-1"
DB_NAME="wordpress"
DB_USER="root"
DB_PASS="rootpass"
BACKUP_DIR="./backups"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p "$BACKUP_DIR"

echo "Starting backup..."
docker exec "$CONTAINER_NAME" \
    mysqldump -u "$DB_USER" -p"$DB_PASS" \
    --single-transaction "$DB_NAME" \
    | gzip > "${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz"

echo "Backup size: $(du -sh ${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz | cut -f1)"

# 保留最近 10 份備份
ls -t "${BACKUP_DIR}"/*.sql.gz | tail -n +11 | xargs -r rm

echo "Backup completed!"

復原演練 — 比備份更重要的事

沒有經過驗證的備份，等於沒有備份。

自動化復原測試腳本

#!/bin/bash
# restore_test.sh — 每月自動復原測試

set -euo pipefail

TEST_DB="restore_test_$(date +%Y%m%d)"
LATEST_BACKUP=$(ls -t /backup/postgresql/*/mydb.dump | head -1)
LOG_FILE="/var/log/restore_test.log"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

log "Starting restore test with: ${LATEST_BACKUP}"

# 建立測試資料庫
createdb "$TEST_DB"
log "Created test database: ${TEST_DB}"

# 還原
pg_restore -d "$TEST_DB" -j 4 "$LATEST_BACKUP" 2>&1 | tee -a "$LOG_FILE"
log "Restore completed"

# 驗證資料完整性
EXPECTED_TABLES=15  # 你的資料庫應有的表數量
ACTUAL_TABLES=$(psql -t -d "$TEST_DB" -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public'")
ACTUAL_TABLES=$(echo "$ACTUAL_TABLES" | xargs)

if [ "$ACTUAL_TABLES" -ge "$EXPECTED_TABLES" ]; then
    log "Table count OK: ${ACTUAL_TABLES} tables"
else
    log "ERROR: Expected ${EXPECTED_TABLES} tables, got ${ACTUAL_TABLES}"
fi

# 驗證重要表的資料量
psql -d "$TEST_DB" -c "
    SELECT
        relname AS table_name,
        reltuples::BIGINT AS row_estimate
    FROM pg_class
    JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
    WHERE nspname = 'public'
      AND relkind = 'r'
    ORDER BY reltuples DESC
" | tee -a "$LOG_FILE"

# 執行一些關鍵查詢
psql -d "$TEST_DB" -c "SELECT COUNT(*) AS user_count FROM users" | tee -a "$LOG_FILE"
psql -d "$TEST_DB" -c "SELECT MAX(created_at) AS latest_record FROM orders" | tee -a "$LOG_FILE"

# 清理測試資料庫
dropdb "$TEST_DB"
log "Test database dropped"

log "Restore test completed successfully"

# 發送報告
# mail -s "Monthly Restore Test Report" admin@company.com < "$LOG_FILE"

不同規模的備份策略建議

小專案（Side Project / 個人部落格）

# 每天一次 mysqldump / pg_dump + 上傳到 S3
# 用 cron 排程，保留 30 天
# 成本：幾乎為零

# 最簡單的方案
0 3   * mysqldump -u root -psecret wordpress | gzip | \
    aws s3 cp - s3://my-backups/wordpress/$(date +\%Y\%m\%d).sql.gz

中型專案

• 每日邏輯備份（pg_dump 自訂格式）
• 開啟 WAL archiving
• 每週物理備份
• 備份上傳到 S3
• 每月做一次復原演練
• 保留 90 天

正式環境 / 生產系統

• RPO（Recovery Point Objective）：可容忍的資料遺失量
• RTO（Recovery Time Objective）：可容忍的停機時間

| 指標 | 小專案 | 中型 | 正式環境 |
|——|——–|——|———|
| RPO | 24 小時 | 1 小時 | < 5 分鐘 | | RTO | 數小時 | 1 小時 | < 15 分鐘 | | 備份頻率 | 每日 | 每日 + WAL | 即時 WAL + 每日 | | 異地備份 | 選用 | 建議 | 必要 | | 復原演練 | 偶爾 | 每月 | 每週 |

小結

資料庫備份這件事，技術上不難，難的是「持續做」和「定期驗證」。我見過最常見的災難不是備份方案太簡單，而是根本沒有備份、或者備份了但從來沒試過還原。

我的建議很簡單：

1. 今天就設定自動備份——用最簡單的 cron + mysqldump/pg_dump 就好
2. 備份上傳到異地——至少傳到 S3 或另一台機器
3. 每個月做一次還原測試——確認備份真的能用
4. 逐步升級——從邏輯備份開始，需要時再加入 WAL archiving 和 PITR

記住：備份不是備份，能還原才是備份。

延伸閱讀

• PostgreSQL 官方：備份與還原
• MySQL 官方：備份與還原
• Percona XtraBackup 文件
• pgBackRest — PostgreSQL 進階備份工具
• Barman — 另一個 PostgreSQL 備份管理工具