MySQL 資料庫匯入匯出詳解：SQL 腳本法與直接複製資料目錄法完整操作指南

方法一：SQL 腳本匯出匯入 (標準、跨版本相容)

2.1 匯出 SQL 腳本

phpMyAdmin 圖形介面

• 選擇「結構」+「資料」
• 勿勾選「Drop DATABASE」「Drop TABLE」
• 勾選「另存為檔案」，大資料庫勾選「gzipped」
• 下載 .sql 或 .sql.gz 檔案

mysqldump 命令列 (推薦自動化)

# 基本格式
mysqldump -u username -p database_name > database_name.sql

# 範例
mysqldump -u root -p abc > abc.sql
# 提示輸入密碼

2.2 建立空資料庫

控制面板/管理介面建立新資料庫 (如 abc)，指派全權限用戶 (如 abc_f)。

2.3 匯入執行 SQL

phpMyAdmin 匯入

• 進入資料庫管理 → SQL 分頁
• 瀏覽選擇 .sql 檔 → 執行
• 大檔案先 gzip 壓縮 (文字檔壓縮率 1:5+)

命令列匯入

# 基本格式
mysql -u username -p database_name < database_name.sql

# 範例
mysql -u abc_f -p abc < abc.sql

進入 mysql 後 source 匯入

mysql -u root -p
mysql> CREATE DATABASE abc;
mysql> USE abc;
mysql> SOURCE d:/backup/abc.sql;

方法二：直接複製資料目錄 (極快、但有限制)

適用場景

• 資料庫極大 (百 GB+)，mysqldump 太慢
• 來源/目標完全相同：MySQL 版本、OS、架構、儲存引擎
• 可接受短暫停機

操作步驟

1. 打包來源

   tar czf mydb.tar.gz /var/lib/mysql/mydb

2. 目標建立同名空資料庫 (或直接複製)
3. 解壓至臨時目錄

   cd /tmp
   tar xzf mydb.tar.gz

4. 複製檔案到資料目錄

   # Linux (常見)
   cp -r /tmp/mydb/* /var/lib/mysql/mydb/
   
   # FreeBSD
   cp -r /tmp/mydb/* /var/db/mysql/mydb/

5. 修正權限 (關鍵！)

   chown -R mysql:mysql /var/lib/mysql/mydb/
   chmod -R 660 /var/lib/mysql/mydb/*

6. 重啟 MySQL 或 FLUSH TABLES

風險與限制

mysqldump 進階匯出範例

單庫匯出並壓縮

/usr/local/mysql/bin/mysqldump -u root -p123456 zhumaohai | gzip > /home/backup/zhumaohai.sql.gz

全庫匯出

/usr/local/mysql/bin/mysqldump -u root -p123456 --all-databases | gzip > /home/backup/all.sql.gz

匯入對應指令

# 單庫
gunzip < /home/backup/zhumaohai.sql.gz | /usr/local/mysql/bin/mysql -u root -p123456 zhumaohai

# 全庫
gunzip < /home/backup/all.sql.gz | /usr/local/mysql/bin/mysql -u root -p123456

最佳實務建議

1. 首選 mysqldump：相容性最好、可跨版本遷移
2. 大庫用壓縮管道：mysqldump ... | gzip、gunzip | mysql，不落地中間檔
3. 生產環境加參數：--single-transaction --quick --routines --triggers --events --master-data=2
4. 定期驗證備份可還原：未測試的備份 = 無備份
5. 直接複製僅限緊急克隆：同版本測試環境、極大庫離線遷移