萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

ThinkPHP 數據庫備份功能實現(PDO 版)

ThinkPHP 框架本身未內建數據庫備份導出功能。本文提供一個基於 PDO 的 SqlAction 控制器方法,可導出指定數據庫的結構與數據為標準 SQL 文件,支援字符集、排序規則、觸發器/視圖/存儲過程(可選),並提供下載響應。

控制器代碼

<?php
namespace app\admin\controller;
use think\Controller;
use PDO;
use PDOException;

class Sql extends Controller {
    // 導出入口
    public function outsql() {
        header("Content-Type: text/html; charset=utf-8");

        // 1. 讀取數據庫配置
        $host = config('database.hostname');
        $dbname = config('database.database');
        $user = config('database.username');
        $password = config('database.password');
        $prefix = config('database.prefix') ?? '';

        // 2. 執行導出
        $sql = $this->outputSql($host, $dbname, $user, $password, $prefix, 1);

        // 3. 下載響應
        $filename = $dbname . '_' . date('Ymd_His') . '.sql';
        header("Content-Disposition: attachment; filename=\"{$filename}\"");
        header('Content-Type: application/sql; charset=utf-8');
        echo $sql;
        exit;
    }

    /**
     * 導出數據庫 SQL
     * @param string $host
     * @param string $dbname
     * @param string $user
     * @param string $password
     * @param string $prefix 表前綴(可選,僅導出該前綴表)
     * @param int $flag 0=僅結構, 1=結構+數據
     * @return string SQL 內容
     */
    private function outputSql($host, $dbname, $user, $password, $prefix = '', $flag = 1) {
        try {
            $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $user, $password, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true, // 大結果集緩衝
            ]);
        } catch (PDOException $e) {
            return "-- 連接失敗: " . $e->getMessage() . "\n";
        }

        $sql = "";

        // 0. 設置字符集
        $sql .= "SET NAMES utf8mb4;\n";
        $sql .= "SET FOREIGN_KEY_CHECKS = 0;\n\n";

        // 1. 獲取數據庫創建語句(含字符集、排序規則)
        $stmt = $pdo->query("SHOW CREATE DATABASE `$dbname`");
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($row && isset($row['Create Database'])) {
            // 提取字符集和排序規則
            preg_match('/DEFAULT CHARACTER SET\s+(\S+)/i', $row['Create Database'], $mCharset);
            preg_match('/COLLATE\s+(\S+)/i', $row['Create Database'], $mCollate);
            $charset = $mCharset[1] ?? 'utf8mb4';
            $collate = $mCollate[1] ?? 'utf8mb4_general_ci';
            $sql .= "DROP DATABASE IF EXISTS `$dbname`;\n";
            $sql .= "CREATE DATABASE `$dbname` DEFAULT CHARACTER SET $charset COLLATE $collate;\n";
            $sql .= "USE `$dbname`;\n\n";
        }

        // 2. 獲取所有表名(可按前綴過濾)
        $tables = [];
        $stmt = $pdo->query("SHOW TABLES");
        while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
            $table = $row[0];
            if ($prefix === '' || strpos($table, $prefix) === 0) {
                $tables[] = $table;
            }
        }

        // 3. 遍歷每張表導出結構和數據
        foreach ($tables as $table) {
            // 3.1 表結構
            $stmt = $pdo->query("SHOW CREATE TABLE `$table`");
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            $sql .= "DROP TABLE IF EXISTS `$table`;\n";
            $sql .= $row['Create Table'] . ";\n\n";

            // 3.2 表數據
            if ($flag == 1) {
                $stmt = $pdo->query("SELECT * FROM `$table`");
                $stmt->setFetchMode(PDO::FETCH_ASSOC);

                $firstRow = true;
                $columns = [];
                $batchSize = 500; // 批量 INSERT 行數
                $batchRows = [];

                while ($row = $stmt->fetch()) {
                    if ($firstRow) {
                        $columns = array_keys($row);
                        $firstRow = false;
                    }
                    // 轉義值
                    $values = array_map(function($v) use ($pdo) {
                        if ($v === null) return 'NULL';
                        if (is_int($v) || is_float($v)) return $v;
                        return $pdo->quote($v); // 含引號轉義
                    }, array_values($row));
                    $batchRows[] = '(' . implode(',', $values) . ')';

                    if (count($batchRows) >= $batchSize) {
                        $sql .= "INSERT INTO `$table` (`" . implode('`,`', $columns) . "`) VALUES\n";
                        $sql .= implode(",\n", $batchRows) . ";\n\n";
                        $batchRows = [];
                    }
                }
                // 最後一批
                if ($batchRows) {
                    $sql .= "INSERT INTO `$table` (`" . implode('`,`', $columns) . "`) VALUES\n";
                    $sql .= implode(",\n", $batchRows) . ";\n\n";
                }
            }
        }

        $sql .= "SET FOREIGN_KEY_CHECKS = 1;\n";
        return $sql;
    }
}

路由配置

// route.php
Route::get('admin/sql/outsql', 'admin/Sql/outsql');

使用方式

後台管理頁面放一個鏈接:<a href="/admin/sql/outsql">備份數據庫 (SQL)</a>,點擊直接下載 dbname_20240115_143000.sql。

功能特性

  • PDO 參數化:防注入,支援多種數據庫(只需換 DSN)。
  • 字符集保真:自動提取數據庫字符集/排序規則,避免導入後亂碼。
  • 外鍵檢查控制:導出前關閉、結束後開啟,避免導入順序報錯。
  • 批量 INSERT:每 500 行一條語句,平衡內存與導入速度。
  • 值轉義:使用 PDO::quote() 正確處理字符串、NULL、數字。
  • 表前綴過濾:多應用共庫時只備份本應用表。
  • 內存友好:游標逐行讀取,不將全表載入內存。

擴展建議

  1. 壓縮下載:大 SQL 文件用 gzencode($sql, 9) 輸出 .sql.gz,節省 70%+ 帶寬。
  2. 異步備份:大數據庫導出耗時長,改為後台任務(Queue Worker)生成文件存入 OSS,前端輪詢下載鏈接。
  3. 增量備份:結合 Binlog (Canal/Maxwell) 實現增量同步。
  4. 定時任務:Cron 每日自動備份並上傳至異地存儲。
  5. 驗證恢複:定期在測試環境導入備份文件,驗證可用性。
  6. 注意事項

    • 導出視圖/觸發器/存儲過程需額外查詢 SHOW CREATE VIEW/TRIGGER/PROCEDURE。
    • PDO::quote() 依賴當前連接字符集,確保連接時已 SET NAMES utf8mb4。
    • 大表導出可能超過 PHP max_execution_time,需 set_time_limit(0) 或異步化。
    • 生產環境建議從只讀從庫備份,避免鎖表影響主庫業務。

    此功能填補了 ThinkPHP 官方缺失的數據庫備份能力,代碼精簡、依賴少,可直接集成至現有後台管理模塊。

copyright © 萬盛學電腦網 all rights reserved