Skip to main content
Sumo Logic

Collect MySQL Logs and Metrics (Kubernetes)

Configure collection of logs and metrics for the Sumo Logic app for MySQL, in a Kubernetes environment.

This page provides instructions for configuring log and metric collection for the Sumo Logic App for MySQL in a non-Kubernetes environment.

Collection overview

In Kubernetes environments, we use the Telegraf Operator, which is packaged with our Kubernetes collection. For more information, see Telegraf Collection Architecture.

The diagram below illustrates how data is collected from MySQL in Kubernetes environments. In the architecture shown below, there are four services that make up the metric collection pipeline: Telegraf, Prometheus, Fluentd and FluentBit.

The first service in the pipeline is Telegraf. Telegraf collects metrics from MySQL. Note that we’re running Telegraf in each pod we want to collect metrics from as a sidecar deployment: that is, Telegraf runs in the same pod as the containers it monitors. Telegraf uses the MySQL input plugin to obtain metrics. (For simplicity, the diagram doesn’t show the input plugins.) The injection of the Telegraf sidecar container is done by the Telegraf Operator. Fluentbit collects logs written to standard out and forwards them to FluentD, which in turn sends all the logs and metrics data to a Sumo Logic HTTP Source.

k8s.png

Prerequisites

Ensure that you are monitoring your Kubernetes clusters with the Telegraf operator. If you're not, see Install Telegraf.  

Step 1: Configure Fields in Sumo Logic

Create the following Fields in Sumo Logic prior to configuring collection. This ensures that your logs and metrics are tagged with relevant metadata, which is required by the app dashboards. For information, see Fields topic.

  • pod_labels_component
  • pod_labels_environment
  • pod_labels_db_system
  • pod_labels_db_cluster

Step 2: Configure metrics collection

This configure metrics collection from Kubernetes, add the following annotations to your MySQL pods, and make the edits described below:

primary:
  podAnnotations:
    telegraf.influxdata.com/class: sumologic-prometheus
    prometheus.io/scrape: "true" 
    prometheus.io/port: "9273"
    telegraf.influxdata.com/inputs: |+
      [[inputs.mysql]]
        servers = ["user:passwd@tcp(localhost:3306)/?tls=false"]
        table_schema_databases = []
        gather_slave_status = true
        gather_global_variables = true
        gather_table_io_waits = true
        gather_table_lock_waits = true
        gather_index_io_waits = true
        gather_event_waits = true
        gather_file_events_stats = true
        gather_perf_events_statements = true
        [inputs.mysql.tags]
          environment = "prod"
          component = "database"
          db_system = "mysql"
          db_cluster = "your_mysql_cluster_name"
    tailing-sidecar: sidecarconfig;slowlog:data:/bitnami/mysql/data/mysql-release-0-slow.log

To edit the annotations

Supply values for the following parameters in your annotations.

  • telegraf.influxdata.com/inputs. This contains the required configuration for the Telegraf MySQL Input plugin. For information on configuring the MySQL input plugin for Telegraf, see the MySQL Input Plugin Readme
  • In [[inputs.mysql]]
    • servers. The URL to the MySQL server
    • Configure metrics to collect by uncommenting or setting the following parameters. For more information, see   the MySQL Input Plugin Readme.
      • table_schema_databases = []
      • gather_slave_status = true
      • gather_global_variables = true
      • gather_table_io_waits = true
      • gather_table_lock_waits = true
      • gather_index_io_waits = true
      • gather_event_waits = true
      • gather_file_events_stats = true
      • gather_perf_events_statements = true
    • In [inputs.mysql.tags]:
      • environment. This is the deployment environment where the MySQL cluster identified by the value of servers resides. For example: dev, prod or qa. While this value is optional we highly recommend setting it. 
      • db_cluster. Enter a name to uniquely identify this MySQL cluster. This cluster name will be shown in the Sumo Logic dashboards.

There are additional configuration options that you should not modify, as changing them will prevent the MySQL app from functioning correctly. The settings you should not modify are:

  • telegraf.influxdata.com/class: sumologic-prometheus  instructs the Telegraf operator what output to use. 
  • prometheus.io/scrape: "true" ensures Prometheus will scrape the metrics.
  • prometheus.io/port: "9273"  tells Prometheus what ports to scrape on. 
  • telegraf.influxdata.com/inputs
    • In the [inputs.mysql.tags] section:
      • component: “database” is used by the Sumo Logic app to identify application components. 
      • db_system: “mysql”  identifies the database system.

