萬盛學電腦網

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

MySQL 大數據庫文件導入實戰:命令行、參數調優、分卷策略

導入方式對比

命令行導入標準流程

# 1. 進入容器/服務器
ssh user@db-server

# 2. 可選:創建目標庫
mysql -uroot -p -e "CREATE DATABASE IF NOT EXISTS target_db CHARSET utf8mb4 COLLATE utf8mb4_general_ci;"

# 3. 導入(推薦參數)
mysql -uroot -p \
    --default-character-set=utf8mb4 \
    --max_allowed_packet=1G \
    --net_buffer_length=1M \
    --force \
    --show-warnings \
    target_db < /path/to/dump.sql

# 4. 監控進度(另開終端)
watch -n 5 "mysql -uroot -p -e 'SHOW PROCESSLIST;' | grep -i import"

關鍵參數解析

  • --max_allowed_packet=1G:防止「Packet too large」錯誤(大 BLOB/長 INSERT)
  • --net_buffer_length=1M:增大網絡緩衝,減少往返
  • --force:遇錯繼續(如重複鍵),避免中斷
  • --show-warnings:顯示警告信息
  • --default-character-set:強制客戶端字符集,防止亂碼

大文件分卷導入

拆分 SQL 文件

# 按行數拆分(每 10 萬行一個文件)
split -l 100000 huge_dump.sql chunk_

# 或按表拆分(需工具支持)
# mysqldump --tab=/tmp --tables db t1 t2 ...  # 生成 .sql + .txt 分表文件

並行導入腳本

#!/bin/bash
# parallel_import.sh
MAX_JOBS=4
for f in chunk_*; do
    # 限制併發數
    while [[ $(jobs -r -p | wc -l) -ge $MAX_JOBS ]]; do sleep 2; done
    
    mysql -uroot -p --force target_db < "$f" &
    echo "Started import $f (PID $!)"
done
wait
echo "All imports completed."

phpMyAdmin 導入大文件調優(不得已時)

; php.ini
upload_max_filesize = 1G
post_max_size = 1G
memory_limit = 2G
max_execution_time = 0
max_input_time = 0

; nginx.conf
client_max_body_size 1G;
proxy_read_timeout 3600;
proxy_send_timeout 3600;

修改後重載 PHP-FPM + Nginx。仍不推薦超 200MB 走 Web 導入。

導入前檢查清單

  1. 版本兼容:mysqldump --version ≤ 目標 MySQL 版本
  2. 字符集一致:源庫/目標庫/導入連接均為 utf8mb4
  3. SQL_MODE 兼容:SET SESSION sql_mode=''; 寬鬆模式導入
  4. 外鍵/唯一檢查暫停(加速):
    SET FOREIGN_KEY_CHECKS=0;
    SET UNIQUE_CHECKS=0;
    SET AUTOCOMMIT=0;
    -- 導入
    SET FOREIGN_KEY_CHECKS=1;
    SET UNIQUE_CHECKS=1;
    COMMIT;
  5. 磁盤空間 ≥ 3× 文件大小(臨時文件、binlog、索引構建)

導入後驗證

# 1. 表數量/行數對比
mysql -uroot -p -e "
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA='target_db';"

# 2. 關鍵表抽樣
mysql -uroot -p -e "CHECKSUM TABLE target_db.core_table;"

# 3. 應用煙霧測試
curl -f https://app/health || exit 1

常見錯誤速查

方式適用體量優點缺點
phpMyAdmin/Web UI< 50MB圖形化、直觀受 upload_max_filesize/memory_limit/post_max_size 限制、超時風險
命令行 mysql < file.sql任意(GB 級)無大小限制、速度快、可斷點續傳需服務器登錄權限
MySQL Shell / myloader> 10GB並行導入、進度條、錯誤恢復需額外安裝學習
物理備份恢復> 500GB秒級恢復、無解析開銷版本/配置強綁定、需停庫
錯誤原因解決
ERROR 1153: Got a packet bigger than 'max_allowed_packet'單條 INSERT 過大增大 max_allowed_packet(客戶端+服務端)
ERROR 1062: Duplicate entry主鍵/唯一鍵衝突--force 或 INSERT IGNORE / REPLACE
ERROR 1213: Deadlock併發導入死鎖串行導入或調整順序
ERROR 2006: MySQL server has gone away超時/斷連/封包過大增大 wait_timeout/max_allowed_packet、檢查網絡
Unknown collation: 'utf8mb4_0900_ai_ci'8.0 默認排序規則,5.7 不支持導出時 --default-character-set=utf8mb4 --set-gtid-purged=OFF 或目標升級 8.0
copyright © 萬盛學電腦網 all rights reserved