序言：
业务报警订单提交异常，页面一直没有反应，排查后是事务没有提交或者回滚导致，想到如果及时监控事务的运行状态报警出来，那么就可以及时排查出问题所在，方便运营处理，所以自己就弄了一个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_schem
a.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(TIMEDIF
F(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?
fi
2，在nagios客户端添加脚本监控
先测试下脚本
[root@wgq_idc_dbm_3_61 binlog]# /usr/local/nagios/libexec/check_trx -w 30 -c 60
Warning: 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.cfg
command[check_mysql_trx]=/usr/local/nagios/libexec/check_trx -w 30 -c 60
之后重启nagios客户端监控, service nrpe restart
4,在nagios主监控服务器上面添加配置选项
先去nagios服务器上面