PHP 資料庫備份類：支援多表、結構與資料、大檔案分塊寫入

PHP MySQL 備份類完整實作：結構+資料、記憶體友善、可擴充壓縮加密

將備份邏輯封裝為可重用類別，支援指定資料表、僅結構/僅資料、分塊寫入避免記憶體溢出、可選 gzip 壓縮與 OpenSSL 加密。

DatabaseBackup 類別

<?php
class DatabaseBackup {
    private PDO $pdo;
    private string $outputDir;
    private array $options = [
        'tables' => null,        // null = 全部
        'structureOnly' => false,
        'dataOnly' => false,
        'chunkSize' => 1000,     // 每次 SELECT/INSERT 筆數
        'compress' => false,     // gzip
        'encrypt' => false,      // OpenSSL AES-256
        'encPass' => '',
        'addDropTable' => true,
        'singleTransaction' => true,
    ];

    public function __construct(PDO $pdo, string $outputDir, array $options = []) {
        $this->pdo = $pdo;
        $this->outputDir = rtrim($outputDir, '/');
        $this->options = array_merge($this->options, $options);
        if (!is_dir($this->outputDir)) mkdir($this->outputDir, 0750, true);
    }

    public function backup(): string {
        $filename = $this->options['database'] . '_' . date('Ymd_His') . '.sql';
        if ($this->options['compress']) $filename .= '.gz';
        if ($this->options['encrypt']) $filename .= '.enc';
        $filepath = $this->outputDir . '/' . $filename;

        $outFp = $this->openOutput($filepath);
        try {
            $this->writeHeader($outFp);
            $tables = $this->getTables();
            foreach ($tables as $table) {
                if (!$this->options['dataOnly']) {
                    $this->writeTableStructure($outFp, $table);
                }
                if (!$this->options['structureOnly']) {
                    $this->writeTableData($outFp, $table);
                }
            }
            $this->writeFooter($outFp);
        } finally {
            $this->closeOutput($outFp, $filepath);
        }
        return $filepath;
    }

    private function getTables(): array {
        if ($this->options['tables']) return $this->options['tables'];
        $stmt = $this->pdo->query("SHOW TABLES");
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }

    private function writeHeader($fp) {
        fwrite($fp, "-- Database Backup " . date('Y-m-d H:i:s') . "\n");
        fwrite($fp, "SET FOREIGN_KEY_CHECKS=0;\n");
        fwrite($fp, "SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';\n");
        fwrite($fp, "SET time_zone='+00:00';\n\n");
    }

    private function writeTableStructure($fp, string $table) {
        if ($this->options['addDropTable']) {
            fwrite($fp, "DROP TABLE IF EXISTS `$table`;\n");
        }
        $stmt = $this->pdo->query("SHOW CREATE TABLE `$table`");
        $row = $stmt->fetch(PDO::FETCH_NUM);
        fwrite($fp, $row[1] . ";\n\n");
    }

    private function writeTableData($fp, string $table) {
        $countStmt = $this->pdo->query("SELECT COUNT(*) FROM `$table`");
        $total = (int)$countStmt->fetchColumn();
        if ($total === 0) return;

        $chunkSize = $this->options['chunkSize'];
        $colsStmt = $this->pdo->query("SHOW COLUMNS FROM `$table`");
        $columns = array_column($colsStmt->fetchAll(PDO::FETCH_ASSOC), 'Field');
        $colList = '`' . implode('`,`', $columns) . '`';

        for ($offset = 0; $offset < $total; $offset += $chunkSize) {
            $stmt = $this->pdo->prepare("SELECT $colList FROM `$table` LIMIT $chunkSize OFFSET $offset");
            $stmt->execute();
            while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
                $values = array_map(fn($v) => $v === null ? 'NULL' : $this->pdo->quote($v), $row);
                fwrite($fp, "INSERT INTO `$table` ($colList) VALUES (" . implode(',', $values) . ");\n");
            }
        }
        fwrite($fp, "\n");
    }

    private function writeFooter($fp) {
        fwrite($fp, "SET FOREIGN_KEY_CHECKS=1;\n");
    }

    private function openOutput(string $filepath) {
        if ($this->options['encrypt'] && !$this->options['encPass']) {
            throw new InvalidArgumentException('Encryption requires password');
        }
        if ($this->options['compress'] && $this->options['encrypt']) {
            // pipe: gzip -> openssl -> file
            $cmd = "gzip -c | openssl enc -aes-256-cbc -pbkdf2 -pass pass:" . escapeshellarg($this->options['encPass']) . " > " . escapeshellarg($filepath);
            return popen($cmd, 'w');
        } elseif ($this->options['compress']) {
            return gzopen($filepath, 'wb9');
        } elseif ($this->options['encrypt']) {
            $cmd = "openssl enc -aes-256-cbc -pbkdf2 -pass pass:" . escapeshellarg($this->options['encPass']) . " > " . escapeshellarg($filepath);
            return popen($cmd, 'w');
        }
        return fopen($filepath, 'wb');
    }

    private function closeOutput($fp, string $filepath) {
        if (is_resource($fp)) {
            $meta = stream_get_meta($fp);
            if (isset($meta['mode']) && strpos($meta['mode'], 'p') !== false) {
                pclose($fp);
            } elseif (get_resource_type($fp) === 'stream' && function_exists('gzclose')) {
                gzclose($fp);
            } else {
                fclose($fp);
            }
        }
        // 權限收斂
        chmod($filepath, 0600);
    }
}

// 使用範例
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$backup = new DatabaseBackup($pdo, '/backups', [
    'database' => 'test',
    'chunkSize' => 500,
    'compress' => true,
    'encrypt' => true,
    'encPass' => 'StrongPass123!',
]);

$file = $backup->backup();
echo "備份完成: $file\n";

關鍵設計點

• PDO 預處理語句 防注入，quote() 處理特殊字元。
• 分塊讀取 LIMIT ... OFFSET 避免一次載入大量資料。
• 串流管道 popen('gzip | openssl') 邊讀邊壓縮加密，不落地中間檔。
• 外鍵檢查關閉 還原時避免順序問題。
• 權限 600 保護備份檔敏感資料。

還原腳本

# 解密 -> 解壓 -> 還原
openssl enc -d -aes-256-cbc -pbkdf2 -pass pass:StrongPass123! -in backup.sql.gz.enc | gunzip | mysql -u user -p test

擴充建議

• 支援 PostgreSQL、SQLite (切換 Driver)。
• 加入 --where 條件備份增量資料。
• 整合雲端儲存 (S3, GCS) 自動上傳。
• 建立 Web UI 監控備份狀態、下載、刪除。