Monitoring MySQL Logs with ClickStack

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/use-cases/observability/clickstack/ingesting-data/integration-examples/mysql.md)

TL;DR

Collect and visualize MySQL error and slow query logs in ClickStack using the OTel `filelog` receiver. Includes a demo dataset and pre-built dashboard.

## Integration with existing MySQL[​](#existing-mysql "Direct link to Integration with existing MySQL")

This section covers configuring your existing MySQL installation to send logs to ClickStack by modifying the ClickStack OTel collector configuration.

If you would like to test the MySQL logs integration before configuring your own existing setup, you can test with our preconfigured setup and sample data in the ["Demo dataset"](/docs/use-cases/observability/clickstack/integrations/mysql-logs#demo-dataset) section.

##### Prerequisites[​](#prerequisites "Direct link to Prerequisites")

- ClickStack instance running
- Existing MySQL installation (version 5.7 or newer)
- Access to modify MySQL configuration files
- Sufficient disk space for log files

Configure MySQL logging

#### Configure MySQL logging[​](#configure-mysql "Direct link to Configure MySQL logging")

MySQL supports multiple log types. For comprehensive monitoring with OpenTelemetry, we recommend enabling the error log and slow query log.

The `my.cnf` or `my.ini` configuration file is typically located at:

- **Linux (apt/yum)**: `/etc/mysql/my.cnf` or `/etc/my.cnf`
- **macOS (Homebrew)**: `/usr/local/etc/my.cnf` or `/opt/homebrew/etc/my.cnf`
- **Docker**: Configuration is usually set via environment variables or mounted config file

Add or modify these settings in the `[mysqld]` section:

```
[mysqld]
# Error log configuration
log_error = /var/log/mysql/error.log

# Slow query log configuration
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = ON

# Optional: General query log (verbose, use with caution in production)
# general_log = ON
# general_log_file = /var/log/mysql/mysql-general.log
```

Note

The slow query log captures queries that take longer than `long_query_time` seconds. Adjust this threshold based on your application's performance requirements. Setting it too low will generate excessive logs.

After making these changes, restart MySQL:

```
# For systemd
sudo systemctl restart mysql

# For Docker
docker restart <mysql-container>
```

Verify logs are being written:

```
# Check error log
tail -f /var/log/mysql/error.log

# Check slow query log
tail -f /var/log/mysql/mysql-slow.log
```

Create custom OTel collector configuration

#### Create custom OTel collector configuration[​](#custom-otel "Direct link to Create custom OTel collector configuration")

ClickStack allows you to extend the base OpenTelemetry Collector configuration by mounting a custom configuration file and setting an environment variable. The custom configuration is merged with the base configuration managed by HyperDX via OpAMP.

Create a file named `mysql-logs-monitoring.yaml` with the following configuration:

```
receivers:
  filelog/mysql_error:
    include:
      - /var/log/mysql/error.log
    start_at: end
    multiline:
      line_start_pattern: '^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}'
    operators:
      - type: regex_parser
        parse_from: body
        parse_to: attributes
        regex: '^(?P<timestamp>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{6}[+-]\d{2}:\d{2})\s+(?P<thread_id>\d+)\s+\[(?P<level>[^\]]+)\]\s+(\[(?P<error_code>[^\]]+)\]\s+)?(?P<message>.*)$'
        
      - type: time_parser
        parse_from: attributes.timestamp
        layout_type: gotime
        layout: '2006-01-02T15:04:05.999999-07:00'
        parse_to: body
      
      - type: add
        field: attributes.source
        value: "mysql-error"
      
      - type: add
        field: resource["service.name"]
        value: "mysql-production"

  filelog/mysql_slow:
    include:
      - /var/log/mysql/mysql-slow.log
    start_at: end
    multiline:
      line_start_pattern: '^# Time:'
    operators:
      - type: regex_parser
        parse_from: body
        parse_to: attributes
        regex: '^# Time: (?P<timestamp>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d+Z)\n# User@Host: (?P<user>[^\[]+)\[(?P<user_host>[^\]]*)\]\s+@\s+(?P<host>[^\[]*)\[(?P<ip>[^\]]*)\].*\n# Query_time: (?P<query_time>[\d.]+)\s+Lock_time: (?P<lock_time>[\d.]+)\s+Rows_sent: (?P<rows_sent>\d+)\s+Rows_examined: (?P<rows_examined>\d+)'
      
      - type: time_parser
        parse_from: attributes.timestamp
        layout_type: gotime
        layout: '2006-01-02T15:04:05.999999Z'
        parse_to: body
      
      - type: add
        field: attributes.source
        value: "mysql-slow"
      
      - type: add
        field: resource["service.name"]
        value: "mysql-production"

service:
  pipelines:
    logs/mysql:
      receivers: [filelog/mysql_error, filelog/mysql_slow]
      processors:
        - memory_limiter
        - transform
        - batch
      exporters:
        - clickhouse
```

This configuration:

- Reads MySQL error logs and slow query logs from their standard locations
- Handles multi-line log entries (slow queries span multiple lines)
- Parses both log formats to extract structured fields (level, error\_code, query\_time, rows\_examined)
- Preserves original log timestamps
- Adds `source: mysql-error` and `source: mysql-slow` attributes for filtering in HyperDX
- Routes logs to the ClickHouse exporter via a dedicated pipeline

Note

Two receivers are required because MySQL error logs and slow query logs have completely different formats. The `time_parser` uses `gotime` layout to handle MySQL's ISO8601 timestamp format with timezone offsets.

Configure ClickStack to load custom configuration

#### Configure ClickStack to load custom configuration[​](#load-custom "Direct link to Configure ClickStack to load custom configuration")

To enable custom collector configuration in your existing ClickStack deployment, mount the custom config file at `/etc/otelcol-contrib/custom.config.yaml` and set the environment variable `CUSTOM_OTELCOL_CONFIG_FILE=/etc/otelcol-contrib/custom.config.yaml`.

Update your ClickStack deployment configuration:

```
services:
  clickstack:
    # ... existing configuration ...
    environment:
      - CUSTOM_OTELCOL_CONFIG_FILE=/etc/otelcol-contrib/custom.config.yaml
      # ... other environment variables ...
    volumes:
      - ./mysql-logs-monitoring.yaml:/etc/otelcol-contrib/custom.config.yaml:ro
      - /var/log/mysql:/var/log/mysql:ro
      # ... other volumes ...
```

Note

Ensure the ClickStack collector has appropriate permissions to read the MySQL log files. Use read-only mounts (`:ro`) and follow the principle of least privilege.

Verifying Logs in HyperDX

#### Verifying Logs in HyperDX[​](#verifying-logs "Direct link to Verifying Logs in HyperDX")

Once configured, log into HyperDX and verify logs are flowing:

1. Navigate to the search view
2. Set source to Logs
3. Filter by `source:mysql-error` or `source:mysql-slow` to see MySQL-specific logs
4. You should see structured log entries with fields like `level`, `error_code`, `message` (for error logs) and `query_time`, `rows_examined`, `query` (for slow query logs)

## Demo dataset[​](#demo-dataset "Direct link to Demo dataset")

For users who want to test the MySQL logs integration before configuring their production systems, we provide a sample dataset of pre-generated MySQL logs with realistic patterns.

Download the sample dataset

#### Download the sample dataset[​](#download-sample "Direct link to Download the sample dataset")

Download the sample log files:

```
# Download error log
curl -O https://datasets-documentation.s3.eu-west-3.amazonaws.com/clickstack-integrations/mysql/error.log

# Download slow query log
curl -O https://datasets-documentation.s3.eu-west-3.amazonaws.com/clickstack-integrations/mysql/mysql-slow.log
```

The dataset includes: