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