nagios监控mysql

nagios监控mysql的工具比较多，个人比较喜欢的一个是check_mysql_health，下面就来介绍一下。

网址：https://labs.consol.de/nagios/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)
#每秒线程创建数
threads-created      	(Number of threads created per sec)
#线程正在运行数
threads-running      	(Number of currently running threads)
#缓存线程数
threads-cached       	(Number of currently cached threads)
#每秒丢弃的连接数
connects-aborted     	(Number of aborted connections per sec)
#由于客户端关闭，每秒丢弃的连接数
clients-aborted      	(Number of aborted connections (because the client died) per sec)
#slave落后master的时长
slave-lag            	(Seconds behind master)
#slave io进程是否运行
slave-io-running     	(Slave io running: Yes)
#slave sql进程是否运行
slave-sql-running    	(Slave sql running: Yes)
#查询缓存命中率
qcache-hitrate       	(Query cache hitrate)
#由于内存小导致删除缓存中的查询量
qcache-lowmem-prunes 	(Query cache entries pruned because of low memory)
#key缓存命中率
keycache-hitrate     	(MyISAM key cache hitrate)
#innodb缓冲池命中率
bufferpool-hitrate   	(InnoDB buffer pool hitrate)
#innodb缓冲池清理有效页的等待
bufferpool-wait-free 	(InnoDB buffer pool waits for clean page available)
#由于log缓冲太小导致innodb log等待
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)
#表优化
table-fragmentation  	(Show tables which should be optimized)
#打开文件百分比
open-files           	(Percent of opened files)
#慢查询
slow-queries         	(Slow queries)
#长时间运行的进程
long-running-procs   	(long running processes)
#ndb节点运行
cluster-ndbd-running 	(ndnd nodes are up and running)
#返回一个数字的sql语句
sql                  	(any sql command returning a single number)

从以上看出check_mysql_health不仅可以帮助我们监控mysql的运行情况，还有助我们对mysql进行调优。

在此我们只监控mysql基本的运行项，如连接数，slave运行状况及长时间运行的进程。

1.安装

[root@nagios ~]# cd /usr/local/src
[root@nagios src]# wget https://labs.consol.de/assets/downloads/nagios/check_mysql_health-2.2.1.tar.gz
[root@nagios src]# tar -zxvf check_mysql_health-2.2.1.tar.gz
[root@nagios src]# cd check_mysql_health-2.2.1
[root@nagios check_mysql_health-2.2.1]# ./configure --prefix=/usr/local/nagios --with-nagios-user=nagios --with-nagios-group=nagios --with-perl=/usr/bin/perl
[root@nagios check_mysql_health-2.2.1]# make && make install

这样check_mysql_health插件就被安装到/usr/local/nagios/libexec中了。

2.授权

check_mysql_health监控mysql时，需要在mysql上对nagios创建一个监控账号，官方文档建议：

GRANT USAGE ON *.* TO 'nagios'@'10.10.1.73' IDENTIFIED BY 'nagiospassword';
但是在测试过程中发现，如果监控从库的slave-lag，slave-io-running，slave-sql-running时，会报错：“CRITICAL - unable to get replication info”；这是由于监控账户的USAGE权限无法执行show slave status。

因此我们需要对从库的监控账户授权为replication clinet或super权限，主库可以用USAGE权限，如下：

grant replication client ON *.* TO 'nagios'@'X.X.X.X' IDENTIFIED BY 'test';

3.配置监控命令

define command {
    command_name check_mysql_health
    command_line $USER1$/check_mysql_health --hostname $HOSTADDRESS$ --username $USER3$ --password $USER4$ -mode $ARG1$ --warn $ARG2$ --crit $ARG3$ 
}

mysql的监控账户和密码的变量$USER3$，$USER4$在resource.cfg中配置，如下：

vim /usr/local/nagios/etc/resource.cfg 
# Sets $USER1$ to be the path to the plugins
$USER1$=/usr/local/nagios/libexec

# Sets $USER2$ to be the path to event handlers
#$USER2$=/usr/local/nagios/libexec/eventhandlers

# Store some usernames and passwords (hidden from the CGIs)
#check_mysql_health监控mysql
$USER3$=nagios
$USER4$=test

4.配置监控服务

define service{
    use                     local-service
    service_description     MYSQL slave-io
    host_name               mysql-test-slave
    check_command           check_mysql_health!slave-io-running
    service_groups          mysql_services
    check_interval          10
    notifications_enabled   1
    notification_interval   0
    contact_groups          admins
}

define service{
    use                     local-service
    service_description     MYSQL slave-sql
    host_name               mysql-test-slave
    check_command           check_mysql_health!slave-sql-running
    service_groups          mysql_services
    check_interval          10  
    notifications_enabled   1   
    notification_interval   0   
    contact_groups          admin
}

define service{
    use                     local-service,srv-pnp
    service_description     MYSQL slave-lag
    host_name               mysql-test-slave
    check_command           check_mysql_health!slave-lag!10!20
    service_groups          mysql_services
    check_interval          10  
    notifications_enabled   1   
    notification_interval   0   
    contact_groups          admin
}
define service{
    use                     local-service,srv-pnp
    service_description     MYSQL long-running-procs
    host_name               mysql-test-slave,mysql-test-master
    check_command           check_mysql_health!long-running-procs!10!20
    service_groups          mysql_services
    check_interval          10
    notifications_enabled   1
    notification_interval   0
    contact_groups          admin
}

define service{
    use                     local-service,srv-pnp
    service_description     MYSQL threads-connected
    host_name               mysql-test-slave,mysql-test-master
    check_command           check_mysql_health!threads-connected!2000!2500
    service_groups          mysql_services
    check_interval          10
    notifications_enabled   1
    notification_interval   0
    contact_groups          admin
}

ok，至此我们的监控全部完成。