Monitor Mysql Server with Nagios

Steps to monitor Mysql Server

In this article, we are going to monitor Mysql Server with most popular monitoring tool called Nagios Monitoring.
I hope you already install Nagios server and NRPE Service if not or you are new who just started working on Nagios then refer below links which help you to clear your doubts regarding Nagios Application.

Default Nagios plugins installation only provide you some plugins which help us to monitor default services and application on the server. In such case, we need to install custom plugins which help us to monitor particular services and applications on Server.

When we face any issue related to our MySql server we only come to know about this issue when our client informs us 🙂 or when we realized our website is down and after that, we go on server start checking for an issue. After wasting so much time on finding an issue we understand our MySql server causing the issue.
But when we have already monitoring tool which informed us before any critical things happen on the server and then we simply go on a server and fixed the issue without wasting any time. That’s the beauty of any monitoring tool.

This article helps you in how to configure Nagios to monitor your Mysql servers.

Below are the parameters we are going to monitor on Mysql :

  • check_mysql_health-connection-time
  • check_mysql_health-threads-connected 
  • check_mysql-table-lock-contention 
  • check_mysql-tmp-disk-tables
  • check_mysql-slow-queries
  • check_mysql-long-running-procs
  • check_mysql-open-files 
  • check_mysql_health-uptime

Installation and Configuration Of Mysql Custom Plugins

Step 1 : Install Dependencies

Before start installing MySQL plugins we need to install some plugin dependency on the server.
Below are some mysql plugin dependencies :

  • perl-Time-HiRes [centos,redhat]
  • perl-DBD-mysql [centos,redhat]
  • DBD::mysql [Debian/ubuntu]
  • Time::HiRes [Debian/ubuntu]

If you are using centos/Redhat then install above two packages with yum command and if you are using Debian/ubuntu then install last two packaged with cpan command.

Ubuntu/Debian :
  • apt-get install libmysqlclient-dev [This is dependency package to install DBD::mysql on Debian/Ubuntu]
  • cpan install DBD::mysql
  • cpan install Time::HiRes
Centos/Redhat :
  • yum install perl-Time-HiRes
  • yum install perl-DBD-mysql

Step 2 : Download Custom Mysql Plugins

Now download the MySql custom plugins with wget command on the server under the /opt directory and extract with tar command then follow below commands one by one.

  • wget https://labs.consol.de/assets/downloads/nagios/check_mysql_health-3.0.0.5.tar.gz
  • tar -zxvf check_mysql_health-3.0.0.5.tar.gz
  • cd /opt/check_mysql_health-3.0.0.5
  • ./configure
  • make
  • make install

Step 3 : Copy Script file to nagios plugins directory

When you run make install command it will generate new directory under the "/opt/check_mysql_health-3.0.0.5" with the name of plugins-scripts and in this directory, you will have script or plugin called “check_mysql_health”. This script will monitor the parameters of MySQL server. Copy this script to "/usr/local/nagios/libexec/" directory.

  • cd /opt/check_mysql_health-3.0.0.5/plugins-scripts/
  • cp check_mysql_health /usr/local/nagios/libexec/

Step 4 : Syntax to monitor mysql services

Here I have explained or monitor some of important parameters of mysql server. If you want to monitor some more parameters then you will find the name of parameters and the syntax to use those parameter by running below command,

  • /usr/local/nagios/libexec/check_mysql_health --help
  • Syntax : check_mysql_health [-v] [-t ] --hostname= [--port ] --username= --password= --mode=

Step 5 : Change name of Script file

Now copy this check_mysql_health script with attached name of the mysql parameter  which you are going to monitor. It will help you to keep track of which script use for which parameter if you want then keep the same name of script no issues.

  • cp check_mysql_health check_mysql_health-threads-connected
  • cp check_mysql_health check_mysql_health-uptime
  • cp check_mysql_health check_mysql_health-connection-time
  • cp check_mysql_health check_mysql_health-table-lock-contention
  • cp check_mysql_health check_mysql_health-tmp-disk-tables
  • cp check_mysql_health check_mysql_health-slow-queries
  • cp check_mysql_health check_mysql_health-long-running-procs
  • cp check_mysql_health check_mysql_health-open-files

Step 6 : Configuration of nrpe.cfg file

Open the nrpe.cfg file which basically resides under “/etc/nagios” and add the commands to monitor MySQL service. You can change the threshold values or parameters as per your requirements.

vim /etc/nagios/nrpe.cfg

# Mysql Service Monitor

