Monitoring MySQL using Nagios

Installing Nagios

Nagios is a monitoring system that monitors system operation status and network information. Nagios can monitor the specified local or remote hosts and services, and provide exception notification functions

Nagios can run on Linux/Unix platform and provide an optional browser based WEB interface to facilitate system managers to view network status, various system problems, logs and so on.

Open the official document of Nagios and you will find that Nagios basically does not have any dependent packages. It only requires that the system be Linux or other systems supported by Nagios. However, if you do not install apache (http service), you will not have such an intuitive interface to view monitoring information, so apache is a prerequisite for the moment.

Install Apache

sudo yum install httpd
sudo service httpd start
./configure --prefix=/usr/local/nagios --with-nagios-user=nagios --with-nagios-group=nagios --with-perl --with-statefiles-dir=/tmp

Installing Nagios

Install environment package

yum install -y \
-- gcc \
-- glibc \
-- glibc-common \
-- gd \
-- gd-devel \
-- xinetd \
-- openssl-devel

Create administrative user

useradd -s /sbin/nologin nagios

Create the installation directory and specify the primary group

mkdir /usr/local/nagios
chown -R nagios.nagios /usr/local/nagios

Compile and install nagios

Nagios download address

wget https://assets.nagios.com/downloads/nagioscore/releases/nagios-4.4.3.tar.gz
tar -zxvf nagios-4.4.3.tar.gz
cd nagios-4.4.3
./configure --prefix=/usr/local/nagios
make all
make install
make install-init
make install-commandmode
make install-config

Installing Nagios plugins

Nagios plugins download address

wget https://nagios-plugins.org/download/nagios-plugins-2.2.1.tar.gz
tar -zxvf nagios-plugins-2.2.1.tar.gz
cd nagios-plugins-2.2.1
./configure --prefix=/usr/local/nagios
make
make install

Configuring nagios monitoring system

Monitoring Nagios of the three swordsmen

Configuring nagios to monitor MySQL

Using check_mysql_health

check_mysql_health official introduction Homepage

wget https://labs.consol.de/assets/downloads/nagios/check_mysql_health-2.2.2.tar.gz
tar -zxvf check_mysql_health-2.2.2.tar.gz
cd check_mysql_health-2.2.2
./configure --prefix=/usr/local/nagios --with-nagios-user=nagios --with-nagios-group=nagios --with-perl=/usr/bin/perl --with-statefiles-dir=/usr/local/nagios/var/tmp

Create a user

grant usage on *.* to 'nagios'@'%' identified by 'nagios';

Check check_mysql_health plug-in

./check_mysql_health --hostname 192.168.192.129 --port 3306 -username nagios --password nagios --mode connection-time  --warning 0.001 --critical 0.0011

-- without Perl 
yum install 'perl(Data::Dumper)'
-- without DBI.pm
yum install perl-DBI perl-DBD-MySQL -y

Add configuration

Server monitoring (custom): VIM / usr / local / Nagios / etc / objects / hosts cfg

define host{   
        use                     linux-server     ; Reference host linux-server Attribute information of, linux-server Host in templates.cfg Defined in the file.
        host_name               monitor_mysql_60 ; Host name, custom
        alias                   mon_60           ; host alias 
        address                 192.168.20.60    ; The monitored host address, which can be ip´╝îIt can also be a domain name.
}   

define hostgroup{                                 ; Define a host group
        hostgroup_name          monitor-servers   ; Host group name, which can be specified at will.
        alias                   monitorServers    ; Host group alias
        members                 monitor_mysql_60  ; Host group member, where"monitor_60"Is the host defined above.     
}

Server monitoring (custom): VIM / usr / local / Nagios / etc / objects / services cfg

define service{  
        use                     local-service      ; quote local-service Property value of the service, local-service stay templates.cfg Defined in the file.
        host_name               monitor_mysql_60   ; Specify which host to monitor for services, monitor_mysql_60 stay hosts.cfg Defined in the file.
        service_description     check-host-alive   ; Description of monitoring service content for reference of maintenance personnel.
        check_command           check-host-alive   ; Specifies the command to check.
}

