基本語法
mysqldump [OPTIONS] database [tables] > backup_file.sql
- 不指定 tables → 備份整個資料庫
- 輸出重導向至檔案，或透過管道直接傳給另一 MySQL 實例
核心參數詳細表
| 參數 | 短參數 | 說明 | 建議場景 |
|---|
--opt | - | 組合參數：--quick --add-drop-table --add-locks --extended-insert --lock-tables，最快備份/還原 | 預設推薦 |
--quick | -q | 不緩衝查詢結果，直接串流輸出，大表必加 | 大型資料庫 |
--single-transaction | - | InnoDB 熱備份關鍵，啟動一致性快照，不鎖表 | 線上備份 |
--lock-tables | -l | 鎖定所有表（MyISAM 必備），與 single-transaction 互斥 | MyISAM 表 |
--add-drop-table | - | CREATE 前加 DROP TABLE，還原時自動覆蓋 | 遷移/重建 |
--extended-insert | -e | 多行 INSERT，大幅縮減檔案體積與還原時間 | 所有備份 |
--flush-logs | -F | 備份前切換 Binlog，配合增量備份策略 | 主從/增量 |
--master-data=2 | - | 輸出 CHANGE MASTER TO 註解（含 Binlog 位點），從庫建立必備 | 主從搭建 |
--routines | -R | 包含儲存程序/函數 | 完整備份 |
--events | -E | 包含 Event 排程任務 | 完整備份 |
--triggers | - | 包含觸發器（預設開啟） | 完整備份 |
--hex-blob | - | 二進制欄位以十六進制導出，避免字元集問題 | 含 BLOB 表 |
--no-data | -d | 僅導出結構（Schema） | 結構遷移 |
--no-create-info | -t | 僅導出資料，不含 CREATE TABLE | 資料遷移 |
--where | -w | 條件導出，如 -w "id>100" | 分批/篩選 |
--compress | -C | 客戶端/伺服器傳輸壓縮 | 遠端備份 |
常用實戰範例
1. 完整備份（生產環境標準）
mysqldump -u root -p --single-transaction --routines --events --master-data=2 \
--flush-logs --hex-blob --databases db1 db2 > full_backup_$(date +%F).sql
2. 僅備份結構
mysqldump -u root -p -d --add-drop-table db_name > schema_only.sql
3. 備份單一資料表
mysqldump -u root -p db_name table_name > table_backup.sql
4. 遠端備份並壓縮
mysqldump -h remote_host -u root -p -C db_name | gzip > remote_backup_$(date +%F).sql.gz
5. 跨實例直接同步（無中間檔案）
mysqldump -u root -p --single-transaction db_name | mysql -h target_host -u root -p target_db
還原操作
方法一：mysql 客戶端導入
# 先建立目標資料庫
mysqladmin -u root -p create target_db
# 導入
mysql -u root -p target_db < backup_file.sql
方法二：SOURCE 指令（進入 mysql 後）
mysql> USE target_db;
mysql> SET NAMES utf8mb4; -- 確保字元集一致，注意不是 UTF-8
mysql> SOURCE /path/to/backup_file.sql;
大檔案導入優化
- 關閉自動提交：
SET autocommit=0; 導入後 COMMIT;
- 關閉唯一性檢查：
SET unique_checks=0; 導入後 SET unique_checks=1;
- 關閉外鍵檢查：
SET foreign_key_checks=0; 導入後 SET foreign_key_checks=1;
- 調大
max_allowed_packet、innodb_buffer_pool_size
自動化備份腳本範例
#!/bin/bash
# daily_backup.sh
BACKUP_DIR="/backup/mysql"
DATE=$(date +%F_%H-%M)
DB_USER="backup_user"
DB_PASS="xxxxxx"
DATABASES="db1 db2 db3"
mkdir -p $BACKUP_DIR
mysqldump -u$DB_USER -p$DB_PASS \
--single-transaction --routines --events --master-data=2 \
--flush-logs --hex-blob --databases $DATABASES \
| gzip > $BACKUP_DIR/full_$DATE.sql.gz
# 保留 30 天
find $BACKUP_DIR -name "full_*.sql.gz" -mtime +30 -delete
# 上傳至異地存儲（略）
常見坑點與避雷指南
- 未加 --quick：大表導出會佔滿記憶體導致 OOM
- MyISAM 表未加 --lock-tables：備份資料不一致
- 字元集不匹配：導入時必須
SET NAMES 對應導出字元集
- GTID 複製環境：需加
--set-gtid-purged=OFF 避免 GTID 衝突
- 版本不相容：高版本 mysqldump 導出低版本可能含不支援語法，建議用目標版本的 mysqldump
