PHP 實現 MySQL 數據庫導出 .sql 文件：模擬 phpMyAdmin 完整備份腳本

功能特性

• 導出所有表結構（`CREATE TABLE`）含索引、外鍵、自增值
• 導出所有數據（`INSERT INTO ... VALUES (...),(...),...` 批量插入）
• 自動處理特殊字符轉義（反斜線、引號、NULL、換行）
• 支援大表分批寫入，避免內存溢出
• 生成標準 SQL 文件，可直接導入 MySQL/MariaDB/Percona

核心代碼（PDO 版，推薦）

<?php
// backup.php
set_time_limit(0);
ini_set('memory_limit', '512M');

$dbConfig = [
    'host' => 'localhost',
    'dbname' => 'my_database',
    'user' => 'root',
    'pass' => 'password',
    'charset' => 'utf8mb4',
];

$outputFile = __DIR__ . '/backup_' . date('Ymd_His') . '.sql';
$batchRows = 500; // 每批 INSERT 行數

try {
    $pdo = new PDO(
        "mysql:host={$dbConfig['host']};dbname={$dbConfig['dbname']};charset={$dbConfig['charset']}",
        $dbConfig['user'],
        $dbConfig['pass'],
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false] // 非緩衝查詢，大表節省內存
    );
} catch (PDOException $e) {
    die('DB 連接失敗：' . $e->getMessage());
}

$fp = fopen($outputFile, 'wb');
if (!$fp) die('無法創建輸出文件');

// 1. 文件頭
$header = "-- MySQL Backup Generated by PHP Script\n";
$header .= "-- Date: " . date('Y-m-d H:i:s') . "\n";
$header .= "-- Database: `{$dbConfig['dbname']}`\n";
$header .= "-- --------------------------------------------------\n\n";
$header .= "SET NAMES {$dbConfig['charset']};\n";
$header .= "SET FOREIGN_KEY_CHECKS = 0;\n";
$header .= "SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';\n";
$header .= "SET time_zone = '+00:00';\n\n";
fwrite($fp, $header);

// 2. 獲取所有表名
$tables = $pdo->query("SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'")->fetchAll(PDO::FETCH_COLUMN);

foreach ($tables as $table) {
    echo "Processing: $table ...\n";

    // 2.1 表結構
    $createStmt = $pdo->query("SHOW CREATE TABLE `{$table}`")->fetch(PDO::FETCH_ASSOC);
    $createSql = $createStmt['Create Table'] . ";\n\n";
    fwrite($fp, "-- --------------------------------------------------\n");
    fwrite($fp, "-- Table structure for `{$table}`\n");
    fwrite($fp, "-- --------------------------------------------------\n");
    fwrite($fp, "DROP TABLE IF EXISTS `{$table}`;\n");
    fwrite($fp, $createSql);

    // 2.2 表數據
    $countStmt = $pdo->query("SELECT COUNT(*) FROM `{$table}`");
    $totalRows = (int)$countStmt->fetchColumn();
    if ($totalRows === 0) continue;

    fwrite($fp, "-- --------------------------------------------------\n");
    fwrite($fp, "-- Records for `{$table}`\n");
    fwrite($fp, "-- --------------------------------------------------\n");

    // 獲取字段列表（用於顯式指定 INSERT 列，防止表結構變更導致錯誤）
    $colsStmt = $pdo->query("SHOW COLUMNS FROM `{$table}`");
    $columns = $colsStmt->fetchAll(PDO::FETCH_COLUMN);
    $colList = '`' . implode('`,`', $columns) . '`';

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

    $batch = [];
    $rowCount = 0;
    while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
        $rowCount++;
        // 轉義每個值
        $escaped = [];
        foreach ($row as $val) {
            if ($val === null) {
                $escaped[] = 'NULL';
            } elseif (is_bool($val)) {
                $escaped[] = $val ? 'TRUE' : 'FALSE';
            } elseif (is_int($val) || is_float($val)) {
                $escaped[] = $val;
            } else {
                // 字符串：使用 PDO::quote 自動處理引號、反斜線、換行等
                $escaped[] = $pdo->quote($val);
            }
        }
        $batch[] = '(' . implode(',', $escaped) . ')';

        // 批量寫入
        if (count($batch) >= $batchRows) {
            fwrite($fp, "INSERT INTO `{$table}` ({$colList}) VALUES\n" . implode(",\n", $batch) . ";\n\n");
            $batch = [];
        }
    }

    // 剩餘批次
    if ($batch) {
        fwrite($fp, "INSERT INTO `{$table}` ({$colList}) VALUES\n" . implode(",\n", $batch) . ";\n\n");
    }

    echo "  -> {$rowCount} rows exported.\n";
}

