Nagios 監控 MySQL 長時間 RUNNING 事務告警 Shell 腳本實戰

Nagios 監控 MySQL 長時間 RUNNING 事務告警 Shell 腳本實戰

前言： 業務報單提交異常、頁面無響應，排查發現是事務未提交或回滾導致阻塞。若能及時監控長時間運行的事務並告警，可大幅縮短故障發現時間。本文分享一個部署於 Nagios 的 Shell 腳本，專門檢測 InnoDB 事務運行超時情況。

1. 編寫事務監控腳本 check_trx

#!/bin/bash
# author: tim.man
# version: 1.0
# desc: check the RUNNING TRANSACTION over threshold

ST_OK=0
ST_WR=1
ST_CR=2
ST_UK=3

TIME_TRX=10  # 閾值：事務運行超過 10 秒視為異常

print_help() {
    echo "$PROGNAME -w INT -c INT"
    echo "Options:"
    echo " -w/--warning) Sets a warning threshold (seconds)"
    echo " -c/--critical) Sets a critical threshold (seconds)"
    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 "WARNING threshold must be lower than CRITICAL!"
        exit $ST_UK
    elif [ "$wcdiff" = 2 ]; then
        echo "Please set critical value when using warning threshold!"
        exit $ST_UK
    elif [ "$wcdiff" = 3 ]; then
        echo "Please set warning value when using critical threshold!"
        exit $ST_UK
    fi
}

get_wcdiff
val_wcdiff

# 核心檢測邏輯：查詢 information_schema.INNODB_TRX
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}'`

# 若無長事務，設為 0 避免判斷報錯
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}'`

# 依閾值輸出 Nagios 狀態
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 RUNNING, 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.
OK- TRANSACTIONS RAN successfully.

修改 nrpe.cfg 加入監控命令

command[check_mysql_trx]=/usr/local/nagios/libexec/check_trx -w 30 -c 60

3. Nagios 主服務端配置

測試遠端調用

[root@localhost etc]# /usr/local/nagios/libexec/check_nrpe -H 10.254.3.61 -c check_mysql_trx
OK- TRANSACTIONS RAN successfully.

編輯 services.cfg 定義服務

define service{
    host_name mysqlserver
    service_description Check mysql transactions
    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.cfg 定義命令

# add by tim.man on 20141201
define command{
    command_name check_mysql_trx
    command_line $USER1$/check_mysql_trx -w $ARG1$ -c $ARG2$
}

4. 效果驗證

正常狀態：

嚴重告警狀態：

版權聲明：轉載請以連結註明來源。原文：https://blog.itpub.net/26230597/viewspace-1355720/ 作者：黃杉 (mchdba)