Nagios 里面监控MySQL 监控事务夯住（RUNNING）报警通知

业务报警订单提交异常，页面一直没有反应，排查后是事务没有提交或者回滚导致，想到如果及时监控事务的运行状态报警出来，那么就

序言：
业务报警订单提交异常，页面一直没有反应，排查后是事务没有提交或者回滚导致，想到如果及时监控事务的运行状态报警出来，那么就可以及时排查出问题所在，方便运营处理，所以自己就弄了一个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 \    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