// 3. 文件尾
$footer = "SET FOREIGN_KEY_CHECKS = 1;\n";
fwrite($fp, $footer);
fclose($fp);

echo "\nBackup completed: $outputFile (" . number_format(filesize($outputFile)) . " bytes)\n";
?>

關鍵技術點解析

1. 使用 `PDO::quote()` 而非手工 `addslashes()`

`PDO::quote()` 根據當前連接字符集、SQL 模式自動處理：

• 字符串加單引號
• 單引號轉義為 `''`（標準 SQL）或 `\'`（取決於 `NO_BACKSLASH_ESCAPES`）
• 反斜線、換行符、控制字符正確轉義
• 二進制數據自動轉為十六進制 `0x...` 或 `X'...'`

手工 `addslashes()` 只處理反斜線、單雙引號，**無法正確處理多字節字符集（GBK/UTF8MB4）下的邊界字節**，極易導致 SQL 注入或導入錯誤。

2. 非緩衝查詢 `PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false`

默認 PDO 會將整個結果集緩存在 PHP 內存中。大表（百萬行）會導致 OOM。非緩衝模式逐行從 MySQL 服務器拉取，**內存佔用恆定**，但要求：

• 同一連接同一時間只能有一個活躍非緩衝查詢
• 需在讀取完畢前不執行其他查詢（本腳本滿足）

3. 批量 INSERT 優化

單條 `INSERT` 執行極慢（每條需解析、事務、索引更新）。批量 `INSERT ... VALUES (...),(...),...` 可將速度提升 **10-50 倍**。本腳本每 500 行刷新一次，平衡內存與性能。

4. 顯式列名 `INSERT INTO \`t\` (\`a\`,\`b\`) VALUES ...`

防止表結構變更（增刪列、調整順序）導致導入錯誤或數據錯位。

5. 外鍵檢查開關

`SET FOREIGN_KEY_CHECKS = 0/1` 允許任意順序建表導入，避免外鍵依賴順序問題。

進階：壓縮輸出、分卷、上傳雲存儲

// 1. 直接輸出 gz 壓縮流
$fp = gzopen($outputFile . '.gz', 'wb9'); // 9 級壓縮
// 後續 fwrite($fp, ...) 不變
gzclose($fp);

// 2. 分卷（單文件 > 100MB 自動分割）
// 需自行實現：檢查 ftell($fp) > 100MB 時關閉當前、創建 part2.sql...

// 3. 完成後上傳至 S3/OSS/COS
// 使用 AWS SDK / 阿里云 SDK / 騰訊云 SDK
// 建議異步隊列處理，避免阻塞 Web 請求

定時備份部署建議

1. Linux Cron：`0 2 * * * /usr/bin/php /path/backup.php >> /var/log/mysql_backup.log 2>&1`（每天凌晨 2 點）
2. Windows 任務計劃程序：操作 → 啟動程序 → `php.exe`，參數 `C:\path\backup.php`
3. 保留策略：腳本末尾或單獨腳本刪除 7/30 天前備份：`find /backup -name '*.sql*' -mtime +30 -delete`
4. 異地備份：必須將備份文件同步至異地存儲（對象存儲、異地服務器、NAS），單機備份無防災能力。

常見問題 FAQ