MySQL 資料庫備份守則與實戰方法大全

三、 備份鐵律（必遵守）

1. 定期、自動化、有計畫：建立排程（crontab / 任務排程器），嚴格執行。
2. 啟用二進位日誌：備份 = 全量備份 (Full) + 增量日誌。崩潰恢復時：
   還原全量備份 → 重放二進位日誌 → 恢復至崩潰前時間點
3. 有意義的命名：dbname_YYYY-MM-DD.sql.gz，避免 backup1、backup2 這類無資訊名稱。
4. 壓縮與保留策略：gzip 壓縮，設定過期刪除（如保留 7 天全量 + 30 天增量）。
5. 異地備份：備份檔放在不同磁碟、不同機房、甚至雲端儲存（S3、GCS）。
6. 備份你的備份：檔案系統層面再備份一次備份目錄。

四、 mysqldump 實戰技巧

常用組合

• mysqldump --opt --single-transaction --flush-logs --master-data=2 db_name | gzip > db_$(date +%F).sql.gz
  含優化、熱備份、刷新日誌、記錄 Binlog 位置（從庫同步用）。
• 大庫拆表備份：mysqldump db_name table1 table2 > subset.sql
• 定期刷新從庫：mysqldump --add-drop-table --opt db_name | mysql -h slave_host db_name

效能優化參數

• --quick：逐行輸出，防 OOM。
• --compress：跨網路傳輸壓縮。
• --delayed-insert：還原時用 INSERT DELAYED 減少對線上查詢衝擊。

五、 實體備份操作步驟

1. 停止服務或全域鎖表：FLUSH TABLES WITH READ LOCK;
2. 複製 datadir 目標資料庫目錄：cp -r /var/lib/mysql/db_name /backup/
3. 釋放鎖：UNLOCK TABLES;
4. 跨機器遷移：確保目標機 MySQL 版本一致、my.cnf 相容、innodb_data_file_path 相同。

六、 還原策略

整庫還原

1. 若還原 mysql 系統庫，啟動加 --skip-grant-tables。
2. 載入全量備份：mysql db_name < backup.sql 或直接複製檔案回 datadir。
3. 按時間順序重放二進位日誌：
   mysqlbinlog binlog.000001 | mysql --one-database db_name
4. 執行 mysqladmin flush-privileges 重載權限。

單表還原

• 從全量 SQL 檔提取目標表片段（配合 sed/awk 或 MySQL_find_rows）。
• 或另啟一臨時實例還原全庫，再 SELECT ... INTO OUTFILE 導出單表，複製回主庫。

七、 複製與高可用

MySQL 3.23+ 引入 Replication（主從同步），可作為即時備份與讀寫分離基礎。但複製不等於備份：主庫 DROP TABLE 會同步到從庫，仍需定期全量備份配合二進位日誌實現 PITR。