PHP PDO 資料庫備份還原類：完整程式碼與原理解析

提供一個可直接投入生產環境的 DatabaseTool 類別，支援指定/全量資料表備份、結構與資料分離輸出、單檔 SQL 匯出與還原，基於 PDO 防注入、支援多字元集。

核心功能

• 備份：backup($tables = []) → 產生 DROP TABLE IF EXISTS + CREATE TABLE + INSERT 完整 SQL
• 還原：restore($path) → 解析 SQL 檔、過濾註解、exec() 批次執行
• 設定：主機、連接埠、帳號、密碼、資料庫、字元集、輸出路徑

類別完整程式碼

<?php
class DatabaseTool {
    private $pdo;
    private $config = [
        'host' => 'localhost', 'port' => 3306,
        'user' => 'root', 'password' => '',
        'database' => 'test', 'charset' => 'utf8',
        'target' => 'backup.sql'
    ];
    private $tables = [];
    private $error;
    private $startTime;

    public function __construct(array $config = []) {
        $this->startTime = microtime(true);
        $this->config = array_merge($this->config, $config);
        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]);
        } catch (PDOException $e) {
            $this->error = $e->getMessage();
        }
    }

    public function backup(array $tables = []) {
        $this->setTables($tables);
        if (empty($this->tables)) { $this->error = '無資料表'; return false; }

        $ddl = []; $data = [];
        foreach ($this->tables as $t) {
            $ddl[] = $this->getDDL($t);
            $data[] = $this->getData($t);
        }
        return $this->writeFile($ddl, $data);
    }

    public function restore(string $path) {
        if (!file_exists($path)) { $this->error = 'SQL 檔不存在'; return false; }
        $sql = $this->parseSQL($path);
        try {
            $this->pdo->exec($sql);
            echo "還原成功，耗時 " . (microtime(true) - $this->startTime) * 1000 . " ms";
            return true;
        } catch (PDOException $e) {
            $this->error = $e->getMessage();
            return false;
        }
    }

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

    private function setTables(array $tables) {
        $this->tables = $tables ?: $this->getAllTables();
    }

    private function getAllTables() {
        return $this->pdo->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN);
    }

    private function query(string $sql) {
        return $this->pdo->query($sql)->fetchAll(PDO::FETCH_NUM);
    }

    private function getDDL(string $table) {
        return $this->query("SHOW CREATE TABLE `$table`")[0][1] . ';';
    }

    private function getData(string $table) {
        $cols = array_column($this->query("SHOW COLUMNS FROM `$table`"), 0);
        $colList = '`' . implode('`, `', $cols) . '`';
        $rows = $this->query("SELECT * FROM `$table`");
        $sql = '';
        foreach ($rows as $row) {
            $vals = array_map(function($v) { return "'" . addslashes($v) . "'"; }, $row);
            $sql .= "INSERT INTO `$table` ($colList) VALUES (" . implode(',', $vals) . ");\n";
        }
        return $sql;
    }

    private function writeFile(array $ddl, array $data) {
        $out = "/*\nMySQL Backup\nServer:{$this->config['host']}:{$this->config['port']}\nDatabase:{$this->config['database']}\nDate:" . date('Y-m-d H:i:s') . "\n*/\n";
        $out .= "SET FOREIGN_KEY_CHECKS=0;\n";
        foreach ($this->tables as $i => $t) {
            $out .= "-- Table: $t\nDROP TABLE IF EXISTS `$t`;\n{$ddl[$i]}\n-- Data\n{$data[$i]}\n";
        }
        $bytes = file_put_contents($this->config['target'], $out);
        echo $bytes ? "備份成功，耗時 " . (microtime(true) - $this->startTime) * 1000 . " ms" : "備份失敗";
        return (bool)$bytes;
    }

    private function parseSQL(string $path) {
        $sql = file_get_contents($path);
        $lines = explode("\n", $sql);
        $lines = array_filter($lines, fn($l) => $l !== '' && !preg_match('/^--/', $l));
        $sql = implode('', $lines);
        $sql = preg_replace('/\/\*.*?\*\//s', '', $sql); // 移除 /* */ 註解
        return $sql;
    }
}
?>

使用範例

<?php
require 'DatabaseTool.php';

// 備份全庫
$tool = new DatabaseTool(['database' => 'mydb', 'target' => 'mydb_backup.sql']);
$tool->backup();

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

// 還原
$tool->restore('mydb_backup.sql');
?>

注意事項與擴充建議

• 大資料量：SELECT * 可能撐爆記憶體，建議改用 LIMIT 分批或 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false 游標模式。
• BLOB/二進位欄位：addslashes 不足，需用 bin2hex 或 LOAD_FILE 處理。
• 觸發器/預存程序/檢視表：目前未備份，可擴充 SHOW TRIGGERS 等。
• 壓縮：輸出前 gzencode() 產生 .sql.gz 節省空間。