MySQL 資料庫備份與還原完整策略指南

備份的重要性

資料表遺損、損壞、系統崩潰、甚至人為誤操作（如用文字編輯器直接修改表檔）都可能導致資料災難。完善的備份策略是最後防線。

兩大備份方法比較

MyISAM 格式優勢

MySQL 3.23+ 引入 MyISAM，機器無關格式，滿足兩條件即可跨架構直接複製：

1. 目標機器運行 MySQL 3.23+
2. 表為 MyISAM 格式（非 ISAM）

備份黃金原則

1. 定期自動化：建立排程嚴格執行
2. 啟用更新日誌：崩潰後可從備份點重放日誌恢復至崩潰瞬間
3. 完整 + 增量：備份檔 = 完整傾倒，更新日誌 = 增量傾倒
4. 有意義命名：資料庫名_YYYY-MM-DD，如 samp_db.1999-10-02
5. 壓縮儲存：mysqldump db | gzip > backup.gz
6. 設定保留期限：避免塞滿磁碟
7. 異地備份備份檔：防單點災難
8. 分離儲存：備份檔放在不同檔案系統/磁碟

mysqldump 實戰操作

備份整個資料庫

mysqldump samp_db > /usr/archives/mysql/samp_db.1999-10-02

輸出檔案結構

# MySQL Dump 6.0
# Host: localhost Database: samp_db
# Server version 3.23.2-alpha-log

# Table structure for table 'absence'
CREATE TABLE absence (
  student_id int(10) unsigned DEFAULT '0' NOT NULL,
  date date DEFAULT '0000-00-00' NOT NULL,
  PRIMARY KEY (student_id,date)
);

# Dumping data for table 'absence'
INSERT INTO absence VALUES (3,'1999-09-03');
INSERT INTO absence VALUES (5,'1999-09-03');
INSERT INTO absence VALUES (10,'1999-09-08');
...

壓縮備份

mysqldump samp_db | gzip > /usr/archives/mysql/samp_db.1999-10-02.gz

還原資料庫

mysql samp_db < /usr/archives/mysql/samp_db.1999-10-02
# 或壓縮檔：
zcat /usr/archives/mysql/samp_db.1999-10-02.gz | mysql samp_db

直接複製檔案法操作

1. 鎖定表：FLUSH TABLES WITH READ LOCK;
2. 複製 datadir/資料庫名/ 目錄下所有檔案
3. 解鎖：UNLOCK TABLES;

僅限 MyISAM 表，且需相同 MySQL 版本或支援 MyISAM 的版本。

資料庫遷移

備份技術同樣適用於跨機器遷移：備份 → 傳輸 → 還原。最常見為跨主機遷移，同機多實例遷移亦適用。