PHP 實現 MySQL 備份還原完整類庫：結構導出、資料轉義、分隔符處理與事務性還原

從零構建生產可用的 MySQL 備份還原 PHP 類

基於 `mysql_*` 擴充 (舊版 PHP，現代建議用 `mysqli`/`PDO`)，核心邏輯通用：連線 → 列表 → `SHOW CREATE TABLE` 導出結構 → `SELECT *` 組裝 `INSERT` → 檔案落地 → 還原時 `DROP TABLE` → 解析 SQL 分批執行。本文重點講解**資料轉義、NULL 處理、分隔符設計、大檔案記憶體控制**等關鍵細節。

核心類別架構

class DbBackup {
    public $host = 'localhost';
    public $user = 'root';
    public $pwd  = '';
    public $database;
    public $charset = 'utf8';
    public $delimiter = ';--  --';  // SQL 分割標記 (必須為註釋形式)

    // 連線
    private function connect() { ... }

    // 取得所有表名
    private function getTables() { ... }

    // 導出結構 SQL
    public function dumpStructure() { ... }

    // 導出資料 INSERT SQL
    public function dumpData() { ... }

    // 執行備份
    public function backup($filepath) { ... }

    // 執行還原
    public function restore($filepath) { ... }
}

關鍵細節深度解析

1. 結構導出：`SHOW CREATE TABLE`

public function dumpStructure() {
    $sql = '';
    foreach ($this->getTables() as $table) {
        $row = mysql_fetch_row(mysql_query("SHOW CREATE TABLE `$table`"));
        $sql .= "-- 表結構：{$row[0]} --\r\n";
        $sql .= "{$row[1]}" . $this->delimiter . "\r\n\r\n";
    }
    return $sql;
}

2. 資料導出：逐行組裝 INSERT (記憶體友善)

public function dumpData() {
    $sql = '';
    foreach ($this->getTables() as $table) {
        $res = mysql_query("SELECT * FROM `$table`");
        if (!mysql_num_rows($res)) continue;  // 空表跳過

        $sql .= "-- 表資料：$table --\r\n";
        $sql .= "INSERT INTO `$table` VALUES\r\n";

        $firstRow = true;
        while ($row = mysql_fetch_row($res)) {
            if (!$firstRow) $sql .= ",\r\n";
            $firstRow = false;

            $sql .= '(';
            $vals = [];
            foreach ($row as $val) {
                if ($val === null) {
                    $vals[] = 'NULL';
                } else {
                    // 必須轉義：防止單引號、反斜線、控制字元注入破壞 SQL
                    $vals[] = "'" . mysql_real_escape_string($val) . "'";
                }
            }
            $sql .= implode(',', $vals) . ')';
        }
        $sql .= $this->delimiter . "\r\n\r\n";
    }
    return $sql;
}

關鍵點：

• `mysql_real_escape_string` **強制轉義**，防止 SQL 注入與語法錯誤。
• `NULL` vs `'NULL'`：資料庫 NULL 必須無引號，字串 `'NULL'` 有引號。
• 分批寫入檔案 (`fwrite`) 而非累積巨大字串，避免記憶體耗盡 (大表 GB 級)。

3. 分隔符設計：`;-- --`

• 以 `;` 結束 SQL 語句 (MySQL 要求)。
• 緊接註釋標記 `-- --`，`explode` 解析時不影響 SQL 語法。
• 換行 `\r\n\r\n` 僅為可讀性。

4. 還原邏輯：安全重建

public function restore($filepath) {
    $this->connect();

    // 1. 刪除現有表 (按外鍵順序較複雜，簡化版全刪)
    $tables = $this->getTables();
    if ($tables) {
        $dropSql = 'DROP TABLE ' . implode(',', array_map(fn($t)=>"`$t`", $tables));
        if (!mysql_query($dropSql)) return false;
    }

    // 2. 讀取檔案並分割執行
    $content = file_get_contents($filepath);  // 大檔案建議 fgets 逐行緩衝解析
    $statements = explode($this->delimiter, $content);
    array_pop($statements);  // 移除末尾空段

    foreach ($statements as $stmt) {
        $stmt = trim($stmt);
        if ($stmt === '') continue;
        if (!mysql_query($stmt)) {
            error_log("Restore failed: " . mysql_error() . " | SQL: " . substr($stmt, 0, 200));
            return false;
        }
    }
    return true;
}

使用範例

// 備份
$backup = new DbBackup();
$backup->database = 'my_db';
$backup->user = 'backup_user';
$backup->pwd  = 'strong_pwd';
$result = $backup->backup('/backups/my_db_' . date('Ymd_His') . '.sql');
var_dump($result);  // int(bytes) or false

// 還原
$restore = new DbBackup();
$restore->database = 'my_db';
$restore->user = 'root';
$restore->pwd  = 'pwd';
$result = $restore->restore('/backups/my_db_20230101_000000.sql');
var_dump($result);  // bool

生產環境強化清單 (遷移至 mysqli/PDO 時必做)

1. 大檔案流式處理：`fopen` + `fgets` 緩衝讀取，累積到 `;` + 註釋標記才執行，避免 `file_get_contents` 吃光記憶體。
2. 外鍵順序：還原前 `SET FOREIGN_KEY_CHECKS=0;`，結束後 `=1;`，或依拓撲序建表/導入。
3. 字元集一致：備份/還原連線 `SET NAMES utf8mb4`，檔案儲存 UTF-8 無 BOM。
4. 權限分離：備份帳號 `SELECT, SHOW VIEW, LOCK TABLES, PROCESS`；還原帳號 `CREATE, DROP, INSERT, ALTER, INDEX`。
5. 壓縮與加密：`gzip` 壓縮備份檔，`openssl` 加密傳輸/存儲。
6. 驗證機制：還原後 `CHECKSUM TABLE`、`COUNT(*)` 抽樣比對、關鍵業務查詢冒煙測試。
7. 定時任務 + 保留策略：cron + `find -mtime +7 -delete` / 上傳至 S3/OSS 冷備份。

總結