MYSQL integration with KloudMate

10min

The MySQL integration provides almost real-time health and performance metrics for your MySQL database. You can visualize these metrics with the included dashboard and set up alerts to inform your team about different MySQL conditions.
This example demonstrates how to use OpenTelemetry and KloudMate to monitor MySQL performance, establish baseline metrics, and proactively address performance issues, ensuring optimal operation of your MySQL database.
Pre-requisites:
MySQL installed and running
Install the OpenTelemetry collector on the specific server that requires monitoring. 
Check Installing and Configuring OpenTelemetry Collector. 
MySQL username and password for authentication.
The MySQL logs must be enabled from configurations.
Step 1: Configure the Receivers to scrape metrics as well as Logs.
To start monitoring MySQL with Otel Collector, you need to configure mysql receiver 
Linux Users: Open the file located at /etc/otelcol-contrib/config.yaml using your preferred text editor.
Windows Users: Create a new file called config.yaml in the C:\Program Files\OpenTelemetry Collector folder. You can use Notepad or any text editor to do this.
Add the suitable extensions : 
YAML
extensions:
  health_check:
  pprof:
    endpoint: 0.0.0.0:1777
  zpages:
    endpoint: 0.0.0.0:55679
  file_storage:
    create_directory: true
extensions:
  health_check:
  pprof:
    endpoint: 0.0.0.0:1777
  zpages:
    endpoint: 0.0.0.0:55679
  file_storage:
    create_directory: true
