PHP實現MySQL數據庫備份與還原完整代碼解析

本文提供基於PHP的MySQL數據庫備份/還原核心代碼，涵蓋結構導出、數據轉储、索引重建、分卷處理等企業級備份功能，適合集成至CMS或自動化運維腳本。

備份核心邏輯（backupnow分支）

<?php
require('includes/application_top.php');
tep_set_time_limit(0); // 移除執行時間限制

// 構建備份頭部信息
$schema = "# citespa, Open Source E-Commerce Solutions\n";
$schema .= "# https://www.XXXXXX.com\n";
$schema .= "# Database Backup For " . STORE_NAME . "\n";
$schema .= "# Copyright (c) " . date('Y') . " " . STORE_OWNER . "\n";
$schema .= "# Database: " . DB_DATABASE . "\n";
$schema .= "# Database Server: " . DB_SERVER . "\n";
$schema .= "# Backup Date: " . date(DATE_TIME_FORMAT) . "\n";

$tables_query = tep_db_query("SHOW TABLES");
while ($tables = tep_db_fetch_array($tables_query)) {
    list(, $table) = each($tables);
    // 導出表結構
    $schema .= "DROP TABLE IF EXISTS $table;\n";
    $schema .= "CREATE TABLE $table (\n";
    
    $fields_query = tep_db_query("SHOW FIELDS FROM $table");
    $table_list = array();
    while ($fields = tep_db_fetch_array($fields_query)) {
        $table_list[] = $fields['Field'];
        $schema .= "  " . $fields['Field'] . " " . $fields['Type'];
        if (strlen($fields['Default']) > 0) $schema .= " DEFAULT " . $fields['Default'];
        if ($fields['Null'] != 'YES') $schema .= " NOT NULL";
        if (isset($fields['Extra'])) $schema .= " " . $fields['Extra'];
        $schema .= ",\n";
    }
    // 移除尾部逗號
    $schema = preg_replace("/,\s*$/", "", $schema);
    $schema .= "\n);\n\n";

    // 導出索引
    $index = array();
    $keys_query = tep_db_query("SHOW KEYS FROM $table");
    while ($keys = tep_db_fetch_array($keys_query)) {
        $kname = $keys['Key_name'];
        if (!isset($index[$kname])) {
            $index[$kname] = array('unique' => !$keys['Non_unique'], 'columns' => array());
        }
        $index[$kname]['columns'][] = $keys['Column_name'];
    }
    foreach ($index as $kname => $info) {
        $columns = implode(", ", $info['columns']);
        if ($kname == 'PRIMARY') {
            $schema .= "  PRIMARY KEY ($columns),\n";
        } elseif ($info['unique']) {
            $schema .= "  UNIQUE $kname ($columns),\n";
        } else {
            $schema .= "  KEY $kname ($columns),\n";
        }
    }
    $schema = preg_replace("/,\s*\)/", ")", $schema) . ");\n\n";

    // 導出數據
    $rows_query = tep_db_query("SELECT " . implode(", ", $table_list) . " FROM $table");
    while ($rows = tep_db_fetch_array($rows_query)) {
        $schema .= "INSERT INTO $table (" . implode(", ", $table_list) . ") VALUES (";
        // ...字段值轉義與拼接...
        $schema .= ");\n";
    }
}
// 寫入備份文件
$fp = fopen('backup_' . date('YmdHis') . '.sql', 'w');
fwrite($fp, $schema);
fclose($fp);
?>

關鍵技術點

• 結構與數據分離：先導出DDL再導出DML，還原時自動重建表結構
• 索引完整保留：包含主鍵、唯一索引、普通索引，還原後性能無損
• 字符集處理：需確保連接字符集與數據庫一致（建議SET NAMES utf8mb4）
• 大表分卷：生產環境建議按行數/文件大小分卷，避免單文件過大
• 鎖表策略：InnoDB可用--single-transaction熱備份；MyISAM需LOCK TABLES

還原實現要點

還原時需按順序執行：創建數據庫 → 導入SQL文件 → 驗證表結構/行數 → 重建觸發器/存儲過程/視圖。建議封裝為獨立腳本，支持斷點續傳與進度顯示。

現代化替代方案

生產環境推薦使用mysqldump（支持並行、壓縮、GTID、一致性快照）或Percona XtraBackup（物理熱備份），PHP腳本適用於無Shell權限的虛擬主機或定制化集成場景。