MySQL 資料庫備份基礎知識全覽

備份的重要性

系統崩潰、硬體故障、人為誤操作（甚至管理員直接用編輯器修改表檔）皆可能導致資料遺失。定期備份是最後防線。

兩大備份路徑比較

備份最佳實踐原則

1. 定期排程：制定計畫並嚴格執行。
2. 啟用二進制日誌：作為增量備份，支援點對點恢復。
3. 命名規範：資料庫名_YYYY-MM-DD.sql(.gz)，一眼可識別。
4. 壓縮與過期：gzip 壓縮、設定保留天數避免塞滿磁碟。
5. 異地備份：備份檔置於不同檔案系統/異地伺服器，防單點災難。
6. 備份驗證：定期演練還原，確保備份可用。

mysqldump 進階技巧

常用參數組合

• --opt：綜合優化（速度最快，但鎖全庫）。
• --quick：逐行輸出，低記憶體佔用。
• --flush-logs --lock-tables：建立檢查點，新 binlog 僅含備份後變更。
• --add-drop-table：還原前自動刪除同名表，避免衝突。
• --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），否則改用邏輯備份。

Replication（複製）簡介

MySQL 3.23.15+ 內建，實現主從近即時同步，屬高可用架構，非傳統備份替代品。

恢復策略

整庫恢復

1. 系統庫 mysql 需 --skip-grant-tables 啟動。
2. 保留現有目錄供事後分析。
3. 還原最新完整備份。
4. 按時間序重放所有相關 binlog：
   ls -t -r update.[0-9]* | xargs cat | mysql --one-database 資料庫
5. 重放前移除 binlog 中的 DROP/DELETE 誤操作語句。

單表恢復

• 從完整備份 SQL 擷取目標表段落導入。
• 或另架臨時實例恢復整庫，再 mysqldump 導出單表。
• 使用 mysql_find_rows 從 binlog 抽取該表查詢（進階）。

總結

備份無價值，恢復才有價值。結合「定期全量 + 每日增量 + 異地存放 + 定期演練」四位一體，才能在災難來臨時從容應對。