Tuesday, 5 August 2014

Monitoring MySQL using Nagios

Monitoring MySQL using Nagios


This tutorial will discuss the various ways using which you can effectively monitor your MySQL Database using Nagios.



Before we begin, do note that the steps shown here are actually a continuation from my earlier series of tutorials based on Nagios:


Prerequisites:
  • Nagios Monitoring System: Installed and configured (Refer steps HERE)
  • MySQL Database: Installed, configured and located on a remote system on the same network as that of the Nagios Server. Here, I am using a LAMP server (192.168.50.152) on a CentOS 6.5 OS as my client.

NOTE: We are going to monitor our MySQL Database using two methods:
1) Using in-built Plugins
These are the standard set of Plugins that are shipped with Nagios distributions, such as check_tcp, check_mysql.
2) Third-Party Plugin
In this case, we will be installing a 3rd party plugin called check_mysql_health. You can read more about it HERE

So without further adieu, lets get busy!!

1) Using in-built Plugins
Firstly, we need to locate where the Nagios Plugins are stored on our Nagios Server. If you have installed Nagios using binaries or from yum, then they would generally be placed at /usr/lib64/plugins/nagios if your host runs on CentOS or even at /usr/lib/plugins/nagios in case of a Ubuntu-based host.

As you can see from the below screenshot, there are quite a few plugins provided already by Nagios. For our tutorial, we will be using two of them: check_tcp and check_mysql


But first things first, we need to do few modifications to our Database so that it can allow Nagios to monitor it. 

Login to the MySQL database using root as a user.



Execute the following commands one at a time:

# The following command will create a user named "nagios" with a password "password" and grant him access on the local MySQL DB.
CREATE USER 'nagios'@'localhost' IDENTIFIED BY 'password'; 

# The following command issues full permissions to the nagios user.
GRANT ALL PRIVILEGES ON *.* TO 'nagios'@'localhost';

# The following command will create a user named "nagios" with a password "password" and allow him to access the DB from any (%) location.
CREATE USER 'nagios'@'%' IDENTIFIED BY 'password'; 

# The following command issues full permissions to the nagios user from any location.
GRANT ALL PRIVILEGES ON *.* TO 'nagios'@'%';

flush privileges;

NOTE: Make sure MySQL can also listen on all interfaces, besides the default loopback address (127.0.0.1). You can change this setting by editing the my.cfg file and commenting out the bind-address field as shown:

# bind-address          =   127.0.0.1



Next, we configure the plugin on our Nagios Server.

a) check_tcp
This plugin basically tests TCP connections (here, port 3306) with a particular host. 

First, add the "command definition" in the objects/commands.cfg file as shown

define command {
    command_name        check_tcp
    command_line           $USER1$/check_tcp -H $HOSTNAME$ -p $ARG1$
    }



define its corresponding "service" as well in the servers/clients.cfg file as shown:

define service {
    use generic-service lamp
    service_description Check MYSQL via TCP port 
    check_command check_tcp!3306 
}



Restart the nagios service:

# service nagios restart



Check your MySQL Host's "Services" Tab from the Nagios UI. You should see the plugin status as shown:




b) check_mysql
This plugin is used to test connections to a local or remote MySQL Server. 

You can test the Plugin by executing it directly as shown below. Note that I am executing this plugin from /usr/lib64/nagios/plugins directory. 

Syntax
./check_mysql -H <Hostname> -u <MySQL_User> -p <MySQL_Password> -d <Database_Name>

For Example, the following command will return details about a specific MySQL Database such as uptime, threads, open tables etc
# ./check_mysql -H 192.168.50.152 -u nagios -p password -d mysql


Next, we add the "command definition" for check_mysql in the objects/commands.cfg file as shown below:



define command {
    command_name        check_mysql
    command_line           $USER1$/check_mysql -H $HOSTADDRESS$ -u $ARG1$ -p $ARG2$ -d $ARG3$
    }

Save the file and quit the editor



Add the corresponding "service" definition in the /servers/clients.cfg file as well


define service {
    use generic-service lamp
    service_description Check MySQL Database 
    check_command check_mysql!nagios!password!mysql
}

Save the file and exit the editor.



Restart the nagios service:

# service nagios restart


Check your MySQL Host's "Services" Tab from the Nagios UI. You should see the plugin status as shown:




2) Third-Party Plugin
Nagios provides a lot of third party plugins over at Nagios Exchange which you can use according to your needs and requirements. In this case, we will be installing a 3rd party plugin called check_mysql_health. You can read more about it HERE

As per Nagios Exchange "check_mysql_health is a plugin for Nagios that allows you to monitor a MySQL database. Among the list of metrics are time to login, index usage, bufferpool hit rate, query cache hit rate, slow queries, temp tables on disk, table cache hit rate, connected threads, and many more. "

The first thing we need to do is download the Plugin and install it on our nagios Server. To do so, follow these simple steps:

# wget http://labs.consol.de/download/shinken-nagios-plugins/check_mysql_health-2.1.8.2.tar.gz

NOTE: It really doesn't matter which folder you download this Plugin to. It will be moved never the less to its correct destination in the coming steps. 


For the sake of simplicity, I move all my custom and third party plugin sources to the /opt directory. Once there, simply untar the contents of your Plugin as shown:

# mv /check_mysql_health-2.1.8.2.tar.gz /opt

# tar -xvzf check_mysql_health-2.1.8.2.tar.gz



Once the contents of your Plugin are uncompressed, simply follow the three commands listed below to compile the Plugin:

# ./configure



Make sure there were no errors during the execution of the previous command, If so, proceed to the nest step, i.e. make. If not, then do look into them before proceeding:

# make 


The final command will install the plugin and make it ready for use:

# make install


Our Plugin is almost ready for use. if you run an ls command, you will see that a new folder named "plugin-scripts" has been created. A further look into that folder, and we find our Plugin s there.

NOTE: There are two plugins here. One; a perl file (*.pl) and the other is an executable file. You can use either of them for monitoring your MySQL Database. In my case, I have selected the executable file.


Copy either of the Plugins to the Plugins folder for Nagios, i.e. /usr/lib64/nagios/plugins

Once there, you can execute it and test if its working well.


Syntax
./check_mysql_health -H <Hostname> --user <MySQL_User> --password <MySQL_Password> --mode <MODE>

Where <MODE> can be either of:
  • connection-time (Time to connect to the server) 
  • uptime (Time the server is running) 
  • threads-connected (Number of currently open connections) etc
For example, the following command will return the uptime for the specific MySQL 
# ./check_mysql_health -H 192.168.50.152 --user nagios --password password --mode uptime


You can configure the Plugin to work from the Nagios UI as well. Simply follow the same procedure as we did for the rest of our plugins.

First add the "Command Definition" in the /objects/commands.cfg file:

define command {
    command_name        check_mysql_health
    command_line           $USER1$/check_mysql_health -H $ARG1$ --port $ARG2$ --username $ARG3$ -password $ARG4$ -mode $ARG5$
    }

Save the file and quit the editor



next, define the "Service Definition" as well at the /servers/clients.cfg file:

define service {
    use generic-service lamp
    service_description Check MySQL status using PLUGIN 
    check_command check_mysql_health!192.168.50.152!3306!nagios!password!uptime
}

Save the file and exit the editor.



Don't forget to restart the Nagios Service. If you see any errors here, its probably because of mis-configuration either at your commands.cfg file or the clients.cfg file.

# service nagios restart


Check your Nagios UI for the newly added Plugin under the "Services" Tab.




Well, thats it for this post... Next up, Monitoring Vyatta with Nagios!!



No comments :

Post a Comment