mysqldump 將數據庫/表導出為 SQL 文本文件，包含 CREATE TABLE、INSERT 等語句，跨版本、跨平台遷移通用，是最基礎的備份與遷移手段。
| 場景 | 命令模板 | 實戰示例 | 關鍵參數解析 |
|---|---|---|---|
| 導出整個數據庫 | mysqldump -u$user -p$pass $db > $file.sql | mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql | 含建表+數據，單線程，大庫慢 |
| 導出單表 | mysqldump -u$user -p$pass $db $table > $file.sql | mysqldump -u wcnc -p smgp_apps_wcnc users > wcnc_users.sql | 精準導出，適合單表遷移/測試 |
| 僅導出結構 (無數據) | mysqldump -u$user -p$pass -d --add-drop-table $db > $file.sql | mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc > d:\wcnc_db.sql | -d 無數據；--add-drop-table 便於重建覆蓋 |
| 導入數據庫 | mysql -u$user -p$pass $db < $file.sql 或 source | 見下文 | 兩種方式擇一，source 適合交互式 |
# 1. 生產級完整備份 (單事務、含主從位置、存儲過程、觸發器、事件)
mysqldump -u backup_user -p \
--single-transaction \
--master-data=2 \
--routines --triggers --events \
--hex-blob \
--quick \
--max_allowed_packet=1G \
--net_buffer_length=1M \
db_name > backup_$(date +%F_%H-%M).sql
# 2. 僅導出數據 (不含建表)，適合同步數據到預建表
mysqldump -u$user -p$pass --no-create-info db_name table_name > data_only.sql
# 3. 壓縮輸出 (節省磁盤/網絡)
mysqldump -u$user -p$pass db_name | gzip > db_name_$(date +%F).sql.gz
# 4. 遠程直接導入 (無中間文件)
mysqldump -h src_host -u$user -p$pass db_name | mysql -h dst_host -u$user -p$pass db_name
# 5. 排除特定表 (如大日志表、緩存表)
mysqldump -u$user -p$pass db_name --ignore-table=db_name.log_table --ignore-table=db_name.cache_table > filtered.sql
# 6. 兼容舊版本導入 (MySQL 5.7 導出導入 8.0)
mysqldump --column-statistics=0 -u$user -p$pass db_name > compat.sql
mysql -u root -p
# 進入交互模式
mysql> USE target_db;
mysql> source /path/to/backup.sql;
# 或指定絕對路徑
mysql> source d:\wcnc_db.sql;
優點：可見進度、錯誤即時顯示、支持 \. 別名。
缺點：大文件 (＞1GB) 交互式極慢、無法自動化。
# 基礎
mysql -u root -p target_db < backup.sql
# 帶進度條 (需安裝 pv)
pv backup.sql | mysql -u root -p target_db
# 並行導入 (需 mydumper/myloader 或自行分表並發)
# 大表拆分多文件後：
ls chunk_*.sql | xargs -P 4 -I {} sh -c 'mysql -u root -p target_db < {}'
當單表 ＞ 50GB、全庫 ＞ 200GB，邏輯備份/恢復耗時數小時甚至數天。
Percona XtraBackup 物理熱備份：
✅ 不阻塞寫入 (InnoDB)
✅ 備份/恢復速度 ≈ 磁盤 I/O 速度 (通常 10-50x mysqldump)
✅ 支持增量備份、壓縮、加密、流式傳輸
# 備份
xtrabackup --backup --target-dir=/backup/full_$(date +%F) --parallel=4 --compress
# 準備 (應用事務日志)
xtrabackup --prepare --target-dir=/backup/full_$(date +%F)
# 恢復 (需停庫、清空 datadir)
systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full_$(date +%F)
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld
| 現象 | 原因 | 對策 |
|---|---|---|
導入報錯 Unknown collation: 'utf8mb4_0900_ai_ci' | 高版本導出導入低版本，校對規則不存在 | 導出加 --column-statistics=0、目標庫升級、或 sed 替換校對規則 |
導入報錯 ERROR 1118 (42000): Row size too large | 目標表 ROW_FORMAT=COMPACT 列過多/過長 | 目標表指定 ROW_FORMAT=DYNAMIC/COMPRESSED 或調整列類型 |
| 導出卡住 / OOM | 大表無 --quick 導致緩存全量結果集 | 必加 --quick (默認開啟)，避免手動關閉 |
| 導入後自增 ID 衝突 | --master-data 導致主從位置混入，或多源合併 | 去除 AUTO_INCREMENT 值或使用 pt-table-sync 校驗 |
| 中文亂碼 | 導出/導入字符集不一致 | 全鏈路 --default-character-set=utf8mb4、目標表 CHARSET=utf8mb4 |
#!/bin/bash
# /usr/local/bin/mysql_daily_backup.sh
set -euo pipefail
USER="backup_user"
PASS="$(cat /etc/mysql/backup.pass)" # 密碼文件 600 權限
DBS="app_db log_db"
DEST="/backup/mysql/daily"
RETENTION=7
mkdir -p "$DEST"
DATE=$(date +%F_%H-%M)
for DB in $DBS; do
FILE="$DEST/${DB}_${DATE}.sql.gz"
mysqldump -u"$USER" -p"$PASS" \
--single-transaction --master-data=2 --routines --triggers --events \
--hex-blob --quick --max_allowed_packet=1G \
"$DB" | gzip > "$FILE"
# 校驗
if [[ $(zcat "$FILE" | tail -5 | grep -c "Dump completed") -eq 1 ]]; then
echo "[$(date)] $DB 備份成功: $FILE"
else
echo "[$(date)] $DB 備份失敗!" >&2
exit 1
fi
done
# 清理舊備份
find "$DEST" -name "*.sql.gz" -mtime +$RETENTION -delete
加入 crontab：0 2 * * * /usr/local/bin/mysql_daily_backup.sh >> /var/log/mysql_backup.log 2>&1
mysqldump + source 是 MySQL DBA 的基本功，**但僅適合中小數據集 (＜50GB)**。大數據量場景**必須掌握 xtrabackup 物理備份**。無論工具，核心在於：**定期備份、異地存儲、校驗完整性、季度演練恢復**。備份不演練，等於沒備份。
