You should have a Nagios Core 4.0 or newer server with at least one host configured already. We'll use the example of delphi.example.net, a host defined in its own file. You should also understand the basics of how hosts and services relate, which is covered in the recipes in Chapter 1, Understanding Hosts, Services, and Contacts.

For a check on a remote host to work from the monitoring server, the database server will need to be listening on an appropriate network interface. It's also necessary to make sure that an appropriate database user account exists with which the check_mysql plugin may authenticate. It's a good idea to make this into a dedicated user with no privileges on any database, because the credentials need to be stored in plain text, which could be a security risk if more sensitive credentials were used.

For MySQL, we could create a new user with no privileges with the following, assuming that the monitoring server olympus.example.net has 192.0.2.11 as an IPv4 address. I've used a randomly generated password here:

mysql> CREATE USER 'nagios'@'192.0.2.11' IDENTIFIED BY 'UVocjPHoH0';

We can then check the connectivity using the mysql client on the monitoring server:

$ mysql --host=delphi.example.net --user=nagios --password=UVocjPHoH0
mysql>

Or alternatively, by running the plugin directly from the command line as the nagios user:

# sudo -s -u nagios
$ /usr/local/nagios/libexec/check_mysql -H delphi.example.net -u nagios -p UVocjPHoH0
Uptime: 1631  Threads: 1  Questions: 102  Slow queries: 0  Opens: 99  
Flush tables: 1  Open tables: 23  Queries per second avg: 0.62

Newer versions of the plugins may also include extra performance information after the preceding output.

If we did not have the MySQL libraries installed when we built the Nagios plugins, we may find that we do not have the check_mysql and check_mysql_query binaries in /usr/local/nagios/libexec. We can fix this by installing the MySQL shared libraries on the monitoring system and rebuilding and reinstalling the Nagios Plugins package.

By default, it's also necessary to define new commands to actually use these plugins as well, which we'll do in this recipe.

We can set up some basic database monitoring for our MySQL server as follows:

With this done, a new service check with the description MYSQL will be added for the delphi.example.net host, which will employ the check_mysql plugin to report the status of the MySQL server. The output will also include statistics about its uptime, open tables, and query averages, and will be visible in the web interface under Services like all service output.

This configuration defines a new command named check_mysql to use the plugin of the same name, accepting two arguments—the first being the username of the test Nagios Core user, in this case nagios, and the second being the password for that user. The check_mysql plugin acts as a MySQL client using the credentials provided to it and requests diagnostic information from the database, which it returns as part of its check.

If it has problems connecting to or using the MySQL server, it will flag a status of CRITICAL and generate appropriate notifications.

We can optionally check access to a specific database using the plugin by supplying a value to the -d parameter. This should be a database to which the nagios user has been given access, otherwise the check would fail.

If we want to check that we can actually run a query after connecting, we could extend this even further to use the check_mysql_query plugin:

define command {
command_name  check_mysql_query
command_line  $USER1$/check_mysql_query -H $HOSTADDRESS$ -u $ARG1$ -p $ARG2$ -d $ARG3$ -q $ARG4$
}
define service {
    use                  generic-service
    host_name            delphi.example.net
	service_description  MYSQL_QUERY
	check_command        check_mysql_query!nagios!UVocjPHoH0!exampledb!"SELECT COUNT(1) FROM exampletbl"
}

The preceding code would attempt to run the query SELECT COUNT(1) FROM exampletbl on the exampledb database. Note that it is important to wrap the query in quotes so that it gets processed as one argument rather than several.

A similar service check to the one specified in this recipe could be configured for PostgreSQL database servers using the check_pgsql plugin, also part of the standard Nagios Plugins set. The command and service check definitions might look something like this:

define command {
    command_name  check_pgsql
    command_line  $USER1$/check_pgsql -H $HOSTADDRESS$ -p $ARG1$
}
define service {
    use                  generic-service
    host_name            delphi.example.net
    service_description  PGSQL
    check_command        check_pgsql!N4Nw8o8X
}

In the preceding example, access would need to be granted on the PostgreSQL server for the monitoring server's IP address in pg_hba.conf, with access to the default standard template1 database.

In production environments, it's often the case that, for security or programming policy reasons, database servers are not actually configured to accept direct connections over network interfaces, even on secure interfaces. Packaged MySQL and PostgreSQL servers on many systems will, in fact, default to listening only on the localhost interface on 127.0.0.1.

This can complicate the monitoring setup a little, but it can usually be addressed by installing a remote Nagios plugin execution agent on the database server, such as NRPE or NSclient++. NRPE usage is addressed in Chapter 6, Enabling Remote Execution, of this book and uses a MySQL server configured in this way as a demonstration of the concept.