<?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();
}
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false
// 使用 mysqlnd 驅動時,結果集不預載入記憶體,逐行 fetch
每 500-1000 筆合併一條 `INSERT ... VALUES (...),(...),...`,大幅縮小檔案、加快匯入速度。
SET FOREIGN_KEY_CHECKS=0; -- 備份開頭
... DROP TABLE / CREATE TABLE / INSERT ...
SET FOREIGN_KEY_CHECKS=1; -- 備份結尾
`SET NAMES utf8mb4` 確保還原時字元集正確。
`SET TIME_ZONE='+00:00'` 避免時間戳時區差異。
| 特性 | 本類別 | mysqldump |
|---|---|---|
| 執行環境 | PHP 進程內 | 外部命令列 |
| 跨平台 | ✅ 純 PHP | 需安裝 client tools |
| 大表效能 | 中 (單執行緒) | 高 (優化 C 代碼) |
| 整合框架 | ✅ 易整合 Laravel/Symfony | 需 exec() |
| 資料一致性 | 需自行鎖表/事務 | --single-transaction |
對於中小型專案、需整合至 PHP 部署流程、或無法使用 shell 執行環境,此類別提供完整可用的備份還原能力。