MySQL 備份與恢復全攻略：mysqldump、物理備份、Binlog 恢復、異機遷移

備份策略核心原則

1. 定期自動化：制定排程 (每日全量 + 小時增量)，嚴格執行
2. 啟用 Binlog：log-bin=mysql-bin，恢復時重放增量達到 RPO≈0
3. 命名規範：dbname_YYYYMMDD_HHMMSS.sql(.gz)，含資料庫/時間/類型
4. 異地存儲：備份文件跨磁盤、跨機房、雲存儲，防單點災難
5. 壓縮與保留：gzip 壓縮 70%+ 空間；設置過期清理策略
6. 定期演練：每季度至少一次全量恢複演練，驗證 RTO/RPO

方式一：邏輯備份 — mysqldump (推薦常規業務)

核心參數解析

常用命令範例

# 全量備份 (生產推薦)
mysqldump --single-transaction --master-data=2 --flush-logs \
  --routines --triggers --events --hex-blob --quick \
  -u backup -p dbname | gzip > dbname_$(date +%F_%H%M).sql.gz

# 單表備份
mysqldump -u backup -p dbname table1 table2 > tables.sql

# 異機直接同步 (無中間文件)
mysqldump --single-transaction --master-data=2 dbname \
  | mysql --compress -h target_host -u root -p dbname

# 結構與數據分離
mysqldump -d dbname > schema.sql       # 僅結構
mysqldump -t dbname > data.sql         # 僅數據

方式二：物理備份 — 文件拷貝 (適合大庫、極速恢復)

前提條件

• 表引擎為 **MyISAM** 或 **InnoDB (需同版本、同架構、同頁面大小)**
• **停庫或全局讀鎖**：FLUSH TABLES WITH READ LOCK → 拷貝 → UNLOCK TABLES
• 或使用 **Percona XtraBackup** (InnoDB 熱備、不鎖表、流式、增量)

操作步驟

# 停服務法 (最簡單、最安全)
systemctl stop mysqld
tar czf /backup/mysql_$(date +%F).tar.gz /var/lib/mysql
systemctl start mysqld

# XtraBackup 熱備 (生產首選)
xtrabackup --backup --target-dir=/backup/full_$(date +%F) --user=backup --password=xxx
xtrabackup --prepare --target-dir=/backup/full_$(date +%F)  # 恢復前必須 prepare

恢復實戰：PITR (Point-In-Time Recovery)

場景：誤執行 DROP TABLE / DELETE，需恢復到誤操作前 1 分鐘

1. 停止寫入：應用層切流量、或 FLUSH TABLES WITH READ LOCK
2. 找到最近全量備份：如 dbname_20240115_0200.sql.gz
3. 恢復全量：zcat dbname_20240115_0200.sql.gz | mysql -u root -p dbname
4. 定位 Binlog 位點：備份文件頭部註釋 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000123', MASTER_LOG_POS=456789;
5. 提取增量：mysqlbinlog --start-position=456789 --stop-datetime="2024-01-15 10:30:00" mysql-bin.000123 | mysql -u root -p dbname
6. 驗證數據：關鍵表行數、業務抽樣、Checksum 比對
7. 解鎖/恢復服務

單表恢復技巧

• 從全量 SQL 中 grep -A 10000 'CREATE TABLE `target`' 提取結構+數據
• 或臨時實例恢復全庫 → SELECT * INTO OUTFILE → 導入主庫
• 利用 mysqlbinlog --database=dbname --table=target 過濾 Binlog (5.6+)

遷移異機：最小停機方案

1. 源庫全量備份 + 恢復到目標庫
2. 目標庫啟用 Replication (GTID/文件位點) 指向源庫
3. 同步追上 (Seconds_Behind_Master=0)
4. 應用雙寫驗證 / 切換只讀 → 修改 DNS/連接串 → 切流量
5. 觀察 30 分鐘無錯誤 → 下線源庫

工具生態推薦

• Percona XtraBackup：開源、免費、InnoDB 物理熱備標杆
• myloader/mydumper：多線程邏輯導入導出、大表加速 5-10x
• mysqlpump：MySQL 5.7+ 內置、並行導出、壓縮、對象過濾
• Orchestrator / MHA：自動故障轉移、拓撲管理、備份整合

結語

備份的價值不在「有沒有備份」，而在「能不能在 RTO 內恢復到 RPO」。**邏輯備份通用遷移、物理備份極速恢複、Binlog 實現 PITR、XtraBackup 解決熱備痛點、自動化驗證消除假安全**。建立分級備份體系 (每日全量 + 每小時增量 + 實時 Binlog 異地同步)，並納入 CI/CD 流程定期演練，才是生產級備份的正確姿勢。

1. 上一篇文章： MSSQL 與 MySQL 安全性深度對比：內部控制、網絡暴露與腳本注入三維分析
2. 下一篇文章： MySQL 通信安全硬化：從明文傳輸到 SSL/TLS 加密與 SSH 隧道

MySQL 資料庫相關知識

Windows XP 下 MySQL 5.5.28 圖文安裝配置完整教程 (典型/自定義/配置向導全流程)

MySQL 5.5 on Windows XP 手把手安裝指

Linux 異構網路環境下 MySQL 資料庫備份策略：本地、DVD 與網路備份實戰

針對中小型 Linux 網站（RHEL + PHP + My

JDBC 連線 MySQL 實戰：從建庫到 Java 程式碼完整範例

環境準備 安裝 MySQL，建立資料庫 studentin

MySQL OOM 系列三：擺脫被 OOM Killer 終結的厄運

前言 前兩章分析了 Linux 記憶體過量分配策略與 OOM