資料庫遷移、備份還原、環境同步是 DBA 與開發的日常高頻操作。本文系統整理 MySQL 官方工具 mysqldump、客戶端 source、mysqlimport 的核心參數與實戰場景，並附贈字符集亂碼避坑指南。

一、mysqldump 導出篇

1. 導出整個資料庫

mysqldump -u 用戶名 -p 資料庫名 > 導出檔名.sql
# 例
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql

2. 導出單一資料表

mysqldump -h 主機 -u 用戶名 -p 密碼 資料庫名 表名 > 導出檔名.sql
# 例
mysqldump -hlocalhost -uroot -p hqgr t_ug_user > user.sql

3. 導出存儲過程、函數、觸發器、事件

# -n 不導出資料 -t 不導出建表語句 -d 不導出資料 -R 導出存儲過程/函數 --triggers 導出觸發器 --events 導出事件
mysqldump -h 主機 -u 用戶名 -p 密碼 -ntd -R --triggers --events 資料庫名 > backup.sql

4. 僅導出結構（不含資料）

mysqldump -u 用戶名 -p -d --add-drop-table 資料庫名 > schema.sql
# -d: 無資料  --add-drop-table: 每個 CREATE TABLE 前加 DROP TABLE IF EXISTS

5. 帶 WHERE 條件導出部分資料

mysqldump -u user -p --where="order_date >= '2023-01-01'" db orders > orders_2023.sql

二、mysqldump 核心參數速查表

參數	作用
--opt	啟用 quick、add-drop-table、add-locks、extended-insert、lock-tables（預設開啟）
--quick, -q	逐行讀取導出，不緩存整表到記憶體，適合大表
--single-transaction	InnoDB 熱備份關鍵，一致性非阻塞
--lock-tables, -l	鎖定所有表（MyISAM 適用），InnoDB 建議用 single-transaction
--add-drop-table	CREATE TABLE 前加 DROP IF EXISTS，避免導入報錯
--extended-insert, -e	多行合併 INSERT，大幅提升導入速度
--complete-insert, -c	INSERT 含欄位名，跨庫遷移更安全
--hex-blob	二進位欄位十六進位導出，避免特殊字元問題
--default-character-set=utf8mb4	指定導出字符集，防止亂碼
--no-create-info, -t	僅導出資料
--no-data, -d	僅導出結構
--tab=路徑, -T	導出為 .sql (結構) + .txt (資料) 兩檔案，需目錄存在且有權限

三、資料導入篇

1. mysql 客戶端直接導入（推薦大檔案）

mysql -h 主機 -u 用戶名 -p 資料庫名 < 備份檔.sql
# 例
mysql -uroot -p mydb < mydb_backup.sql

2. source 命令（需先進入 mysql 交互模式）

mysql -u root -p
mysql> USE 目標資料庫;
mysql> SOURCE /完整路徑/備份檔.sql;

3. 從壓縮檔直接導入（節省磁碟空間）

gunzip < 備份檔.sql.gz | mysql -u 用戶名 -p 資料庫名
# 或
zcat 備份檔.sql.gz | mysql -u 用戶名 -p 資料庫名

4. mysqlimport 批量導入文本檔（CSV/TSV）

# 檔名必須與表名一致，如 customers.txt 對應 customers 表
mysqlimport -u 用戶名 -p --local --fields-terminated-by=',' --lines-terminated-by='\n' 資料庫名 /路徑/customers.txt

四、跨伺服器直接遷移（無落地檔案）

# 從 host1 導出並直接導入 host2
mysqldump -h host1 -u user -p db_name | mysql -h host2 -u user -p db_name

五、字符集亂碼終極解決方案

1. 導出時指定字符集：mysqldump --default-character-set=utf8mb4 ...
2. 導入時指定字符集：mysql --default-character-set=utf8mb4 db < file.sql
3. 檢查原庫字符集：SHOW VARIABLES LIKE 'character_set_%'; SHOW CREATE DATABASE db;
4. 建表/建庫統一 utf8mb4：CREATE DATABASE db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

六、mysqlhotcopy（僅 MyISAM、本機檔案系統）

# 備份整個資料庫目錄
mysqlhotcopy -h localhost -u user -p db_name /backup/dir
# 還原：直接複製回目錄並修正權限
cp -rf db_name /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/db_name

注意：mysqlhotcopy 已在 MySQL 5.7+ 棄用，僅適用 MyISAM，InnoDB 請用 mysqldump --single-transaction 或 Percona XtraBackup。