核心指令速查表
| 場景 | 指令範例 | 關鍵參數說明 |
|---|
| 導出全庫 | mysqldump -u root -p --all-databases > alldb.sql | 含 mysql 系統庫，遷移慎用 |
| 導出單庫（含建庫語句） | mysqldump -u root -p -B dbname > db.sql | -B 產生 CREATE DATABASE 與 USE |
| 導出單庫（不含建庫） | mysqldump -u root -p dbname > db.sql | 目標庫需預先存在 |
| 導出單表 | mysqldump -u root -p dbname table1 table2 > tables.sql | 多表以空格分隔 |
| 僅導結構 | mysqldump -u root -p -d dbname > schema.sql | -d / --no-data |
| 僅導資料 | mysqldump -u root -p -t dbname > data.sql | -t / --no-create-info |
| 指定字元集導出 | mysqldump -u root -p --default-character-set=utf8mb4 dbname > db.sql | 避免 latin1 亂碼，**不可寫 utf-8** |
| 單一事務一致性備份 (InnoDB) | mysqldump -u root -p --single-transaction --master-data=2 dbname > db.sql | 不鎖表、記錄 binlog 位點、建 Slave 必備 |
| 鎖表備份 (MyISAM/混合) | mysqldump -u root -p --lock-all-tables --master-data=1 dbname > db.sql | 全域讀鎖、影響寫入，僅適合維護窗口 |
| 壓縮導出 | mysqldump -u root -p dbname | gzip > db.sql.gz | 管道串流，不佔臨時磁碟 |
| 分卷導出 | mysqldump -u root -p dbname | split -b 100m - db.sql.part. | 大檔案傳輸友善 |
導入/還原指令
# 方式 1：mysql 客戶端直導（最常用）
mysql -u root -p dbname < db.sql
# 方式 2：source 命令（已在 mysql 互動模式）
mysql -u root -p
USE dbname;
SOURCE /path/to/db.sql;
# 方式 3：壓縮檔直導
gunzip < db.sql.gz | mysql -u root -p dbname
zcat db.sql.gz | mysql -u root -p dbname
# 方式 4：多執行緒並行還原 (mydumper/myloader)
myloader -h 127.0.0.1 -u root -p -B dbname -d ./dumpdir -t 8
字元集陷阱與避坑指南
鐵律：--default-character-set 必須與資料庫實際儲存編碼一致。
錯誤寫法：--default-character-set=utf-8（MySQL 不認識 utf-8，只認 utf8 / utf8mb4）。
正確寫法：--default-character-set=utf8mb4（支援 Emoji/補充字元）或 utf8（BMP 字元）。
驗證：file db.sql 顯示 UTF-8 Unicode text；head -c 500 db.sql | grep -a "SET NAMES" 應見 SET NAMES utf8mb4。
跨版本遷移最佳實踐
- 5.6 → 5.7/8.0：必加
--column-statistics=0（關閉統計資訊導出，避免 8.0 客戶端對 5.6 伺服器報錯）。
- 5.7 → 8.0：注意
authentication_string、caching_sha2_password、保留字變更（如 groups、rank），建議先 mysqlcheck -u root -p --all-databases --check-upgrade。
- MariaDB ↔ MySQL：引擎、系統表、GTID 實作差異大，建議邏輯導出
--compatible=mysql40 測試，或用 CDC 工具 (Canal/Debezium) 同步遷移。
自動化備份腳本範例（含保留策略、上傳 S3、驗證、告警）
#!/bin/bash
# /usr/local/bin/mysql_backup.sh
set -euo pipefail
DB_USER="backup_user"
DB_PASS="$(cat /etc/mysql/backup.pass)"
DB_HOST="127.0.0.1"
BACKUP_DIR="/backup/mysql/$(date +%F)"
RETENTION_DAYS=7
S3_BUCKET="s3://my-company-mysql-backups"
LOG_FILE="/var/log/mysql_backup.log"
mkdir -p "$BACKUP_DIR"
exec > >(tee -a "$LOG_FILE") 2>&1
echo "=== $(date) Backup START ==="
# 1. 導出（單事務、記錄位點、壓縮）
databases=$(mysql -h"$DB_HOST" -u"$DB_USER" -p"$DB_PASS" -e "SHOW DATABASES;" -s --skip-column-names | grep -Ev "^(mysql|information_schema|performance_schema|sys)$")
for db in $databases; do
echo "Dumping $db..."
mysqldump -h"$DB_HOST" -u"$DB_USER" -p"$DB_PASS" \
--single-transaction --master-data=2 --routines --triggers --events \
--default-character-set=utf8mb4 --hex-blob "$db" | gzip > "$BACKUP_DIR/${db}.sql.gz"
# 2. 基本驗證
if ! gzip -t "$BACKUP_DIR/${db}.sql.gz"; then
echo "ERROR: $db backup corrupt!" | mail -s "MySQL Backup Failed" admin@company.com
exit 1
fi
echo "$db OK ($(du -h "$BACKUP_DIR/${db}.sql.gz" | cut -f1))"
done
# 3. 上傳 S3 (需 aws cli 配置)
aws s3 sync "$BACKUP_DIR" "$S3_BUCKET/$(date +%F)/" --storage-class STANDARD_IA
# 4. 清理舊備份
find /backup/mysql -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \;
aws s3 ls "$S3_BUCKET/" | while read -r line; do
# 簡易保留邏輯，實際建議用 S3 Lifecycle Policy
:
done
echo "=== $(date) Backup END ==="
# Crontab：每天凌晨 2:30 執行
30 2 * * * /usr/local/bin/mysql_backup.sh
還原驗證標準化流程 (RTO/RPO 演練)
- 建立測試實例（或臨時容器）。
- 還原最近一份全備 + 增量 Binlog（
mysqlbinlog 重放）。
- 核心表
CHECKSUM TABLE 對比來源/目標。
- 關鍵業務查詢
EXPLAIN 對比執行計畫。
- 記錄耗時、問題、改進項 → 更新運行手冊。
備份不等於可恢復。只有經過定期演練、有文檔記錄 RTO/RPO、並納入變更管理的備份體系，才是真正的資產保護。
