PHP 導出 MySQL 數據庫完整 SQL 備份腳本 (結構+數據)

功能特性

• 導出指定數據庫所有表結構 (CREATE TABLE) 含引擎、字符集、自增、註釋
• 導出所有表數據 (INSERT 語句) 含字段名、轉義處理
• 支援大數據量：設置 max_execution_time、分批處理
• 直接輸出下載 .sql 文件，適合定時備份/遷移

完整腳本 (db_backup.php)

<?php
ini_set('max_execution_time', 300);
ini_set('memory_limit', '512M');

/** 配置區 **/
$host     = 'localhost';
$dbname   = 'your_database';
$username = 'root';
$password = '';
$charset  = 'utf8mb4';
/************/

header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="' . $dbname . '_' . date('Ymd_His') . '.sql"');
header('Pragma: no-cache');
header('Expires: 0');

$mysqli = new mysqli($host, $username, $password, $dbname);
if ($mysqli->connect_errno) {
    die("連線失敗: {$mysqli->connect_error}");
}
$mysqli->set_charset($charset);

$crlf = "\r\n";
$output = "-- {$dbname} backup at " . date('Y-m-d H:i:s') . $crlf . $crlf;

// 禁用外鍵檢查
$output .= "SET FOREIGN_KEY_CHECKS = 0;{$crlf}{$crlf}";

$tables = $mysqli->query("SHOW TABLES");
while ($row = $tables->fetch_row()) {
    $table = $row[0];
    
    // 1. 表結構
    $create = $mysqli->query("SHOW CREATE TABLE `{$table}`")->fetch_row();
    $output .= "-- Table: {$table}{$crlf}";
    $output .= "DROP TABLE IF EXISTS `{$table}`;{$crlf}";
    $output .= $create[1] . ";{$crlf}{$crlf}";
    
    // 2. 表數據
    $result = $mysqli->query("SELECT * FROM `{$table}`");
    $fields = [];
    while ($finfo = $result->fetch_field()) {
        $fields[] = "`{$finfo->name}`";
    }
    $fieldList = implode(', ', $fields);
    
    $batch = 0;
    $batchSize = 500; // 批次提交大小
    $insertPrefix = "INSERT INTO `{$table}` ({$fieldList}) VALUES ";
    $values = [];
    
    while ($row = $result->fetch_row()) {
        $escaped = array_map(fn($v) => $v === null ? 'NULL' : "'" . $mysqli->real_escape_string($v) . "'", $row);
        $values[] = '(' . implode(', ', $escaped) . ')';
        $batch++;
        
        if ($batch >= $batchSize) {
            $output .= $insertPrefix . implode(', ', $values) . ";{$crlf}";
            $values = [];
            $batch = 0;
            // 定期刷新輸出緩衝，防止記憶體過大
            echo $output;
            $output = '';
        }
    }
    
    if ($values) {
        $output .= $insertPrefix . implode(', ', $values) . ";{$crlf}";
    }
    $output .= $crlf;
    
    // 釋放結果集
    $result->free();
}

$output .= "SET FOREIGN_KEY_CHECKS = 1;{$crlf}";
echo $output;
$mysqli->close();

關鍵技術點解析

進階增強建議

1. 壓縮輸出：header('Content-Encoding: gzip'); echo gzencode($output);
2. 並行導出：大表拆分多進程導出，再合併文件
3. 增量備份：記錄 binlog 位點，配合 mysqldump --master-data
4. 物理備份：Percona XtraBackup 熱備份不鎖表，生產環境首選
5. 驗證校驗和：導出後計算 SHA256，導入前校驗完整性

替代方案對比