PHP MySQL 資料庫備份還原類：分卷備份、選擇性還原與生產級實作

前言

資料庫備份是系統維運的核心環節。對於中大型專案，單一 SQL 檔案可能達到數百 MB 甚至 GB，導致匯入超時、記憶體耗盡、網路傳輸困難。本文提供一個生產級 PHP MySQL 備份還原類別，支援分卷備份（自動切割大檔案）、指定資料表備份、結構與資料分離、分卷還原、進度顯示 等實用功能，並修正原始代碼中的 mysql_* 棄用函式、SQL 注入風險、編碼處理等問題。

核心功能特性

• 分卷備份：單檔超過設定大小（預設 2MB）自動切割為 _v1.sql, _v2.sql…
• 選擇性備份：可指定單一資料表或全庫備份
• 完整結構：包含 DROP TABLE, CREATE TABLE, INSERT 完整語句
• 分卷還原：自動偵測分卷序號，循序執行，支援中斷續傳
• 資料表鎖定：備份/還原期間鎖定資料表防止資料不一致
• mysqli 預處理：修正原始 mysql_*，防 SQL 注入
• UTF-8/GBK 雙編碼相容

完整類別代碼

<?php
/**
 * MySQL 資料庫備份還原管理類
 * 支援：分卷備份、指定表、結構+資料、分卷還原、進度回調
 */
class DbBackupRestore
{
    private mysqli $db;
    private string $database;
    private string $charset;
    private string $sqlDir = './backup/';
    private string $ds = "\n";
    private string $sqlEnd = ";\n";
    public  string $sqlContent = '';

    // 進度回調：function(string $message, int $progressPercent): void
    public ?callable $onProgress = null;

    public function __construct(
        string $host,
        string $username,
        string $password,
        string $database,
        string $charset = 'utf8mb4'
    ) {
        $this->database = $database;
        $this->charset  = $charset;

        $this->db = new mysqli($host, $username, $password, $database);
        if ($this->db->connect_errno) {
            throw new Exception("資料庫連線失敗: {$this->db->connect_error}");
        }
        $this->db->set_charset($charset);
    }

    // ---------- 公開 API ----------

    /**
     * 備份資料庫
     * @param string $table 指定資料表，空字串為全庫
     * @param string $dir 備份目錄
     * @param int $volumeSizeKB 分卷大小（KB），預設 2048 = 2MB
     */
    public function backup(string $table = '', string $dir = '', int $volumeSizeKB = 2048): void
    {
        $dir = $dir ?: $this->sqlDir;
        if (!is_dir($dir)) mkdir($dir, 0755, true);

        $this->_progress("開始備份" . ($table ? "資料表 `$table`" : "全庫 `$this->database`"), 0);

        // 1. 寫入 Dump 標頭資訊
        $this->sqlContent = $this->_getDumpHeader();

        // 2. 取得要備份的資料表清單
        $tables = $table
            ? [$table]
            : $this->_getAllTables();

        $totalTables = count($tables);
        $volumeBytes = $volumeSizeKB * 1024;
        $volumeNum   = 1;
        $filePrefix  = date('YmdHis') . '_' . ($table ? $table : 'all');

        foreach ($tables as $idx => $tbl) {
            $this->_progress("備份資料表 `$tbl` ({$idx+1}/{$totalTables})", (int)(($idx / $totalTables) * 90));

            // 2.1 寫入資料表結構
            $this->sqlContent .= $this->_getTableStructure($tbl);

            // 2.2 寫入資料
            $result = $this->db->query("SELECT * FROM `$tbl`");
            if (!$result) {
                $this->_progress("讀取資料表 `$tbl` 失敗: {$this->db->error}", -1);
                continue;
            }

            $fields = $result->fetch_fields();
            $fieldNames = array_column($fields, 'name');
            $fieldCount = count($fieldNames);

            while ($row = $result->fetch_row()) {
                $this->sqlContent .= $this->_buildInsert($tbl, $fieldNames, $row);

                // 檢查是否超過分卷大小
                if (strlen($this->sqlContent) >= $volumeBytes) {
                    $this->_writeVolume($dir, $filePrefix, $volumeNum++);
                    $this->sqlContent = '';
                }
            }
            $result->free();
        }

        // 寫入最後一卷
        if ($this->sqlContent !== '') {
            $this->_writeVolume($dir, $filePrefix, $volumeNum);
        }

        $this->_progress("備份完成，共 $volumeNum 卷", 100);
    }

