mysqldump [options] -u user -p db_name [tbl_name ...] > dump_file.sql
| 參數 | 說明 | 建議值 |
|---|---|---|
| -u / --user | 用戶名 | root 或專用備份賬戶 |
| -p / --password | 密碼（提示輸入更安全） | -p 回車後輸入 |
| -h / --host | 主機地址 | localhost / 127.0.0.1 / 遠程 IP |
| -P / --port | 端口 | 3306 |
| --single-transaction | InnoDB 熱備份（一致性快照，無鎖） | **必加** |
| --master-data=2 | 輸出 CHANGE MASTER TO 註釋（從庫搭建用） | 2（註釋形式） |
| --routines | 導出存儲過程/函數 | 加上 |
| --triggers | 導出觸發器 | 加上 |
| --events | 導出事件調度器 | 加上 |
| --hex-blob | 二進制列用十六進制導出 | 加上 |
| --default-character-set | 連接字符集 | utf8mb4 |
| --no-data / -d | 僅導出結構 | 結構遷移時用 |
| --no-create-info / -t | 僅導出數據 | 數據同步時用 |
| --where | 導出條件過濾 | "id > 1000" |
mysqldump -u backup_user -p -h 127.0.0.1 -P 3306 \ --single-transaction \ --master-data=2 \ --routines --triggers --events \ --hex-blob \ --default-character-set=utf8mb4 \ --set-gtid-purged=OFF \ my_database > my_database_$(date +%F_%H%M).sql
# 1. 登錄 mysql -u root -p # 2. 創建目標庫（如不存在） CREATE DATABASE IF NOT EXISTS target_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; # 3. 切換庫並導入 USE target_db; SOURCE /path/to/dump_file.sql;
# 直接管道導入（無需進入交互模式） mysql -u root -p -h 127.0.0.1 -P 3306 \ --default-character-set=utf8mb4 \ target_db < dump_file.sql # 或者帶進度條（需安裝 pv） pv dump_file.sql | mysql -u root -p target_db
| 場景 | 優化方案 |
|---|---|
| 導出耗時長 | 並行導出：mysqldump --tab + 多線程壓縮 / mydumper 工具 |
| 導入耗時長 | 1. 關閉索引：ALTER TABLE ... DISABLE KEYS（僅 MyISAM） 2. 批量提交：SET autocommit=0; ... COMMIT; 3. 禁用外鍵：SET FOREIGN_KEY_CHECKS=0; 4. 禁用唯一檢查：SET UNIQUE_CHECKS=0; 5. 增大 innodb_buffer_pool_size / innodb_log_file_size 6. 使用 myloader（mydumper 配套）並行導入 |
| 跨版本遷移 | mysql_upgrade 檢查兼容性；避免使用已棄用語法 |
| 字符集不匹配 | 導出/導入均指定 --default-character-set=utf8mb4 |
SELECT COUNT(*) FROM src.tbl vs SELECT COUNT(*) FROM dst.tblCHECKSUM TABLE tbl 對比SET FOREIGN_KEY_CHECKS=1; SET UNIQUE_CHECKS=1; 重新啟用後無報錯| 錯誤 | 原因 | 解決 |
|---|---|---|
| ERROR 1153: Got a packet bigger than 'max_allowed_packet' | 單條 SQL 超過限制 | 導入端設置 SET GLOBAL max_allowed_packet=1G |
| ERROR 1062: Duplicate entry | 主鍵/唯一鍵衝突 | REPLACE INTO / ON DUPLICATE KEY UPDATE / 先清空目標表 |
| ERROR 1217: Cannot delete or update a parent row | 外鍵約束 | SET FOREIGN_KEY_CHECKS=0 導入後再開啟 |
| Unknown collation: 'utf8mb4_0900_ai_ci' | 目標版本不支援排序規則 | 導出時加 --skip-set-charset / 手動修改文件中的 COLLATE |
命令行導出導入是 DBA 的基本功，**參數精準、流程標準化、驗證自動化**，才能在遷移、備份、災難恢復中做到萬無一失。
