不依賴 mysqldump 外部命令,純 PHP PDO 實現的數據庫備份類,支援結構與數據分離導出、大表分批寫入、GZIP 壓縮、多數據庫遷移兼容。適合無 Shell 權限的虛擬主機、跨平台部署、CI/CD 流水線集成。
class DbBackup {
private PDO $pdo;
private string $dbName;
private string $savePath;
private int $batchSize = 1000;
private bool $compress = false;
public function __construct(PDO $pdo, string $savePath, string $dbName = '') {
$this->pdo = $pdo;
$this->savePath = rtrim($savePath, '/');
$this->dbName = $dbName ?: $pdo->query('SELECT DATABASE()')->fetchColumn();
}
public function setBatchSize(int $size): self { $this->batchSize = $size; return $this; }
public function enableCompression(bool $gzip = true): self { $this->compress = $gzip; return $this; }
public function backup(): string {
$file = $this->savePath . '/' . $this->dbName . '_' . date('YmdHis') . '.sql' . ($this->compress ? '.gz' : '');
$fh = $this->compress ? gzopen($file, 'wb9') : fopen($file, 'wb');
if (!$fh) throw new RuntimeException("無法創建備份文件: $file");
// 1. 寫入表結構
$tables = $this->pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
foreach ($tables as $table) {
$create = $this->pdo->query("SHOW CREATE TABLE `$table`")->fetch(PDO::FETCH_NUM);
$this->write($fh, "DROP TABLE IF EXISTS `$table`;\n");
$this->write($fh, $create[1] . ";\n\n");
}
// 2. 寫入數據(分批)
foreach ($tables as $table) {
$stmt = $this->pdo->prepare("SELECT * FROM `$table`");
$stmt->execute();
$columns = [];
for ($i = 0; $i < $stmt->columnCount(); $i++) {
$columns[] = $stmt->getColumnMeta($i)['name'];
}
$colList = '`' . implode('`,`', $columns) . '`';
$placeholders = '(' . implode(',', array_fill(0, count($columns), '?')) . ')';
$insertHead = "INSERT INTO `$table` ($colList) VALUES ";
$batch = [];
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
$batch[] = $placeholders;
if (count($batch) >= $this->batchSize) {
$this->write($fh, $insertHead . implode(',', $batch) . ";\n");
$batch = [];
}
}
if ($batch) {
$this->write($fh, $insertHead . implode(',', $batch) . ";\n");
}
}
$this->compress ? gzclose($fh) : fclose($fh);
return $file;
}
private function write($fh, string $data): void {
$this->compress ? gzwrite($fh, $data) : fwrite($fh, $data);
}
}
$pdo = new PDO('mysql:host=localhost;dbname=myapp;charset=utf8mb4', 'backup_user', 'strong_password', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false, // 非緩衝查詢,大結果集不佔記憶體
]);
$backup = new DbBackup($pdo, '/var/backups/mysql');
$backup->setBatchSize(500)->enableCompression(true);
$file = $backup->backup();
echo "備份完成: $file (" . number_format(filesize($file)/1024/1024, 2) . " MB)";
| 決策點 | 選擇 | 理由 |
|---|---|---|
| 數據庫連接 | PDO | 跨驅動(MySQL/PostgreSQL/SQLite/SQL Server)統一介面,支援預處理語句 |
| 查詢模式 | 非緩衝 MYSQL_ATTR_USE_BUFFERED_QUERY=false | 大表逐行獲取,記憶體常數級 |
| 數據導出格式 | 標準 SQL INSERT 多值語句 | 通用性強,可直接 mysql < file.sql 還原 |
| 批次大小 | 可配置,默認 1000 | 平衡單條 SQL 長度限制與網絡往返次數 |
| 壓縮 | GZIP 流式寫入 gzopen/gzwrite | 無需臨時文件,邊寫邊壓,節省磁盤 IO |
| 錯誤處理 | PDO::ERRMODE_EXCEPTION + try-catch | 統一異常模型,便於上層捕獲記錄、告警 |
AUTO_INCREMENT、ENGINE=InnoDB、utf8mb4)。目標庫為 PostgreSQL/SQL Server 時,需轉換工具(pgloader、SQL Server Migration Assistant)或人工調整。SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; 導入後恢復並 COMMIT。SELECT + 多文件寫入,最後 cat *.sql > full.sql 合併(需注意外鍵順序)。此類為教學與中小型項目提供「零依賴、可遷移」備份能力;核心業務建議結合 Percona XtraBackup(物理熱備份)、云廠商快照、Binlog 增量同步構建 RPO=0/RTO<分鐘的災備體系。