MySQL 數據遷移、備份恢復、環境克隆常用三種導入導出方式,各有適用場景與優缺點。本文對比邏輯導出、命令行重定向、物理文件複製的操作步驟、版本兼容性、權限要求與避坑要點。
# 導出整庫結構+數據
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
# 方法 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 字符串。
適用條件:
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:
SET GLOBAL innodb_fast_shutdown=0 緩慢關閉,確保數據刷盤ibdata1、ib_logfile*、.ibd、.frm)innodb_data_file_path、innodb_log_file_size、innodb_page_size 配置完全一致風險:版本不匹配、頁大小不同、表空間 ID 衝突均會導致啟動失敗。
| 維度 | mysqldump/SQL腳本 | 命令行查詢導出 | 物理文件複製 |
|---|---|---|---|
| 速度 | 中(受SQL解析限制) | 快(無解析) | 極快(文件級複製) |
| 跨版本 | ✅ 支持 | ✅ 支持 | ❌ 強綁定版本 |
| 跨引擎 | ✅ 支持 | ✅ 支持 | ❌ 僅MyISAM安全 |
| 文件大小 | 較大(含SQL關鍵字) | 較小(純數據) | 原始數據大小 |
| 權限要求 | SELECT, LOCK TABLES等 | SELECT, FILE(導出) | 文件系統讀寫權限 |
| 適用場景 | 常規備份、遷移、審核 | 大表導出、異構導入、ETL | 同版本克隆、緊急恢復、MyISAM表 |
mysqldump --single-transaction --master-data=2 + 定時任務 + 異地存儲SELECT ... INTO OUTFILE + LOAD DATA INFILE 或 pt-archiver 分批遷移mysql_upgradeCHECK TABLE、核對行數、關鍵業務查詢驗證