萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP 資料庫備份還原類:PDO 驅動、結構與資料完整匯出、大檔案支援

類別設計目標

  • 支援 MySQL/MariaDB/PostgreSQL (透過 PDO)
  • 完整備份:表結構 (CREATE TABLE) + 資料 (INSERT)
  • 單一 SQL 檔案輸出,含註釋、外鍵檢查控制
  • 記憶體友善:逐表、逐批次處理大資料表
  • 還原支援:解析 SQL 檔、過濾註釋、事務執行

完整類別程式碼

<?php
class DatabaseBackup {
    private PDO $pdo;
    private array $config;
    private array $tables = [];
    private string $error = '';
    private float $startTime;

    public function __construct(array $config = []) {
        $this->startTime = microtime(true);
        $defaults = [
            'host' => '127.0.0.1',
            'port' => 3306,
            'user' => 'root',
            'password' => '',
            'database' => 'test',
            'charset' => 'utf8mb4',
            'target' => 'backup_' . date('Ymd_His') . '.sql',
            'batch_size' => 500, // 每批 INSERT 筆數
        ];
        $this->config = array_merge($defaults, $config);
        $this->connect();
    }

    private function connect(): void {
        try {
            $dsn = "mysql:host={$this->config['host']};port={$this->config['port']};dbname={$this->config['database']};charset={$this->config['charset']}";
            $this->pdo = new PDO($dsn, $this->config['user'], $this->config['password'], [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NUM,
                PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false, // 游標模式,省記憶體
            ]);
        } catch (PDOException $e) {
            $this->error = $e->getMessage();
            throw new RuntimeException("DB Connection failed: {$e->getMessage()}");
        }
    }

    /** 公開:執行備份 */
    public function backup(array $tables = []): bool {
        $this->setTables($tables);
        if (empty($this->tables)) {
            $this->error = 'Database has no tables';
            return false;
        }

        $sqlParts = [];
        $sqlParts[] = $this->buildHeader();

        foreach ($this->tables as $table) {
            $sqlParts[] = $this->buildTableStructure($table);
            $sqlParts[] = $this->buildTableData($table);
        }

        $sqlParts[] = "SET FOREIGN_KEY_CHECKS=1;\n";
        $finalSql = implode("\n", $sqlParts);

        $bytes = @file_put_contents($this->config['target'], $finalSql, LOCK_EX);
        if ($bytes === false) {
            $this->error = 'Write file failed: ' . $this->config['target'];
            return false;
        }

        $elapsed = round((microtime(true) - $this->startTime) * 1000, 2);
        echo "Backup success! File: {$this->config['target']} ({$bytes} bytes, {$elapsed}ms)\n";
        return true;
    }

    /** 設定要備份的表 */
    private function setTables(array $tables): void {
        if (!empty($tables)) {
            $this->tables = $tables;
        } else {
            $this->tables = $this->getAllTables();
        }
    }

    /** 取得所有表名 */
    private function getAllTables(): array {
        $stmt = $this->pdo->query('SHOW TABLES');
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }

    /** 產生檔案頭部 */
    private function buildHeader(): string {
        $c = $this->config;
        return <<pdo->query("SHOW CREATE TABLE `{$table}`");
        $row = $stmt->fetch();
        $ddl = $row[1] . ';';

        return <<pdo->query("SHOW COLUMNS FROM `{$table}`");
        $columns = array_column($stmt->fetchAll(), 0);
        $colList = '`' . implode('`,`', $columns) . '`';

        $selectSql = "SELECT {$colList} FROM `{$table}`";
        $stmt = $this->pdo->query($selectSql);

        $batch = [];
        $output = "-- Records of {$table}\n";
        $count = 0;

        while ($row = $stmt->fetch()) {
            $escaped = array_map(fn($v) => $this->escapeValue($v), $row);
            $batch[] = '(' . implode(',', $escaped) . ')';
            $count++;

            if (count($batch) >= $this->config['batch_size']) {
                $output .= "INSERT INTO `{$table}` ({$colList}) VALUES\n" . implode(",\n", $batch) . ";\n";
                $batch = [];
            }
        }

        if ($batch) {
            $output .= "INSERT INTO `{$table}` ({$colList}) VALUES\n" . implode(",\n", $batch) . ";\n";
        }

        return "-- ----------------------------\n" . $output;
    }