command[check_mysql_health-threads-connected]=/usr/local/nagios/libexec/check_mysql_health-threads-connected --hostname localhost --user root --password root --mode threads-connected

command[check_mysql_health-uptime]=/usr/local/nagios/libexec/check_mysql_health-uptime --hostname localhost --user root --password root --mode uptime

command[check_mysql_health-connection-time]=/usr/local/nagios/libexec/check_mysql_health-connection-time --hostname localhost --user root --password root --mode connection-time

command[check_mysql_health-table-lock-contention]=/usr/local/nagios/libexec/check_mysql_health-table-lock-contention --hostname localhost --user root --password root --mode table-lock-contention

command[check_mysql_health-tmp-disk-tables]=/usr/local/nagios/libexec/check_mysql_health-tmp-disk-tables --hostname localhost --user root --password root --mode tmp-disk-tables

command[check_mysql_health-slow-queries]=/usr/local/nagios/libexec/check_mysql_health-slow-queries --hostname localhost --user root --password root --mode slow-queries

command[check_mysql_health-long-running-procs]=/usr/local/nagios/libexec/check_mysql_health-long-running-procs --hostname localhost --user root --password root --mode long-running-procs

command[check_mysql_health-open-files]=/usr/local/nagios/libexec/check_mysql_health-open-files --hostname localhost --user root --password root --mode open-files

Save and close the file.

Note : change the username password with your MySQL credentials and if you are using separate MySQL server then defined ip address instead of localhost.

Step 7 : Restart the nrpe service.

  • /etc/init.d/nagios-nrpe-server restart

Step 8 : Configuration on Nagios Server

Now come to the Nagios host server and go to the “/usr/local/nagios/etc/” directory.
Here you can create one directory with the name of your project and touch below two files then add services and commands.

  • cd /usr/local/nagios/etc
  • mkdir projectname
  • cd projectname
  • touch hosts.cfg
  • touch projectname-hosts.cfg

Step 9 : configure hosts.cfg & projectname-hosts.cfg file

Now open hosts.cfg file and add below lines if you want change alias name, hostname then give it as per your requirement.

vim hosts.cfg

define host{
                  use                 linux-server
                  host_name     nagios_host
                  alias               mysql-service-monitor
                  address          127.0.0.1 [if using seprate mysql server use ip of that server instead of 127.0.0.1]
                  }

Open another created file i.e. projectname-hosts.cfg. As of now, i have added MySQL monitor service in a separate file for easy to understand if you want to add server monitor commands in the same file then add it.

vim projectname-hosts.cfg

define service{
use generic-service
host_name nagios_host
service_description mysql-connection-time
check_command check_nrpe!check_mysql_health-connection-time
}


define service{
use generic-service
host_name nagios_host
service_description mysql-uptime
check_command check_nrpe!check_mysql_health-uptime
}


define service{
use generic-service
host_name nagios_host
service_description mysql-threads-connected
check_command check_nrpe!check_mysql_health-threads-connected
}


define service{
use generic-service
host_name nagios_host
service_description mysql-table-lock-contention
check_command check_nrpe!check_mysql_health-table-lock-contention
}


define service{
use generic-service
host_name nagios_host
service_description mysql-tmp-disk-tables
check_command check_nrpe!check_mysql_health-tmp-disk-tables
}


define service{
use generic-service
host_name nagios_host
service_description mysql-slow-queries
check_command check_nrpe!check_mysql_health-slow-queries
}


define service{
use generic-service
host_name nagios_host
service_description mysql-long-running-procs
check_command check_nrpe!check_mysql_health-long-running-procs
}


define service{
use generic-service
host_name nagios_host
service_description mysql-open-files
check_command check_nrpe!check_mysql_health-open-files
}


Save and close the file.

Step 9 : Permissions for files

Give permissions to created files and restart the nrpe and Nagios service.

  • chown -R nagios.nagioscmd projectname
  • /usr/local/nagios/bin/nagios -v /usr/local/nagios/etc/nagios.cfg
  • /etc/init.d/nagios-nrpe-server restart
  • /etc/init.d/nagios restart
Nagios Mysql Monitor

If you’re facing any difficulties while configuring MySQL service monitoring. Please do comment your queries or problem via the comment section, till then stay tuned to techthings.org for more such valuable articles.
I recommended before implementing this article go through below articles which give you more understanding about Nagios Monitoring.


Related Posts :

Latest Comments
  1. sachin bhomale November 28, 2016

Leave a Reply

Your email address will not be published. Required fields are marked *