MySQL 資料庫匯入匯出完整指南：虛擬主機與服務器雙環境實戰

MySQL 資料庫匯入匯出完整指南：虛擬主機與服務器雙環境實戰

資料庫備份與遷移是運維的核心技能。本文針對虛擬主機 (共享環境) 與服務器/VPS (獨享環境) 兩大典型場景，提供從圖形工具到命令列的完整匯入匯出方案，涵蓋 mysqldump、source、PHPMyAdmin、程式內建備份等多種手段，並針對大數據量、權限不足、鎖表報錯等常見問題給出解決方案。

一、虛擬主機環境：受限環境下的生存技巧

方案 1：PHPMyAdmin Web 介面 (最通用)

• 操作：登入 cPanel/Plesk/自建 PHPMyAdmin → 選擇資料庫 → 「匯出」/「匯入」標籤
• 優點：無需 Shell 存取、視覺化操作、支援多種格式 (SQL、CSV、JSON)
• 硬性限制：

# PHP 設定限制 (通常無法修改)
upload_max_filesize = 2M~50M
post_max_size = 8M~100M
max_execution_time = 30~300s
memory_limit = 128M~512M

# 後果：超過限制的大數據庫會導致匯入/匯出超時、記憶體溢位、檔案截斷

• 適用場景：< 50MB 的中小型資料庫、結構調整、測試資料遷移

方案 2：程式內建備份/還原功能 (CMS/框架自帶)

• Discuz!、WordPress、Drupal、Magento 等主流 CMS 均在後台提供「資料庫備份/還原」
• 原理：調用 mysqldump 或 PHP 原生 API 分卷壓縮
• 優勢：自動處理表前綴、過緩存表、分卷避開 PHP 限制
• 注意：僅限同程式版本間遷移，跨版本需核對結構相容性

方案 3：主機商控制面板一鍵備份 (最省力)

• 功能：控制面板「資料庫管理」→「備份」→ 生成 .sql.gz → FTP/面板下載
• 還原：上傳備份檔 → 「還原」按鈕
• 優勢：繞過 PHP 限制、由主機商底層執行、速度較快
• 前提：主機商提供此功能 (多數共享主機標配)

二、服務器/VPS 環境：命令列全掌控

前置條件

• SSH 存取權限 (Linux) 或 遠程桌面 (Windows)
• root 或資料庫專用帳號密碼
• 確認 mysqldump/mysql 命令在 PATH 中 (Linux 通常在 /usr/bin/，Windows 在 安裝目錄/bin/)

核心工具：mysqldump (匯出) + mysql/source (匯入)

場景 1：單庫完整備份 (結構 + 數據)

# 匯出 (備份)
mysqldump -u root -p --databases db1 > /backup/db1_$(date +%F).sql
# 輸入密碼後執行

# 進階：排除表、壓縮、單事務一致性
mysqldump -u root -p \
  --single-transaction \
  --routines --triggers --events \
  --ignore-table=db1.log_table \
  --databases db1 | gzip > /backup/db1_$(date +%F).sql.gz

# 匯入 (還原)
mysql -u root -p < /backup/db1_2024-01-15.sql
# 或進入 mysql 後
SOURCE /backup/db1_2024-01-15.sql;

場景 2：多庫備份

# 指定多個庫
mysqldump -u root -p --databases db1 db2 db3 > multi_db.sql

# 所有庫 (含系統庫 mysql、sys、performance_schema、information_schema)
mysqldump -u root -p --all-databases > all_dbs.sql
# 注意：還原時會覆蓋目標實例的用戶、權限、系統表，跨版本/跨實例慎用

場景 3：僅結構 / 僅數據

# 僅結構 (無數據)
mysqldump -u root -p --no-data --databases db1 > db1_struct.sql

# 僅數據 (無結構，需配合 --no-create-db --no-create-info)
mysqldump -u root -p --no-create-db --no-create-info --databases db1 > db1_data.sql

# 單表結構
mysqldump -u root -p --no-data db1 tbl1 > tbl1_struct.sql

場景 4：條件導出 (WHERE 子句)

mysqldump -u root -p --where="created_at > '2024-01-01'" db1 tbl1 > tbl1_2024.sql
# 僅導出符合條件的行，適合歷史數據歸檔、分表遷移

場景 5：導出 CSV 供數據分析

mysqldump -u root -p -T /tmp/export_dir \
  --fields-terminated-by=',' \
  --fields-enclosed-by='"' \
  --lines-terminated-by='\n' \
  db1 tbl1
# 產生 tbl1.sql (結構) + tbl1.txt (CSV 數據)
# 需求：Server 有 /tmp/export_dir 寫入權限、secure_file_priv 允許該路徑

三、Windows 環境特殊操作

# 1. 進入命令列
Win+R → cmd

# 2. 切換到 MySQL bin 目錄
cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

# 3. 執行命令 (參數同 Linux)
mysqldump -u root -p db1 > D:\backup\db1.sql
mysql -u root -p db1 < D:\backup\db1.sql

# 4. 解決 "when using LOCK TABLES" 報錯
mysqldump -u root -p --skip-lock-tables db1 > db1.sql
# 原因：備份帳號無 LOCK TABLES 權限，跳過鎖表 (InnoDB 配合 --single-transaction 不需鎖表)

四、常見問題與解決方案

五、大數據量優化策略 (>10GB)

1. 並行導出/導入：mydumper / myloader (多線程、分塊、壓縮)
2. 分表導出：按主鍵範圍 --where="id BETWEEN 1 AND 1000000" 分批次
3. 壓縮傳輸：mysqldump ... | gzip | ssh target "gunzip | mysql ..." (避免落地磁碟)
4. 物理備份：Percona XtraBackup (熱備份、不鎖表、增量、恢復快)
5. 檔案系統快照：LVM/ZFS/EBS 快照 + 應用層一致性鎖 (FLUSH TABLES WITH READ LOCK)

六、驗證與校驗清單

# 1. 行數校驗
SELECT COUNT(*) FROM src_db.tbl;
SELECT COUNT(*) FROM dst_db.tbl;

# 2. 校驗和對比 (需相同排序)
mysqldump --no-create-info --order-by-primary db1 tbl1 | md5sum
# 目標庫同理

# 3. 關鍵業務抽樣
SELECT * FROM dst_db.tbl WHERE id IN (1,100,1000,10000);

# 4. 應用層煙霧測試
# 啟動應用指向新庫，跑核心流程 (登入、查詢、寫入、報表)

選擇正確的匯入匯出工具與參數，是資料安全遷移的前提。**虛擬主機借力平台工具，服務器掌握命令列核心，大數據上物理備份/並行工具**。建立標準化備份腳本、驗證流程、定期演練，才能在災難發生時從容應對。