mysql+实例夯住的监控_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 \< $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

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网