For information about properties that can be configured globally in the Telegraf agent, see the Configuration documentation for Telegraf. 

Sumo Logic Kubernetes collection will automatically start collecting metrics from the pods with the labels and annotations you added in the previous step. 

To verify the metrics have been ingested, run this metrics query:

db_cluster=<your_mysql_cluster_name> component="database" and db_system="mysql"

Step 3: Configure log collection

This section explains the steps to collect MySQL logs from a Kubernetes environment. Follow the steps in Option A or Option B, depending on whether your logs are being written to standard output or to log files.

Option A: Collect MySQL logs written to standard output

If your MySQL Helm chart/pod is writing the logs to standard output then follow these steps:

Apply the following labels to your MySQL pods:

 labels:
    environment: "prod"
    component: "database"
    db_system: "mysql"
    db_cluster: "your_mysql_cluster_name”

Enter in values for the following parameters (marked in bold above):

  • environment. This is the deployment environment where the MySQL cluster identified by the value of servers resides. For example: dev, prod or qa. While this value is optional we highly recommend setting it.
  • db_cluster. Enter a name to identify this MySQL cluster. This cluster name will be shown in the Sumo Logic dashboards.

There are additional configuration options that you should not modify, as changing them will prevent the MySQL app from functioning correctly. The settings you should not modify are:

  • component: “database” is used by Sumo Logic apps to identify application components. 
  • db_system: “mysql” identifies the database system.

For information about properties that can be configured globally in the Telegraf agent, see the Configuration documentation for Telegraf. 

The Sumo Logic Kubernetes Collection process will automatically capture the logs from stdout and send the logs to Sumo Logic. For more information on deploying the sumologic-kubernetes-collection, see Collect Logs and Metrics for the Kubernetes App.

Option B: Collect MySQL logs written to log files

If your MySQL helm chart/pod is writing its logs to log files, you can use a sidecar to send log files to standard out. To do so:

  1. Determine the location of the MySQL log file on Kubernetes. You can determine this from the my.cnf file for your MySQL cluster along with the volume mounts on the MySQL pods.
  2. Install the Sumo Logic tailing sidecar operator.
  3. Add the following annotation in addition to the existing annotations.

    primary:
     podAnnotations:
      tailing-sidecar: sidecarconfig;container_name:<mount_volume>:<path_of_mysql_log_file>/<mysql_log_file_name>

   For example:

  primary:
   podAnnotations:
    tailing-sidecar: sidecarconfig;slowlog:data:/bitnami/mysql/data/mysql-release-0-slow.log

To verify that the MySQL pods are running and annotations are applied, run this command:

kubectl describe pod <mysql_pod_name>

Sumo Logic Kubernetes collection will automatically start collecting logs from the pods having the annotations defined above. 

Step 4: Add an FER to normalize the fields in Kubernetes environments

Labels created in Kubernetes environments are automatically prefixed with pod_labels. To normalize these for our app to work, we'll create a Field Extraction Rule, Database Application Components, assuming it does not already exist,

  1. Go to Manage Data > Logs > Field Extraction Rules.
  2. Click the + Add.
  3. The Add Field Extraction pane appears.
    fer.png
  4. Rule Name. Enter "App Observability - Database".
  5. Applied At. Choose "Ingest Time".
  6. Scope. Select "Specific Data".
    • Scope. Enter the following keyword search expression: 
      pod_labels_environment=* pod_labels_component=database pod_labels_db_system=* pod_labels_db_cluster=*
  7. Parse Expression. Enter the following parse expression:
    | if (!isEmpty(pod_labels_environment), pod_labels_environment, "") as environment
    | pod_labels_component as component
    | pod_labels_db_system as db_system
    | pod_labels_db_cluster as db_cluster 
  8. Click Save to create the rule.
  9. To verify logs are flowing into Sumo Logic, run this query:
    component=database db_system=mysql db_cluster=<your_mysql_cluster_name> 