﻿
In this step, two receivers are integrated to retrieve telemetry data from MySQL.
MySQL Receiver: To retrieve metrics
File Log Receiver: To retrieve logs
To get the metrics use only Mysql receiver
YAML
receivers:
  mysql:
    endpoint: localhost:3306
    username: root  #Change the username
    password: 'YourStr0ngPassword!'  #Change the password
    collection_interval: 60s
    initial_delay: 1s
    statement_events:
      digest_text_limit: 120
      time_limit: 24h
      limit: 250

  filelog/mysqlerror:
    include: [/var/log/mysql/error.log]
    exclude: [/var/log/mysql/*.gz]
    storage: file_storage
    retry_on_failure:
      enabled: true
    operators:
      - type: regex_parser
        parse_to: body
        regex: '(?P<time>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}.\d+Z)\s(?P<id>\d+)\s\[(?P<severity>[^ ]*)\]\s\[(?P<name>[^ ]*)\]\s\[(?P<server>[^ ]*)\]\s(?P<message>.*)'
        timestamp:
          parse_from: body.timestamp
          layout: '%Y-%m-%d %H:%M:%S.%f %Z'
        severity:
          parse_from: body.severity
          mapping:
            info:
              - STATEMENT
              - LOG
              - SYSTEM
      - type: remove
        field: body.severity
      - type: remove
        field: body.timestamp

  filelog/mysqlslowquery:
    include: [/var/log/mysql/*-slow.log]
    exclude: [/var/log/mysql/*.gz]
    storage: file_storage
    multiline:
      line_start_pattern: '^# Time: \d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{6}Z'
    retry_on_failure:
      enabled: true
    operators:
      - type: regex_parser
        parse_to: body
        regex: '(?P<time>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d+Z)\n#\sUser@Host:\s(?P<user>.*)\s+Id:\s+(?P<Id>\d+)\n# Query_time:\s(?P<query_time>\d+.\d+)\s+Lock_time:\s(?P<lock_time>\d+.\d+)\sRows_sent:\s(?P<rows_sent>\d+)\s+Rows_examined:\s(?P<rows_examined>\d+)\n(?P<query>[\s\S]+)'
        timestamp:
          parse_from: body.timestamp
          layout: '%Y-%m-%d %H:%M:%S.%f %Z'
        severity:
          parse_from: body.severity
          mapping:
            info:
              - STATEMENT
              - LOG
      - type: remove
        field: body.severity
      - type: remove
        field: body.timestamp


receivers:
  mysql:
    endpoint: localhost:3306
    username: root  #Change the username
    password: 'YourStr0ngPassword!'  #Change the password
    collection_interval: 60s
    initial_delay: 1s
    statement_events:
      digest_text_limit: 120
      time_limit: 24h
      limit: 250

  filelog/mysqlerror:
    include: [/var/log/mysql/error.log]
    exclude: [/var/log/mysql/*.gz]
    storage: file_storage
    retry_on_failure:
      enabled: true
    operators:
      - type: regex_parser
        parse_to: body
        regex: '(?P<time>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}.\d+Z)\s(?P<id>\d+)\s\[(?P<severity>[^ ]*)\]\s\[(?P<name>[^ ]*)\]\s\[(?P<server>[^ ]*)\]\s(?P<message>.*)'
        timestamp:
          parse_from: body.timestamp
          layout: '%Y-%m-%d %H:%M:%S.%f %Z'
        severity:
          parse_from: body.severity
          mapping:
            info:
              - STATEMENT
              - LOG
              - SYSTEM
      - type: remove
        field: body.severity
      - type: remove
        field: body.timestamp

  filelog/mysqlslowquery:
    include: [/var/log/mysql/*-slow.log]
    exclude: [/var/log/mysql/*.gz]
    storage: file_storage
    multiline:
      line_start_pattern: '^# Time: \d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{6}Z'
    retry_on_failure:
      enabled: true
    operators:
      - type: regex_parser
        parse_to: body
        regex: '(?P<time>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d+Z)\n#\sUser@Host:\s(?P<user>.*)\s+Id:\s+(?P<Id>\d+)\n# Query_time:\s(?P<query_time>\d+.\d+)\s+Lock_time:\s(?P<lock_time>\d+.\d+)\sRows_sent:\s(?P<rows_sent>\d+)\s+Rows_examined:\s(?P<rows_examined>\d+)\n(?P<query>[\s\S]+)'
        timestamp:
          parse_from: body.timestamp
          layout: '%Y-%m-%d %H:%M:%S.%f %Z'
        severity:
          parse_from: body.severity
          mapping:
            info:
              - STATEMENT
              - LOG
      - type: remove
        field: body.severity
      - type: remove
        field: body.timestamp

﻿
Step 2: Set up the processor component to identify resource information from the host and either append or replace the resource values in the telemetry data with this information.
Please choose one of the following options for configuration based on your provider (AWS EC2, Azure VM or on-premises server).
Server(Can be on-premise, non-cloud or cloud)
YAML
processors:
  batch:
    send_batch_size: 5000
    timeout: 10s

  resourcedetection:
    detectors: [system]
    system:
      resource_attributes:
        host.name:
          enabled: true
        host.id:
          enabled: true
        os.type:
          enabled: false
  resource:
    attributes:
      - key: service.name
        action: insert
        from_attribute: host.name
processors:
  batch:
    send_batch_size: 5000
    timeout: 10s

  resourcedetection:
    detectors: [system]
    system:
      resource_attributes:
        host.name:
          enabled: true
        host.id:
          enabled: true
        os.type:
          enabled: false
  resource:
    attributes:
      - key: service.name
        action: insert
        from_attribute: host.name
﻿
AWS EC2:
(optional)
To retrieve AWS EC2 instance tags along with logs and metrics, users need to associate an IAM role with the EC2 instance that includes the EC2:DescribeTags policy. 
YAML
processors:
  batch:
    send_batch_size: 5000
    timeout: 10s

  resourcedetection/ec2:
    detectors:  ["ec2"]
    ec2:
      tags:
        - ^tag1$
        - ^tag2$
        - ^label.*$ 
        - ^Name$

  resource:
    attributes:
      - key: service.name
        action: insert
        from_attribute: ec2.tag.Name
      - key: service.instance.id
        action: insert
        from_attribute: host.id
processors:
  batch:
    send_batch_size: 5000
    timeout: 10s

  resourcedetection/ec2:
    detectors:  ["ec2"]
    ec2:
      tags:
        - ^tag1$
        - ^tag2$
        - ^label.*$ 
        - ^Name$

  resource:
    attributes:
      - key: service.name
        action: insert
        from_attribute: ec2.tag.Name
      - key: service.instance.id
        action: insert
        from_attribute: host.id
﻿
Azure Virtual Machines:
YAML
processors:
  batch:
    send_batch_size: 5000
    timeout: 10s

  resourcedetection/azure:
    detectors: ["azure"]
    azure:
      tags:
        - ^tag1$
        - ^tag2$
        - ^label.*$
        - ^Name$

  resource:
    attributes:
      - key: service.name
        action: insert
        from_attribute: azure.vm.name
      - key: service.instance.id
        action: insert
        from_attribute: host.id
processors:
  batch:
    send_batch_size: 5000
    timeout: 10s

  resourcedetection/azure:
    detectors: ["azure"]
    azure:
      tags:
        - ^tag1$
        - ^tag2$
        - ^label.*$
        - ^Name$

  resource:
    attributes:
      - key: service.name
        action: insert
        from_attribute: azure.vm.name
      - key: service.instance.id
        action: insert
        from_attribute: host.id
﻿
Step 3: Configure the exporter, extension and save the configuration
Set up the KloudMate Backend on the exporter part of the Open Telemetry configuration file and configure the pipeline.
YAML
exporters:
  debug:
    verbosity: detailed
  otlphttp:
    endpoint: 'https://otel.kloudmate.com:4318'
    headers:
      Authorization: xxxxxxxx  # Use the auth key

service:
  pipelines:

    metrics:
      receivers: [mysql]
      processors: [batch, resourcedetection, resource]  #use the correct resourcedetection
      exporters: [otlphttp]
    logs:
      receivers: [filelog/mysqlslowquery, filelog/mysqlerror]
      processors: [batch, resourcedetection, resource]  #use the correct resourcedetection
      exporters: [otlphttp]
  extensions: [health_check, pprof, zpages, file_storage]
exporters:
  debug:
    verbosity: detailed
  otlphttp:
    endpoint: 'https://otel.kloudmate.com:4318'
    headers:
      Authorization: xxxxxxxx  # Use the auth key

service:
  pipelines:

    metrics:
      receivers: [mysql]
      processors: [batch, resourcedetection, resource]  #use the correct resourcedetection
      exporters: [otlphttp]
    logs:
      receivers: [filelog/mysqlslowquery, filelog/mysqlerror]
      processors: [batch, resourcedetection, resource]  #use the correct resourcedetection
      exporters: [otlphttp]
  extensions: [health_check, pprof, zpages, file_storage]
﻿
Step 4:  To restart and verify the status of the OpenTelemetry (Otel) Collector, follow these steps:
For Linux:
Execute the following commands:
Text
sudo systemctl restart otelcol-contrib 
sudo systemctl status otelcol-contrib
sudo systemctl restart otelcol-contrib 
sudo systemctl status otelcol-contrib
﻿
These commands will restart the Otel Collector and display its current status.
For Windows:
Open the Services window:
Press Win + R, type services.msc, and press OK.
Alternatively, search for "Services" in the Windows Start menu.
In the Services window, locate the "OpenTelemetry Collector" service.
Right-click the service and select "Restart."
Subsequently, monitor the metrics on the KloudMate dashboard and set up an alarm to receive notifications if the potential metrics for a specific application rise. 
﻿
Metric Name
 Description
mysql.operations
Total operations including fsync, reads and writes
mysql_buffer_pool.data_pages
Number of data pages for an InnoDB buffer pool 
mysql_buffer_pool.limit
Configured size of the InnoDB buffer pool 
mysql_buffer_pool.operations
Number of operations on the InnoDB buffer pool 
mysql_buffer_pool.page_flushes
Sum of Requests to flush pages for the InnoDB buffer pool 
mysql_mysqlx_connections
Total MySQLx connections 
mysql.tmp_resources
Number of temporary resources created 
mysql_uptime
Number of seconds since the server has been up 
mysql_index_io_wait_count
Total time of I/O wait events for a particular index 
mysql_handlers
Number of requests to various MySQL handlers 
mysql_locks
Total MySQL locks 
mysql_log_operations 
Number of InnoDB log operations
mysql.index.io.wait.time
Total time of I/O wait events for a particular index
mysql_row_locks
Total InnoDB row locks present
mysql_buffer_pool_usage
Number of bytes in the InnoDB buffer pool
mysql.page_operations
Total operation on InnoDB pages 
mysql.double_writes 
Number of writes to the InnoDB doublewrite buffer pool 
mysql.table.io.wait.count 
Total I/O wait events for a specific table 
mysql.table.io.wait.time 
Total wait time for I/O events for a table 
mysql.prepared_statements 
Number of times each type of Prepared statement command got issued 
mysql.sorts 
Total MySQL sort execution 
mysql.row_operations 
Total row operations executed 
mysql.threads 
Current state of MySQL threads 
mysql.opened_resources 
Total opened resources 
mysql.buffer_pool.pages 
Sum of pages in the InnoDB buffer pool
﻿

Metric Name	Description
mysql.operations	Total operations including fsync, reads and writes
mysql_buffer_pool.data_pages	Number of data pages for an InnoDB buffer pool
mysql_buffer_pool.limit	Configured size of the InnoDB buffer pool
mysql_buffer_pool.operations	Number of operations on the InnoDB buffer pool
mysql_buffer_pool.page_flushes	Sum of Requests to flush pages for the InnoDB buffer pool
mysql_mysqlx_connections	Total MySQLx connections
mysql.tmp_resources	Number of temporary resources created
mysql_uptime	Number of seconds since the server has been up
mysql_index_io_wait_count	Total time of I/O wait events for a particular index
mysql_handlers	Number of requests to various MySQL handlers
mysql_locks	Total MySQL locks
mysql_log_operations	Number of InnoDB log operations
mysql.index.io.wait.time	Total time of I/O wait events for a particular index
mysql_row_locks	Total InnoDB row locks present
mysql_buffer_pool_usage	Number of bytes in the InnoDB buffer pool
mysql.page_operations	Total operation on InnoDB pages
mysql.double_writes	Number of writes to the InnoDB doublewrite buffer pool
mysql.table.io.wait.count	Total I/O wait events for a specific table
mysql.table.io.wait.time	Total wait time for I/O events for a table
mysql.prepared_statements	Number of times each type of Prepared statement command got issued
mysql.sorts	Total MySQL sort execution
mysql.row_operations	Total row operations executed
mysql.threads	Current state of MySQL threads
mysql.opened_resources	Total opened resources
mysql.buffer_pool.pages	Sum of pages in the InnoDB buffer pool

Did this page help you?

MySQL Monitoring

Monitor MSSQL using OpenTelemetry receivers