MySQL 資料庫備份全攻略：從檔案級複製到 mysqldump 規範操作

資料是企業核心資產，備份策略直接決定災難恢復能力。本文對比「檔案級暴力複製」與「官方 mysqldump 邏輯備份」兩大流派，深度解析適用場景、操作細節與避坑指南，助您建立分級備份體系。

一、捷徑方案：檔案級直接複製（僅限特定場景）

實驗環境

• OS：WinNT 4.0，MySQL 3.22.34，phpMyAdmin 2.1.0
• 來源主機 HostA：含資料庫 TestA
• 目標主機 HostB：已安裝 MySQL，無 TestA

操作步驟

1. 停止兩側 MySQL 服務（關鍵：確保檔案一致性）
2. 複製 HostA data/TestA 目錄至 HostB data/ 下
3. 啟動 HostB MySQL，phpMyAdmin 刷新即可見 TestA
4. 查詢、修改測試正常

結論與嚴重警示

• ✅ 同版本、同架構、同配置、服務停止下可行
• ❌ 生產環境嚴禁：版本不一致、引擎差異、InnoDB 共享表空間、權限表不同步、Binlog 座標錯位等風險極高
• ❌ 無法實現增量備份、時間點恢復（PITR）
• ❌ 非官方支援，升級遷移極易翻車

二、正規軍：mysqldump 邏輯備份（官方推薦）

核心語法

mysqldump [OPTIONS] database [tables...]

不指定表即備份整庫。大庫務必加 --quick 或 --opt（預設開啟），避免全量結果集載入記憶體導致 OOM。

關鍵參數解析

常用組合範例

# 線上 InnoDB 熱備份 + Binlog 座標 + 壓縮
mysqldump -h127.0.0.1 -uroot -p --single-transaction --master-data=2 --routines --triggers --events --hex-blob --compress mydb | gzip > mydb_$(date +%F).sql.gz

# 僅導出結構（不含資料）
mysqldump -d mydb > mydb_structure.sql

# 僅導出指定表
mysqldump mydb table1 table2 > partial.sql

恢復操作

# 全量恢復
mysql -uroot -p mydb < mydb_backup.sql

# 指定時間點恢復（需 Binlog）
mysqlbinlog --start-datetime="2024-01-15 10:00:00" --stop-datetime="2024-01-15 12:00:00" binlog.000001 | mysql -uroot -p mydb

三、文中殘缺的「第三步：建立互為主從」補充說明

原文片段提到雙主互備配置，補充完整流程：

1. 兩側均啟用 log-bin、設定唯一 server-id
2. 互建複寫帳號：GRANT REPLICATION SLAVE ON *.* TO 'repl'@'對方IP' IDENTIFIED BY 'pwd';
3. 雙向 CHANGE MASTER TO 指向對方 Binlog 位置
4. 警示：雙主寫入極易衝突（自增 ID、唯一鍵），生產環境建議「一寫一備」或引入 Galera Cluster/PXC 實現真正多主同步。

四、企業級備份策略建議

總結

檔案級複製僅供測試環境快速克隆；生產環境必須採用 mysqldump（邏輯）或 XtraBackup（物理）建立規範、可驗證、分級的備份體系。記住：未經恢復演練的備份，等於沒有備份。