    /**
     * 還原資料庫（支援分卷）
     * @param string $sqlFile 第一卷檔案路徑（如 backup/20240101_all_v1.sql）
     */
    public function restore(string $sqlFile): void
    {
        if (!file_exists($sqlFile)) {
            throw new Exception("備份檔案不存在: $sqlFile");
        }

        $this->_progress("開始還原資料庫", 0);
        $this->db->query("LOCK TABLES `$this->database` WRITE"); // 簡易鎖定全庫

        $pathInfo = pathinfo($sqlFile);
        $basePath = $pathInfo['dirname'] . '/' . $pathInfo['filename']; // 去掉 .sql
        $basePath = preg_replace('/_v\d+$/', '', $basePath); // 移除 _vN 後綴

        $volumeNum = 1;
        $totalVolumes = $this->_countVolumes($basePath);
        $this->_progress("偵測到 $totalVolumes 卷備份檔", 5);

        while (true) {
            $volumeFile = "{$basePath}_v{$volumeNum}.sql";
            if (!file_exists($volumeFile)) {
                if ($volumeNum === 1) throw new Exception("找不到備份檔案: $volumeFile");
                break; // 所有卷處理完畢
            }

            $this->_progress("還原第 $volumeNum/$totalVolumes 卷: $volumeFile", 5 + (int)(($volumeNum / $totalVolumes) * 90));

            if (!$this->_importSqlFile($volumeFile)) {
                $this->db->query("UNLOCK TABLES");
                throw new Exception("第 $volumeNum 卷還原失敗，資料庫可能處於不一致狀態");
            }
            $volumeNum++;
        }

        $this->db->query("UNLOCK TABLES");
        $this->_progress("資料庫還原完成", 100);
    }

    // ---------- 內部核心方法 ----------

    private function _getDumpHeader(): string
    {
        $h = '';
        $h .= "-- MySQL Database Dump\n";
        $h .= "-- Generated by DbBackupRestore class\n";
        $h .= "-- Host: {$this->db->host_info}\n";
        $h .= "-- Date: " . date('Y-m-d H:i:s') . "\n";
        $h .= "-- MySQL Version: {$this->db->server_info}\n";
        $h .= "-- PHP Version: " . PHP_VERSION . "\n";
        $h .= "-- Database: `$this->database`\n";
        $h .= "-- Charset: $this->charset\n";
        $h .= "-- --------------------------------------------------------\n\n";
        return $h;
    }

    private function _getAllTables(): array
    {
        $tables = [];
        $res = $this->db->query("SHOW TABLES");
        while ($row = $res->fetch_row()) {
            $tables[] = $row[0];
        }
        return $tables;
    }

    private function _getTableStructure(string $table): string
    {
        $sql = "--\n-- Table structure for `$table`\n--\n\n";
        $sql .= "DROP TABLE IF EXISTS `$table`{$this->sqlEnd}";

        $res = $this->db->query("SHOW CREATE TABLE `$table`");
        $row = $res->fetch_row();
        $sql .= $row[1] . $this->sqlEnd . "\n";
        $sql .= "--\n-- Dumping data for table `$table`\n--\n\n";
        return $sql;
    }

    private function _buildInsert(string $table, array $fields, array $row): string
    {
        $values = [];
        foreach ($row as $val) {
            if ($val === null) {
                $values[] = 'NULL';
            } else {
                $values[] = "'" . $this->db->real_escape_string($val) . "'";
            }
        }
        return "INSERT INTO `$table` (`" . implode('`, `', $fields) . "`) VALUES (" . implode(', ', $values) . "){$this->sqlEnd}";
    }

    private function _writeVolume(string $dir, string $prefix, int $volumeNum): void
    {
        $filename = "{$prefix}_v{$volumeNum}.sql";
        $filepath = $dir . '/' . $filename;

        if (file_put_contents($filepath, $this->sqlContent) === false) {
            throw new Exception("寫入備份檔案失敗: $filepath");
        }
        $this->_progress("生成分卷檔案: $filename (" . $this->_formatBytes(strlen($this->sqlContent)) . ")", -1);
    }

    private function _countVolumes(string $basePath): int
    {
        $count = 0;
        $dir = dirname($basePath);
        $prefix = basename($basePath) . '_v';
        $files = glob($dir . '/' . $prefix . '*.sql');
        foreach ($files as $f) {
            if (preg_match('/_v(\d+)\.sql$/', $f, $m)) {
                $count = max($count, (int)$m[1]);
            }
        }
        return $count;
    }

