#!/bin/bash
############ User Define ############
### Mysql info define ###
MysqlBaseDir=/usr/local/mysql
SockFile=/tmp/mysql.sock
mycnf=/usr/local/mysql/my.cnf
PW='password'

### Log define ###
LogDir=/var/log/mysql-status
LogKeepDay=0
TarKeepDay=30
#####################################

if [ $UID != "0" ]; then
   echo "[!] Must be run to root!!!"
   exit 0
fi

######## version & help Info ########
CodeEditor=mhkim@osti.kr
ScrVerNum=0.1
ScrName=mysql-status

ARGS=2
E_BADARGS=65

function action
{
    echo $1
}

if [ "$1" == "-v" ]; then
    action "$ScrName Version: $ScrVerNum"
    exit
fi

function action2
{
    echo $1
}

if [ "$1" == "-h" ]; then
    action2 "$ScrName Help: Please modify the script-code of the 'User Define', and do every hour operation by crontab setup. --> inquire: $CodeEditor <--"
    exit
fi
#####################################
CurrDate=`date +%Y-%m-%d`
CurrDate2=`date +%y%m%d`
CurrTime=`date +%Y-%m-%d" "%T`
CurrTime2=`date +%Y-%m-%d"_"%T`
Ctime=$(echo $CurrTime | awk -F " " '{print $2}' | awk -F ":" '{print $1$2}')
Chour=$(echo $CurrTime | awk -F " " '{print $2}' | awk -F ":" '{print $1}')
LogName=$(echo $LogDir | awk -F "/" '{print $4"-" }')

if [ ! -z $LogDir/$LogName$CurrTime2.log ]; then
    if [ ! -d $LogDir ]; then
        mkdir -p $LogDir
        touch $LogDir/$LogName$CurrTime2.log
    elif [ -d $LogDir ]; then
        touch $LogDir/$LogName$CurrTime2.log
    fi
fi

WhileNum=6
while [ $WhileNum -ge 1 ]
do
    CurrTime=`date +%Y-%m-%d" "%T`
    echo "-----------$CurrTime-------------" >> $LogDir/$LogName$CurrTime2.log
    $MysqlBaseDir/bin/mysql -e "show global status like '%Threads_c%';" -u root -p$PW -S $SockFile >> $LogDir/$LogName$CurrTime2.log
    if [ $? == "1" ]; then
        echo "mysql login error."
        exit
    fi
    echo "===========================================" >> $LogDir/$LogName$CurrTime2.log
    echo "" >> $LogDir/$LogName$CurrTime2.log
    sleep 10 ;
    clear ;
    echo "mysql status reporting... 1 minute."
    WhileNum=$((WhileNum-1))
    if [ $WhileNum == "0" ]; then
        SUM=0
        MaxCon=$(grep max_connections $mycnf | sed '/^#/d' | awk -F "=" '{print $2}' | awk -F " " '{print $1}')
        TTC=$(grep 'Threads_connected' $LogDir/$LogName$CurrTime2.log | awk -F " " '{print $2}' | egrep -v 'Threads_connected')
        HourMaxThreadConnect=$(grep 'Threads_connected' $LogDir/$LogName$CurrTime2.log | awk -F " " '{print $2}' | sort -n | tail -n 1)
        HourMinThreadConnect=$(grep 'Threads_connected' $LogDir/$LogName$CurrTime2.log | awk -F " " '{print $2}' | sort -n | head -n 1)
        for i in $TTC
        do
            let SUM=$SUM+$i
        done
        echo "" >> $LogDir/$LogName$CurrTime2.log
        echo "###########################################" >> $LogDir/$LogName$CurrTime2.log
        echo "my.cnf define max_connections = $MaxCon" >> $LogDir/$LogName$CurrTime2.log
        MaxUsage=$(echo "scale=3; $HourMaxThreadConnect/$MaxCon*100" | bc)
        MinUsage=$(echo "scale=3; $HourMinThreadConnect/$MaxCon*100" | bc)
        echo "Maximum Threads_connected     = $HourMaxThreadConnect ($MaxUsage%)" >> $LogDir/$LogName$CurrTime2.log
        echo "Minimum Threads_connected     = $HourMinThreadConnect ($MinUsage%)" >> $LogDir/$LogName$CurrTime2.log
        Uptimes=$($MysqlBaseDir/bin/mysql -e "show global status like '%Uptime%';" -u root -p$PW -S $SockFile | grep 'Uptime' | head -1 | awk -F " " '{print $2'})
        TotalCon=$($MysqlBaseDir/bin/mysql -e "show global status like '%Connections%';" -u root -p$PW -S $SockFile | grep 'Connections' | head -1 | awk -F " " '{print $2'})
        echo "Uptime                        = $Uptimes sec" >> $LogDir/$LogName$CurrTime2.log
        echo "After uptime total connections= $TotalCon" >> $LogDir/$LogName$CurrTime2.log
        echo "###########################################" >> $LogDir/$LogName$CurrTime2.log
        sed -i 's/(\./(0\./g' $LogDir/$LogName$CurrTime2.log
    fi