Sample Log Messages

{
"timestamp":1617810938497,
"log":"2021-04-07T15:55:34.261220Z 0 [System] [MY-010931] [Server] /opt/bitnami/mysql/bin/mysqld: ready for connections. Version: '8.0.23'  socket: '/opt/bitnami/mysql/tmp/mysql.sock'  port: 3306  Source distribution.",
"stream":"stdout",
"time":"2021-04-07T15:55:34.261397194Z"
}

Sample query

This sample Query is from the Logs panel of the MySQL - Logs dashboard.

Query String - “Top 10 Slow Queries by Average Execution Time”

db_system=mysql db_cluster={{db_cluster}} "User@Host" "Query_time"  
| parse regex "(?<query_block># User@Host:[\S\s]+?SET timestamp=\d+;[\S\s]+?;)" multi
| parse regex field=query_block "# User@Host: \S+?\[(?<user>\S*?)\] @ (?<host_name>\S+)\s\[(?<ip_addr>\S*?)\]" nodrop // Pttrn1-vrtn1
| parse regex field=query_block "# User@Host: \S+?\[(?<user>\S*?)\] @\s+\[(?<ip_addr>\S*?)\]\s+Id:\s+(?<Id>\d{1,10})" nodrop // Pttrn1-vrtn2
| parse regex field=query_block "# User@Host: \S+?\[(?<user>\S*?)\] @ (?<host_name>\S+)\s\[(?<ip_addr>\S*?)\]\s+Id:\s+(?<Id>\d{1,10})" // Pttrn1-vrtn3
| parse regex field=query_block "Schema: (?<schema>(?:\S*|\s)?)\s*Last_errno[\s\S]+?Query_time:\s+(?<query_time>[\d.]*)\s+Lock_time:\s+(?<lock_time>[\d.]*)\s+Rows_sent:\s+(?<rows_sent>[\d.]*)\s+Rows_examined:\s+(?<rows_examined>[\d.]*)\s+Rows_affected:\s+(?<rows_affected>[\d.]*)\s+Rows_read:\s+(?<rows_read>[\d.]*)\n" nodrop // Pttrn2-vrtn1
| parse regex field=query_block "Schema: (?<schema>(?:\S*|\s)?)\s*Last_errno[\s\S]+?\s+Killed:\s+\d+\n" nodrop // Pttrn2-vrtn2
| parse regex field=query_block "Query_time:\s+(?<query_time>[\d.]*)\s+Lock_time:\s+(?<lock_time>[\d.]*)\s+Rows_sent:\s+(?<rows_sent>[\d]*)\s+Rows_examined:\s+(?<rows_examined>[\d]*)\s+Rows_affected:\s+(?<rows_affected>[\d]*)\s+" nodrop // Pttrn2-vrtn3
| parse regex field=query_block "Query_time:\s+(?<query_time>[\d.]*)\s+Lock_time:\s+(?<lock_time>[\d.]*)\s+Rows_sent:\s+(?<rows_sent>[\d]*)\s+Rows_examined:\s+(?<rows_examined>[\d]*)" // Pttrn2-vrtn4
| parse regex field=query_block "# Bytes_sent:\s+(?<bytes_sent>\d*)\s+Tmp_tables:\s+(?<tmp_tables>\d*)\s+Tmp_disk_tables:\s+(?<temp_disk_tables>\d*)\s+Tmp_table_sizes:\s+(?<tmp_table_sizes>\d*)\n" nodrop // Pttrn3-vrtn1
| parse regex field=query_block "# Bytes_sent:\s+(?<bytes_sent>\d*)\n" nodrop // Pttrn3-vrtn2
| parse regex field=query_block "SET timestamp=(?<set_timestamp>\d*);(?:\\n|\n)(?<sql_cmd>[\s\S]*);" nodrop
| fields -query_block
| avg(query_time) as avg_time, sum(query_time) as total_time, min(query_time) as min_time, max(query_time) as max_time, avg(rows_examined) as avg_rows_examined, avg(rows_sent) as avg_rows_sent, avg(Lock_Time) as avg_lock_time, count as frequency group by sql_cmd, db_cluster
| sort by avg_time | limit 10