自己編寫Nagios 監控MySQL外掛程式

主從監控外掛程式編寫：
#!/bin/bash
MYSQL="/usr/local/mysql/bin/mysql -uslave -pslave12345 -h127.0.0.1 -P"
VAR_LOG=/tmp/check_mysql_status.log.$2 && >$VAR_LOG
function usage(){
    cat <<EOF
`basename $0` need at least value: `basename $0` -[s|m] port
第一個參數是 -s 那麼以是檢查從模式, -m就是檢查主模式 不能省略
第二個參數是資料庫連接埠，如3308 3309,不能省略
mysql許可權，需執行如下SQL：grant replication client,super on *.* to 'slave'@'127.0.0.1' identified by 'slave12345';
EOF
exit 1
}
function check_mysql_status(){
    #echo $MYSQL $@
    echo "show status;" | $MYSQL$@ | perl -p -e 's/\s+(\w+)/="$1"/g' >> $VAR_LOG
    #echo "$mysql_status"
}
function check_mysql_slave_status(){
    #echo $MYSQL $@
    echo "show slave status\G" | $MYSQL$@ | perl -p -e 's/:\s(.*)$/="$1"/g; s/^\s+//g; s/^\*.*\*//;' >> $VAR_LOG
}
function check_slave_results(){
    if [ "$Slave_IO_Running" == "Yes" ] && [ "$Slave_SQL_Running" == "Yes" ];then
        if [ $tmp_code -ne 0 ] && [ $Seconds_Behind_Master -gt 100 ] ;then
            code=2
        elif [ $Seconds_Behind_Master -lt 50 ] ;then
            code=0
        else
            code=1
        fi
    else
        code=2
    fi     
}
if [ $# -le 0 ] || [ "$1" == "-h" ];then
    usage
fi
if [ "$1" == "-s" ];then
    #echo "檢查從模式"
    check_mysql_slave_status $2
fi
code=0
check_mysql_status $2
. $VAR_LOG
PER_SECOND_QUESTION=`echo "$Uptime $Queries" | awk '{printf ("%4.3f") ,$2/$1}END{if($2/$1<0.05) exit 0 ;else exit 1;}'`
tmp_code=$?
if [ -z $Uptime ];then
    echo "$MYSQL $@ 串連失敗，請檢查服務" && exit 2
fi
if [ "$1" == "-s" ];then    ##輸出不同的資訊
check_slave_results
    printf "主庫:$Master_Host:$Master_Port Uptime:$Uptime Threads:$Threads_running Questions:$Questions Slow_queries:$Slow_queries Open_tables:$Open_tables Queries per second avg:%s Slave_IO_Running:$Slave_IO_Running Slave_SQL_Running:$Slave_SQL_Running Seconds_Behind_Master:$Seconds_Behind_Master|Threads=$Threads_running Questions=$Questions Slowqueries=$Slow_queries Opens=$Opened_tables Opentables=$Open_tables Queriespersecondavg=%s BehindMaster=$Seconds_Behind_Master" $PER_SECOND_QUESTION $PER_SECOND_QUESTION
else    ##輸出不同的狀態資訊
    printf "Uptime:$Uptime Threads:$Threads_running Questions:$Questions Slow_queries:$Slow_queries Open_tables:$Open_tables Queries per second avg:%s|Threads=$Threads_running Questions=$Questions Slowqueries=$Slow_queries Opens=$Opened_tables Opentables=$Open_tables Queriespersecondavg=%s" $PER_SECOND_QUESTION $PER_SECOND_QUESTION
fi
exit $code

 

mysql串連數監控：（監控mysql的最大串連數（最大串連數小於1000警示），和當前串連數 ，可用串連數少於50警示）
#!/bin/bash
#MYSQL="/usr/local/mysql/bin/mysql -unagios -pmonitor_slave#status -h$3 -P"
MYSQL="/usr/local/mysql/bin/mysql -unagios -pmonitor_slave#status -h127.0.0.1 -P"
VAR_LOG=/tmp/check_mysql_status_con_$2.log && >$VAR_LOG
#Threads_con_log=/tmp/check_mysql_tread_conn.log.$2 && >$Threads_con_log
#Max_conn_log=/tmp/check_mysql_max_con.log.$2 && >$Max_conn_log
function usage(){
        cat <<EOF
`basename $0` need at least value: `basename $0` -[s|m] port
first arg  -s : monitor slave mode
            -m : moitor master mode
second arg port: mysqld prot 3306 or 3307 or 3308 ...
mysql privilege :you neeed exec :grant replication client,super on *.* to 'slave'@'127.0.0.1' identified by 'slave12345';
EOF
exit 1
}
function check_mysql_status(){
        #echo $MYSQL $@
        echo "show status;" | $MYSQL$@ | perl -p -e 's/\s+(\w+)/="$1"/g' >> $VAR_LOG
        #echo "$mysql_status"
}
function check_mysql_con_status(){
        #echo $MYSQL $@
        echo "show status like 'Threads_connected';" | $MYSQL$@ | perl -p -e 's/\s+(\w+)/="$1"/g' >> $VAR_LOG
        echo "show variables like 'max_connections';" |$MYSQL$@ | perl -p -e 's/\s+(\w+)/="$1"/g' >> $VAR_LOG
        #echo "$mysql_status"
}
function check_mysql_con_results(){
  #echo "$max_connections,$Threads_connected"
  Residue_conn=$(($max_connections-$Threads_connected))
  #echo $Residue_conn
    if [ $max_connections -lt 1000 ];then
        if [ $Residue_conn -lt 50 ] ;then
            code=2
        else
            code=1
        fi
    else
        code=0
    fi     
}
if [ $# -le 0 ] || [ "$1" == "-h" ];then
        usage
fi
code=0
check_mysql_status $2
check_mysql_con_status $2
. $VAR_LOG
Residue_conn=$max_connections
check_mysql_con_results $2
PER_SECOND_QUESTION=`echo "$Uptime $Queries" | awk '{printf ("%4.3f") ,$2/$1}END{if($2/$1<0.05) exit 0 ;else exit 1;}'`
tmp_code=$?
#
if [ -z $Uptime ];then
        echo "$MYSQL $@ Connect Failed,Please check!" && exit 2
fi
#
 printf "Uptime:$Uptime Threads_connected:$Threads_connected Max_connections:$max_connections Residue_connectios:$Residue_conn Open_tables:$Open_tables Queries per second avg:%s|Threads=$Threads_running Questions=$Questions Slowqueries=$Slow_queries Opens=$Opened_tables Opentables=$Open_tables Queriespersecondavg=%s" $PER_SECOND_QUESTION $PER_SECOND_QUESTION
#
exit $code

Nagios 的詳細介紹：請點這裡
Nagios 的：請點這裡

網路監控器Nagios全攻略

Nagios搭建與配置詳解

Nginx環境下構建Nagios監控平台

在RHEL5.3上配置基本的Nagios系統(使用Nagios-3.1.2)

CentOS 5.5+Nginx+Nagios監控端和被控端安裝配置指南

Ubuntu 13.10 Server 安裝 Nagios Core 網路監控運用