萬盛學電腦網

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

MySQL 數據庫導入導出三大方式:SQL 腳本、命令行重定向、物理文件複製全解析

MySQL 數據遷移、備份恢復、環境克隆常用三種導入導出方式,各有適用場景與優缺點。本文對比邏輯導出、命令行重定向、物理文件複製的操作步驟、版本兼容性、權限要求與避坑要點。

方式一:SQL 腳本文件(邏輯備份,版本無關、跨平台)

1. 導出

# 導出整庫結構+數據
mysqldump -u root -p db_name > /backup/db_name_$(date +%F).sql

# 導出單表
mysqldump -u root -p db_name table_name > /backup/table_name.sql

# 僅導出結構
mysqldump -u root -p -d db_name > schema.sql

2. 導入

# 方法 A:命令行重定向(推薦大文件)
mysql -u root -p target_db < /backup/db_name.sql

# 方法 B:MySQL 客戶端 source 命令
mysql -u root -p
USE target_db;
SOURCE /backup/db_name.sql;

方式二:命令行直接查詢導出(輕量、無中間文件)

# 導出查詢結果到文件(Tab 分隔,含表頭)
mysql -h 192.168.1.100 -u user -p db_name -e "SELECT * FROM users WHERE status=1" > users.txt

# 導出 CSV 格式(需手動處理分隔符)
mysql -u root -p db_name -B -e "SELECT * FROM users" | sed 's/\t/,/g' > users.csv

參數說明:-B/--batch 禁用交互格式,輸出 Tab 分隔;-e 執行 SQL 字符串。

方式三:物理文件複製(極速、版本強綁定、引擎限制)

適用條件:

  • 同版本、同操作系統、同編譯參數的 MySQL 實例
  • 僅限 MyISAM 引擎表(InnoDB 需共享表空間 ibdata1)
  • 源實例需停止服務或加全局讀鎖 FLUSH TABLES WITH READ LOCK

操作步驟

# 1. 源端打包
tar -zcvf mydb.tar.gz /var/lib/mysql/mydb

# 2. 目標端創建同名空庫
mysql -e "CREATE DATABASE mydb;"

# 3. 目標端解壓並複製
cd /tmp
tar -zxvf mydb.tar.gz
cp -r mydb/* /var/lib/mysql/mydb/

# 4. 修正權限(關鍵!)
chown -R mysql:mysql /var/lib/mysql/mydb
chmod 660 /var/lib/mysql/mydb/*

# 5. 啟動/重啟 MySQL
service mysqld restart

InnoDB 物理複製特殊處理(不推薦常規使用)

若必須物理遷移 InnoDB:

  1. 源庫 SET GLOBAL innodb_fast_shutdown=0 緩慢關閉,確保數據刷盤
  2. 拷貝 整個 datadir(含 ibdata1、ib_logfile*、.ibd、.frm)
  3. 目標端 innodb_data_file_path、innodb_log_file_size、innodb_page_size 配置完全一致
  4. 啟動目標實例,InnoDB 執行崩潰恢復

風險:版本不匹配、頁大小不同、表空間 ID 衝突均會導致啟動失敗。

三種方式對比選型表

維度mysqldump/SQL腳本命令行查詢導出物理文件複製
速度中(受SQL解析限制)快(無解析)極快(文件級複製)
跨版本✅ 支持✅ 支持❌ 強綁定版本
跨引擎✅ 支持✅ 支持❌ 僅MyISAM安全
文件大小較大(含SQL關鍵字)較小(純數據)原始數據大小
權限要求SELECT, LOCK TABLES等SELECT, FILE(導出)文件系統讀寫權限
適用場景常規備份、遷移、審核大表導出、異構導入、ETL同版本克隆、緊急恢復、MyISAM表

最佳實踐建議

  1. 常規備份:mysqldump --single-transaction --master-data=2 + 定時任務 + 異地存儲
  2. 大表遷移:SELECT ... INTO OUTFILE + LOAD DATA INFILE 或 pt-archiver 分批遷移
  3. 整庫克隆:XtraBackup 物理熱備份(支持 InnoDB,不鎖表,恢復快)
  4. 版本升級:邏輯導出導入(mysqldump → 新版本導入)+ mysql_upgrade
  5. 驗證機制:導入後必執行 CHECK TABLE、核對行數、關鍵業務查詢驗證
copyright © 萬盛學電腦網 all rights reserved