萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 命令列匯入大型 SQL 檔案完整指南

為何不用 phpMyAdmin?

  • 上傳檔案大小限制(upload_max_filesize、post_max_size)
  • HTTP 超時(max_execution_time)導致中斷
  • 記憶體耗盡、介面無回應
  • 無法顯示詳細進度與錯誤

標準匯入流程

1. 開啟終端機

# Windows
Win+R → cmd
# Linux/macOS
Terminal

2. 進入 MySQL bin 目錄(或已加入 PATH)

# Windows 範例
cd /d D:\mysql\bin
# Linux 通常已在 PATH
# which mysql

3. 登入 MySQL

mysql -u root -p
# 輸入密碼(不顯示)

4. 選擇目標資料庫

USE your_database_name;

5. 設定字符集並匯入

SET NAMES utf8mb4;
SOURCE /full/path/to/backup.sql;
-- 或
\. /full/path/to/backup.sql

進階技巧與大檔案優化

單行命令非互動模式(適合腳本/自動化)

mysql -u root -p'password' --default-character-set=utf8mb4 your_db < backup.sql

注意:密碼直接寫在命令列有安全風險,生產環境請使用 --defaults-extra-file。

使用 pv 顯示進度(Linux/macOS)

pv backup.sql | mysql -u root -p your_db

分割大檔案匯入

# 按行分割,每 10 萬行一檔
split -l 100000 backup.sql chunk_
# 依序匯入
for f in chunk_*; do mysql -u root -p your_db < "$f"; done

關閉索引加速匯入(MyISAM/InnoDB 次要索引)

-- 匯入前
ALTER TABLE large_table DISABLE KEYS;
-- 匯入後
ALTER TABLE large_table ENABLE KEYS;

InnoDB 5.7+ 支援 ALTER TABLE ... IMPORT TABLESPACE 更高效。

關閉 Binlog 減少 IO(僅臨時)

SET SESSION sql_log_bin = 0;
SOURCE backup.sql;
SET SESSION sql_log_bin = 1;

常見錯誤與解決

錯誤原因解決
ERROR 2006 (HY000): MySQL server has gone away 封包過大或超時 調大 max_allowed_packet=1G,增加 wait_timeout
ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' 單條 SQL 過長 同上,或拆分大 INSERT
ERROR 1062 (23000): Duplicate entry 主鍵/唯一鍵衝突 使用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE
字符集亂碼 匯入/匯出字符集不一致 統一指定 --default-character-set=utf8mb4

匯出對應指令

# 完整備份(含結構+資料)
mysqldump -u root -p --single-transaction --routines --triggers --events --default-character-set=utf8mb4 db_name > backup.sql

# 僅結構
mysqldump -u root -p --no-data --default-character-set=utf8mb4 db_name > structure.sql

# 僅資料
mysqldump -u root -p --no-create-info --default-character-set=utf8mb4 db_name > data.sql

# 壓縮備份
mysqldump -u root -p db_name | gzip > backup_$(date +%F).sql.gz

總結

命令列 SOURCE 或重導向 < 是匯入大型 SQL 最可靠、效能最佳的方式。關鍵在於:明確指定字符集、調大 max_allowed_packet、善用事務與索引控制、必要時分割檔案。建立標準化備份/還原 SOP,定期演練,才能在災難發生時從容應對。

copyright © 萬盛學電腦網 all rights reserved