MySQL Logs Monitoring with OpenTelemetry Collector

The steps above ship raw error log lines. Expand any of these to parse those lines into fields, or to collect MySQL's other log files.

Parse the logs into fields

The minimal config sends each error log line as raw text. Add regex_parser operators to pull out the timestamp, severity, and message so you can filter and aggregate on them in SigNoz.

Replace the operators block of filelog/mysql_error with:

    operators:
      # Error log lines look like:
      # 2020-08-06T14:25:02.835618Z 0 [Warning] [MY-010068] [Server] <message>
      - type: regex_parser
        parse_from: body
        regex: '^(?P<ts>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d+Z)\s+(?P<thread_id>\d+)\s+\[(?P<log_level>[^\]]+)\]\s+\[(?P<error_code>[^\]]+)\]\s+\[(?P<subsystem>[^\]]+)\]\s+(?P<message>.*)$'
        timestamp:
          parse_from: attributes.ts
          layout: '%Y-%m-%dT%H:%M:%S.%fZ'
        severity:
          parse_from: attributes.log_level
          mapping:
            error: ERROR
            warn: Warning
            info:
              - Note
              - System
        on_error: send
      - type: move
        if: attributes.message != nil
        from: attributes.message
        to: body
      - type: remove
        if: attributes.ts != nil
        field: attributes.ts
      - type: add
        field: attributes.source
        value: mysql
      - type: add
        field: attributes.log_type
        value: error

Restart the Collector. New entries arrive with thread_id, error_code, subsystem, and severity_text as attributes. on_error: send sends unparseable lines through with their raw text intact instead of dropping them.

Collect slow query logs

The slow query log records statements that run longer than long_query_time seconds. It is off by default.

Enable the slow query log

Set these under [mysqld] in your MySQL configuration file and restart MySQL:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
# Optional: log statements that run without an index
log_queries_not_using_indexes = 1

For a quick test, set these at runtime with SET GLOBAL instead, which needs no restart. MySQL marks log_error as non-dynamic, so the error log path always requires a restart.

MySQL's own default for slow_query_log_file is <hostname>-slow.log in the data directory (typically /var/lib/mysql/). Use any absolute path the Collector can read.

# Time: 2024-01-15T14:32:45.123456Z
# User@Host: appuser[appuser] @ localhost []  Id:    42
# Query_time: 12.567890  Lock_time: 0.001234  Rows_sent: 25  Rows_examined: 1000000
SET timestamp=1705334365;
SELECT id, email FROM users WHERE country = 'US' ORDER BY created_at;

Add the receiver

Add a second receiver to mysql-logs-collection-config.yaml and include it in the pipeline:

receivers:
  filelog/mysql_slow:
    include: ["${env:MYSQL_SLOW_LOG}"]
    # Each entry starts with "# Time:" and spans several lines. Recombine
    # them into one log record for parsing.
    multiline:
      line_start_pattern: '^# Time:'
    operators:
      - type: regex_parser
        parse_from: body
        regex: '# Time: (?P<ts>\S+)\n# User@Host:\s+(?P<user>\S+)\s+@\s+(?P<host>\S*)\s+\[(?P<ip>[^\]]*)\](?:\s+Id:\s+(?P<conn_id>\d+))?\n# Query_time:\s+(?P<query_time>[\d.]+)\s+Lock_time:\s+(?P<lock_time>[\d.]+)\s+Rows_sent:\s+(?P<rows_sent>\d+)\s+Rows_examined:\s+(?P<rows_examined>\d+)\n(?:SET timestamp=\d+;\n)?(?P<query>(?s:.*))'
        timestamp:
          parse_from: attributes.ts
          layout: '%Y-%m-%dT%H:%M:%S.%fZ'
        on_error: send
      - type: remove
        if: attributes.ts != nil
        field: attributes.ts
      - type: add
        field: attributes.source
        value: mysql
      - type: add
        field: attributes.log_type
        value: slow_query

service:
  pipelines:
    logs/mysql:
      receivers: [filelog/mysql_error, filelog/mysql_slow]

Set MYSQL_SLOW_LOG to the configured slow_query_log_file path, then restart the Collector:

export MYSQL_SLOW_LOG="/var/log/mysql/mysql-slow.log"

Run SELECT SLEEP(3); to generate an entry, then filter by log_type = slow_query in SigNoz. Entries carry query_time, lock_time, rows_sent, rows_examined, user, and the statement itself.

Collect general query logs

The general query log records client connections and statements received by MySQL. It is verbose and has a measurable performance cost, so keep it off in production and enable it for short debugging sessions.

[mysqld]
general_log = 1
general_log_file = /var/log/mysql/mysql.log

Entries are tab-separated with Time, Id (connection ID), Command, and Argument:

2024-01-15T10:45:23.000000Z	   42	Query	SELECT * FROM users WHERE id = 1
2024-01-15T10:45:26.000000Z	   43	Connect	root@localhost on  using TCP/IP

Add a filelog/mysql_general receiver pointed at general_log_file and include it in the logs pipeline. A minimal receiver that keeps the raw line in body and tags the source:

receivers:
  filelog/mysql_general:
    include: ["${env:MYSQL_GENERAL_LOG}"]
    operators:
      - type: add
        field: attributes.source
        value: mysql
      - type: add
        field: attributes.log_type
        value: general

Set MYSQL_GENERAL_LOG to the configured general_log_file path, then add filelog/mysql_general to service.pipelines.logs/mysql.receivers.

No logs in SigNoz: Run tail against each configured log path as the Collector's OS user. Check the Collector logs for permission denied or no such file errors, and verify MYSQL_ERROR_LOG and MYSQL_SLOW_LOG point to the real paths (SHOW VARIABLES LIKE 'slow_query_log_file';).

Slow-query entries split across multiple records: The multiline.line_start_pattern must match the start of each entry. Confirm your slow log begins each block with # Time:. If the last entry is delayed, set force_flush_period: 5s on the receiver.

Fields not parsed (raw line in body): The regex targets the MySQL 8.0 format. On MySQL 5.7 the error-log line has no [MY-code]/[subsystem] fields; drop those groups from the regex. With on_error: send, unparsed lines reach SigNoz with the raw text in body.

Logs missing from the file: The slow and general query logs are off by default. Verify SHOW VARIABLES LIKE 'slow_query_log'; returns ON and that log_output includes FILE (SHOW VARIABLES LIKE 'log_output';).