您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 綜合知識

PHP 實現 MySQL 整庫備份生成 SQL 檔案完整方案

編輯：PHP 綜合知識

前言

資料庫備份是系統維護的核心環節。本文提供一個基於 PHP 的 MySQL 整庫備份腳本，支援結構與資料完整導出，生成標準 SQL 檔案，可直接用於還原或遷移。

核心功能特點

完整備份：包含資料表結構（引擎、字符集、自增值、註釋等）與所有資料
標準 SQL 格式：生成的檔案可直接在 MySQL 客戶端執行還原
記憶體友善：採用流式輸出，不佔用大量記憶體
自動下載：透過 HTTP Header 觸發瀏覽器下載

完整實作代碼

1. 基礎配置與初始化

<?php
ini_set("max_execution_time", "180"); // 防止大資料庫導出超時

$host = "";        // 資料庫主機
$dbname = "";      // 資料庫名稱
$username = "";    // 使用者名稱
$passw = "";       // 密碼

$filename = date("Y-m-d_H-i-s") . "-" . $dbname . ".sql";

// 設定下載 Header
header("Content-disposition: filename=" . $filename);
header("Content-type: application/octet-stream");
header("Pragma: no-cache");
header("Expires: 0");

2. 資料庫連線與備份主流程

$crlf = "\r\n";
$dbconn = mysql_connect($host, $username, $passw);
mysql_select_db($dbname, $dbconn);
mysql_query("SET NAMES 'utf8'");

$tables = mysql_list_tables($dbname, $dbconn);
$num_tables = @mysql_num_rows($tables);

echo "-- filename=" . $filename;

for ($i = 0; $i < $num_tables; $i++) {
    $table = mysql_tablename($tables, $i);
    echo $crlf;
    
    // 輸出資料表結構
    echo get_table_structure($dbname, $table, $crlf) . ";{$crlf}{$crlf}";
    
    // 輸出資料表資料
    echo get_table_content($dbname, $table, $crlf);
}

3. 取得完整資料表結構（核心改進）

使用 SHOW CREATE TABLE 直接獲取完整建表語句，保留引擎、字符集、自增值、註釋等完整資訊：

function get_table_structure($db, $table, $crlf) {
    global $drop;
    
    $schema_create = "";
    if (!empty($drop)) {
        $schema_create .= "DROP TABLE IF EXISTS `$table`;$crlf";
    }
    
    $result = mysql_db_query($db, "SHOW CREATE TABLE $table");
    $row = mysql_fetch_array($result);
    
    $schema_create .= $crlf . "-- " . $row[0] . $crlf;
    $schema_create .= $row[1] . $crlf;
    
    return $schema_create;
}

4. 取得資料表內容（資料導出）

function get_table_content($db, $table, $crlf) {
    $schema_create = "";
    $temp = "";
    
    $result = mysql_db_query($db, "SELECT * FROM $table");
    $i = 0;
    
    while ($row = mysql_fetch_row($result)) {
        $schema_insert = "INSERT INTO `$table` VALUES (";
        
        for ($j = 0; $j < mysql_num_fields($result); $j++) {
            if (!isset($row[$j])) {
                $schema_insert .= " NULL,";
            } elseif ($row[$j] != "") {
                $schema_insert .= " '" . addslashes($row[$j]) . "',";
            } else {
                $schema_insert .= " '',";
            }
        }
        
        $schema_insert = preg_replace("/,$/", "", $schema_insert);
        $schema_insert .= ");$crlf";
        $temp .= $schema_insert;
        $i++;
    }
    
    return $temp;
}

重要注意事項

1. 相容性警示

原代碼使用已棄用的 mysql_* 函數族群（PHP 5.5.0 棄用，PHP 7.0.0 移除）。生產環境必須遷移至 mysqli 或 PDO。

2. 現代化重構建議

// 使用 PDO 版本片段
$pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $passw);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);

foreach ($tables as $table) {
    // 結構
    $stmt = $pdo->query("SHOW CREATE TABLE `$table`");
    $row = $stmt->fetch(PDO::FETCH_NUM);
    echo $row[1] . ";\n\n";
    
    // 資料
    $stmt = $pdo->query("SELECT * FROM `$table`");
    while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
        $values = array_map(function($v) use ($pdo) {
            return $v === null ? 'NULL' : $pdo->quote($v);
        }, $row);
        echo "INSERT INTO `$table` VALUES (" . implode(',', $values) . ");\n";
    }
}

3. 大資料庫優化策略

分批導出：使用 LIMIT offset, count 分頁查詢
壓縮輸出：配合 gzip 壓縮 SQL 檔案
異步處理：大型資料庫建議使用隊列系統背景執行
權限最小化：備份帳號僅需 SELECT, LOCK TABLES, SHOW VIEW 權限

替代方案推薦

工具	適用場景	優點
mysqldump	生產環境標準備份	官方工具、效能最佳、支援熱備份、壓縮、單一交易
Percona XtraBackup	大型線上資料庫	實體熱備份、不鎖表、增量備份、備份速度極快
PHP 腳本	無 Shell 存取權限的虛擬主機	純 PHP 實作、透過 HTTP 下載、易於整合至後台管理