ICINGA2 – Monitoring MySql

If you are responsible for one or more MySQL servers at your company, you probably need to know how they are performing or how to get the most out of your hardware.

In this post we will introduce monitoring plugins and apply Icinga2 configuration examples for monitoring MySQL single instances and master/slave with replication.

This post continues the Icinga2 web monitor series. In order to make some sense over the example provided below we recommend to make a tour for our previous icinga2 post

Note: All examples provided can also be applied to MariaDB and Percona Server.

Requirements:

There are sets of monitoring plugins that you can use with Icinga2 to check on your MySQL server(s).

• check_mysql: simple plugin that can be used to test connections to a MySQL server
• check_mysql_query: sends SQL queries to a MySQL server and checks their results against different threshold levels
• check_mysql_health: more elaborate plugin for monitoring the uptime, connection time, connected threads, hit rate in the thread or query cache, delay between master and slave, etc.

check_mysql_health really stands out since it offers several modes to monitor database servers. So in this post we will concentrate on it. Its command definition is included in the Icinga Template Library (ITL) which means that you don’t need to worry about creating your own check commands, instead, simply install the plugin and you’re good to go.

All these plugins are part of the standard monitoring tools installation, so you can use them without having to develop MySQL-specific tools yourself. You can find standard plugins on /usr/lib/nagios/plugins
If your standard install did not have some of them: do not worry as you can install them with the following monitoring-plugins package for Debian/Ubuntu distros.

Before we start, we need to make sure to create a dedicated MySQL account for monitoring purposes.

GRANT USAGE ON mysql.* TO 'monitoring'@'monitor.icinga.com' IDENTIFIED BY 'supersecret';

Monitoring a Single MySQL Server

Define a HOST object with a set of VARIABLES ready to be used.

Example:

/etc/icinga2/conf.d/mysql-master.icinga.com.conf

object Host “mysql-master.icinga.com" {
  import "generic-host"
  address = “mysql-master.icinga.com"
  vars.os = "Linux"
  vars.mysql = true
}

Uptime

Based on the VARIABLES created in previous HOST definition you can now apply SERVICE rules to dynamically generated services.

Example:

/etc/icinga2/conf.d/apply_mysql.conf

apply Service "mysql-connect" {
  import "generic-service"
  display_name = "MySQL Connection"
  
  check_command = "mysql_health"
  vars.mysql_health_mode = "uptime"
  
  vars.mysql_health_username = "monitoring"
  vars.mysql_health_password = "supersecret"
  
  assign where host.vars.mysql == true
}

This will create a SERVICE with the display name MySQL Connection for all HOSTS where the custom variable vars.mysql is defined.
This example shows how to monitor the MySQL server’s uptime (vars.mysql_health_mode = “uptime”).

Output:

OK - database is up since 9523 minutes | uptime=571380s

Clients Connected

The check_mysql_health plugin can also report how many connection threads are currently open.
We will add a new generated service as it follows

/etc/icinga2/conf.d/apply_mysql.conf

apply Service "mysql-clients" {
  import "generic-service"

  display_name = "MySQL Clients Connected"
  check_command = "mysql_health"
  vars.mysql_health_mode = "threads-connected"
  vars.mysql_health_warning = "10"
  vars.mysql_health_critical = "20"

  vars.mysql_health_username = "monitoring"
  vars.mysql_health_password = "supersecret"

  assign where host.vars.mysql == true
}

Output:

OK - 15 client connection threads | threads_connected=15;10;20

As you can see, it’s possible to define values for WARNING and CRITICAL thresholds.
Depending on the size of your MySQL server you might want to adjust the numbers and set the WARNING for example at 100 and the CRITICAL at 250.

Detect Lagging MySQL Slaves

If you have a MySQL replication setup, then you probably want to know if a slave gets too far behind the master.
Checking the slave lag is something that can easily be accomplished with the check_mysql_health plugin.

In order to retrieve the necessary data from the database server, the MySQL user needs additional permissions than we’ve seen so far:

GRANT REPLICATION CLIENT ON *.* TO 'monitoring'@'%' IDENTIFIED BY 'supersecret';

We are in need to extend our HOSTS with Mysql SLAVE server instance.

Example:

/etc/icinga2/conf.d/mysql-slave.icinga.com.conf)

object Host "mysql-slave.icinga.com" {
  import "generic-host"

  address = "mysql-slave.icinga.com"

  vars.os = "Linux"

  vars.mysql = true
  vars.mysql_slave = true
}

