Monday, August 13, 2012

Nagios monitoring mysql

SkyHi @ Monday, August 13, 2012

I was asked by a client to configure nagios to monitor two database servers, running on Redhat
Enterprise Linux 5. Here are the steps, including a couple mis-steps to get it working. Nagios
was already set up and running on a server called monitor, which is running CentOS5.
I had two options. I could directly monitor the databases from monitor using the check_mysql or
run check_mysql on the database server and call it through check_by_ssh. I started out configuring
the method over ssh.
First, in each database server I created a user nagios and set a password for that user. I then
created a set of keys:
ssh-keygen -t dsa
I set no passphrase for the key, since I intended it to have a single purpose and limited
access to the database servers. I then tested the access to see if there were any glitches.
It worked on one server but not the other. After a quick once over, I decided to proceed and
solve that problem later. Since I needed check_mysql I compiled the plugins
From the nagios plugins web site, I downloaded nagios-plugins-1.4.14.tar.gz to each of the
database servers. However, because not all the necessary mysql packages were in place, it
threw some errors during the configure stage.
./configure –with-nagios-user=nagios –with-nagios-group=nagios –with-mysql=/usr
The main error that interfered with my plans was the failure to build the check_mysql plugin.
After some research I discovered that the failure was probably due to the absence of some
mysql libraries that would be in a development package. However, RHEL5 doesn’t have such a
package in their repository for RHEL5. It is available in some of the alternate repositories. It’s not really that surprising, when I think about it, that the package is unavailable, RHEL isn’t intended to be a development platform, it’s a server platform. I didn’t want to add alternate repositories without permission from the client.
So I went for plan B. I decided to configure the check-mysql to run on the monitor server
and attach to the mysql database over the network. There is a danger that If not carefully configured this could represent a security vulnerability for the database server. To make it as secure as possible
I logged into mysql on each of the database servers and created special access rules for this
purpose. I created a special nagios user with it’s own password and gave it read only permissions
and only on one database.
grant select on database.* to nagios@monitor identified by “password”
Now the user nagios can read that database. It doesn’t have any more privileges, so it’s unlikely to be used to damage the database, even if the monitor were compromised. The bad guys won’t
be able to use the compromise of the monitor to also compromise or damage the database server.
To test my work I issued the following command:
/usr/local/nagios/libexec/check_mysql -d database -u nagios -p password -H $HOSTNAME$
$HOSTNAME is the ip of the database server.
The data came back:
Uptime: 528011 Threads: 61 Questions: 83799845 Slow queries: 38527 Opens:
11365 Flush tables: 1 Open tables: 1003 Queries per second avg: 158.709
To prevent the username and password from being exposed in the web interface I put some of the command values in resource.cfg
###########################################################################
#
# RESOURCE.CFG – Sample Resource File for Nagios 3.0b6
#
# Last Modified: 09-10-2003
#
# You can define $USERx$ macros in this file, which can in turn be used
# in command definitions in your host config file(s).  $USERx$ macros are
# useful for storing sensitive information such as usernames, passwords,
# etc.  They are also handy for specifying the path to plugins and
# event handlers – if you decide to move the plugins or event handlers to
# a different directory in the future, you can just update one or two
# $USERx$ macros, instead of modifying a lot of command definitions.
#
# The CGIs will not attempt to read the contents of resource files, so
# you can set restrictive permissions (600 or 660) on them.
#
# Nagios supports up to 32 $USERx$ macros ($USER1$ through $USER32$)
#
# Resource files may also be used to store configuration directives for
# external data sources like MySQL…
#
###########################################################################
# Sets $USER1$ to be the path to the plugins
$USER1$=/usr/local/nagios/libexec
# Sets $USER2$ to be the path to event handlers
#$USER2$=/usr/local/nagios/libexec/eventhandlers
# Store some usernames and passwords (hidden from the CGIs)
$USER3$=nagios
$USER4$=password
###############################################################################################
commands.cfg is where I put my tested command
############################### commands.cfg ######################
define command{
command_name check-mysql
command_line $USER1$/check_mysql -d tracking -u $USER3$ -p $USER4$ -H $HOSTADDRESS$
}
####################################################################
Next I need to tell nagios where to send messages.
############################### contacts.cfg #######################
define contact{
contact_name pacneil
use generic-contact
alias Neil Schneider
email pacneil@linuxgeek.net
}
define contactgroup {
contactgroup_name pacneil
alias Test Group
members pacneil
}
######################################################################
What group of servers are we going to monitor?
############################### host_groups.cfg #####################
define hostgroup{
hostgroup_name db-host-group
alias Database Servers Host Group
hostgroup_members db-slave-host-group
}
define hostgroup{
hostgroup_name db-slave-host-group
alias Slave Database Servers Host Group
}
######################################################################
And we need to configure some parameters how we want to display the hosts in the web interface.
############################### hosts.cfg ############################
define host{
use db-server
host_name db3.servers.pmc
hostgroups db-slave-host-group,lb1-host-group,rackspace-host-group
alias db3
display_name Db3
address 74.205.65.35
parents app2.servers.pmc
2d_coords 100,0
3d_coords -5,4,1
}
define host{
use db-server
host_name db4.servers.pmc
hostgroups db-slave-host-group,lb1-host-group,rackspace-host-group
alias db4
display_name Db4
address 74.205.65.36
parents app2.servers.pmc
2d_coords 200,0
3d_coords -5,4,-1
}
######################################################################
And I create a service group just for database servers.
########################## service_groups.cfg ########################
define servicegroup{
servicegroup_name db-server-service-group
alias Database Server Service Group
servicegroup_members server-service-group
}
######################################################################
Then I define the service
############################ services.cfg ############################
define service{
use server-service
name db-server-service
servicegroups db-server-service-group
hostgroup_name db-host-group
register 0
}
######################################################################

REFERENCES
http://www.linuxgeek.net/2010/06/24/nagios-monitoring-mysql/