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,至此我们的监控全部完成。