改良版 mysqldump 分表備份策略：避免全域鎖、降低 CPU/IO 衝擊、支援混合引擎

標準 mysqldump 痛點

• --single-transaction 僅對 InnoDB 有效，MyISAM/Memory 仍加全域讀鎖 (FLUSH TABLES WITH READ LOCK)。
• 大庫備份時長鎖定，導致：CPU 飆升、磁碟 IO 滿載、所有查詢變慢查詢、主從延遲。
• 混合引擎環境 (MyISAM + InnoDB + Memory) 無完美單一參數解。

分表備份核心思路

逐表 mysqldump，每表獨立交易/鎖定，避免長時間全域鎖定。搭配 --skip-lock-tables (InnoDB) 或 --lock-tables (MyISAM) 靈活控制。

生產級備份腳本

#!/bin/bash
# fast_backup.sh - 分表備份主腳本
# 用法: ./fast_backup.sh db_name

set -euo pipefail

DBNAME="${1:-}"
BACKUP_BASE="/backup/mysql"
DATE_STR=$(date '+%Y%m%d_%H%M%S')
BACKUP_DIR="${BACKUP_BASE}/${DBNAME}_${DATE_STR}"
MYSQL_USER="backup_user"
MYSQL_PASS="backup_pass"
MYSQL_HOST="127.0.0.1"
MYSQL_PORT=3306
MYSQL_BIN="/usr/local/mysql/bin"
THREADS=4  # 並行度，視 CPU 核心調整

[[ -z "$DBNAME" ]] && { echo "Usage: $0 "; exit 1; }
mkdir -p "$BACKUP_DIR"

# 1. 獲取所有表名 (排除系統表)
TABLES=$(${MYSQL_BIN}/mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USER} -p${MYSQL_PASS} -N -e "SHOW TABLES FROM \`${DBNAME}\`")

# 2. 並行備份每張表
echo "$TABLES" | xargs -P${THREADS} -I{} sh -c '
    TABLE="{}"
    OUT_FILE="'${BACKUP_DIR}'/${TABLE}.sql"
    echo "Dumping ${TABLE} -> ${OUT_FILE}"
    '${MYSQL_BIN}'/mysqldump -h'${MYSQL_HOST}' -P'${MYSQL_PORT}' -u'${MYSQL_USER}' -p'${MYSQL_PASS}' \
        --single-transaction --quick --skip-lock-tables --routines --triggers --events \
        --set-gtid-purged=OFF \
        '${DBNAME}' "${TABLE}" > "${OUT_FILE}" 2>> '${BACKUP_DIR}'/error.log
    if [ $? -eq 0 ]; then
        gzip "${OUT_FILE}"
        echo "${TABLE} done."
    else
        echo "${TABLE} FAILED!" >> '${BACKUP_DIR}'/failed_tables.log
    fi
'

# 3. 彙總存儲過程/觸發器/事件 (單獨備份)
${MYSQL_BIN}/mysqldump -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USER} -p${MYSQL_PASS} \
    -n -t -d -R --skip-lock-tables ${DBNAME} | gzip > "${BACKUP_DIR}/routines_triggers_events.sql.gz"

# 4. 生成還原腳本
cat > "${BACKUP_DIR}/restore.sh" <<'EOF'
#!/bin/bash
# 還原腳本：按依賴順序還原 (需人工調整外鍵順序)
DBNAME="$1"
MYSQL_USER="restore_user"
MYSQL_PASS="restore_pass"
for f in *.sql.gz; do
    echo "Restoring $f ..."
    gunzip -c "$f" | mysql -u${MYSQL_USER} -p${MYSQL_PASS} ${DBNAME}
done
EOF
chmod +x "${BACKUP_DIR}/restore.sh"

# 5. 清理舊備份 (保留 7 天)
find "${BACKUP_BASE}" -maxdepth 1 -type d -name "${DBNAME}_*" -mtime +7 -exec rm -rf {} \;

echo "Backup completed: ${BACKUP_DIR}"
ls -lh "${BACKUP_DIR}"

關鍵參數解析

定時任務部署

# /etc/cron.d/mysql-backup
0 2 * * * backup_user /opt/scripts/fast_backup.sh my_site >> /var/log/mysql_backup.log 2>&1
0 3 * * 0 backup_user /opt/scripts/fast_backup.sh other_db >> /var/log/mysql_backup.log 2>&1

還原驗證流程

1. 建立空庫：CREATE DATABASE db_name CHARSET utf8mb4;
2. 執行 restore.sh db_name。
3. 核對表數量、行數、Checksum：CHECKSUM TABLE tbl;
4. 啟用應用驗證功能正常。

進階優化

• Percona XtraBackup：物理熱備份，InnoDB 不鎖表、速度極快、支援增量、流式傳輸，企業級首選。
• MyDumper/MyLoader：多線程邏輯備份/還原，比 mysqldump 快 5-10 倍。
• 雲廠商快照：AWS RDS/Aurora、阿里雲 RDS 自動備份、跨區域複製。

分表備份腳本解決了混合引擎、長鎖定、大庫備份三大痛點，是遷移到物理備份前的最佳過渡方案。