核心工具對比
| 工具 | 類型 | 適用場景 | 優點 | 缺點 |
|---|
| mysqldump | 邏輯備份 (SQL 文本) | 全量/單庫/單表遷移、跨版本、異構、小數據量 | 可讀、可編輯、跨版本兼容、支持壓縮/過濾 | 大數據量慢、導入慢、不含統計信息/權限(需參數) |
| mysqlpump | 並行邏輯備份 (5.7+) | 大庫並行導出 | 多線程、壓縮、進度條 | 8.0 移除，不如 mydumper |
| mydumper/myloader | 並行邏輯備份 (第三方) | **大數據量邏輯備份首選** | 高並行、一致性快照、分文件、極快導入 | 需額外安裝、不支持所有數據類型 |
| xtrabackup | 物理熱備份 (Percona) | **大數據量生產備份/建從庫首選** | 非阻塞、增量、流式傳輸、極快恢復 | 同版本/同參數恢復、不支持跨版本/異構 |
| SELECT ... INTO OUTFILE / LOAD DATA | 文本導入導出 | 單表大數據遷移、ETL | 極快、可指定格式 | 需 FILE 權限、secure_file_priv 限制、無結構信息 |
| source / mysql < file | SQL 腳本執行 | 導入 mysqldump 文件 | 簡單通用 | 單線程、無進度、報錯難定位 |
mysqldump 核心參數速查
# 單庫備份 (推薦生產參數)
mysqldump -u root -p \
--single-transaction \ # InnoDB 一致性快照，無鎖
--routines --triggers --events \ # 包含存儲過程/觸發器/事件
--master-data=2 \ # 註釋中記錄 Binlog 位點 (建從庫用)
--hex-blob \ # 二進制列用十六進制 (避免編碼問題)
--default-character-set=utf8mb4 \
--quick \ # 不緩存結果集直接流式輸出 (大表必加)
--compress \ # 網絡傳輸壓縮
db_name > /backup/db_name_$(date +%F).sql
# 單表備份
mysqldump -u root -p db_name tbl_name --where="created_at > '2024-01-01'" > tbl_part.sql
# 僅導出結構 (無數據)
mysqldump -u root -p -d db_name > schema.sql
# 只導出數據 (無建表語句)
mysqldump -u root -p -t db_name > data.sql
# 壓縮輸出 (管道)
mysqldump ... | gzip > backup.sql.gz
# 或
mysqldump ... | zstd -T0 -3 > backup.sql.zst
導入 (恢復) 最佳實踐
- 創建目標庫：
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
- 調大參數加速導入 (臨時會話級)：
SET SESSION sql_log_bin=0; # 不寫 Binlog (非主庫/建從庫時)
SET SESSION foreign_key_checks=0; # 關閉外鍵檢查
SET SESSION unique_checks=0; # 關閉唯一檢查
SET SESSION autocommit=0; # 手動提交 (配合大事務)
# 導入後
COMMIT;
SET SESSION foreign_key_checks=1;
SET SESSION unique_checks=1;
- 執行導入：
# 方式 1: 客戶端重定向 (推薦，支持壓縮管道)
mysql -u root -p db_name < backup.sql
zcat backup.sql.gz | mysql -u root -p db_name
# 方式 2: source 命令 (需進入 mysql 客戶端)
mysql -u root -p db_name
SOURCE /path/backup.sql;
- 大文件導入優化：
- 拆分文件：
split -l 100000 backup.sql part_ 並行導入多個分片 (無外鍵依賴時)。
- 使用
mydumper/myloader 並行導入 (需先用 mydumper 導出)。
- 關閉雙寫緩衝
SET GLOBAL innodb_doublewrite=0; (極端加速，**風險自負**，導入後重開)。
SELECT ... INTO OUTFILE / LOAD DATA 實戰
-- 導出 (服務端寫文件，需 FILE 權限、secure_file_priv 目錄)
SELECT * FROM db.tbl
INTO OUTFILE '/var/lib/mysql-files/tbl_export.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n';
-- 導入 (極快，跳過索引維護、約束檢查可進一步加速)
LOAD DATA INFILE '/var/lib/mysql-files/tbl_export.csv'
INTO TABLE db.tbl
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES; -- 若有表頭
避坑清單
- 版本不匹配：高版本導出導入低版本可能報錯 (保留字、語法、數據類型)：**加
--compatible=mysql57** 或目標版本相同。
- 字符集亂碼：導出導入全鏈路指定
--default-character-set=utf8mb4，目標庫/表編碼一致。
- GTID/主從環境：導入主庫需
SET SESSION sql_log_bin=1；建從庫用 --master-data=2 獲取位點。
- 大事務導入卡住：調大
max_allowed_packet (1G)、innodb_log_file_size (4G+)、innodb_buffer_pool_size (內存 70%)。
- 權限導出：
mysqldump --system=all (8.0+) 導出用戶/權限；或用 pt-show-grants。
