MySQL 匯入大型 SQL 腳本報錯 2006 解決方案：max_allowed_packet 與超時調優

錯誤現象

ERROR 2006 (HY000): MySQL server has gone away
-- 或 --
Error Code: 2006 - MySQL server has gone away

典型場景：匯入 10MB+ 的大型 SQL 檔案（含大量 INSERT 或巨大 BLOB），過程中斷連線。

根因分析

• 封包過大：SQL 語句或單行資料超過 max_allowed_packet 限制（預設 4MB/16MB/64MB 視版本）
• 連線逾時：匯入耗時過長，超過 wait_timeout/interactive_timeout（預設 28800 秒）
• 伺服器重啟/崩潰：錯誤日誌檢查 OOM Killer、磁碟滿、核心恐慌等

解決方案：調整配置參數

1. 修改 my.cnf / my.ini

[mysqld]
# 最大允許封包：建議 256MB-1GB，視最大 SQL 行大小而定
max_allowed_packet = 512M

# 連線等待超時：延長至 24 小時以上，防止長時間匯入中斷
wait_timeout = 86400
interactive_timeout = 86400

# 針對大量寫入的額外優化
innodb_buffer_pool_size = 4G  # 視記憶體調整，建議 50-70% 實體記憶體
innodb_log_file_size = 512M   # 加大 Redo Log，減少 checkpoint 壓力
innodb_flush_log_at_trx_commit = 2  # 匯入期間暫設 2，完成後改回 1
sync_binlog = 0               # 匯入期間暫關閉 Binlog 同步，完成後改回 1

2. 重啟 MySQL 生效

systemctl restart mysqld
# 或
service mysql restart

3. 驗證參數生效

SHOW VARIABLES LIKE 'max_allowed_packet';
SHOW VARIABLES LIKE 'wait_timeout';

客戶端同步調整（關鍵）

mysqldump、mysql 客戶端也有自己的 max_allowed_packet，必須同步調大：

# 導入時指定
mysql --max_allowed_packet=512M -u root -p db_name < large_backup.sql

# 或在客戶端配置檔
[mysql]
max_allowed_packet = 512M

大檔案匯入優化完整清單

1. 分割檔案：用 split -l 10000 將大 SQL 拆為多個小檔，逐個匯入
2. 關閉檢查：

   SET autocommit=0;
   SET unique_checks=0;
   SET foreign_key_checks=0;
   SOURCE large_backup.sql;
   SET foreign_key_checks=1;
   SET unique_checks=1;
   COMMIT;

3. 使用專用工具：
   • mydumper/myloader：多線程並行，速度快 5-10 倍
   • pt-fifo-split：管道分割導入
4. 調大 InnoDB 相關參數（匯入期間臨時調整，完成後還原）
5. 避免觸發器/外鍵/事件：匯入前暫時移除，匯入後重建

排查清單：調整後仍失敗怎麼辦？

1. 檢查錯誤日誌 /var/log/mysql/error.log 確認具體原因
2. 監控 dmesg | grep -i kill 排除 OOM Killer
3. 確認磁碟空間 df -h 與 inode df -i
4. 檢查 SQL 檔案編碼、語法錯誤、超長行
5. 嘗試 mysqlbinlog 重放 Binlog 替代 SQL 匯入

預防建議

• 備份腳本加入 --max_allowed_packet=512M 參數
• 監控系統設定 max_allowed_packet 變更告警
• 定期演練大庫還原，驗證參數足夠
• 生產環境預設調大至 256MB 以上