使用PHP读取MySQL中配置信息并自动批量生成Nginx反向代理配置

前言

又造了个轮子....用来给SHPC用的，后续开通新的SHPC时不用再调用API操作Caddy了，直接Nginx搞定。
简单描述一下流程：
1. 支付回调确认付款成功，系统自动开通SHPC容器，这时候需要做一个反向代理，将客户的rstudio和jupyterhub暴露给公网访问。
2. 系统将需要反代的信息写入MySQL数据库。
3. 定时任务每5分钟执行一次PHP脚本，脚本读取MySQL中的配置并生成对应Nginx的conf文件。
4. 确认生成成功后Nginx重载配置（Reload）

代码

首先是MySQL部分：

CREATE TABLE `reverse_proxies` (
  `id` int UNSIGNED NOT NULL,
  `hostname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `listen_port` smallint UNSIGNED NOT NULL DEFAULT '80',
  `protocol` enum('HTTP','HTTPS') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'HTTP',
  `proxy_address` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  `proxy_hostname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `enable_websocket` tinyint(1) NOT NULL DEFAULT '0',
  `metadata` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `reverse_proxies`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_hostname_port` (`hostname`,`listen_port`);
  
ALTER TABLE `reverse_proxies`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
COMMIT;

然后是PHP部分

<?php
/**
 * Script to generate/update Nginx reverse proxy configuration based on MySQL table entries.
 * 
 * Usage: php update_nginx_config.php
 */

// Database configuration
$dbHost = 'localhost';          // Database host
$dbName = 'reverse_proxies'; // Database name
$dbUser = 'reverse_proxies';      // Database username
$dbPass = 'XXXXXXXXXXX';      // Database password

// Nginx configuration file path
$nginxConfigPath = './0.auto-reverse-proxy.conf';

// Nginx configuration header
$nginxConfigHeader = <<<EOL
# Auto-generated reverse proxy configuration
# Generated on: {date}

EOL;

// Function to escape variables for Nginx
function escape_nginx($string) {
    return addcslashes($string, '\\"');
}

try {
    // Establish a PDO connection
    $dsn = "mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4";
    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // Enable exceptions
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // Fetch associative arrays
        PDO::ATTR_EMULATE_PREPARES   => false,                  // Disable emulation
    ];
    $pdo = new PDO($dsn, $dbUser, $dbPass, $options);

    // Fetch all reverse proxy entries
    $stmt = $pdo->query("SELECT hostname, listen_port, protocol, proxy_address, proxy_hostname, enable_websocket FROM reverse_proxies");
    $reverseProxies = $stmt->fetchAll();

    // Initialize the configuration content
    $configContent = $nginxConfigHeader;
    $configContent = str_replace('{date}', date('Y-m-d H:i:s'), $configContent);

    // Generate Nginx server blocks
    foreach ($reverseProxies as $proxy) {
        // Sanitize and escape variables
        $hostname = escape_nginx($proxy['hostname']);
        $listenPort = (int)$proxy['listen_port'];
        $protocol = strtoupper($proxy['protocol']);
        $proxyAddress = escape_nginx($proxy['proxy_address']);
        $proxyHostname = escape_nginx($proxy['proxy_hostname']);
        $enableWebsocket = (bool)$proxy['enable_websocket'];

        // Initialize listen directives
        $listenDirectives = '';
        $sslConfig = '';
        $websocketConfig = '';

        if ($protocol === 'HTTPS') {
            // For HTTPS, include both HTTP and HTTPS listen directives
            $listenDirectives = "listen 80;\n    listen [::]:80;\n    listen 443 ssl;\n    listen [::]:443 ssl;";
            
            // Paths to SSL certificate and key files
            // Ensure these paths are correct and certificates exist
            $sslCertPath = "/www/server/panel/vhost/cert/cloudraft.cn/fullchain.pem";
            $sslKeyPath = "/www/server/panel/vhost/cert/cloudraft.cn/privkey.pem";

            $sslConfig = <<<EOL

    ssl_certificate     {$sslCertPath};
    ssl_certificate_key {$sslKeyPath};
    ssl_protocols       TLSv1.2 TLSv1.3;
    ssl_ciphers         HIGH:!aNULL:!MD5;
    ssl_prefer_server_ciphers on;

EOL;
        } else { // HTTP
            // For HTTP, only include the HTTP listen directive
            $listenDirectives = "listen {$listenPort};\n    listen [::]:{$listenPort};";
        }
        
        // WebSocket Configuration (if enabled)
        if ($enableWebsocket) {
            $websocketConfig = <<<EOL

        # WebSocket support
        proxy_http_version 1.1;
        proxy_set_header Upgrade \$http_upgrade;
        proxy_set_header Connection "upgrade";

EOL;
        }

        // Construct the server block
        $serverBlock = <<<EOL
server {
    {$listenDirectives}
    server_name {$hostname};
    root /www/wwwroot/router-sh-bgp.cloudraft.cn/reverse-proxy;

{$sslConfig}
    location / {
        proxy_pass {$proxyAddress};
        proxy_set_header Host {$proxyHostname};
        proxy_set_header X-Real-IP \$remote_addr;
        proxy_set_header X-Forwarded-For \$proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto \$scheme;
    }
}

EOL;

        // Append the server block to the configuration content
        $configContent .= $serverBlock;
    }

    // Write the configuration to the file
    if (file_put_contents($nginxConfigPath, $configContent) === false) {
        throw new Exception("Failed to write to Nginx configuration file: {$nginxConfigPath}");
    }

    echo "Nginx configuration successfully updated.\n";

    // Optionally, reload Nginx to apply changes
    // Uncomment the following lines if you want the script to reload Nginx automatically
    /*
    $reloadOutput = [];
    $reloadStatus = 0;
    exec('sudo systemctl reload nginx', $reloadOutput, $reloadStatus);
    if ($reloadStatus !== 0) {
        throw new Exception("Failed to reload Nginx. Output: " . implode("\n", $reloadOutput));
    }
    echo "Nginx reloaded successfully.\n";
    */

} catch (PDOException $e) {
    // Handle database connection errors
    error_log("Database error: " . $e->getMessage());
    exit("Database error occurred. Check logs for details.\n");
} catch (Exception $e) {
    // Handle general errors
    error_log("Error: " . $e->getMessage());
    exit("An error occurred. Check logs for details.\n");
}
?>

最后是定时任务：

sudo -u root bash -c 'curl https://router-sh-bgp.cloudraft.cn/reverse-proxy/update.php && /etc/init.d/nginx reload'