核心工具對比
| 工具/命令 | 適用場景 | 特點 |
|---|
mysqldump | 邏輯備份、遷移、導出 SQL 文件 | 功能最全、支持壓縮、遠程、單表/單庫/全庫、一致性備份 |
source / `<` | 導入 SQL 文件 | source 在 mysql 客戶端內部執行；`<` 在 Shell 管道導入 |
mysqlpump | 大數據並行導出 (5.7+) | 多線程、壓縮、更快 |
mydumper/myloader | 超大庫高性能並行備份恢復 | 第三方工具，多線程、斷點續傳、壓縮 |
一、導出 (備份)
1. 備份單個數據庫
# 基礎語法
mysqldump -u root -p db_name > /backup/db_name.sql
# 生產推薦參數
mysqldump -u root -p \
--single-transaction \ # InnoDB 一致性備份不鎖表
--routines --triggers \ # 包含存儲過程/觸發器
--events \ # 包含事件
--master-data=2 \ # 記錄 Binlog 位置 (從庫恢復用)
--hex-blob \ # 二進制字段十六進制導出
--quick \ # 大表流式讀取，不緩存全表
--compress \ # 壓縮傳輸 (需服務端支持)
db_name > /backup/db_name_$(date +%F).sql
2. 備份單張表
mysqldump -u root -p db_name table_name > /backup/table_name.sql
3. 備份多個數據庫
mysqldump -u root -p --databases db1 db2 db3 > /backup/multi_db.sql
4. 備份所有數據庫
mysqldump -u root -p --all-databases > /backup/all_dbs.sql
5. 只導出結構 / 只導出數據
# 只結構
mysqldump -u root -p -d db_name > structure.sql
# 只數據
mysqldump -u root -p -t db_name > data.sql
二、導入 (恢復)
方法 A：Shell 管道重定向 (推薦，非交互、適合腳本)
# 先創建目標庫 (若備份文件無 CREATE DATABASE)
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS db_name CHARACTER SET utf8mb4;"
# 導入
mysql -u root -p db_name < /backup/db_name.sql
方法 B：MySQL 客戶端 `source` 命令 (交互式、可見進度)
mysql -u root -p
# 進入 mysql> 提示符後
mysql> USE db_name;
mysql> SOURCE /backup/db_name.sql;
方法 C：壓縮文件直接導入 (節省磁盤 IO)
# 導出時壓縮
mysqldump -u root -p db_name | gzip > db_name.sql.gz
# 導入時解壓管道
gunzip < db_name.sql.gz | mysql -u root -p db_name
三、遠程導入導出 (跨服務器遷移)
1. 遠程導出到本地
mysqldump -h remote_ip -u user -p db_name > local_backup.sql
2. 本地導入到遠程
mysql -h remote_ip -u user -p db_name < local_backup.sql
3. 服務器間直連管道 (最快，無本地落盤)
mysqldump -h src_ip -u user -p db_name | mysql -h dst_ip -u user -p db_name
四、常見問題與最佳實踐
| 問題 | 原因 | 解決 |
|---|
| 導入報錯 `Unknown database` | 目標庫不存在 | 先 `CREATE DATABASE` 或用 `--databases` 參數導出帶建庫語句 |
| 導入報錯 `Character set` / 亂碼 | 字符集不匹配 | 導出導入指定 `--default-character-set=utf8mb4`，目標庫表同字符集 |
| 大文件導入極慢 / 卡死 | 單線程、索引逐行構建、自動提交 | 導入前 `SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;` 導入後恢復並 `COMMIT;` |
| `max_allowed_packet` 錯誤 | 單條 SQL/行過大 | 導出加 `--hex-blob`，導入調大 `max_allowed_packet=1G` |
| 版本不兼容 (5.7→8.0) | 系統表/語法差異 | 導出加 `--column-statistics=0`，人工修正不兼容語法 |
五、生產環境備份腳本模板
#!/bin/bash
# backup_mysql.sh
USER="backup_user"
PASS="xxx"
HOST="127.0.0.1"
DBS="db1 db2"
BAK_DIR="/data/backup/mysql"
DATE=$(date +%F_%H-%M)
mkdir -p $BAK_DIR
for DB in $DBS; do
FILE="${BAK_DIR}/${DB}_${DATE}.sql.gz"
mysqldump -h$HOST -u$USER -p$PASS \
--single-transaction --routines --triggers --events \
--master-data=2 --hex-blob --quick \
$DB | gzip > $FILE
# 保留最近 7 天
find $BAK_DIR -name "${DB}_*.sql.gz" -mtime +7 -delete
done
總結
- **導出**：`mysqldump` 為核心，加上 `--single-transaction`、`--master-data=2` 等生產參數。
- **導入**：Shell 管道 `<` 最通用，`source` 適合交互調試。
- **大庫遷移**：管道直連 `mysqldump | mysql` 最快無落盤。
- **字符集、版本、大事務** 是遷移三大坑，預先測試驗證。
