Advanced MariaDB monitoring with Nagios

Nagios, one of the most popular hardware, network, and application monitoring tools, can also handle advanced MariaDB monitoring. With Nagios you can monitor the MariaDB server and its performance, as well as individual MariaDB databases and the information in them.

For this article I used the free Nagios Core, but the procedures I describe should be similar for the commercial version of Nagios and for Nagios derivatives such as Shinken.

Installation

To follow this article you should have some knowledge of Nagios and how it works. To get the most out of it you should have a Nagios server on which to practice. If you need help with getting started, check the Nagios documentation for basic installation and configuration instructions.

The simplest way to monitor a MariaDB service is to monitor the process and/or telnet to its TCP port, but for more advanced monitoring you should connect to the MariaDB server and run SQL queries. To do that you need a Nagios plugin such as Hari Sekhon’s Check MySQL Plugin. This plugin is written in Perl, but you don’t need to understand Perl to use it.

To install the plugin, first download it to your Nagios plugins directory, which is usually defined in the $USER1 variable in Nagios’s resource configuration (file /usr/local/nagios/etc/resource.cfg). By default, the plugins directory is /usr/local/nagios/libexec. After you download the plugin, make it executable by running the command chmod +x check_mysql_query.pl.

The SQL check plugin has some minimal requirements. First, you need the libraries from the file Hari Sekhon’s Utils.pm. Download this file and put it in one of the directories in Perl’s include path. If you are not sure which directories these are, run the shell command perl -e "print "@INC"". Then copy the module into one of the listed directories. Second, you need Perl’s JSON module (JSON.pm). You can downloaded it with CPAN in any Linux distro, including Red Hat-based. In Debian and Ubuntu default software repositories there is also a package under the name libjson-perl.

Plugin options and possible checks

The SQL plugin takes the following essential options:

• connection details – these include host (-H), port (-P), user (-u), password (-p), database (-d), and socket (-s)
• query (-q) – the query that should be executed
• output (-o) – the exact value the plugin execution should return. You can also use an option for regex (-r) to check the result, or use the option field (-f) to match only a certain field of the output.
• thresholds – warning (-w) and critical (-c) specify thresholds to be compared with the result from the plugin execution.

You can find more options by running the command /usr/local/nagios/libexec/check_mysql_query.pl.

Once you know which options you need for your checks, you should define the monitoring command in Nagios. Unless you use a custom Nagios configuration, define the command in the file /usr/local/nagios/etc/objects/commands.cfg. Create a new configuration block like this:

define command{
        command_name    check_mariadb_sql
        command_line    $USER1$/check_mysql_query.pl -H $HOSTADDRESS$ $ARG1$
        }

This is a simple command definition that assumes that all command arguments except the hostname (-H) will be passed dynamically with the ARG1 variable.

Before creating each MariaDB Nagios check you should test the corresponding command and its parameters from the command line. For example, suppose you want to check the memory usage of a MariaDB server. You can do it with a query such as select round(VARIABLE_VALUE/1024/1024) from information_schema.GLOBAL_STATUS where VARIABLE_NAME = "Memory_used";. This query should return the used memory in MB. The corresponding plugin command and its output look like this:

./check_mysql_query.pl -H centos7.example.org -u monitor -p monitor_password -d information_schema -q 'SELECT ROUND(VARIABLE_VALUE/1024/1024) FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = "Memory_used"' -w 512 -c 1024
OK: query returned '261' | mysql_query_time=0.0021s

In the above example I ran the query with warning value of 512 (MB) and critical value of 1024 (MB). If the memory used were above these thresholds I would get either a warning or critical message. The above output shows an OK status because the example centos7.example.org MariaDB server uses only 261MB of memory.

Similarly, you can run a check for the number of current connections like this:

./check_mysql_query.pl -H centos7.example.org -u monitor -p monitor_password -d information_schema -q 'SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = "Connections"' -w 50 -c 100
OK: query returned '25' | mysql_query_time=0.0021s

The plugin can also match the result of the command against a certain field. This powerful option allows you to work with even more complex checks. For example, suppose you have the employees database and you want to make sure that the person with the highest salary has last name Facello (Because that’s the boss’s name). Here is how that command should look:

./check_mysql_query.pl -H centos7.example.org -u employees_user -p employees_password -d employees -q 'SELECT MAX(salaries.salary), employees.last_name FROM salaries LEFT JOIN employees on salaries.emp_no=employees.emp_no' -f 'last_name' -o 'Facello'
OK: query returned 'Facello' | mysql_query_time=2.4555s

The two new options here are -f for field (last_name) and -o for expected output (Facello). If someone else were to start receiving a higher salary than Mr. Facello, you would get a critical Nagios message.

Nagios service check definition

Once you have tested the SQL plugin commands you can proceed with defining your Nagios checks. You should already have the target host configured, so you can just add the extra MariaDB checks to it. Here’s the way a check for memory should look for the host centos7.example.org:

define service{
        use                             local-service
        host_name                       centos7.example.org
        service_description             MariaDB Memory Used
        check_command                   check_mariadb_sql!-u monitor -p monitor_password -d information_schema -q 'SELECT ROUND(VARIABLE_VALUE/1024/1024) FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = "Memory_used"' -w 512 -c 1024
        }

This code should be included along with the rest of the checks for your hosts in a file such as /usr/local/nagios/etc/objects/hosts.cfg. In the above definition the only omitted argument from the original command is for the host (-h), because it is automatically determined. Use the default local-service template, provided with every Nagios installation, for setting up the usual variables for service_notification_period, service_notification_commands, and so on.

As you can see, it’s not hard to create even complex MariaDB monitoring checks. With Nagios and an extra module you can monitor even employee salaries stored in a MariaDB database.