prometheus/mysqld_exporter

MySQL Server Exporter

Prometheus exporter for MySQL server metrics.

Supported versions:

• MySQL >= 5.6.
• MariaDB >= 10.3

NOTE: Not all collection methods are supported on MySQL/MariaDB < 5.6

Building and running

Required Grants

CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'XXXXXXXX' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';

NOTE: It is recommended to set a max connection limit for the user to avoid overloading the server with monitoring scrapes under heavy load. This is not supported on all MySQL/MariaDB versions; for example, MariaDB 10.1 (provided with Ubuntu 18.04) does not support this feature.

Build

make

Running

Running using an environment variable:

export DATA_SOURCE_NAME='user:password@(hostname:3306)/'
./mysqld_exporter <flags>

Running using ~/.my.cnf:

./mysqld_exporter <flags>

Example format for flags for version > 0.10.0:

--collect.auto_increment.columns
--no-collect.auto_increment.columns

Example format for flags for version <= 0.10.0:

-collect.auto_increment.columns
-collect.auto_increment.columns=[true|false]

Collector Flags

General Flags

TLS and basic authentication

The MySQLd Exporter supports TLS and basic authentication.

To use TLS and/or basic authentication, you need to pass a configuration file
using the --web.config.file parameter. The format of the file is described
in the exporter-toolkit repository.

Setting the MySQL server's data source name

The MySQL server's data source name
must be set via the DATA_SOURCE_NAME environment variable.
The format of this variable is described at https://github.com/go-sql-driver/mysql#dsn-data-source-name.

Customizing Configuration for a SSL Connection

If The MySQL server supports SSL, you may need to specify a CA truststore to verify the server's chain-of-trust. You may also need to specify a SSL keypair for the client side of the SSL connection. To configure the mysqld exporter to use a custom CA certificate, add the following to the mysql cnf file:

ssl-ca=/path/to/ca/file

To specify the client SSL keypair, add the following to the cnf.

ssl-key=/path/to/ssl/client/key
ssl-cert=/path/to/ssl/client/cert

Customizing the SSL configuration is only supported in the mysql cnf file and is not supported if you set the mysql server's data source name in the environment variable DATA_SOURCE_NAME.

Using Docker

You can deploy this exporter using the prom/mysqld-exporter Docker image.

For example:

docker network create my-mysql-network
docker pull prom/mysqld-exporter

docker run -d \
  -p 9104:9104 \
  --network my-mysql-network  \
  -e DATA_SOURCE_NAME="user:password@(hostname:3306)/" \
  prom/mysqld-exporter

heartbeat

With collect.heartbeat enabled, mysqld_exporter will scrape replication delay
measured by heartbeat mechanisms. Pt-heartbeat is the
reference heartbeat implementation supported.

Filtering enabled collectors

The mysqld_exporter will expose all metrics from enabled collectors by default. This is the recommended way to collect metrics to avoid errors when comparing metrics of different families.

For advanced use the mysqld_exporter can be passed an optional list of collectors to filter metrics. The collect[] parameter may be used multiple times. In Prometheus configuration you can use this syntax under the scrape config.

params:
  collect[]:
  - foo
  - bar

This can be useful for having different Prometheus servers collect specific metrics from targets.

Example Rules

There is a set of sample rules, alerts and dashboards available in the mysqld-mixin

MySQL Exporter Helm Chart

Helm chart to install MySQL

$ helm repo add bitnami https://charts.bitnami.com/bitnami
$ helm install my-release  bitnami/Mysql

Installing MySQL Exporter

The MySQL exporter can be deployed in Kubernetes using the Helm chart. The Helm chart used for deployment is from the Prometheus community, which can be found here. To deploy this Helm chart, users can either follow the steps in the above link or refer to the ones outlined below:

$ helm repo add add prometheus-community https://prometheus-community.github.io/helm-charts
$ helm repo update
$ helm install [RELEASE_NAME] prometheus-community/prometheus-mysql-exporter

Some of the common parameters that must be changed in the values file include: 

mysql.host: Defines the Mysql URL you want to monitor.
mysql.user: Mysql connection User.
mysql.pass: Mysql password.

Additional parameters can be changed based on individual needs - such as enabling and disabling collectors, parameters, etc. All these parameters can be tuned via the values.yaml file here.

mysql:
  db: ""
  host: "localhost"
  param: ""
  pass: "password"
  port: 3306
  protocol: ""
  user: "exporter"
  # secret with full DATA_SOURCE_NAME env var as stringdata
  existingSecret: ""
  # secret only containing the password
  existingPasswordSecret:
    name: ""
    key: ""

In addition to the native way of setting up Prometheus monitoring, a service monitor can be deployed (if the Prometheus operator is being used) to scrap the data from MySQL, and Prometheus then scraps the data from the service monitor. With this approach, multiple MySQL can be scrapped without altering the Prometheus configuration. Every MySQL exporter comes with its own service monitor.

In the above-mentioned chart, a service monitor can be deployed by turning it on from the values.yaml file here.

