您当前位置:
首页 >
数据库 >
数据库应用 > Nagios 里面监控MySQL事务一直RUNNING没有结束的报警Shell脚本
Nagios 里面监控MySQL事务一直RUNNING没有结束的报警Shell脚本
来源:程序员人生 发布时间:2014-12-09 08:35:16 阅读次数:4119次
叙言:
业务报警定单提交异常,页面1直没有反应,排查后是事务没有提交或回滚致使,想到如果及时监控事务的运行状态报警出来,那末就能够及时排查出问题所在,方便运营处理,所以自己就弄了1个shell脚本放在nagios来处理事务报警情况。
1,编写事务监控脚本
#!/bin/bash# author: tim.man# version: 1.0# desc: check the RUNNING TRANSACTION over ST_OK=0 ST_WR=1 ST_CR=2 ST_UK=3 TIME_TRX=10# 提示信息print_help() { echo "$PROGNAME -w INT -c INT" echo "Options:" echo " -w/--warning)" echo " Sets a warning number" echo " -c/--critical)" echo " Sets a critical level for io" exit $ST_UK }while test -n "$1"; do case "$1" in -help|-h) print_help exit $ST_UK ;; --warning|-w) warning=$2 shift ;; --critical|-c) critical=$2 shift ;; *) echo "Unknown argument: $1" print_help exit $ST_UK ;; esac shift done get_wcdiff() { if [ ! -z "$warning" -a ! -z "$critical" ] then wclvls=1 if [ ${warning} -gt ${critical} ] then wcdiff=1 fi elif [ ! -z "$warning" -a -z "$critical" ] then wcdiff=2 elif [ -z "$warning" -a ! -z "$critical" ] then wcdiff=3 fi } # 脚本判断val_wcdiff() { if [ "$wcdiff" = 1 ] then echo "Please adjust your warning/critical thresholds. The warning must be lower than the critical level!" exit $ST_UK elif [ "$wcdiff" = 2 ] then echo "Please also set a critical value when you want to use warning/critical thresholds!" exit $ST_UK elif [ "$wcdiff" = 3 ] then echo "Please also set a warning value when you want to use warning/critical thresholds!" exit $ST_UK fi } get_wcdiff val_wcdiff # 统计mysql的事务中最大运行时间max_over_time=`/usr/local/mysql/bin/mysql --user=nagios --password="nagiosq@xxx" -NS /usr/local/mysql/mysql.sock -e "SELECT TIME_TO_SEC(TIMEDIFF(NOW(),t.trx_started)) FROM information_schema.INNODB_TRX t WHERE TIME_TO_SEC(TIMEDIFF(NOW(),t.trx_started))>$TIME_TRX ORDER BY TIME_TO_SEC(TIMEDIFF(NOW(),t.trx_started)) DESC LIMIT 1;" |awk '{print $1}'`# 如果当前没有RUNNING的事务,则直接赋值为0,以避免下面if判断出错if [ ! -n "$max_over_time" ];then max_over_time=0 fi# 获得当前所以阻塞的事务数量num_trx=`/usr/local/mysql/bin/mysql --user=nagios --password="nagiosq@xxx" -NS /usr/local/mysql/mysql.sock -e "SELECT COUNT(1) FROM information_schema.INNODB_TRX t WHERE TIME_TO_SEC(TIMEDIFF(NOW(),t.trx_started))>$TIME_TRX;" |awk '{print $1}'`if [ -n "$warning" -a -n "$critical" ] then if [ `expr $max_over_time > $warning` -eq 1 -a `expr $max_over_time < $critical` -eq 1 ] then echo "WARNING - $num_trx TRANSACTIONS RUNNING,go over for $max_over_time seconds" exit $ST_WR elif [ `expr $max_over_time > $critical` -eq 1 ] then echo "CRITICAL- $num_trx TRANSACTIONS RUNNNING,go over for $max_over_time seconds" exit $ST_CR else echo "OK- TRANSACTIONS RAN successfully." exit $ST_OK fi fi2,在nagios客户端添加脚本监控
先测试下脚本[root@wgq_idc_dbm_3_61 binlog]# /usr/local/nagios/libexec/check_trx -w 30 -c 60Warning: Using a password on the command line interface can be insecure.Warning: Using a password on the command line interface can be insecure.OK- TRANSACTIONS RAN successfully.[root@wgq_idc_dbm_3_61 binlog]#
在nrpe.cfg里面添加监控命令[root@wgq_idc_dbm_3_61 binlog]# vim /usr/local/nagios/etc/nrpe.cfgcommand[check_mysql_trx]=/usr/local/nagios/libexec/check_trx -w 30 -c 60
以后重启nagios客户端监控, service nrpe restart4,在nagios主监控服务器上面添加配置选项
先去nagios服务器上面check1下[root@localhost etc]# /usr/local/nagios/libexec/check_nrpe -H10.254.3.61 -c check_mysql_trxOK- TRANSACTIONS RAN successfully.[root@localhost etc]# 在services.cfg里面添加事务监控选项:define service{ host_name mysqlserver service_description Check mysql transctions check_command check_nrpe!check_mysql_trx max_check_attempts 5 check_command check_nrpe!check_mysql_trx max_check_attempts 5 normal_check_interval 3 retry_check_interval 2 check_period 24x7 notification_interval 10 notification_period 24x7 notification_options w,u,c,r contact_groups opsweb }在commands.cnf里面添加事务监控命令:# add by tim.man on 20141201define command{ command_name check_mysql_trx command_line $USER1$/check_mysql_trx -w $ARG1$ -c $ARG2$ }邮件短信报警电话报警已添加,所以无需重新配置。然后重新加载nagios[root@localhost objects]# service nagios reloadRunning configuration check...Reloading nagios configuration...done[root@localhost objects]#
5,去nagios主监控界面查看监控效果
正常监控效果:
严重监控效果:
----------------------------------------------------------------------------------------------------------------
<版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!>
原博客地址: http://blog.itpub.net/26230597/viewspace⑴355720/
原作者:黄杉 (mchdba)
----------------------------------------------------------------------------------------------------------------
生活不易,码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
------分隔线----------------------------
------分隔线----------------------------