How to monitor MySQL using Nagios

How to configuring Nagios to monitor your MySQL server. Before we begin, there are a few prerequisites that you have to fulfill


Prerequisites

1. Operating System - CentOS.


2. Nagios Core - Installed and configured.


3. Nagios Plugins should be installed.


4. MySQL server(or MariaDB) should be installed on your server.

MySQL User Creation

1. Log in to your MySQL server with the following command.



# mysql -u root -p



2. Type in the following commands.



mysql> CREATE USER 'nagios'@'localhost' IDENTIFIED BY 'nagiospasswd'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'nagios'@'localhost'; mysql> CREATE USER 'nagios'@'%' IDENTIFIED BY 'nagiospasswd'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'nagios'@'%'; mysql> flush privileges;

These commands create a user "nagios" that can be used by local host as well as remotely. We have also granted all privileges to the user.


Install of the perl-modules DBI and DBD::mysql

Log in to nagios server use the following command to install perl-modules DBI and DBD::mysql

# yum -y install perl-DBI # yum -y install "perl(DBD::mysql)"

MySQL Plugin Installation

1. Run the following command to download the "check_mysql_health-2.2.2.tar.gz" package.



# wget https://labs.consol.de/assets/downloads/nagios/check_mysql_health-2.2.2.tar.gz



2. Extract the downloaded package.



# tar -zxvf check_mysql_health-2.2.2.tar.gz



3. Change into the check_mysql_health-2.2.2 directory.



# cd check_mysql_health-2.2.2



4. Run the following commands to configure and install the plugin.



# ./configure --prefix=/usr/local/nagios --with-nagios-user=nagios --with-nagios-group=nagios --with-perl=/usr/bin/perl # make # make install

Nagios Configuration for MySQL Monitoring

1. "check_mysql_health" Command Definition

The following file contains all the commands pertaining to Nagios. Open it in any text editor of your choice("vi" in this case).



# vi /usr/local/nagios/etc/objects/commands.cfg


Edit the file and add the "check_mysql_health" command block.



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



2. "check_mysql_health" Service Definition

Open the file "hosttomonitor.cfg" to add the service definition to the host that will be monitored.



# vi /usr/local/nagios/etc/servers/hosttomonitor.cfg


Add the following service blocks of Connection-time, io thread and sql thread. This will perform different tasks.



define service{ use local-service host_name hosttomonitor service_description MySQL connection-time check_command check_mysql_health!192.168.30.10!nagios!nagiospasswd!3306!connection-time! } define service{ use local-service host_name hosttomonitor service_description MySQL slave-io-running check_command check_mysql_health!192.168.30.10!nagios!nagiospasswd!3306!slave-io-running! } define service{ use local-service host_name hosttomonitor service_description MySQL slave-sql-running check_command check_mysql_health!192.168.30.10!nagios!nagiospasswd!3306!slave-sql-running! }



3. Restart the Nagios server.



# service nagios restart

You can configure other MySQL services as well whose information can be found on the website.

https://labs.consol.de/nagios/check_mysql_health/index.html