mysqldump 是 MySQL 官方邏輯備份工具，支援全庫、單表、條件導出、結構/資料分離、壓縮、遠程備份等豐富功能。本文彙整最常用場景範例、核心參數詳解表與生產環境最佳實踐。
核心場景速查
| 場景 | 指令範例 |
|---|
| 備份整個資料庫 | mysqldump -u root -p db_name > db_backup.sql |
| 備份單一資料表 | mysqldump -u root -p db_name table_name > table_backup.sql |
| 僅備份結構 (不含資料) | mysqldump -u root -p -d --add-drop-table db_name > schema_only.sql |
| 備份多個資料庫 | mysqldump -u root -p --databases db1 db2 > multi_db.sql |
| 備份所有資料庫 | mysqldump -u root -p --all-databases > full_backup.sql |
| 條件導出 (WHERE) | mysqldump -u root -p db_name table_name --where="status=1 AND created>'2024-01-01'" > filtered.sql |
| 壓縮備份 (管道) | mysqldump -u root -p db_name | gzip > db_backup.sql.gz |
| 遠程備份到本地 | mysqldump -h db_host -u root -p db_name > local_backup.sql |
還原操作
# 方法 1：mysql 客戶端 source (交互式)
mysql -u root -p
CREATE DATABASE IF NOT EXISTS db_name;
USE db_name;
SET NAMES utf8mb4;
SOURCE /path/to/backup.sql;
# 方法 2：管道導入 (非交互式，適合腳本)
mysql -u root -p db_name < backup.sql
# 方法 3：壓縮檔直接導入
gunzip < backup.sql.gz | mysql -u root -p db_name
核心參數詳細表 (節選高頻項)
| 參數 | 短參 | 說明 | 建議值/備註 |
|---|
--single-transaction | - | InnoDB 一致性備份 (不鎖表) | InnoDB 必加；MyISAM 無效 |
--lock-tables | -l | 鎖定所有表 (READ LOCAL) | MyISAM 必加；與 single-transaction 互斥 |
--master-data=2 | - | 輸出 CHANGE MASTER 註解 (含 Binlog 座標) | 搭配單點恢復/建立從庫 |
--flush-logs | -F | 備份前切換 Binlog | 配合 master-data 實現增量備份基點 |
--hex-blob | - | 二進位欄位以十六進位導出 | 含 BLOB/VARBINARY 必加 |
--routines | -R | 包含存儲過程/函數 | 預設開啟 (8.0+)，舊版需顯式加上 |
--triggers | - | 包含觸發器 | 預設開啟 |
--events | -E | 包含 Event 排程器 | 使用 Event 必加 |
--default-character-set | - | 連線字元集 | utf8mb4 (避免 utf8 截斷 emoji) |
--set-gtid-purged=OFF | - | 不輸出 GTID 資訊 | 跨版本遷移/非 GTID 環境建議關閉 |
--quick | -q | 流式讀取不緩衝 | 大表必加，預設開啟 |
--extended-insert | -e | 多值 INSERT (縮小檔案、加速導入) | 預設開啟 |
--complete-insert | -c | INSERT 含欄位名 | 便於異構遷移，檔案較大 |
--no-create-info | -t | 不輸出 CREATE TABLE | 僅導出資料 |
--no-data | -d | 不輸出資料 | 僅導出結構 |
--ignore-table | - | 忽略指定表 (可重複) | --ignore-table=db.log_table |
--result-file | -r | 直接輸出到檔案 (處理 CRLF) | Windows 環境建議使用 |
生產環境推薦組合
# InnoDB 核心業務庫：一致性 + Binlog 座標 + 壓縮 + 異地傳輸
mysqldump -u backup_user -p \
--single-transaction \
--master-data=2 \
--flush-logs \
--hex-blob \
--routines --triggers --events \
--default-character-set=utf8mb4 \
--set-gtid-purged=OFF \
--databases core_db | gzip | ssh backup@storage "cat > /backups/core_db_$(date +%F).sql.gz"
# MyISAM 唯讀報表庫：鎖表 + 結構資料分離
mysqldump -u backup_user -p \
--lock-tables \
--no-data \
--databases report_db > report_schema_$(date +%F).sql
mysqldump -u backup_user -p \
--lock-tables \
--no-create-info \
--databases report_db | gzip > report_data_$(date +%F).sql.gz
效能調優提示
- 大表 (>10GB) 建議分表並行導出：
--where="id BETWEEN 1 AND 1000000" 多執行緒。
- 導入加速：目標庫暫時關閉
foreign_key_checks=0、unique_checks=0、autocommit=0、調大 innodb_buffer_pool_size、innodb_log_file_size。
- 網路瓶頸：同機房部署備份主機，走內網 10GbE；或使用
--compress (MySQL 8.0+ --compression-algorithms=zstd)。
常見錯誤與對策
| 錯誤 | 原因 | 解決 |
|---|
max_allowed_packet exceeded | 單行/單語句過大 (BLOB/長文本) | 調大客戶端/伺服端 max_allowed_packet=1G；加 --hex-blob |
| Access denied for user | 備份帳號缺少 SELECT、LOCK TABLES、SHOW VIEW、TRIGGER、EVENT 權限 | GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT ON *.* TO 'backup'@'%'; |
| Table doesn't exist (LOCK TABLES) | 並行 DDL 導致表暫時消失 | 加 --single-transaction (InnoDB) 或備份窗口避開 DDL |
| GTID purged error | 目標庫未啟用 GTID 或版本不支援 | 加 --set-gtid-purged=OFF |
- 上一篇文章： 使用 Expect 實現 MySQL 免密碼互動式登入腳本
- 下一篇文章： 解決「Can't connect to local MySQL server through socket」連線失敗全攻略
Copyright ©
程式碼之家 All Rights Reserved
