一、導出工具對比
| 工具/語句 | 適用場景 | 優點 | 缺點 |
|---|
mysqldump | 邏輯備份、跨版本遷移、結構+數據導出 | SQL 文本可讀、支持 WHERE 篩選、壓縮傳輸、跨引擎 | 大表導出/導入慢、佔用磁盤大 |
SELECT ... INTO OUTFILE | 大批量導出文本/CSV、報表生成 | 速度極快、可指定分隔符/引號、支持 JOIN 複雜查詢 | 需 FILE 權限、只能導出到服務器本地、不含建表語句 |
mysqlhotcopy | MyISAM 物理熱備份 | 極快、鎖表時間短 | 僅 MyISAM、僅 Unix/NetWare、需文件系統訪問 |
| 物理拷貝數據目錄 | 全庫物理冷備份/快照 | 最快、支持增量（配合 LVM/ZFS/btrfs 快照） | 需停庫或全局鎖、InnoDB 需配合 ib_logfile |
| 二進制日志 | 增量備份、時間點恢復 (PITR) | 增量、可重放、支持主從 | 需解析、恢復慢、佔磁盤 |
二、mysqldump 核心參數與實戰
常用參數速查
# 必備
--single-transaction # InnoDB 一致性無鎖導出（必加）
--master-data=2 # 記錄 binlog 位置（CHANGE MASTER TO 註釋）
--routines --triggers --events # 導出存儲過程/觸發器/事件
--hex-blob # 二進制列十六進制導出
--default-character-set=utf8mb4 # 字符集顯式指定
# 性能
--quick # 流式導出，不緩存全表結果集
--extended-insert # 多行 INSERT（默認開啟）
--opt # 等同 --add-drop-table --add-locks --extended-insert --quick --lock-tables --set-charset --disable-keys
# 結構控制
--no-data (-d) # 只導結構
--no-create-info (-t) # 只導數據
--add-drop-table # 每表前加 DROP TABLE IF EXISTS
--compact # 精簡輸出（去除註釋等）
# 篩選
--where="id > 1000" # 導出滿足條件的行
--tables tbl1 tbl2 # 指定表（配合 --databases）
--ignore-table=db.tbl # 排除表
典型命令示例
# 1. 全庫邏輯備份（生產標準）
mysqldump -u backup -p --single-transaction --master-data=2 \
--routines --triggers --events --hex-blob \
--default-character-set=utf8mb4 \
--databases db1 db2 > /backup/full_$(date +%F).sql
# 2. 大表分批導出（配合 WHERE 主鍵範圍）
mysqldump -u backup -p --single-transaction --where="id BETWEEN 1 AND 1000000" \
db big_table > /backup/big_table_part1.sql
# 3. 只導結構（遷移審計）
mysqldump -u backup -p --no-data --routines --triggers --events db > schema.sql
# 4. 遠程直接導入另一實例
mysqldump -h src_host -u backup -p --single-transaction db | \
mysql -h dst_host -u root -p db
三、SELECT INTO OUTFILE / LOAD DATA INFILE 高性能文本互導
導出
SELECT id, name, created_at
FROM users
WHERE created_at >= '2023-01-01'
INTO OUTFILE '/tmp/users_2023.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n';
- 需
FILE 權限、secure_file_priv 目錄寫入權限。
- 文件不能存在，否則報錯。
- 支持任意 SELECT（含 JOIN、聚合），可作報表生成器。
導入
LOAD DATA INFILE '/tmp/users_2023.csv'
INTO TABLE users
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES -- 若有表頭
(id, name, @created_at)
SET created_at = STR_TO_DATE(@created_at, '%Y-%m-%d %H:%i:%s');
LOCAL 關鍵字：客戶端讀文件發送給服務器，無需服務器 FILE 權限、繞過 secure_file_priv。REPLACE/IGNORE 處理主鍵/唯一鍵衝突。SET col = expr 列轉換/賦值。LOW_PRIORITY 延遲插入直到無讀鎖。
四、mysqlimport 批量導入工具
# 等價於 LOAD DATA，命令行批量處理多文件
mysqlimport -u user -p --local --fields-terminated-by=, \
--fields-enclosed-by='"' --lines-terminated-by='\n' \
--ignore-lines=1 --columns='id,name,created_at' \
db /path/users_2023.csv
文件名 = 表名（不含擴展名），支持通配符批量導入。
五、備份恢復策略設計
分級策略
| 級別 | 頻率 | 方式 | 保留 | RPO/RTO |
|---|
| 全量邏輯備份 | 每日 1 次（低峰） | mysqldump --single-transaction | 7-30 天 | RPO 24h / RTO 視數據量 |
| 增量備份 | 每小時/每 15 分鐘 | binlog (FLUSH LOGS 切割) 或 xtrabackup --incremental | 7-30 天 | RPO 1h/15min / RTO 較長 |
| 物理快照 | 每日/每 6 小時 | LVM/ZFS/云盤快照 + FLUSH TABLES WITH READ LOCK | 3-7 天 | RPO 6h / RTO 分鐘級 |
| 異地災備 | 實時/每日 | 主從複製 + 從庫備份 / 跨區域 binlog 同步 | 長期 | RPO 秒級 / RTO 分鐘級 |
核心原則
- 備份必須可恢復：定期演練恢復流程，驗證文件完整性。
- 主從架構下從庫備份：不阻塞主庫，
STOP SLAVE SQL_THREAD 保證一致性點。
- InnoDB 物理備份首選 xtrabackup：支持熱備份、增量、壓縮、流式傳輸，比 mysqldump 快 10 倍+。
- binlog 必開：
log_bin=on, binlog_format=ROW, expire_logs_days=7, sync_binlog=1（金融級）。
- 加密傳輸與存儲：备份文件加密、傳輸走 SSL/VPN。
六、避坑清單
mysqldump 無 --single-transaction 導致 MyISAM 鎖表、InnoDB 一致性風險。- 字符集不匹配導致亂碼：導出導入均顯式指定
--default-character-set=utf8mb4。
secure_file_priv 限制導致 INTO OUTFILE 失敗：檢查變量值，文件放對應目錄或用 LOCAL。- 大事務導入
LOAD DATA 卡住：分批、關閉自動提交、調大 innodb_log_file_size。
- 備份文件未壓縮佔滿磁盤：管道接
gzip/pigz 或 zstd。
