您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 命令行導入導出數據庫完整操作手冊

編輯：MySQL 資料庫相關知識

導出篇

mysqldump 核心語法

mysqldump [options] -u username -p database_name [table_name...] > dump_file.sql

常用導出場景

場景	命令範例	關鍵參數說明
全庫結構+數據	mysqldump -uroot -p --single-transaction --routines --triggers --events --master-data=2 db_name > full.sql	--single-transaction 一致性視圖 (InnoDB) --master-data=2 記錄 Binlog 位點 (CHANGE MASTER TO 註釋)
單表	mysqldump -uroot -p db_name table_name > table.sql
多表	mysqldump -uroot -p db_name t1 t2 t3 > multi.sql
僅結構	mysqldump -uroot -p -d db_name > schema.sql	-d / --no-data
僅數據	mysqldump -uroot -p -t db_name > data.sql	-t / --no-create-info
WHERE 條件導出子集	mysqldump -uroot -p --where="id>1000" db_name t1 > subset.sql	--where 不加 ORDER BY/LIMIT
壓縮導出	mysqldump -uroot -p db_name \| gzip > dump_$(date +%F).sql.gz	管道流式壓縮，不佔臨時空間
排除表	mysqldump -uroot -p db_name --ignore-table=db_name.log_table > dump.sql	--ignore-table 可重複
導出為 CSV	mysqldump -uroot -p --tab=/tmp/export --fields-terminated-by=',' --fields-enclosed-by='"' db_name t1	需 secure_file_priv 權限目錄

Windows 環境特殊說明

# 1. 進入 bin 目錄或配置 PATH
cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

# 2. 執行導出 (PowerShell 推薦)
mysqldump -uroot -p db_name > dump.sql
# 輸入密碼後生成文件

# 3. 指定輸出編碼 (避免中文亂碼)
mysqldump -uroot -p --default-character-set=utf8mb4 db_name > dump.sql

導入篇

方式一：mysql 客戶端 source (交互式)

# 1. 登入 MySQL
mysql -uroot -p

# 2. 創建目標庫 (如不存在)
CREATE DATABASE IF NOT EXISTS db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# 3. 切換庫
USE db_name;

# 4. 導入
SOURCE /path/to/dump.sql;
# 或
\. /path/to/dump.sql

方式二：管道導入 (非交互，腳本/自動化首選)

# 基礎導入
mysql -uroot -p db_name < /path/to/dump.sql

# 指定默認字符集 (關鍵！防止導入亂碼)
mysql -uroot -p --default-character-set=utf8mb4 db_name < /path/to/dump.sql

# 壓縮文件直接導入
zcat dump.sql.gz | mysql -uroot -p --default-character-set=utf8mb4 db_name

# 遠程導入
mysql -h 10.0.0.2 -uroot -p --default-character-set=utf8mb4 db_name < /path/to/dump.sql

方式三：Windows 命令行

# CMD
mysql -uroot -p db_name < dump.sql

# PowerShell (需處理重定向編碼)
Get-Content dump.sql | mysql -uroot -p db_name
# 或
cmd /c "mysql -uroot -p db_name < dump.sql"

進階技巧與避坑

1. 大文件導入優化

關閉自動提交：SET autocommit=0; 文件開頭，COMMIT; 文件結尾 (mysqldump --single-transaction 已自動處理)
禁用外鍵檢查：SET FOREIGN_KEY_CHECKS=0; ... SET FOREIGN_KEY_CHECKS=1;
禁用唯一性檢查：SET UNIQUE_CHECKS=0; ... SET UNIQUE_CHECKS=1;
調大網絡緩衝：mysql --max-allowed-packet=1G ...

2. 異構版本遷移 (5.7 → 8.0)

# 導出時加上兼容參數
mysqldump -uroot -p --column-statistics=0 --single-transaction --routines --triggers --events \
    --set-gtid-purged=OFF --default-character-set=utf8mb4 db_name > dump_57_to_80.sql

# 導入前檢查
grep -i "create table" dump.sql | head -20  # 確認無 5.7 已移除語法 (如 TYPE=MyISAM)
mysqlcheck --all-databases --check-upgrade -uroot -p  # 導入後執行升級檢查

3. 斷點續傳大文件導入

# 使用 pv 監控進度並支持斷點 (需安裝 pv)
pv dump.sql | mysql -uroot -p db_name

# 或分割文件
split -l 10000 dump.sql dump_part_
for f in dump_part_*; do mysql -uroot -p db_name < $f; done

4. 常見錯誤對照表

錯誤信息	原因	解決
Access denied for user	密碼錯誤/權限不足	確認用戶/密碼/GRANT 權限
Unknown database	目標庫不存在	先 CREATE DATABASE
ERROR 1062 Duplicate entry	主鍵/唯一鍵衝突	TRUNCATE 目標表或用 REPLACE/INSERT IGNORE
ERROR 1153 Packet too large	SQL 語句超過 max_allowed_packet	增大 max_allowed_packet (客戶端/服務端)
ERROR 1118 Row size too large	行超過 65535 字節限制	拆表/用 TEXT/BLOB 離頁存儲
ERROR 1290 Secure file priv	SELECT INTO OUTFILE/LOAD DATA 目錄受限	檢查 secure_file_priv 變量，用允許目錄

Linux 系統服務化備份腳本範例

#!/bin/bash
# /usr/local/bin/mysql_backup.sh
set -euo pipefail

DB_USER="backup_user"
DB_PASS="xxx"
DB_NAME="app_db"
BACKUP_DIR="/backup/mysql"
RETENTION_DAYS=30

mkdir -p "$BACKUP_DIR"
DATE=$(date +%F_%H%M)
FILE="$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"

mysqldump -u"$DB_USER" -p"$DB_PASS" \
    --single-transaction --routines --triggers --events \
    --master-data=2 --default-character-set=utf8mb4 \
    "$DB_NAME" | gzip > "$FILE"

# 刪除舊備份
find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: $FILE"

加入 crontab：0 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

熟練掌握命令行導入導出，配合自動化腳本、壓縮傳輸、字符集顯式指定，能應對 99% 的備份遷移與災難恢復場景。