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

How to Install Nagios Plugins in CentOS

Prerequisites - Common

These are the common set of packages required for compiling most of the plugins. SNMP and required modules are included here are they are one of the most common types of network monitoring.

CentOS 6.x / 7.x

yum install -y gcc glibc glibc-common make gettext automake autoconf wget openssl-devel net-snmp net-snmp-utils epel-release yum install -y perl-Net-SNMP

Download latest nagios-plugins source and install using following commands

cd /opt wget http://nagios-plugins.org/download/nagios-plugins-2.2.1.tar.gz tar xzf nagios-plugins-2.2.1.tar.gz cd nagios-plugins-2.2.1

Now compile and install nagios plugins

./configure --with-nagios-user=nagios --with-nagios-group=nagios make make install

Setup the nagios user:

useradd nagios passwd nagios

Plugin Installation Location

The plugins will now be located in /usr/local/nagios/libexec/.

How to Install - Oracle Plugin check_oracle_health (CentOS 6)

Prerequisites

Oracle software

The Oracle database server or instant client must be up and running.

CentOS packages

yum -y install cpan sharutils

Perl packages

Find out the absolute path of ORACLE_HOME and set the Oracle environment.
This is required for the installation of DBD::Oracle.

if Oracle 11gR2 server installated

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 export LD_LIBRARY_PATH=$ORACLE_HOME/lib

if Oracle Instantclient installated

export ORACLE_HOME=/usr/lib/oracle/11.2/client64 export LD_LIBRARY_PATH=$ORACLE_HOME/lib

Install Perl DBD::Oracle module

wget http://www.cpan.org/modules/by-module/DBD/DBD-Oracle-1.74.tar.gz tar zxvf DBD-Oracle-1.74.tar.gz cd DBD-Oracle-1.74 perl Makefile.PL -l make && make test make install

Download and install the check_oracle_health plugin

Check for the latest version at http://labs.consol.de/lang/en/nagios/check_oracle_health/, scroll down to "Download".

cd /tmp wget https://labs.consol.de/assets/downloads/nagios/check_oracle_health-3.1.0.3.tar.gz tar xzf check_oracle_health-3.1.0.3.tar.gz cd /tmp/check_oracle_health-* ./configure make make install cp /usr/local/nagios/libexec/check_oracle_health /usr/lib64/nagios/plugins/

Set up the Oracle user "nagios" in target server

# as root: su - oracle sqlplus / as sysdba CREATE USER nagios IDENTIFIED BY oradbmon; GRANT CREATE SESSION TO nagios; GRANT SELECT any dictionary TO nagios; GRANT SELECT ON V_$SYSSTAT TO nagios; GRANT SELECT ON V_$INSTANCE TO nagios; GRANT SELECT ON V_$LOG TO nagios; GRANT SELECT ON SYS.DBA_DATA_FILES TO nagios; GRANT SELECT ON SYS.DBA_FREE_SPACE TO nagios; exit # leave SQL*Plus exit # back to root

Test locally

Please replace the path to ORACLE_HOME and the connect string as appropriate. In the following example,
the connect string refers to the alias in $ORACLE_HOME/network/admin/tnsnames.ora if your install server version
if you install instantclient connect string refers to the alias in /etc/tnsnames.ora

/usr/local/nagios/libexec/check_oracle_health --connect XE --user nagios --password oradbmon --environment ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe --mode tablespace-usage

Now you have to configure the check command in nagios. For the command to work, it needs to have the oracle client paths added to the environment variables – ORACLE_HOME, LD_LIBRARY_PATH,PATH. To avoid all sorts of issues in passing environment variables in nagios and altering the startup script of nagios to set them (as described on the author’s site), just pass the variables directly in the command. The latest version of check_oracle_health allows that.

Define command in /usr/local/nagios/etc/objects/command.cfg

vi /usr/local/nagios/etc/objects/command.cfg

# Oracle Database check health command # check_oracle_health    !  SID  !  USER  !  Pass  !  Warn  !  crit  !  mode define command {         command_name check_oracle_health         command_line $USER1$/check_oracle_health --environment PATH=$PATH:$ORACLE_HOME/bin --environment LD_LIBRARY_PATH=$ORACLE_HOME/lib --connect $ARG1$ --user $ARG2$ --password $ARG3$ --mode $ARG4$         } # Oracle Check Tablespace per name # check_oracle_health_tbs_name    !  SID  !  USER  !  Pass  !  Warn  !  crit  !  mode  ! tbs_name define command {         command_name check_oracle_health_tbs_name         command_line $USER1$/check_oracle_health --environment PATH=$PATH:$ORACLE_HOME/bin --environment LD_LIBRARY_PATH=$ORACLE_HOME/lib --connect $ARG1$ --user $ARG2$ --password $ARG3$ --mode $ARG4$ --name $ARG5$         }

