Nagios上监控mysql数据库

wget ftp://ftp.ntua.gr/pub/databases/mysql/Downloads/MySQL-6.0/MySQL-shared-compat-6.0.9-0.rhel5.i386.rpm
rpm -ivh –force MySQL-shared-compat-6.0.9-0.rhel5.i386.rpm
— check_mysql_health安装
# wget http://labs.consol.de/wp-content/uploads/2010/03/check_mysql_health-2.1.1.tar.gz
# tar zxvf check_mysql_health-2.1.1.tar.gz
# ./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 \
–with-perl=/usr/bin/perl
# make
# make install
— check_mysql_health监控配置
定义commands.cfg
#’check_mysql_health’ command definition
define command{
command_name    check_mysql_health
command_line    $USER1$/check_mysql_health –hostname $ARG1$ –username $ARG2$ –password $ARG3$ –mode $ARG4$ –warning $AR
G5$ –critical $ARG6$
}

services.cfg

#################check_mysql_health##########################
define service{
use                             generic-service,srv-pnp         ; Name of service template to use
host_name                       mysql-bbs-168
process_perf_data               1
service_description             MYSQL
check_command                   check_tcp!3306
}
define service{
use                             generic-service,srv-pnp         ; Name of service template to use
host_name                       mysql-bbs-168
service_description             Mysql connection-time
check_command                   check_mysql_health!10.1.1.168!cacti!cacti!connection-time!10!20
}
#define service{
#        use                             generic-service,srv-pnp         ; Name of service template to use
#        host_name                       mysql-bbs-168
#        service_description             Mysql threadcache-hitrate
#        check_command                   check_mysql_health!10.1.1.168!cacti!cacti!threadcache-hitrate!99.9!99.99
#        }
define service{
use                             generic-service,srv-pnp         ; Name of service template to use
host_name                       mysql-bbs-168
service_description             Mysql tmp-disk-tables
check_command                   check_mysql_health!10.1.1.168!cacti!cacti!tmp-disk-tables!30!40
}
#define service{
#        use                             generic-service,srv-pnp         ; Name of service template to use
#        host_name                       mysql-bbs-168
#        service_description             Mysql InnoDB buffer pool hitrate
#        check_command                   check_mysql_health!10.1.1.168!cacti!cacti!bufferpool-hitrate!99.8!99.9
#        }
define service{
use                             generic-service,srv-pnp         ; Name of service template to use
host_name                       mysql-bbs-168
service_description             Mysql long-running-procs
check_command                   check_mysql_health!10.1.1.168!cacti!cacti!long-running-procs!900!950
}
define service{
use                             generic-service,srv-pnp         ; Name of service template to use
host_name                       mysql-bbs-168
service_description             Mysql threads-connected
check_command                   check_mysql_health!10.1.1.168!cacti!cacti!threads-connected!900!950
}
define service{
use                             generic-service,srv-pnp         ; Name of service template to use
host_name                       mysql-bbs-168
service_description             Mysql table-lock-contention
check_command                   check_mysql_health!10.1.1.168!cacti!cacti!table-lock-contention!30!40
}
define service{
use                             generic-service,srv-pnp         ; Name of service template to use
host_name                       mysql-bbs-168
service_description             Mysql uptime
check_command                   check_mysql_health!10.1.1.168!cacti!cacti!uptime
}

–查看当前连接到数据库的连接数:
[root@localhost mysql]# /usr/local/nagios/libexec/check_mysql_health –hostname IP –port 3306 –username “USER” –password “PASSWORD” –mode threads-connected
OK – 10 client connection threads | threads_connected=10;10;20
如果此处报错:
CRITICAL – cannot connect to information_schema. Can’t locate DBI.pm in @INC (@INC contains: /usr/local/nagios/libexec /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.8/i386-linux-thread-multi /usr/lib/perl5/5.8.8 .)
或者当出现Can’t locate DBI.pm的错误时
安装dbd-mysql:
yum install perl-DBD-MySQL perl-DBI
check_mysql_health实现多种监控模式:
connection-time (Time to connect to the server)
uptime (Time the server is running)
threads-connected (Number of currently open connections)线程数
threadcache-hitrate (Hit rate of the thread-cache)慢查询
slave-lag (Seconds behind master)
slave-io-running (Slave io running: Yes)主从热备
slave-sql-running (Slave sql running: Yes)主从热备
qcache-hitrate (Query cache hitrate)
qcache-lowmem-prunes (Query cache entries pruned because of low memory)
keycache-hitrate (MyISAM key cache hitrate)
bufferpool-hitrate (InnoDB buffer pool hitrate)
bufferpool-wait-free (InnoDB buffer pool waits for clean page available)
log-waits (InnoDB log waits because of a too small log buffer)
tablecache-hitrate (Table cache hitrate)
table-lock-contention (Table lock contention)锁表率
index-usage (Usage of indices)
tmp-disk-tables (Percent of temp tables created on disk)
slow-queries (Slow queries)
long-running-procs (long running processes)
cluster-ndbd-running (ndnd nodes are up and running)
sql (any sql command returning a single number)

监控图形:

image11

 

此条目发表在Nagios分类目录。将固定链接加入收藏夹。