v-update-sys-rrd-mysql

#!/bin/bash
# info: update MySQL rrd
# options: PERIOD
#
# The function is for updating mysql rrd database and graphic.


#----------------------------------------------------------#
#                    Variable&Function                     #
#----------------------------------------------------------#

# Argument defenition
period=${1-daily}

# Includes
source $VESTA/func/main.sh
source $VESTA/conf/vesta.conf


#----------------------------------------------------------#
#                       Action                             #
#----------------------------------------------------------#

# Switching on time period
case $period in
    daily)   start='-1d'; end='now'; grid='MINUTE:30:HOUR:1:HOUR:4:0:%H:%M';;
    weekly)  start='-7d'; end='now'; grid='HOUR:8:DAY:1:DAY:1:0:%a %d';;
    monthly) start='-1m'; end='now'; grid='WEEK:1:WEEK:1:WEEK:1:0:%b %d';;
    yearly)  start='-1y'; end='now'; grid='MONTH:1:YEAR:1:MONTH:2:2419200:%b';;
    *) exit $E_RRD ;;
esac

# Checking directory
if [ ! -d "$RRD/db" ]; then
    mkdir $RRD/db
fi

# Parsing db hosts
conf="$VESTA/conf/mysql.conf"
fields='$HOST'
nohead=1
hosts=$(shell_list)
check_row=$(echo "$hosts" |wc -l)
if [ 0 -eq "$check_row" ]; then
    exit
fi

# Parsing excludes
for exclude in $(echo ${RRD_MYSQL_EXCLUDE//,/ }); do
    hosts=$(echo "$hosts" |grep -vw "$exclude" )
done

for host in $hosts; do
    # Checking database
    if [ ! -e "$RRD/db/mysql_$host.rrd" ]; then
        # Adding database
        rrdtool create $RRD/db/mysql_$host.rrd --step $RRD_STEP \
            DS:A:COUNTER:600:U:U \
            DS:S:COUNTER:600:U:U \
            RRA:AVERAGE:0.5:1:600 \
            RRA:AVERAGE:0.5:6:700 \
            RRA:AVERAGE:0.5:24:775 \
            RRA:AVERAGE:0.5:288:797 \
            RRA:MAX:0.5:1:600 \
            RRA:MAX:0.5:6:700 \
            RRA:MAX:0.5:24:775 \
            RRA:MAX:0.5:288:797
    fi

    if [ "$period" = 'daily' ]; then
        # Defining host credentials
        host_str=$(grep "HOST='$host'" $conf)
        for key in $host_str; do
            eval ${key%%=*}=${key#*=}
        done
        sql="mysql -h $HOST -u $USER -p$PASSWORD -e"

        # Checking empty vars
        if [ -z $HOST ] || [ -z $USER ] || [ -z $PASSWORD ]; then
            echo "Error: config is broken"
            log_event "$E_PARSING" "$EVENT"
            exit $E_PARSING
        fi

        # Parsing data
        status=$($sql "SHOW GLOBAL STATUS" 2>/dev/null); code="$?"
        if [ '0' -ne "$code" ]; then
            active=0
            slow=0
        else
            active=$(echo "$status"|grep 'Queries'|cut -f 2)
            slow=$(echo "$status"|grep 'Slow_queries'|cut -f 2)
        fi

        # Updating rrd
        rrdtool update $RRD/db/mysql_$host.rrd N:$active:$slow
    fi

    # Updating daily graph
    rrdtool graph  $RRD/db/$period-mysql_$host.png \
        --imgformat PNG \
        --height="150" \
        --width="670" \
        --start "$start" \
        --end "$end" \
        --vertical-label "Queries" \
        --x-grid "$grid" \
        -c "BACK#ffffff" \
        -c "SHADEA#ffffff" \
        -c "SHADEB#ffffff" \
        -c "FONT#555555" \
        -c "CANVAS#302c2d" \
        -c "GRID#666666" \
        -c "MGRID#AAAAAA" \
        -c "FRAME#302c2d" \
        -c "ARROW#FFFFFF" \
        DEF:a=$RRD/db/mysql_$host.rrd:A:AVERAGE \
        DEF:s=$RRD/db/mysql_$host.rrd:S:AVERAGE \
        COMMENT:'\r' \
        LINE1:a#fefda0:"Queries"\
        GPRINT:a:'LAST: Current\:''%8.0lf'  \
        GPRINT:a:'MIN: Min\:''%8.0lf'  \
        GPRINT:a:'MAX: Max\:''%8.0lf\j'  \
        AREA:s#f57900:"Slow   " \
        GPRINT:s:'LAST:Current\:''%8.0lf' \
        GPRINT:s:'MIN:Min\:''%8.0lf' \
        GPRINT:s:'MAX:Max\:''%8.0lf\j'  &>/dev/null; result=$?

    if [ "$result" -ne 0 ]; then
        exit $E_RRD
    fi

done


#----------------------------------------------------------#
#                       Vesta                              #
#----------------------------------------------------------#

exit