Are you responsible for one or more MySQL servers in your company or organisation? Do you run a MySQL replication setup or multiple MySQL instances on your machine? Then you probably want to know if they are performing as well as they could do and if you’re getting the most out of your hardware. Monitoring your MySQL server(s) provides valuable information — both for avoiding and dealing with problems. If you’re looking for a free and open-source solution, then it’s time to learn our tips and tricks for monitoring MySQL with Icinga 2.

This document introduces the necessary monitoring plugins and shows some Icinga 2 configuration examples for monitoring various MySQL setups, from single servers to master/slave setups with replication, to multiple MySQL instances on a single server.

Tip: All sample listings can also apply to MariaDB and Percona Server. So, whenever we talk about MySQL, this includes the two spin-offs.

Requirements

Monitoring a database service starts with checking for the availability of the service itself. End-to-end monitoring makes sure that you can actually connect to the MySQL application instead of just checking that the process is running. In other words, your Icinga 2 server should be able to connect to the MySQL service running on the database server, so please configure your firewall(s) accordingly. If your DB Server is not accessible from remote, have a look at the documentation on how to run Icinga 2 as an agent.

There are various monitoring plugins that you can use with Icinga 2 to check on your MySQL server(s). They’re all part of the standard monitoring tools installation, so you can use them without having to develop MySQL-specific things for yourself:

  • 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, hitrate in the thread or query cache, delay between master and slave, etc.

The third one really stands out since it offers several modes to monitor database servers. So, in this document we will concentrate on check_mysql_health. 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.

Note: Before you start, make sure to create a dedicated MySQL account for monitoring purposes. It should have limited privileges, because its clear-text password will end up in the Icinga 2 configuration files. For a simple connection check minimal permissions are sufficient:

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

Monitoring a Single MySQL Server

Even if it’s only a single MySQL server you’re going to monitor, it most likely contains several databases. For that reason it’s a good idea to start by defining some custom variables and apply rules. Don’t worry if you don’t know yet what that means — it will become obvious when you continue reading.

First, define a host object, e.g. in /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 you created in that host definition you can now apply service rules to dynamically create services (/etc/icinga2/conf.d/apply_mysql.conf):

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

  display_name = "MySQL Connect"
  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 Connect for all hosts where the custom variable vars.mysql is defined. So far, we’ve only created one host object (mysql-master.icinga.com), so the apply rules don’t give us any advantage yet. As soon as you add more hosts to your Icinga 2 configuration and set the same variables for them, the corresponding MySQL check(s) will be automatically created, with no explicit configuration needed per host.

So, the last example shows how to monitor the MySQL server’s uptime (vars.mysql_health_mode = "uptime"). It returns information like this:

OK - database is up since 14523 minutes | uptime=871409s

Clients connected

The check_mysql_health plugin can also report how many connection threads are currently open (mode threads-connected):

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

Again, it’s possible to define values for warning and critical thresholds (defaults: 10 and 20). 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:

[...]
  check_command = "mysql_health"
[...]
  vars.mysql_health_mode = "threads-connected"
  vars.mysql_health_warning = "100"
  vars.mysql_health_critical = "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';

Again, using custom variables in host definitions and combining them with apply rules is a good idea and makes it easier to extend the setup and connect more slaves. Here is an example for a host definition of a MySQL slave (file /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 host 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 hosts that work as MySQL slave (file /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_username = "monitoring"
  vars.mysql_health_password = "supersecret"

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

Tip: Remember to define a warning and a critical threshold for slave-lag if you’re not happy with the default values (10 and 20 seconds).

Sending SQL Queries

The check_mysql_health plugin has an extensive feature list. In case you’re missing an option, then maybe an SQL query can provide the information you’re looking for. 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. Icinga 2 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.

This next example shows the host object definition from the file /etc/icinga2/conf.d/mysql-master.icinga.com.conf (see this paragraph) with an additional dictionary definition. In Icinga 2 a dictionary can combine multiple settings for a service check:

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 your variable in /etc/icinga2/conf.d/apply_mysql.conf:

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.icingaweb2 mysql dbsize

Multiple MySQL Instances

There are many reasons to have more than one MySQL installation. As a database administrator you may run multiple instances of MySQL on a single server, e.g. to keep your customers’ data separate. Or you might want to test different MySQL versions on the same machine. Monitoring multiple instances can be tricky, especially if you want to check many different hosts, each with more than one database instance.

To keep the Icinga 2 configuration clear and simple, you can use the apply loop from the previous example. In the first step we define every MySQL instance of the associated host object. Using dictionaries makes it possible to describe specific settings for each instance (/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_instance["instance-1"] = {
    mysql_health_port = 3306
    mysql_health_username = "user1"
    mysql_health_password = "supersecret"
  }

  vars.mysql_instance["instance-2"] = {
    mysql_health_port = 3307
    mysql_health_username = "user2"
    mysql_health_password = "verysecret"
  }

  vars.mysql_instance["instance-3"] = {
    mysql_health_port = 3308
    mysql_health_username = "user3"
    mysql_health_password = "reallysecret"
  }
}

Next, we’re going to loop over each instance to create the services. Remember that the single MySQL servers are configured in the dictionary of the host object and the settings of the check are defined in the apply rule:

apply Service "mysql-multi-connect" for (mysql_instance => config in host.vars.mysql_instance) {
  import "generic-service"

  display_name = "MySQL Connect " + mysql_instance
  check_command = "mysql_health"

  vars.mysql_health_mode = "connection-time"

  vars += config
}

As a result, this apply rules creates three service checks — one for every MySQL instance described in the host object.icingaweb2 mysql multi instances

Note: This is one of the key features in Icinga 2 — it’s possible to create configuration files according to rules instead of manually specifying every check for every host. Using these techniques saves you a lot of effort while setting up the monitoring environment. It also means that it’s easy to include more MySQL instances or databases, even in very large environments that frequently scale up or down.

Performance Monitoring aka Graphing

Most of the check_mysql_health modes collect performance data that provide additional information about a service check. For example, when you’re testing the connection to the MySQL server you will also see how long it took to connect. Why not visualize that data to understand it better and to detect trends before something goes wrong? In Icinga 2 you can forward the performance data to a time series database like Graphite or InfluxDB for long-term storage.

For more information on how to store performance data have a look at the Icinga 2 Documentation and read about the Graphite feature or the InfluxDB feature. The example below uses Grafana to vizualize the data.

icinga2 grafana mysql