数据库连接器

ilert MCP Connector

A HTTP server that exposes information from internal tools (Kafka, MySQL, PostgreSQL, ClickHouse, Redis) via REST endpoints. Designed to be used with ilert and MCP servers.

Features

• Health and Readiness Endpoints: Kubernetes-ready with /health and /ready endpoints
• Kafka Integration: List topics, describe topics, manage consumer groups, and monitor consumer lag
• MySQL Integration: Query databases, tables, schemas, and metrics
• PostgreSQL Integration: Query databases, tables, schemas, and metrics
• ClickHouse Integration: Query databases, tables, schemas, and metrics
• Redis Integration: Query databases, scan keys, get key info, and monitor metrics
• Configurable: Enable/disable tools individually via environment variables
• Prometheus Metrics: /metrics endpoint for monitoring (no authentication required)
• Structured Logging: Uses zerolog with configurable log levels and formats
• Docker Support: Containerized and ready for Kubernetes deployment

Endpoints

Health & Readiness

• GET /health - Health check endpoint (Public - No Auth Required)
• GET /ready - Readiness check endpoint (verifies connections to enabled tools) - Requires Authentication
• GET /version - Version information endpoint (returns version and commit) (Public - No Auth Required)
• GET /metrics - Prometheus metrics endpoint (Public - No Auth Required)

Authentication

The following endpoints require authentication via the Authorization header:

