MySQL 備份與恢復全攻略：邏輯/物理、遷移、導入導出工具大全

MySQL 備份與恢復全攻略：邏輯/物理、遷移、導入導出工具大全

備份是資料庫生存底線。本文系統梳理 MySQL 邏輯備份 (mysqldump)、物理備份 (mysqlhotcopy/直複製)、恢復策略、異構遷移、以及 SELECT ... INTO OUTFILE / LOAD DATA INFILE 導入導出實戰，並對比 SQL Server/Oracle 差異，幫你建立完整備份體系。

一、邏輯備份：mysqldump 核心技法

1. 常用參數速查

2. 實戰場景

# 單庫全備（含建庫語句）
mysqldump -u root -p --databases school --single-transaction --master-data=2 > school_$(date +%F).sql

# 多庫備份
mysqldump -u root -p --databases school test > multi_$(date +%F).sql

# 全例備份
mysqldump -u root -p --all-databases --single-transaction --master-data=2 > all_$(date +%F).sql

# 單表備份
mysqldump -u root -p school book > school_book_$(date +%F).sql

# 條件導出（如僅導 2023 年訂單）
mysqldump -u root -p --where="order_date >= '2023-01-01'" shop orders > orders_2023.sql

3. 輸出檔案結構解讀

• 檔頭：版本、主機、資料庫、Server version
• SET 語句：保存/恢復 character_set、time_zone、sql_mode 等會話變數，保證恢復環境一致
• 版本註釋 /*!40101 ... */：僅 MySQL 4.01.01+ 執行，提升可移植性
• 每表：DROP TABLE IF EXISTS → CREATE TABLE → LOCK TABLES → DISABLE KEYS → INSERT → ENABLE KEYS → UNLOCK TABLES

二、物理備份：極速但有限制

1. 直複製資料目錄

• 適用：MyISAM、停機或 FLUSH TABLES WITH READ LOCK
• 不可用：InnoDB（需 innodb_fast_shutdown=0 且同版本恢復）
• 步驟：FLUSH TABLES WITH READ LOCK → cp -r /var/lib/mysql /backup/ → UNLOCK TABLES

2. mysqlhotcopy（僅 MyISAM/Archive）

# 语法
mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory

# 示例
mysqlhotcopy -u root -p test /usr/backup

優點：極快（檔案級複製）、自動鎖表/flush。缺點：僅本機、非跨平台、不支援 InnoDB。

三、還原策略

四、資料庫遷移

1. 同版本遷移（推薦管道流）

# 單庫
mysqldump -h src -u root -p dbname | mysql -h dst -u root -p

# 全例
mysqldump -h src -u root -p --all-databases | mysql -h dst -u root -p

管道避免落地大檔案，網路帶寬許可下最快。

2. 跨版本遷移

• 舊→新：mysqldump 導出 → 新版 mysql 導入（InnoDB 必須此法）
• 新→舊：極不推薦；若必須，需 --compatible=mysql40 等相容參數，並人工檢查語法差異
• 字符集坑：4.x latin1 → 5.x utf8，導出後 sed -i 's/latin1/utf8/g' dump.sql 或導入時指定 --default-character-set=utf8

3. 異構遷移

• MySQL ↔ SQL Server：MySQL Migration Toolkit / SSMA / MyODBC + 導入導出嚮導
• MySQL ↔ Oracle：SQL Developer / ora2mysql / CSV 中轉
• 關鍵差異：資料型別映射（如 MySQL DATETIME vs Oracle DATE）、標識符引用符（反引號 vs 雙引號）、自增 vs Sequence

五、導入導出文本文件（CSV/TSV/HTML/XML）

1. SELECT ... INTO OUTFILE（服務端寫檔）

SELECT * FROM test.person
INTO OUTFILE '/tmp/person.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\"'
LINES TERMINATED BY '\r\n';

需 FILE 權限，檔案生成於 Server 端。

2. mysqldump -T（生成 .sql + .txt）

mysqldump -T /tmp -u root -p test person
-- 產生 person.sql (結構) + person.txt (數據，預設 tab 分隔)

3. mysql -e / --html / --xml（客戶端導出，適合報表）

mysql -u root -p --execute="SELECT * FROM test.person" test > person.txt
mysql -u root -p --html --execute="SELECT * FROM test.person" test > person.html
mysql -u root -p --xml  --execute="SELECT * FROM test.person" test > person.xml

4. LOAD DATA INFILE（高速導入）

LOAD DATA INFILE '/tmp/person.csv'
INTO TABLE test.person
FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;  -- 跳過標題行

• LOCAL 關鍵字：客戶端讀檔，無需 Server FILE 權限
• REPLACE / IGNORE：控制主鍵衝突行為
• 可指定欄位列表：INTO TABLE t (col1, col3) 實現欄位映射，異構導入利器

5. mysqlimport（命令列封裝 LOAD DATA）

mysqlimport -u root -p --fields-terminated-by=',' --ignore-lines=1 test /tmp/person.csv

檔名即表名，不支援欄位映射，適合同結構批量導入。

六、工具選型決策樹

資料量 < 10GB 且需跨版本/異構 → mysqldump
資料量 > 50GB、純 MyISAM、同版本 → mysqlhotcopy / 直複製
資料量 > 100GB、InnoDB 為主、需熱備 → Percona XtraBackup (物理增量、流式、壓縮)
雲上托管 → 云廠商快照/備份服務 (RDS 自動備份、PITR)

七、常見坑點提醒

1. 未加 --databases：僅導表無 CREATE DATABASE，刪庫後無法直接恢復。
2. 忽略 --single-transaction：InnoDB 熱備不加會鎖表或數據不一致。
3. 版本不匹配還原：5.7 備份還原到 5.6 易報錯，反向相對安全。
4. 字符集未顯式指定：導出導入全鏈路加 --default-character-set=utf8mb4。
5. 備份未驗證：定期演練恢復，檢查 CHECKSUM TABLE、行數、關鍵業務查詢。

建立「備份→驗證→異地存儲→定期演練」閉環，才是資料安全的真正保障。