mysqldump 是 MySQL 官方邏輯備份工具，將數據庫導出為可重放的 SQL 文本，支持**全量/增量/結構/數據/單表/跨版本**多種場景，是備份、遷移、審計的基石。
mysqldump [options] > dump.sql # 所有庫
mysqldump [options] --databases db1 db2 > dump.sql # 指定庫
mysqldump [options] db_name tbl1 tbl2 > dump.sql # 指定表
| 類別 | 參數 | 作用 | 推薦場景 |
|---|---|---|---|
| 輸出控制 | --no-create-info (-t) | 僅導數據，無 CREATE TABLE | 增量同步、數據修正 |
--no-data (-d) | 僅導結構，無 INSERT | Schema 審計、異構遷移建表 | |
--add-drop-table | 每表前加 DROP IF EXISTS | 重複導入不報錯 | |
--add-drop-database | 每庫前加 DROP IF EXISTS | 全庫重建 | |
| 性能優化 | --quick (-q) | 流式讀取，不緩存全表結果集 | **大表必加**，防 OOM |
--single-transaction | InnoDB 一致性快照，無鎖 | **線上熱備必加** | |
--opt | 開啟 quick、add-drop-table、add-locks、extended-insert 等組合 | 默認開啟，導出最快 | |
--extended-insert | 多行合併 INSERT，減小體積加快導入 | 默認開啟 | |
--max-allowed-packet=1G | 防超大行截斷 | 含 BLOB/長文本 | |
| 一致性與鎖 | --lock-tables (-l) | 鎖定所有表 (MyISAM 適用) | 非事務引擎 |
--lock-all-tables (-x) | 全局讀鎖 (FLUSH TABLES WITH READ LOCK) | 混合引擎、需全局一致點 | |
--master-data=2 | 輸出 CHANGE MASTER TO 註釋 (位置/GTID) | **搭建從庫必加** | |
--flush-logs (-F) | 導出前滾動 binlog | 配合增量備份 | |
| 數據過濾 | --where="cond" (-w) | 按條件篩選行 | 分表導出、歷史數據歸檔 |
--ignore-table=db.tbl | 排除表 | 跳過大日志/臨時表 | |
--tables | 顯式指定表列表 | 腳本化控制 | |
--databases (-B) | 顯式指定庫列表 | 多庫並行備份 | |
| 格式轉換 | --tab=/path (-T) | 輸出 .sql (結構) + .txt (數據，制表符分隔) | LOAD DATA 快速導入、異構導入 |
--fields-terminated-by=, | CSV 字段分隔符 | Excel/BI 工具直讀 | |
--hex-blob | 二進制列輸出十六進制 | 跨字符集遷移安全 |
mysqldump -u backup -p -h 127.0.0.1 -P 3306 \
--single-transaction --quick --max-allowed-packet=1G \
--master-data=2 --flush-logs --routines --triggers --events \
--all-databases | gzip > /backup/full_$(date +%F_%H%M).sql.gz
關鍵點：--single-transaction 無鎖一致性、--master-data=2 記錄 binlog 位置/GTID 供增量/從庫、--routines --triggers --events 完整對象。
mysqldump -u backup -p --single-transaction --master-data=2 --all-databases > base.sql
# 從庫導入後：
CHANGE MASTER TO MASTER_HOST='...', MASTER_USER='repl', MASTER_PASSWORD='...', MASTER_AUTO_POSITION=1;
START SLAVE;
# 導出 (配合 mydumper 更高效)
mysqldump --single-transaction --quick --where="id BETWEEN 1 AND 1000000" db big_table > part1.sql
mysqldump --single-transaction --quick --where="id BETWEEN 1000001 AND 2000000" db big_table > part2.sql
# 導入
mysql -u root -p db < part1.sql &
mysql -u root -p db < part2.sql &
wait
# 5.7 導出
mysqldump --single-transaction --routines --triggers --events --all-databases --column-statistics=0 > dump_57.sql
# 8.0 導入前預檢
mysqlsh --util check-for-server-upgrade --config-file=/etc/my.cnf --target-version=8.0.33
# 導入
mysql -u root -p < dump_57.sql
# 導入後必跑
mysql_upgrade -u root -p
注意：--column-statistics=0 關閉 8.0 客戶端對 5.7 不支持的統計信息查詢。
mysqldump --tab=/tmp/export --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by='\n' --no-create-info db table
# 產生 table.txt (CSV) + table.sql (結構)
# PostgreSQL: COPY table FROM '/tmp/export/table.txt' CSV HEADER QUOTE '"';
MASTER_LOG_FILE/POS 或 GTID，定期 mysqlbinlog --start-position=... 導出增量 SQLmysqlbinlog --gtid-set=gtid_executed_from_backup 精確增量| 現象 | 原因 | 解決 |
|---|---|---|
| 導出卡住 / OOM | 大表緩存全量結果集 | **必加 --quick** 或用 mydumper |
| 導入報錯 "Unknown collation" | 版本差異導致字符集不兼容 | 導出加 --default-character-set=utf8mb4、目標庫預建 utf8mb4 |
| 導入慢 | 單行 INSERT、索引實時維護 | --extended-insert (默認)、導入前 SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; 導入後恢復 |
| GTID 不連續 | 手工注入事務、binlog 清理 | 重新基準全量同步 |
| 權限不足 | 備份賬戶缺 PROCESS/LOCK TABLES/RELOAD | GRANT SELECT, PROCESS, LOCK TABLES, RELOAD, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'%'; |
#!/bin/bash
# /usr/local/sbin/mysql_backup.sh
set -euo pipefail
BACKUP_DIR="/backup/mysql"
RETENTION_DAYS=14
MYSQL_USER="backup"
MYSQL_PASS="BackupPass!"
MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
mkdir -p "$BACKUP_DIR"
FILE="$BACKUP_DIR/full_$(date +%F_%H%M).sql.gz"
mysqldump -u"$MYSQL_USER" -p"$MYSQL_PASS" -h"$MYSQL_HOST" -P"$MYSQL_PORT" \
--single-transaction --quick --max-allowed-packet=1G \
--master-data=2 --flush-logs --routines --triggers --events \
--all-databases | gzip -c > "$FILE"
# 驗證完整性
if ! gzip -t "$FILE"; then
echo "Backup corrupted: $FILE" | mail -s "MySQL Backup Failed" admin@example.com
exit 1
fi
# 清理舊備份
find "$BACKUP_DIR" -name "full_*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "Backup completed: $FILE"
mysqldump 雖老但彈性極強，掌握 **--single-transaction --quick --master-data=2** 三大核心參數，覆蓋 90% 生產場景。大規模/高頻場景請引入 XtraBackup (物理) + mydumper (並行邏輯) 組合，構建分層備份體系：日全量 + 時增量 + 實時 binlog 歸檔，RPO ≤ 1min，RTO ≤ 30min。