done ;

if [ $Chour == "23" ]; then
    SUM=0
    TodayMaxThreadConnect=$(grep 'Threads_connected' $LogDir/$LogName$CurrDate* | awk -F " " '{print $2}' | egrep -v 'Maximum|Minimum|Threads_connected' | sort -n | tail -n 1)
    TodayMinThreadConnect=$(grep 'Threads_connected' $LogDir/$LogName$CurrDate* | awk -F " " '{print $2}' | egrep -v 'Maximum|Minimum|Threads_connected' | sort -n | head -n 1)
    TodayMaxConnection=$(grep 'After uptime total connections' $LogDir/$LogName$CurrDate* | awk -F "=" '{print $2}' | sort -n | tail -n 1)
    TodayMinConnection=$(grep 'After uptime total connections' $LogDir/$LogName$CurrDate* | awk -F "=" '{print $2}' | sort -n | head -n 1)
    echo "########### $CurrDate Today's usage ##########" > $LogDir/$LogName$CurrDate.log
    echo "my.cnf define max_connections   = $MaxCon" >> $LogDir/$LogName$CurrDate.log
    MaxUsage=$(echo "scale=3; $TodayMaxThreadConnect/$MaxCon*100" | bc)
    MinUsage=$(echo "scale=3; $TodayMinThreadConnect/$MaxCon*100" | bc)
    TodayTotalConnection=`expr $TodayMaxConnection - $TodayMinConnection`
    echo "Today Maximum Threads_connected = $TodayMaxThreadConnect ($MaxUsage%)" >> $LogDir/$LogName$CurrDate.log
    echo "Today Minimum Threads_connected = $TodayMinThreadConnect ($MinUsage%)" >> $LogDir/$LogName$CurrDate.log
    echo "Today Total connections         = $TodayTotalConnection" >> $LogDir/$LogName$CurrDate.log
    echo "###############################################" >> $LogDir/$LogName$CurrDate.log
    sed -i 's/(\./(0\./g' $LogDir/$LogName$CurrDate.log
    BackupCheckTime=$(date --rfc-2822 | awk -F " " '{print $1$5}' | awk -F ":" '{print $1}')
    if [ $BackupCheckTime = "Sun,23" ]; then
        LogName2=$(echo $LogName | sed 's/-$//g')
        TARGET="$CurrDate-$LogName2.tar.gz"
        cd $LogDir
        tar zcf $TARGET ./$LogName*
        find $LogDir -name '*.log' -type f -mtime $LogKeepDay -user root -exec rm -f {} \;
        find $LogDir -name "*-$LogName2.tar.gz" -type f -mtime +$TarKeepDay -user root -exec rm -f {} \;
        echo "Time is now Sunday, 23 hour. So log files weekly tar-backup and old log remove."
    fi
else
    exit;
fi