    /** 數值轉 SQL 字面值 */
    private function escapeValue(mixed $value): string {
        if ($value === null) return 'NULL';
        if (is_bool($value)) return $value ? '1' : '0';
        if (is_int($value) || is_float($value)) return (string)$value;
        // 字串:逃逸單引號、反斜線、控制字元
        return "'" . str_replace(["\\", "'", "\0", "\n", "\r", "\x1a"], ["\\\\", "\\'", "\\0", "\\n", "\\r", "\\Z"], $value) . "'";
    }

    /** 公開:還原資料庫 */
    public function restore(string $path = ''): bool {
        $file = $path ?: $this->config['target'];
        if (!is_file($file)) {
            $this->error = "SQL file not found: $file";
            return false;
        }

        $sql = $this->parseSqlFile($file);
        if ($sql === '') {
            $this->error = 'No valid SQL statements';
            return false;
        }

        try {
            $this->pdo->beginTransaction();
            $this->pdo->exec($sql);
            $this->pdo->commit();
            $elapsed = round((microtime(true) - $this->startTime) * 1000, 2);
            echo "Restore success! ({$elapsed}ms)\n";
            return true;
        } catch (PDOException $e) {
            $this->pdo->rollBack();
            $this->error = $e->getMessage();
            return false;
        }
    }

    /** 解析 SQL 檔:移除註釋、分割語句 */
    private function parseSqlFile(string $file): string {
        $content = file_get_contents($file);
        if ($content === false) return '';

        // 1. 移除單行註釋 -- ...
        $lines = explode("\n", $content);
        $cleanLines = [];
        foreach ($lines as $line) {
            $trimmed = trim($line);
            if ($trimmed === '' || str_starts_with($trimmed, '--')) continue;
            $cleanLines[] = $line;
        }
        $content = implode("\n", $cleanLines);

        // 2. 移除多行註釋 /* ... */
        $content = preg_replace('/\/\*.*?\*\//s', '', $content);

        // 3. 分割語句 (簡易:以 ;\n 分割)
        // 實際專案建議用完整 SQL 解析器
        $statements = array_filter(array_map('trim', explode(";\n", $content)));

        return implode(";\n", $statements) . ';';
    }

    public function getError(): string {
        return $this->error;
    }
}

// 使用範例
try {
    $backup = new DatabaseBackup([
        'database' => 'myapp',
        'target' => __DIR__ . '/backups/db_' . date('Ymd_His') . '.sql',
        'batch_size' => 1000,
    ]);

    // 備份指定表
    $backup->backup(['users', 'orders', 'products']);

    // 或備份全部
    // $backup->backup();

    // 還原
    // $backup->restore('/path/to/backup.sql');
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}

關鍵設計細節

1. 記憶體優化:非緩衝查詢

PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false
// 使用 mysqlnd 驅動時,結果集不預載入記憶體,逐行 fetch

2. 批次 INSERT 減少 SQL 體積

每 500-1000 筆合併一條 `INSERT ... VALUES (...),(...),...`,大幅縮小檔案、加快匯入速度。

3. 外鍵檢查控制

SET FOREIGN_KEY_CHECKS=0; -- 備份開頭
... DROP TABLE / CREATE TABLE / INSERT ...
SET FOREIGN_KEY_CHECKS=1; -- 備份結尾

4. 字元集明確指定

`SET NAMES utf8mb4` 確保還原時字元集正確。

5. 時區統一

`SET TIME_ZONE='+00:00'` 避免時間戳時區差異。

擴充功能建議

  • 壓縮輸出:`gzencode($sql)` 存 `.sql.gz`,還原時 `gzdecode()`
  • 雲端上傳:備份完成自動上傳至 S3/OSS/GCS
  • 增量備份:記錄 `binlog` 位置或 `UPDATE_TIME`,僅備份變更表
  • 平行處理:多表並行 `mysqldump --parallel` 或 PHP 多進程
  • 驗證機制:還原後自動抽樣比對資料筆數、檢查碼

對比 mysqldump

特性本類別mysqldump
執行環境PHP 進程內外部命令列
跨平台✅ 純 PHP需安裝 client tools
大表效能中 (單執行緒)高 (優化 C 代碼)
整合框架✅ 易整合 Laravel/Symfony需 exec()
資料一致性需自行鎖表/事務--single-transaction

對於中小型專案、需整合至 PHP 部署流程、或無法使用 shell 執行環境,此類別提供完整可用的備份還原能力。

copyright © 萬盛學電腦網 all rights reserved