Define service in /usr/local/nagios/etc/servers/oracledbtest.cfg

define host{ use                                     linux-server host_name                               oracledbtest alias                                   oracledbtest address                                 192.168.xx.yy max_check_attempts                      5 check_period                            24x7 notification_interval                   30 notification_period                     24x7 } ### Change last argument to the different modes ### tns-ping , process-usage , sga-shared-pool-free define service {         use                             local-service         host_name                       oracledbtest         service_description             Oracle Connection Time         check_command                   check_oracle_health!XE!nagios!oradbmon!connection-time         notifications_enabled           1 } define service {         use                             local-service         host_name                       oracledbtest         service_description             Oracle Connected Users         check_command                   check_oracle_health!XE!nagios!oradbmon!connected-users         notifications_enabled           1 } ### Change last argument to the different tablespace ### users, undotbs define service {         use                             local-service         host_name                       oracledbtest         service_description             Oracle Tablespace Usage Users         check_command                   check_oracle_health_tbs_name!XE!nagios!oradbmon!tablespace-usage!users         notifications_enabled           1 }

You can add all the modes you want. You can manually test the command like so. It’s very important that the ORACLE_HOME and other variables are set. They can be passed to the command directly, this way you don’t need to modify your nagios startup script to set the vars in the nagios user enviornment.

Install Oracle Instant Client on Linux CentOS

The installation of an Oracle client on a server is always a tedious task and although you have done it many times, there are always things to review.

In this case we are going to install an Oracle InstantClient of version 11 for the CentOS operating system.

As we also want to be able to compile applications that will be used by Oracle libraries, we will also have to install the header files.


Download installation packages

The installation files can be found at this URL:

https://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

The client version to be used is 11.2.0.4.0

The packages to download are:

oracle-instantclient11.2-basic-11.2.0.4.0–1.x86_64.rpm oracle-instantclient11.2-devel-11.2.0.4.0–1.x86_64.rpm oracle-instantclient11.2-sqlplus-11.2.0.4.0–1.x86_64.rpm

The package with sqlplus is only needed to check that the installation was correct


Installing the RPM files

We proceed to the installation of previously downloaded packages

$ sudo yum localinstall -y oracle-instantclient11.2-basic-11.2.0.4.0–1.x86_64.rpm $ sudo yum localinstall -y oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm $ sudo yum localinstall -y oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm

Environment Variables

We will need to define some environment variables for the Oracle client to work correctly

$ export ORACLE_HOME=/usr/lib/oracle/11.2/client64 $ export TNS_ADMIN=$ORACLE_HOME/network/admin $ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib $ export PATH=$PATH:$ORACLE_HOME/bin

To be able to work with the tnsnames.ora file, we will have to copy it to the directory defined in the environment variable

$ sudo mkdir -p /usr/lib/oracle/11.2/client64/network/admin

Create new /usr/lib/oracle/11.2/client64/network/admin/tnsnames.ora like this:

TEST =   (DESCRIPTION =     (ADDRESS_LIST =         (ADDRESS =                  (PROTOCOL = TCP)           (Host = testdboracle)           (Port = 1521)         )     )     (CONNECT_DATA = (SID = ORCL)     )   )

Installation test

To verify that the installation was correct, we will open a sqlplus session against one of our databases

Connect to Your database with:

$ sqlplus scott/tiger@TEST

You can also use the Easy Connect connection string format (does not use TNSNAMES):

$ sqlplus scott/tiger@testdboracle:1521/ORCL

Monitor ssh on non-default port with Nagios

In ssh pluging config /usr/local/nagios/etc/objects/commands.cfg, there an alias named check_ssh_port.
if it not existe you may define it like above:

define command{ command_name check_ssh_port command_line $USER1$/check_ssh  -p $ARG1$ $HOSTADDRESS$ }

in the service file /usr/local/nagios/etc/servers/hosttomonitor.cfg, define you ssh service to monitor like this:

define host{ use                                 linux-server host_name                           hosttomonitor.localnet alias                               hosttomonitor address                             10.10.10.19 max_check_attempts                  5 check_period                        24x7 notification_interval               30 notification_period                 24x7 } define service {         use                         generic-service         host_name                   hosttomonitor.localnet         service_description         SSH2         check_command               check_ssh_port!1422         notifications_enabled       0         }

replace 1422 by you default ssh port and server by your target.

finally restart nagios service