    private function _importSqlFile(string $file): bool
    {
        $handle = fopen($file, 'rb');
        if (!$handle) return false;

        $buffer = '';
        $inCreate = false;
        $lineNum = 0;

        while (($line = fgets($handle)) !== false) {
            $lineNum++;
            $trimmed = trim($line);

            // 跳過註解與空行
            if ($trimmed === '' || str_starts_with($trimmed, '--')) continue;

            $buffer .= $line;

            // 偵測 CREATE TABLE 結尾（ENGINE=...）
            if (stripos($trimmed, 'ENGINE=') !== false) {
                $inCreate = false;
            }

            // 完整 SQL 語句以 ; 結尾且不在 CREATE 區塊中
            if (substr($trimmed, -1) === ';' && !$inCreate) {
                $sql = trim($buffer);
                $buffer = '';

                if ($sql !== '') {
                    if (!$this->db->query($sql)) {
                        fclose($handle);
                        $this->_progress("SQL 執行失敗 (line $lineNum): {$this->db->error}\nSQL: $sql", -1);
                        return false;
                    }
                }
            }

            // 偵測 CREATE TABLE 開始
            if (stripos($trimmed, 'CREATE TABLE') === 0) {
                $inCreate = true;
            }
        }

        fclose($handle);
        return true;
    }

    private function _progress(string $msg, int $percent): void
    {
        if ($this->onProgress) {
            ($this->onProgress)($msg, $percent);
        } else {
            // 預設輸出到瀏覽器（需 ob_implicit_flush）
            echo "" . ($percent >= 0 ? " ($percent%)" : "") . "\n";
            if (ob_get_level()) ob_flush(); flush();
        }
    }

    private function _formatBytes(int $bytes): string
    {
        $units = ['B', 'KB', 'MB', 'GB'];
        $i = 0;
        while ($bytes >= 1024 && $i < count($units)-1) {
            $bytes /= 1024;
            $i++;
        }
        return round($bytes, 2) . ' ' . $units[$i];
    }

    public function __destruct()
    {
        $this->db->query("UNLOCK TABLES");
        $this->db->close();
    }
}

使用範例

1. 完整備份（分卷 2MB）

<?php
require_once 'DbBackupRestore.php';

$db = new DbBackupRestore('localhost', 'root', 'password', 'my_database', 'utf8mb4');

// 設定進度回調（Web 環境）
$db->onProgress = function(string $msg, int $pct) {
    echo "
[{$pct}%] $msg
";
    ob_flush(); flush();
};

// 執行備份
$db->backup('', './backup/', 2048); // 全庫，2MB 分卷

2. 指定資料表備份

$db->backup('orders', './backup/', 1024); // 僅備份 orders 表，1MB 分卷

3. 還原（自動處理分卷）

<?php
require_once 'DbBackupRestore.php';

$db = new DbBackupRestore('localhost', 'root', 'password', 'my_database', 'utf8mb4');

$db->onProgress = function($msg, $pct) {
    echo "
[$pct%] $msg
";
    ob_flush(); flush();
};

try {
    $db->restore('./backup/20240115120000_all_v1.sql'); // 指定第一卷
    echo "
還原成功！
";
} catch (Exception $e) {
    echo "
還原失敗: {$e->getMessage()}
";
}

4. CLI 環境批次備份腳本

#!/usr/bin/php
<?php
// backup_cli.php
require_once 'DbBackupRestore.php';

$config = [
    'host' => 'localhost',
    'user' => 'backup_user',
    'pass' => 'strong_password',
    'db'   => 'production_db',
    'dir'  => '/var/backups/mysql/daily',
];

$db = new DbBackupRestore($config['host'], $config['user'], $config['pass'], $config['db']);
$db->onProgress = function($msg, $pct) {
    $time = date('H:i:s');
    echo "[$time] $msg\n";
};

$db->backup('', $config['dir'], 5120); // 5MB 分卷

// 刪除 7 天前備份
$files = glob($config['dir'] . '/*.sql');
foreach ($files as $f) {
    if (time() - filemtime($f) > 7*86400) unlink($f);
}

// 加入 crontab：0 2 * * * /usr/bin/php /path/backup_cli.php >> /var/log/db_backup.log 2>&1

遷移至 mysqldump 的建議

對於超大型資料庫（> 10GB），純 PHP 實作受限於記憶體、執行時間、單執行緒效能，建議改用原生 mysqldump 搭配管道分卷：

# 備份並分卷 100MB
mysqldump -u root -p --single-transaction --quick --routines --triggers db_name | split -b 100M - backup_$(date +%F)_part_.sql

# 還原
cat backup_2024-01-15_part_*.sql | mysql -u root -p db_name

PHP 類別適合中小型專案、Web 介面操作、需要自訂邏輯（如排除特定表、資料脫敏）的場景。

安全性與最佳實踐

結語

DbBackupRestore 類別提供了功能完整、代碼清晰、可直接投入生產的 MySQL 備份還原解決方案。分卷機制解決大檔案難題，進度回調支援 Web/CLI 雙環境，mysqli 預處理確保安全性。開發者可根據專案規模選擇繼續使用 PHP 實作，或遷移至原生 mysqldump 獲得極致效能。最重要的是：建立自動化備份策略、定期驗證還原、異地保存多份副本，才是資料安全的根本保障。