• /ready - Readiness check endpoint (contains connection status and potential error messages)
• All /kafka/* endpoints
• All /mysql/* endpoints
• All /postgres/* endpoints
• All /clickhouse/* endpoints
• All /redis/* endpoints

The token can be provided in two ways:

1. Environment Variable: Set AUTH_TOKEN environment variable with your desired token
2. Auto-generated: If AUTH_TOKEN is not set, a random 64-character hex token will be generated and logged on startup

Usage:

# Using Bearer token format
curl -H "Authorization: Bearer YOUR_TOKEN" http://localhost:8383/kafka/topics

# Or without Bearer prefix
curl -H "Authorization: YOUR_TOKEN" http://localhost:8383/kafka/topics

Kafka Endpoints (when KAFKA_ENABLED=true) - Requires Authentication

• GET /kafka/topics - List all Kafka topics
• GET /kafka/topics/:topic - Describe a specific topic
• GET /kafka/consumers - List all consumer groups
• GET /kafka/consumers/:group - Describe a specific consumer group
• GET /kafka/consumers/:group/lag - Get consumer lag for a consumer group

MySQL Endpoints (when MYSQL_ENABLED=true) - Requires Authentication

• GET /mysql/databases - List all databases
• GET /mysql/databases/:database/tables - List tables in a database
• GET /mysql/databases/:database/tables/:table - Describe table schema
• GET /mysql/metrics - Get MySQL metrics (InnoDB stats, global status, variables)

PostgreSQL Endpoints (when POSTGRES_ENABLED=true) - Requires Authentication

• GET /postgres/databases - List all databases
• GET /postgres/databases/:database/tables - List tables in a database (returned as schema.table)
• GET /postgres/databases/:database/tables/:table - Describe table schema with columns and table statistics (:table accepts schema.table, bare names default to the public schema)
• GET /postgres/metrics - Get PostgreSQL metrics:
  • activity - current sessions from pg_stat_activity
  • databaseStats - per-database stats from pg_stat_database (transactions, cache hit ratio, deadlocks, temp files)
  • settings - curated configuration values from pg_settings
  • replication - pg_stat_replication replicas, recovery status, replay lag
  • connections - connection counts per client application_name (with per-state breakdown), per user, per database, per state, plus max_connections headroom
  • locks - total/ungranted lock counts and blocked queries with blocking PIDs
  • bgwriter - checkpoint and buffer statistics (supports both pre- and post-PG17 catalogs)
  • vacuum - top tables by dead tuples and oldest transaction ID age
  • sessions - long-running queries and idle-in-transaction sessions exceeding a threshold (default 60s, override with ?session-threshold=<seconds>)

Note: PostgreSQL does not support cross-database queries over a single connection. When :database differs from POSTGRES_DATABASE, the connector opens a short-lived connection to that database (the configured user needs CONNECT privilege on it).

ClickHouse Endpoints (when CLICKHOUSE_ENABLED=true) - Requires Authentication

• GET /clickhouse/databases - List all databases
• GET /clickhouse/databases/:database/tables - List tables in a database
• GET /clickhouse/databases/:database/tables/:table - Describe table schema with columns and table info
• GET /clickhouse/metrics - Get ClickHouse state and critical metrics (system.metrics, system.events, system.asynchronous_metrics, replicas, processes, merges, mutations)

Redis Endpoints (when REDIS_ENABLED=true) - Requires Authentication

• GET /redis/databases - List all Redis databases
• GET /redis/databases/:database/keys - Scan keys in a database (supports pattern and cursor query parameters)
• GET /redis/databases/:database/keys/:key/info - Get detailed information about a specific key (type, TTL, size, encoding)
• GET /redis/databases/:database/values - Read the values of multiple string keys (keys = JSON array, max 20; value capped at 1KB). Requires REDIS_ENABLE_SENSITIVE_TOOLS=true.
• GET /redis/info - Get Redis INFO output as key-value pairs
• GET /redis/metrics - Get Redis metrics (parsed from INFO command)

Configuration

Configuration is done via environment variables:

Server Configuration

• PORT - Server port (default: 8383)
• LOG_LEVEL - Log level: debug, info, warn, error, fatal, panic (default: info)
• LOG_FORMAT - Log format: json for JSON output, or empty/any other value for pretty console output (default: pretty console)
• AUTH_TOKEN - Authentication token for protecting endpoints. If not set, a random token will be generated and logged on startup (default: auto-generated)

Kafka Configuration

• KAFKA_ENABLED - Enable Kafka endpoints (default: false)
• KAFKA_BROKERS - Comma-separated list of Kafka brokers (default: localhost:9092)
• KAFKA_CLIENT_ID - Kafka client ID (default: ilert-mcp-connector)
• KAFKA_AUTH_TYPE - Authentication type: NONE, PLAIN, SCRAM-SHA-256, SCRAM-SHA-512, SSL, SASL_SSL (default: NONE). Case-insensitive, but uppercase is recommended.
  • If KAFKA_USERNAME and KAFKA_PASSWORD are provided but KAFKA_AUTH_TYPE is not set, it defaults to PLAIN
• KAFKA_USERNAME - Kafka username for SASL authentication (required for PLAIN, SCRAM-SHA-256, SCRAM-SHA-512, SASL_SSL)
• KAFKA_PASSWORD - Kafka password for SASL authentication (required for PLAIN, SCRAM-SHA-256, SCRAM-SHA-512, SASL_SSL)
• KAFKA_SSL_CERT_LOCATION - Path to client certificate file (required for SSL and SASL_SSL)
• KAFKA_SSL_KEY_LOCATION - Path to client private key file (required for SSL and SASL_SSL)
• KAFKA_SSL_CA_LOCATION - Path to CA certificate file (optional, recommended)
• KAFKA_SSL_KEY_PASSWORD - Password for encrypted private key (optional)
• KAFKA_SSL_INSECURE_SKIP_TLS - Skip TLS certificate verification (default: false, not recommended for production)
• KAFKA_LOG_LEVEL - librdkafka log level: 0 (emergency) to 7 (debug) (default: 6 - info). librdkafka logs are integrated with zerolog and appear in the same format as application logs.

MySQL Configuration

• MYSQL_ENABLED - Enable MySQL endpoints (default: false)
• MYSQL_HOST - MySQL host (default: localhost)
• MYSQL_PORT - MySQL port (default: 3306)
• MYSQL_USER - MySQL user (default: root)
• MYSQL_PASSWORD - MySQL password (default: empty)
• MYSQL_DATABASE - MySQL database name (default: empty)

PostgreSQL Configuration

• POSTGRES_ENABLED - Enable PostgreSQL endpoints (default: false)
• POSTGRES_HOST - PostgreSQL host (default: localhost)
• POSTGRES_PORT - PostgreSQL port (default: 5432)
• POSTGRES_USER - PostgreSQL user (default: postgres)
• POSTGRES_PASSWORD - PostgreSQL password (default: empty)
• POSTGRES_DATABASE - PostgreSQL database name (default: postgres)
• POSTGRES_SSLMODE - PostgreSQL SSL mode: disable, require, verify-ca, verify-full (default: disable)

PostgreSQL User Setup

The connector only reads from system catalogs and statistics views. A regular user sees its own sessions in pg_stat_activity; to see all sessions, full query text, and replication details, grant the monitoring role:

CREATE USER mcp_connector WITH PASSWORD 'your_password';
GRANT pg_monitor TO mcp_connector;
GRANT CONNECT ON DATABASE your_database TO mcp_connector;

pg_monitor (PostgreSQL 10+) covers pg_stat_activity, pg_stat_replication, pg_stat_database, pg_stat_bgwriter/pg_stat_checkpointer, and pg_settings. CONNECT is needed on every database that should be reachable via the :database path parameter.

MySQL User Setup

The MCP connector requires a MySQL user with specific read-only privileges. The connector performs the following operations:

• SHOW DATABASES - List all databases
• SHOW TABLES FROM <database> - List tables in a database
• DESCRIBE <database>.<table> - Get table schema
• SHOW TABLE STATUS - Get table metadata
• SHOW STATUS / SHOW GLOBAL STATUS - Get MySQL status variables
• SHOW VARIABLES / SHOW GLOBAL VARIABLES - Get MySQL configuration variables
• SHOW PROCESSLIST - View current queries and connections
• SHOW SLAVE STATUS / SHOW MASTER STATUS - View replication status
• SHOW ENGINE INNODB STATUS - View InnoDB status and lock information

Required Privileges:

The user needs the following privileges:

1. PROCESS - Required for SHOW PROCESSLIST and SHOW ENGINE INNODB STATUS
2. REPLICATION CLIENT - Required for SHOW SLAVE STATUS and SHOW MASTER STATUS
3. SELECT - Required for SHOW DATABASES, SHOW TABLES, DESCRIBE, and SHOW TABLE STATUS on databases you want to query

Note: The following operations work WITHOUT additional privileges:

• SHOW STATUS / SHOW GLOBAL STATUS - works for all users
• SHOW VARIABLES / SHOW GLOBAL VARIABLES - works for all users

Important: SHOW DATABASES only shows databases that the user has privileges on. To see and query databases, you need to grant SELECT privilege on them.

Example SQL Script:

-- Create the user
CREATE USER IF NOT EXISTS 'ilert_mcp_connector'@'%' IDENTIFIED BY 'your_secure_password_here';

-- Grant global privileges for monitoring
GRANT PROCESS ON *.* TO 'ilert_mcp_connector'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'ilert_mcp_connector'@'%';

-- Grant SELECT on all databases (read-only access)
-- Option 1: Grant on all databases
GRANT SELECT ON *.* TO 'ilert_mcp_connector'@'%';

-- Option 2: Grant on specific databases only (more secure)
-- GRANT SELECT ON `database1`.* TO 'ilert_mcp_connector'@'%';
-- GRANT SELECT ON `database2`.* TO 'ilert_mcp_connector'@'%';

-- Apply changes
FLUSH PRIVILEGES;

-- Verify grants
SHOW GRANTS FOR 'ilert_mcp_connector'@'%';

Security Note:

• SELECT privilege is read-only - the user cannot modify data
• For maximum security, grant SELECT only on specific databases you need to monitor
• If you need to monitor all databases, use GRANT SELECT ON *.*

ClickHouse Configuration

• CLICKHOUSE_ENABLED - Enable ClickHouse endpoints (default: false)
• CLICKHOUSE_HOST - ClickHouse host (default: localhost)
• CLICKHOUSE_PORT - ClickHouse port (default: 9000)
• CLICKHOUSE_USER - ClickHouse user (default: default)
• CLICKHOUSE_PASSWORD - ClickHouse password (default: empty)
• CLICKHOUSE_DATABASE - ClickHouse database name (default: default)

Redis Configuration

• REDIS_ENABLED - Enable Redis endpoints (default: false)
• REDIS_HOST - Redis host (default: localhost)
• REDIS_PORT - Redis port (default: 6379)
• REDIS_PASSWORD - Redis password (default: empty)
• REDIS_ENABLE_SENSITIVE_TOOLS - Enable sensitive tools that read stored key values, e.g. GET /redis/databases/:database/values (default: false)

Building

Using Make (Recommended)

# Build the application
make build

# Build and start the server
make start

# Run the application (without building)
make run

# Show all available commands
make help

Manual Build

go build -o ilert-mcp-connector .

Running Locally

# Set environment variables
export KAFKA_ENABLED=true
export KAFKA_BROKERS=localhost:9092
export KAFKA_AUTH_TYPE=PLAIN
export KAFKA_USERNAME=myuser
export KAFKA_PASSWORD=mypassword
export MYSQL_ENABLED=true
export MYSQL_HOST=localhost
export MYSQL_USER=root
export MYSQL_PASSWORD=password

# Run the server
./ilert-mcp-connector

Docker

Build Docker Image

Using Make:

make docker-build
# Or with custom version
make docker-build VERSION=1.0.0 DOCKER_TAG=1.0.0

Manual build:

docker build -t ilert-mcp-connector .

Run Docker Container

Using Make:

make docker-run
# Or with custom port
make docker-run PORT=8383

Manual run:

docker run -p 8383:8383 \
  -e KAFKA_ENABLED=true \
  -e KAFKA_BROKERS=kafka1:9092,kafka2:9092 \
  -e KAFKA_AUTH_TYPE=PLAIN \
  -e KAFKA_USERNAME=myuser \
  -e KAFKA_PASSWORD=mypassword \
  -e MYSQL_ENABLED=true \
  -e MYSQL_HOST=mysql \
  -e MYSQL_USER=root \
  -e MYSQL_PASSWORD=password \
  ilert-mcp-connector

Kubernetes Deployment

Example Kubernetes deployment:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: ilert-mcp-connector
spec:
  replicas: 1
  selector:
    matchLabels:
      app: ilert-mcp-connector
  template:
    metadata:
      labels:
        app: ilert-mcp-connector
    spec:
      containers:
      - name: ilert-mcp-connector
        image: ghcr.io/ilert/ilert-mcp-connector:latest
        ports:
        - containerPort: 8383
        env:
        - name: PORT
          value: "8383"
        - name: KAFKA_ENABLED
          value: "true"
        - name: KAFKA_BROKERS
          value: "kafka:9092"
        - name: KAFKA_AUTH_TYPE
          value: "PLAIN"
        - name: KAFKA_USERNAME
          valueFrom:
            secretKeyRef:
              name: kafka-secret
              key: username
        - name: KAFKA_PASSWORD
          valueFrom:
            secretKeyRef:
              name: kafka-secret
              key: password
        - name: MYSQL_ENABLED
          value: "true"
        - name: MYSQL_HOST
          value: "mysql"
        - name: MYSQL_USER
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: username
        - name: MYSQL_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: password
        livenessProbe:
          httpGet:
            path: /health
            port: 8383
          initialDelaySeconds: 10
          periodSeconds: 10
        readinessProbe:
          httpGet:
            path: /health
            port: 8383
          initialDelaySeconds: 5
          periodSeconds: 5
          # Note: /ready endpoint requires authentication and contains sensitive connection info
          # Use /health for Kubernetes probes, or configure httpHeaders with Authorization token
---
apiVersion: v1
kind: Service
metadata:
  name: ilert-mcp-connector
spec:
  selector:
    app: ilert-mcp-connector
  ports:
  - port: 8383
    targetPort: 8383

Versioning

The server version is set at build time using Go's -ldflags and is logged on startup. The version is synchronized with GitHub tags and Docker image tags.

Building with Version

When building locally, you can set the version:

Using Make:

make build VERSION=1.0.0

Manual build:

go build -ldflags "-X main.Version=1.0.0 -X main.Commit=$(git rev-parse --short HEAD)" -o ilert-mcp-connector .

Docker Build

The Dockerfile accepts build arguments for version information:

• VERSION - Version string (default: dev)
• COMMIT - Git commit hash (default: unknown)

GitHub Actions

The repository includes a GitHub Actions workflow that automatically builds and pushes Docker images to GitHub Container Registry when:

• A tag starting with v is pushed (e.g., v1.0.0)
• The workflow is manually triggered with a custom tag

The workflow automatically:

• Extracts the version from the Git tag (removes v prefix if present)
• Sets the version and commit SHA as build arguments
• Tags Docker images with semantic version tags

Images are tagged with:

• Semantic version tags (e.g., v1.0.0, v1.0, v1)
• Branch names for branch pushes
• latest for the default branch

The version is logged on server startup and available via the /version endpoint.

Development

Testing

# Run all tests
make test

# Run tests without race detector (faster)
make test-short

# Run tests with coverage report
make test-coverage

Code Quality

# Format code
make fmt

# Run go vet
make vet

# Run all checks (fmt, vet, test)
make check

# Run full CI pipeline locally
make ci

Development Workflow

# Download dependencies
make deps

# Format and check code
make fmt vet

# Run tests
make test

# Build and run locally
make start

# Or run without building first
make run