Monitor MySQL service in Nagios using check_mysql

Monitor MySQL service
Monitor MySQL service

Monitor Mysql Service with Nagios

In this article, we are going to monitor the Mysql Server with the 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.

Read: Steps to install Nagios Core in Linux

Read: Monitor Remote Host using Nagios and NRPE Service

With the Default, Nagios plugins provide you some plugins which help us to monitor default services and application on the server.

In such a case, we need to install custom plugins that help us to monitor particular services and applications on Server.

The motive behind monitoring the Mysql Server is that before the client informs us about the website’s unreachability. We should come to know in advance about the Mysql Server health and before it’s going down, we will take precautionary actions and resolve the issue.

It will save lots of our debugging time and also the reputation of our organization in front of the client.

Below are the parameters, we are going to monitor on Mysql server,

  • 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

Step 1: Install the 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 the above two packages with yum command and if you are using Debian/ubuntu then install the last two packaged with “cpan command”.

Debian/Ubuntu

$ 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 Plugin

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 Files

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 the 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/

Syntax :

In this article, I have provided information about some important parameters which need to monitor on Mysql Server. If you want to monitor any other parameter then you will get the information with below syntax,

$ /usr/local/nagios/libexec/check_mysql_health --help

Syntax : check_mysql_health [-v] [-t ] --hostname= [--port ] --username= --password= --mode=

Step 4: Rename the Script File

Now copy this check_mysql_health script with the 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 the 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 5: Configure 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 6: Restart the NRPE Service

To take effect on the configuration changes, we need to restart the NRPE service.

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

Step 7: Configuration on Nagios Server

Now login 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 8: Configure the host.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: Permission for Files

Give permission 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
Monitor mysql service

If you’re facing any difficulties while configuring MySQL service monitoring. Please do comment queries or problems via the comment section, till then stay tuned to Linuxgrow.com for more such valuable articles.

Prashant

Welcome to Linuxgrow. I'm Prashant, a tech-blogger from Mumbai, India. I started Linuxgrow as a passion and to share my knowledge about technologies. Here at Linuxgrow, I write about Linux technologies, Aws Cloud, Wordpress blogging and scripting knowledge. You can read more about me at About us page. Thank You :)

You may also like...

Leave a Reply

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

Facebook