mysqldump 導出核心場景
1. 導出整庫
mysqldump -u root -p dbname > backup.sql
# 遠程
mysqldump -h 127.0.0.1 -P 3306 -u root -p dbname > backup.sql
2. 導出單表/多表
# 單表
mysqldump -u root -p dbname table1 > table1.sql
# 多表 (空格分隔)
mysqldump -u root -p dbname table1 table2 > tables.sql
3. 只導結構 (無數據)
mysqldump -u root -p -d --add-drop-table dbname > struct.sql
# -d: --no-data
# --add-drop-table: 便於導入前清理
4. 排除指定表
mysqldump -u root -p dbname \
--ignore-table=dbname.log_table \
--ignore-table=dbname.tmp_table \
> backup.sql
5. 大表優化參數
mysqldump -u root -p \
--single-transaction \
--quick \
--master-data=2 \
dbname > backup.sql
--single-transaction:InnoDB 一致性視圖,不鎖表
--quick:流式讀取,不緩存全表
--master-data=2:輸出 CHANGE MASTER 註釋,備份點位
SOURCE 導入實戰
方式一:客戶端交互式導入
mysql -u root -p
mysql> USE target_db;
mysql> SOURCE /full/path/backup.sql;
方式二:管道非交互導入 (腳本/自動化)
mysql -u root -p target_db < backup.sql
# 或指定字符集防亂碼
mysql -u root -p --default-character-set=utf8mb4 target_db < backup.sql
避坑指南
| 坑點 | 現象 | 解決 |
| LOCK TABLES 權限不足 | Got error: 1044 Access denied ... when using LOCK TABLES | 加 --skip-lock-tables (單事務下安全) |
| 導入中斷/報錯 | 半數據導入 | 事務型引擎用 --single-transaction 備份;導入前 SET AUTOCOMMIT=0 |
| 字符集亂碼 | 導出 utf8 導入 latin1 環境 | 導出導入均指定 --default-character-set=utf8mb4 |
| 超大 SQL 導入慢 | 單線程回放 | 分表導出/並行導入工具 (mydumper/myloader) |
物理備份替代方案 (大數據量)
- Percona XtraBackup:InnoDB 熱備標準,增量備份、流式傳輸、壓縮加密
- MySQL Enterprise Backup:官方商業工具
- 文件系統快照:LVM/ZFS/EBS snapshot + 刷新日志
備份策略建議
- 每日全量 + 每小時增量 (binlog)
- 異地存儲 (OSS/S3/異地機房)
- 季度恢復演練,驗證 RPO/RTO
- 監控備份大小、耗時、成功率告警