mysqldump 是 MySQL 官方邏輯備份工具，將庫/表結構與數據導出為可執行 SQL 文本，支援跨版本、跨平台遷移。本文系統梳理核心參數、典型場景命令、效能優化與常見陷阱。
| 參數 | 短參 | 作用 | 推薦場景 |
|---|---|---|---|
--single-transaction | - | InnoDB 熱備份：啟動 REPEATABLE READ 事務，獲取一致性快照，不阻塞寫入 | 生產環境 InnoDB 主庫備份 ✅ |
--lock-tables | -l | 鎖定所有表（READ LOCAL），MyISAM 必備；InnoDB 配合 --single-transaction 可省 | MyISAM 混合場景 |
--add-drop-table | - | 每表前加 DROP TABLE IF EXISTS，重複導入不報錯 | 遷移/測試環境覆蓋導入 |
--add-locks | - | INSERT 批次前後加 LOCK/UNLOCK TABLES，加速導入、防併發干擾 | 大數據導入 |
--complete-insert | -c | INSERT 列明寫欄位名，兼容列順序變更、新增列 | 異構遷移、Schema 演進 |
--extended-insert | -e | 多行合併 INSERT（默認開啟），大幅減小文件、加速導入 | 默認即可 |
--quick | -q | 流式讀取（不緩存整張表結果集），大表必加，防 OOM | 大表備份 ✅ |
--where | -w | 導出滿足條件的行，增量/分片導出 | 歷史數據歸檔、分表導出 |
--no-data | -d | 僅導出結構（DDL） | Schema 遷移、代碼評審 |
--no-create-info | -t | 僅導出數據（INSERT） | 數據同步、測試數據構造 |
--routines | -R | 導出存儲過程/函數 | 完整遷移 |
--triggers | -- | 導出觸發器（默認開啟） | 完整遷移 |
--events | -E | 導出 Event 調度器任務 | 完整遷移 |
--set-gtid-purged | -- | GTID 模式下控制 SET @@GLOBAL.GTID_PURGED 行為 | 主從/群組複制遷移 |
--master-data=2 | -- | 輸出註釋形式的 CHANGE MASTER TO 位置，便於搭建從庫 | 搭建 Slave |
--flush-logs | -F | 導出前刷新 Binlog，配合 --master-data 位置精確 | 搭建 Slave |
--hex-blob | -- | 二進制列（BLOB/BINARY/GEOMETRY）以十六進制導出，避免編碼問題 | 含二進制數據的表 |
--default-character-set | -- | 指定連接字符集，防止導出亂碼 | 跨字符集遷移 ✅ |
mysqldump -h127.0.0.1 -uroot -p'Pass' \
--single-transaction --quick --routines --triggers --events \
--master-data=2 --flush-logs --hex-blob \
--databases db1 db2 > /backup/full_$(date +%F_%H%M).sqlmysqldump -uroot -p --no-data --routines --triggers --events db_name > schema.sql# 假設 id 自增，每 1000 萬行一片
mysqldump -uroot -p --single-transaction --quick \
--where="id BETWEEN 1 AND 10000000" db huge_table > part1.sql
mysqldump -uroot -p --single-transaction --quick \
--where="id BETWEEN 10000001 AND 20000000" db huge_table > part2.sql# 先獲取當前 Binlog 位置
mysql -uroot -p -e "SHOW MASTER STATUS\G"
# 再導出自上次備份以來的數據（需配合 --master-data 或手工記錄位置）mysqldump -uroot -p --single-transaction --routines --triggers --events \
--column-statistics=0 \ # 8.0 客戶端備份 5.7 必加，避免 statistics 表報錯
--compatible=mysql40 \ # 極舊版本兼容，一般無需
--default-character-set=utf8mb4 \
db_name > mig.sql# 1. 建庫（若導出未含 CREATE DATABASE）
mysql -uroot -p -e "CREATE DATABASE IF NOT EXISTS db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;"
# 2. 導入（大文件建議用 pv 顯示進度）
pv backup.sql | mysql -uroot -p db_name
# 3. 或分批導入（避免超大事務）
split -l 10000 backup.sql part_
for f in part_*; do mysql -uroot -p db_name < $f; done--quick：防止客戶端緩存整張表結果集導致 OOM。--single-transaction：非阻塞一致性備份；切勿同時加 --lock-tables 否則退化為鎖表。mydumper（多線程並行導出）或 XtraBackup（物理熱備份）。--default-character-set=utf8mb4。--set-gtid-purged=OFF 避免導入時 GTID 衝突，或導入後 RESET MASTER。mysqldump 可備份低版本服務器；反之可能報錯（如 5.7 dump 8.0 觸發 column_statistics 錯誤）。#!/bin/bash
# /usr/local/bin/mysql_backup.sh
set -euo pipefail
DB_USER="backup_user"
DB_PASS="*****"
DB_HOST="127.0.0.1"
BACKUP_DIR="/data/backup/mysql"
RETENTION_DAYS=7
DATE=$(date +%F_%H%M)
mkdir -p "$BACKUP_DIR"
mysqldump -h"$DB_HOST" -u"$DB_USER" -p"$DB_PASS" \
--single-transaction --quick --routines --triggers --events \
--master-data=2 --flush-logs --hex-blob \
--all-databases | gzip > "$BACKUP_DIR/full_$DATE.sql.gz"
# 清理舊備份
find "$BACKUP_DIR" -name 'full_*.sql.gz' -mtime +$RETENTION_DAYS -delete
# 記錄日誌
echo "[$(date)] Backup completed: full_$DATE.sql.gz" >> "$BACKUP_DIR/backup.log"掌握 mysqldump 參數組合，能覆蓋 90% 以上的邏輯備份場景。大數據量、高可用要求場景請引入物理備份工具構建多層備份體系。
