MySQL 資料匯入匯出實戰：mysqldump 與 source 指令完整教學

mysqldump 是 MySQL 官方邏輯備份工具，source 是客戶端匯入指令。兩者結合可覆蓋 90% 的備份、遷移、同步場景。本文系統整理常用參數、進階過濾技巧與避坑指南。

一、mysqldump 匯出指令大全

基礎語法

mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases

常用場景指令

權限不足導致 LOCK TABLES 失敗的解決

# 加上 --skip-lock-tables
mysqldump -u user -p dbname --skip-lock-tables > backup.sql

二、匯入還原指令

方法一：mysql 客戶端 source（互動式、支援大檔）

mysql -u root -p
mysql> USE target_db;
mysql> SOURCE /path/to/backup.sql;

方法二：管道導入（腳本自動化首選）

mysql -u root -p target_db < backup.sql
# 或指定字元集避免亂碼
mysql -u root -p --default-character-set=utf8mb4 target_db < backup.sql

方法三：壓縮檔案直接導入（節省磁碟 I/O）

gunzip < backup.sql.gz | mysql -u root -p target_db
# 或
zcat backup.sql.gz | mysql -u root -p target_db

三、進階技巧與避坑指南

1. 只匯入特定表

# 匯出時已篩選最佳；若需從全量檔提取單表：
sed -n '/^CREATE TABLE `tbl_name`/,/^UNLOCK TABLES;/p' backup.sql > tbl_only.sql
# 或使用 mysqldump 重新匯出該表

2. 字元集亂碼防範

• 匯出指定：--default-character-set=utf8mb4
• 匯入指定：--default-character-set=utf8mb4
• 檔案編碼確認：file backup.sql 應顯示 UTF-8

3. 版本相容性

• 高版本導出 → 低版本導入：可能失敗（語法/功能不支援）。建議目標版本 ≥ 來源版本。
• 跨大版本遷移（5.7→8.0）：需處理保留字、認證插件、預設字元集變更等問題。

4. 大檔案導入優化

• 關閉自動提交：SET autocommit=0; 導入後 COMMIT;
• 關閉唯一檢查/外鍵檢查：SET unique_checks=0; SET foreign_key_checks=0; 導入後恢復
• 調大 max_allowed_packet、innodb_buffer_pool_size
• 使用 LOAD DATA INFILE 替代 INSERT 大量資料

5. 物理複製 vs 邏輯備份選擇

參考《MySQL 資料備份與還原：mysqldump、mysqlhotcopy 與物理複製全攻略》一文。

四、Windows 批次檔自動化備份範例

@echo off
set host=localhost
set user=root
set pwd=your_password
set db=my_db
set backup_dir=D:\db_backup
set date_str=%date:~0,4%%date:~5,2%%date:~8,2%

mysqldump -h%host% -u%user% -p%pwd% --single-transaction --quick %db% > "%backup_dir%\%db%_%date_str%.sql"
if %errorlevel% neq 0 (
    echo Backup failed!
    exit /b 1
)
echo Backup completed: %db%_%date_str%.sql

掌握 mysqldump 精準參數與 source/管道導入技巧，讓資料遷移、備份還原、環境同步成為標準化、可自動化的常規操作。