check_mysql - CMT Monitor Documentation

Skip to content

# MYSQL Module

Version : 1.8+

This module provides:

- simple and standard metrology for MySQL and MariaDB servers
- slave monitoring for master/slave topologies

All host/port/credentials are to be configurer in a mysql.cnf file format.

As for all modules, metrology is sent to all configured targets : Graylog, ElasticSearch, InfluxDB. And alerts are sent to pager if threshold and conditions are met.

## configure

    # conf.yml
    mysql:

        mytestdbcheck:
          defaults_file: /opt/cmt/mysql.cnf
              #  [client]
              #  host     = 127.0.0.1
              #  user     = root
              #  password = xxxxxxx
              #  port     = 3306
              #  socket   = /var/run/mysqld/mysqld.sock
          is_slave: yes
          max_behind: 300
          severity_max: notice
          alert_delay: 300

## Standard metrics collected

- `cmt_mysql_connection`: Threads_connected from SHOW GLOBAL STATUS ; concurrent clients.

- `cmt_mysql_runner`: Threads_running from SHOW GLOBAL STATUS ; active clients.

- `cmt_mysql_memory`: Memory from SHOW GLOBAL STATUS

- `cmt_mysql_read_rate`: Derivative (accross runs) of Com_select

- `cmt_mysql_write_rate`: Derivative of Com_insert + update + delete

- `cmt_mysql_query_rate`: Derivative of Queries from SHOW GLOBAL STATUS

- `cmt_mysql_cx_rate`: Derivative for Connections from SHOW GLOBAL STATUS

## Slave metrics collected

When is_slave is configured, the following metrics are collected.

    cmt_slave_io_run         Yes - Slave_IO_Running
    cmt_slave_sql_run        Yes - Slave_SQL_Running
    cmt_slave_master_logfile mysql-bin.000004 - Master_Log_File
    cmt_slave_relayfile      mysql-bin.000004 - Relay_Master_Log_File
    cmt_slave_behind         0 - Seconds_Behind_Master

An alert is triggered when io_run or sql_run are not Yes or when slave_behind is too high (configuration), meaning that the slave server is out-of-sync with its master server.

## CLI Output (example)

    $ cmt -s mysql

    Check mysql
    cmt_mysql_version        10.3.23-MariaDB-1:10.3.23+maria~bionic-log
    cmt_mysql_connection     2
    cmt_mysql_runner         7
    cmt_mysql_memory         276053016 bytes  [276.1 MB]
    cmt_mysql_read_rate      0.0 - r/sec
    cmt_mysql_write_rate     0.0 - w/sec
    cmt_mysql_query_rate     0.05 - q/sec
    cmt_mysql_cx_rate        0.01 - connection/sec
    OK                       mydbmaster - cx=2 cx/s=0.01 r/s=0.0 w/s=0.0 q/s=0.05 mem=276053016

    Check mysql
    cmt_mysql_version        10.3.23-MariaDB-1:10.3.23+maria~bionic
    cmt_mysql_connection     1
    cmt_mysql_runner         8
    cmt_mysql_memory         277474552 bytes  [277.5 MB]
    cmt_mysql_read_rate      0.0 - r/sec
    cmt_mysql_write_rate     0.0 - w/sec
    cmt_mysql_query_rate     0.03 - q/sec
    cmt_mysql_cx_rate        0.01 - connection/sec
    cmt_mysql_slave_io_run   Yes - Slave_IO_Running
    cmt_mysql_slave_sql_run  Yes - Slave_SQL_Running
    cmt_mysql_slave_mpos     mysql-bin.000004 - Master_Log_File
    cmt_mysql_slave_rpos     mysql-bin.000004 - Relay_Master_Log_File
    cmt_mysql_slave_behind   0 - Seconds_Behind_Master
    OK                       mydbslave - slave 0 sec. behind (limit = 180) - cx=1 cx/s=0.01 r/s=0.0 w/s=0.0 q/s=0.03 mem=277474552