MySQL 資料庫備份與恢復深度解析

備份方法對比

核心備份原則

1. 定期備份：制定排程並嚴格執行。
2. 啟用二進制日誌：作為增量備份，崩潰後可重放至故障點。
3. 命名規範：建議 資料庫名_YYYY-MM-DD.sql，利於識別。
4. 壓縮與過期：備份檔通常龐大，建議 gzip 壓縮並設定保留天數。
5. 異地備份：備份檔放置於不同檔案系統，甚至異地伺服器，防單點故障。

mysqldump 進階用法

常用參數組合

• --opt：綜合優化（含 --quick、--add-drop-table、--lock-tables 等），速度最快但會鎖全庫。
• --quick：逐行輸出，減少記憶體佔用，適合大表。
• --flush-logs --lock-tables：建立檢查點，新二進制日誌僅含備份後變更。
• --delayed：產生 INSERT DELAYED，降低還原時對線上查詢影響。
• --compress：跨網路傳輸時壓縮協議資料。

跨主機直接遷移

# 目標主機先建庫
mysqladmin -h 目標主機 create 目標資料庫
# 管道串流遷移
mysqldump --opt 源資料庫 | mysql --compress -h 目標主機 目標資料庫

實體備份操作細節

1. 停止 MySQL 服務（或 FLUSH TABLES WITH READ LOCK）。
2. 複製資料目錄（如 cp -r /var/lib/mysql/資料庫 /備份路徑）。
3. 重新啟動服務或釋放鎖定。
4. 遷移時確保目標主機同版本、同儲存引擎（MyISAM），否則需改用邏輯備份。

恢復策略

整庫恢復

1. 若為 mysql 系統庫，啟動參數加 --skip-grant-tables。
2. 保留現有資料目錄備份供事後分析。
3. 還原最新完整備份（邏輯備份用 mysql < 備份檔，實體備份停服務後覆蓋目錄）。
4. 按時間順序重放所有相關二進制日誌：
   ls -t -r update.[0-9]* | xargs cat | mysql --one-database 資料庫
5. 若日誌中含 DROP DATABASE 等誤操作，重放前務必手動移除該語句。

單表恢復

較複雜，建議：

• 另架臨時實例恢復整庫，再複製目標表檔案（實體備份）或匯出目標表 SQL（邏輯備份）至原庫。
• 或使用 mysql_find_rows 從二進制日誌抽取該表相關查詢重放。

複製技術簡介

MySQL 3.23.15+ 提供 Replication（主從同步），可實現近即時備援，屬於高可用架構範疇，非傳統備份替代方案。