您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

PHP MySQL 錯誤日誌記錄最佳實踐：結構化、安全、可觀測

編輯：MySQL 資料庫相關知識

原始代碼問題分析


function error() {
    $fp = @fopen("error.dat", "a+");
    $time = date("Y-m-d H:i:s");
    $msg = "{VisitedTime:$time}\t{VisiterIP:$_SERVER[REMOTE_ADDR]}\t{Errormsg:".mysql_error()."}\t{File:->$_SERVER[PHP_SELF]}\t{User:$_SESSION[user]}\r\n";
    @fputs($fp, $msg);
    @fclose($fp);
}

痛點

使用已棄用 mysql_* 擴展 (PHP 7.0+ 移除)。
手工拼接文本格式，難以解析、查詢、告警。
@ 抑制錯誤，掩蓋寫入失敗 (磁盤滿、權限)。
直接暴露 $_SERVER/$_SESSION 敏感信息，無脫敏。
單文件無輪轉，無限增長導致磁盤爆滿。

現代化重構：Monolog + 結構化 JSON + 日誌輪轉

1. Composer 依賴


composer require monolog/monolog

2. 錯誤日誌服務類


setFormatter(new JsonFormatter(JsonFormatter::BATCH_MODE_JSON, true));
        
        $this->logger = new Logger('mysql_errors');
        $this->logger->pushHandler($handler);
        // 自動注入請求上下文 (URL、IP、Method、Session ID 等)
        $this->logger->pushProcessor(new WebProcessor());
        $this->logger->pushProcessor(new MemoryUsageProcessor());
        $this->logger->pushProcessor(new PsrLogMessageProcessor());
    }

    public function logError(Throwable $e, \PDO $pdo = null, array $context = []): void
    {
        $errorInfo = $pdo ? $pdo->errorInfo() : ['driver' => 'unknown'];
        
        $record = array_merge([
            'event' => 'mysql_error',
            'error_code' => $errorInfo[0] ?? 'HY000',
            'error_sqlstate' => $errorInfo[1] ?? '',
            'error_message' => $e->getMessage(),
            'error_file' => $e->getFile(),
            'error_line' => $e->getLine(),
            'error_trace' => $e->getTraceAsString(),
            'sql_query' => $context['query'] ?? null,
            'sql_params' => $this->sanitizeParams($context['params'] ?? []),
            'db_host' => $context['db_host'] ?? null,
            'db_name' => $context['db_name'] ?? null,
        ], $context);

        $this->logger->error($e->getMessage(), $record);
    }

    private function sanitizeParams(array $params): array
    {
        // 脫敏：密碼、Token、身份證、手機號等
        $sensitiveKeys = ['password', 'passwd', 'pwd', 'token', 'secret', 'idcard', 'mobile', 'phone', 'ssn'];
        foreach ($params as $k => $v) {
            if (in_array(strtolower($k), $sensitiveKeys, true)) {
                $params[$k] = '***REDACTED***';
            }
        }
        return $params;
    }
}

3. 在 PDO 異常捕獲中調用


try {
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);
    $stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
    $stmt->execute([$email]);
} catch (PDOException $e) {
    $logger = new \App\Logging\MysqlErrorLogger();
    $logger->logError($e, $pdo, [
        'query' => $stmt->queryString ?? 'unknown',
        'params' => [$email],
        'db_host' => $config['db_host'],
        'db_name' => $config['db_name'],
        'user_id' => $_SESSION['user_id'] ?? null,
    ]);
    // 用戶友好提示，不暴露細節
    throw new \RuntimeException('數據庫暫時不可用，請稍後重試');
}

日誌輸出樣例 (JSON Lines)


{"datetime":"2024-01-15T14:30:45.123+08:00","channel":"mysql_errors","level":40,"level_name":"ERROR","message":"SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'test@test.com' for key 'users.email_unique'","context":{"event":"mysql_error","error_code":"23000","error_sqlstate":"23000","error_message":"SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'test@test.com' for key 'users.email_unique'","error_file":"/var/www/html/app/Repository/UserRepository.php","error_line":42,"error_trace":"#0 ...","sql_query":"SELECT * FROM users WHERE email = ?","sql_params":["test@test.com"],"db_host":"db-master","db_name":"app_prod","user_id":12345,"url":"https://app.example.com/register","ip":"203.0.113.42","http_method":"POST","referrer":"https://app.example.com/","memory_usage":8.5}

可觀測性增強：ELK / Loki / ClickHouse 接入

Filebeat / Promtail 采集 JSON 日誌 → Elasticsearch / Loki 索引。
Kibana / Grafana 儀表板：按 error_code、db_host、sql_query 關鍵字聚合、趨勢、Top N。
告警規則：error_code IN ('HY000','08004','40001') AND rate > 5/min → PagerDuty/企業微信/釘釘。