Configure nagios command line file: VIM / usr / local / nagios / etc / objects / commands cfg

# check_mysql_health command definition
define command{
	command_name check_mysql_health
	command_line $USER1$/check_mysql_health --hostname $ARG1$ --port $ARG2$ --username $ARG3$ --password $ARG4$ --mode $ARG5$ --warning $ARG6$ --critical $ARG7$
}

Configure monitoring service items (customized): VIM / usr / local / Nagios / etc / objects / mysqlservices cfg

define service{
        use local-service
        host_name monitor_mysql_60
        service_description check_mysql_connection_time
        check_command check_mysql_health!192.168.20.60!3306!nagios!nagios!connection-time!2!10
}
define service{
        use local-service
        host_name monitor_mysql_60
        service_description check_mysql_uptime
        check_command check_mysql_health!192.168.20.60!3306!nagios!nagios!uptime!10080!44640
}
define service{
        use local-service
        host_name monitor_mysql_60
        service_description check_mysql_connections
        check_command check_mysql_health!192.168.20.60!3306!nagios!nagios!threads-connected!70!150
}
define service{
        use local-service
        host_name monitor_mysql_60
        service_description check_mysql_table_lock
        check_command check_mysql_health!192.168.20.60!3306!nagios!nagios!table-lock-contention!1!2
}
define service{
        use local-service
        host_name monitor_mysql_60
        service_description check_mysql_slow_queries
        check_command check_mysql_health!192.168.20.60!3306!nagios!nagios!slow-queries!1!2
}

Add custom mysqlservices CFG to the configuration file: / usr / local / Nagios / etc / Nagios cfg

cfg_file=/usr/local/nagios/etc/objects/hosts.cfg
cfg_file=/usr/local/nagios/etc/objects/services.cfg
cfg_file=/usr/local/nagios/etc/objects/mysqlservices.cfg

Verification configuration

/usr/local/nagios/bin/nagios -v /usr/local/nagios/etc/nagios.cfg

Restart service

systemctl restart httpd.service 
systemctl restart nagios.service

Other commands - see official documents for details

-- Time to connect to the server
sudo ./check_mysql_health --hostname 192.168.20.60 --username nagios --password nagios --mode connection-time

sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode connection-time  --warning 0.001 --critical 0.0011

-- Server running time
sudo ./check_mysql_health --hostname 192.168.20.60 --username nagios --password nagios --mode uptime-time

-- Number of currently open connections
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode threads-connected

-- Hit rate of thread cache
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode threadcache-hitrate

-- Number of threads created per second
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode threads-created

-- Number of threads currently running
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode threads-running

-- Number of threads currently cached
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode threads-cached

-- Connection termination: the number of aborted connections per second
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode connects-aborted

-- Client termination
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode clients-aborted

-- Query cache hit rate
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode querycache-hitrate 

-- Query cache entries trimmed due to insufficient memory
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode qcache-lowmem-prunes

-- MyISAM key cache hit rate 
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode keycache-hitrate

-- InnoDB Buffer pool hit rate
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode bufferpool-hitrate

-- InnoDB Buffer pool waiting for available
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode bufferpool-wait-free

-- Because the log buffer is too small, InnoDB Log waiting
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode log-waits

-- Table cache hit rate
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode tablecache-hitrate

-- Table lock contention
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode table-lock-contention

-- Percentage of temporary tables created on disk
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode tmp-disk-tables


-- Long running process
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode long-running-procs

-- Indexes
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode index-usage

-- Displays the table that should be optimized
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode table-fragmentation

-- Percentage of open files
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode open-files

-- Slow query
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode slow-queries

-- Any that returns a single number sql command
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode sql --name 'select 111 from dual'
 
sudo ./check_mysql_health --hostname 192.168.20.60 --port 3306 --username nagios --password nagios --mode sql --name 'select 111 from dual' --name2 myval --units GB

Tags: monitor and control

Posted by tbaink2000 on Thu, 19 May 2022 15:40:08 +0300