In this case, the HOSTS is a Linux server, it also has a MySQL installation and it works as a MySQL slave.

An apply rule can use the information to create proper SERVICES that check the slave lag for all HOST that work as MySQL slave.

/etc/icinga2/conf.d/apply_mysql.conf

apply Service "mysql-slave" {
  import "generic-service"

  display_name = "MySQL Slave Lag"
  check_command = "mysql_health"
  vars.mysql_health_mode = "slave-lag"
  vars.mysql_health_warning = "80"
  vars.mysql_health_critical = "100"

  vars.mysql_health_username = "monitoring"
  vars.mysql_health_password = "supersecret"

  assign where host.vars.mysql && host.vars.mysql_slave
}

Output:

OK - Slave is 0 seconds behind master

Sending SQL Queries

In case you need to track a piece of data with no specific option in the plugin then you can write a SQL query to provide the missing data you need.
check_mysql_health allows you to run any query you like against the MySQL server(s), so you can keep an eye on what’s important for your setup.

For example, a common task is to check the size of a database.

The following query reveals the size of the database Icinga2 in megabytes:

SELECT SUM(data_length + index_length) / 1024 / 1024 AS 'db size' FROM information_schema.tables WHERE table_schema = 'icinga2';

The sql mode of check_mysql_health allows you to send that exact SQL query to your database. Icinga2 takes this even a step further: You can not only retrieve the information about the size, but define thresholds for warning or critical states, configure alerts, and save the performance data to use it in a time series database like Graphite or InfluxDB.

In our example we will use HOSTS object definition from the file /etc/icinga2/conf.d/mysql-master.icinga.com.conf (previously created) with an additional dictionary definition.

Tip: Over Icinga2 a dictionary can combine multiple settings for a service.

Example:

object Host "mysql-master.icinga.com" {
  import "generic-host"
  address = "mysql-master.icinga.com"
  vars.os = "Linux"
  vars.mysql = true
  vars.database["icinga2"] = {
    mysql_health_username = "monitoring"
    mysql_health_password = "supersecret"
    mysql_health_warning = 4096
    mysql_health_critical = 8192
  }
}

Next, we’re using an apply rule to loop over arrays and dictionaries.

This is how to create a service check for each element in our SERVICE.

apply Service "db_size" for (db_name => config in host.vars.database) {
   import "generic-service"
   display_name = "DB Size " + db_name
   check_command = "mysql_health"
   vars.mysql_health_mode = "sql"
   vars.mysql_health_name = "SELECT SUM(data_length + index_length) / 1024 / 1024 AS 'db size' FROM information_schema.tables WHERE table_schema = '" + db_name  +"';"
   vars.mysql_health_name2 = "db_size"
   vars.mysql_health_units = "MB"
   vars += config
 }

The statement vars += config makes sure that all variables defined on the host are being passed on to the service. So, the database specific part is configured in the HOST object and settings that apply to this SERVICE in general are defined in the apply rule.

Output:

OK - db_size: 5.06250MB

Conclusion

Icinga2 plugins comes very handy and provide a set of tools out of the box that help us in our daily operations. We show you here just a couple of functions of check_mysql_health plugin, we only open the box for you and we encourage to look forward into the remaining plugins functions.

Update – 9/29/2020

If for some reason check_mysql_health is not part of monitoring-plugins-basic,monitoring-plugins-common and monitoring-plugins-standard apt packages, we will need to download each script by hand and include it into the nagios path so that icinga2 can read/use those over the monitoring tool.

https://labs.consol.de/nagios/check_mysql_health/index.html

The above url is used to download and understand how check_mysql_health works.

• Download via console: wget https://labs.consol.de/assets/downloads/nagios/check_mysql_health-2.2.2.tar.gz

• Extract it: tar xzf check_mysql_health-2.2.2.tar.gz
• Access extracted folder: cd check_mysql_health-2.2.2
• Copy script: cp check_mysql_health /usr/lib/nagios/plugins
• Check script permissions (depending how monitoring plugins where installed on the server)
• Test script locally
  • /usr/lib/nagios/plugins/check_mysql_health –help
  • /usr/lib/nagios/plugins/check_mysql_health –hostname your-db-ip –username nagios –password your-nagios-password –mode sql –name “SELECT Sum(data_length + index_length) / 1024 / 1024 / 1024 AS ‘Size in GB’ FROM information_schema.tables”
• You are ready to apply it over icinga2 program.