serviceMonitor:
  # enabled should be set to true to enable prometheus-operator discovery of this service
  enabled: false
  # interval is the interval at which metrics should be scraped
  # interval: 30s
  # scrapeTimeout is the timeout after which the scrape is ended
  # scrapeTimeout: 10s
  # additionalLabels is the set of additional labels to add to the ServiceMonitor
  # namespace: monitoring
  additionalLabels: {}
  jobLabel: ""
  targetLabels: []
  podTargetLabels: []
  metricRelabelings: []

Another way of scraping metrics while having the service discovery enabled in Prometheus is by updating the annotation section here with the following:

 annotations:
    prometheus.io/path: /metrics
    prometheus.io/scrape: "true"

This concludes our discussion of the MySQL exporter! If you have any questions, you can reach our team via support@nexclipper.io and stay tuned for further exporter reviews and tips coming soon.

MySQL Exporter Alerts

After digging into all the valuable metrics, this section explains in detail how we can get critical alerts.

PromQL is a query language for the Prometheus monitoring system. It is designed for building powerful yet simple queries for graphs, alerts, or derived time series (aka recording rules). PromQL is designed from scratch and has zero common grounds with other query languages used in time series databases, such as SQL in TimescaleDB, InfluxQL, or Flux. More details can be found here.

Prometheus comes with a built-in Alert Manager that is responsible for sending alerts (could be email, Slack, or any other supported channel) when any of the trigger conditions is met. Alerting rules allow users to define alerts based on Prometheus query expressions. They are defined based on the available metrics scraped by the exporter. Click here for a good source for community-defined alerts.

A general alert looks as follows:

- alert:(Alert Name)
expr: (Metric exported from exporter) >/</==/<=/=> (Value)
for: (wait for a certain duration between first encountering a new expression output vector element and counting an alert as firing for this element)
labels: (allows specifying a set of additional labels to be attached to the alert)
annotation: (specifies a set of informational labels that can be used to store longer additional information)

Some of the recommended MySQL alerts are:

1. Alert - MySQL is Down

 - alert: MysqlDown
    expr: mysql_up == 0
    for: 0m
    labels:
      severity: critical
    annotations:
      summary: MySQL down (instance {{ $labels.instance }})
      description: "MySQL instance is down on {{ $labels.instance }}\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"

2. Alert - MySQL has too many connections

  - alert: MysqlTooManyConnections(>80%)
    expr: avg by (instance) (rate(mysql_global_status_threads_connected[1m])) / avg by (instance) (mysql_global_variables_max_connections) * 100 > 80
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: MySQL too many connections (> 80%) (instance {{ $labels.instance }})
      description: "More than 80% of MySQL connections are in use on {{ $labels.instance }}\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"

3.  Alert - MySQL has slow queries

  - alert: MysqlSlowQueries
    expr: increase(mysql_global_status_slow_queries[1m]) > 0
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: MySQL slow queries (instance {{ $labels.instance }})
      description: "MySQL server mysql has some new slow query.\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"

4.   Alert -  Buffer pool cache

  - alert: BufferPoolHits
    expr: ((rate(mysql_global_status_innodb_buffer_pool_reads[5m])) / rate(mysql_global_status_innodb_buffer_pool_read_requests[5m]) *100 > 2

    for: 0m
    labels:
      severity: critical
    annotations:
      summary: High Read requests rate hitting disk (instance {{ $labels.instance }})
      description: "High number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"

5.  Alert - InnoDB log writes stalling

 - alert: MysqlInnodbLogWaits
    expr: rate(mysql_global_status_innodb_log_waits[15m]) > 10
    for: 0m
    labels:
      severity: warning
    annotations:
      summary: MySQL InnoDB log waits (instance {{ $labels.instance }})
      description: "MySQL innodb log writes stalling\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"

MySQL Exporter Grafana

Graphs are easier to understand and more user-friendly than a row of numbers. For this purpose, users can plot their time series data in visualized format using Grafana.

Grafana is an open-source dashboarding tool used for visualizing metrics with the help of customizable and illustrative charts and graphs. It connects very well with Prometheus and makes monitoring easy and informative. Dashboards in Grafana are made up of panels, with each panel running a PromQL query to fetch metrics from Prometheus.
Grafana supports community-driven graphs for most of the widely used software, which can be directly imported to the Grafana Community.

NexClipper uses the MySQL database by the nasskach dashboard, which is widely accepted and has a lot of useful panels.

What is a Panel?

Panels are the most basic component of a dashboard and can display information in various ways, such as gauge, text, bar chart, graph, and so on. They provide information in a very interactive way. Users can view every panel separately and check the value of metrics within a specific time range. 
The values on the panel are queried using PromQL, which is Prometheus Query Language. PromQL is a simple query language used to query metrics within Prometheus. It enables users to query data, aggregate and apply arithmetic functions to the metrics, and then further visualize them on panels.

Here are some examples of panels:

1. Database overview, connection, table locks

2. Aborted connection, network, memory

3. Command & handlers