萬盛學電腦網

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

MySQL 命令列匯出匯入大型資料庫實戰指南

為什麼要用命令列?

圖形工具(Navicat、Workbench)處理超大資料庫(>1GB)易記憶體溢出、超時、進度不可控。mysqldump + mysql 原生命令列穩定、可腳本化、支援串流壓縮。

mysqldump 核心參數速查

參數作用建議場景
--single-transactionInnoDB 熱備份,不鎖表線上備份必加
--quick / -q串流輸出,不載入記憶體大表必加
--extended-insert多行合併 INSERT,提升匯入速度預設開啟
--add-drop-table建表前 DROP IF EXISTS重複匯入安全
--set-gtid-purged=OFF避免 GTID 污染異構遷移
--no-data / -d僅匯出結構結構遷移
--no-create-info / -t僅匯出資料資料同步
--where條件篩選匯出分批匯出
--compress協議層壓縮傳輸跨網段備份

匯出實戰範例

完整備份(結構+資料)

mysqldump -h localhost -u root -p \
  --single-transaction --quick --extended-insert \
  --set-gtid-purged=OFF \
  mydb > mydb_$(date +%F).sql

僅結構

mysqldump -u root -p -d mydb > mydb_struct.sql

指定表、帶壓縮、遠程直傳

# 壓縮存本地
mysqldump -u root -p mydb tbl1 tbl2 | gzip > mydb_tbls.sql.gz

# 遠程直傳另一台主機(免落地)
mysqldump -h src_host -u root -p mydb | gzip | ssh dst_host "gunzip | mysql -u root -p mydb"

匯入實戰範例

方法一:mysql 命令列(推薦大檔案)

# 建庫並指定字符集
mysql -u root -p -e "CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# 匯入(自動處理大事務、關閉自動提交優化)
mysql -u root -p --default-character-set=utf8mb4 mydb < mydb.sql

方法二:source 命令(需進入 mysql 客戶端)

mysql -u root -p
USE mydb;
SET NAMES utf8mb4;
SOURCE /path/to/mydb.sql;

大資料庫優化技巧

  • 關閉二進制日誌:匯入期間 SET sql_log_bin=0;(需 SUPER 權限)
  • 調大參數:max_allowed_packet=1G、innodb_buffer_pool_size 臨時調大
  • 禁用外鍵/唯一檢查:
    SET FOREIGN_KEY_CHECKS=0;
    SET UNIQUE_CHECKS=0;
    SOURCE data.sql;
    SET FOREIGN_KEY_CHECKS=1;
    SET UNIQUE_CHECKS=1;
  • 分檔並行匯入:用 --tab 導出 TSV + LOAD DATA 並行載入

常見坑點

  • 字符集不一致導致亂碼:匯出/匯入均指定 --default-character-set=utf8mb4
  • 版本差異導致語法錯誤:mysqldump --compatibility=mysql57
  • GTID 衝突:跨實例遷移加 --set-gtid-purged=OFF
copyright © 萬盛學電腦網 all rights reserved