#!/bin/bash
############ User Define ############
### Mysql info define ###
MysqlBaseDir=/iosp/mysql2
SockFile=/iosp/mysql2/run/mysql2.sock
mycnf=/iosp/mysql2/my.cnf
PW='password'

### Log define ###
LogDir=/var/log/mysql2-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: $CodeEdi
tor <–"
    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
tor <–"
    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_conne
cted' | sort -n | tail -n 1)
    TodayMinThreadConnect=$(grep 'Threads_connected' $LogDir/$LogName$CurrDate* | awk -F " " '{print $2}' | egrep -v 'Maximum|Minimum|Threads_conne
cted' | 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

만약 mysql 5.6 이상 버전에서 쉘에서 mysql 비번을 입력해야되는 문제가 된다면 http://mapoo.net/owner/entry/edit/1025 를 참조할 것.
아래는 스크립트 파일이다. 위 내용과 User Define 부분과 password 설정을 하고 접속하도록 하는 부분이 다르다.
3731206322.xxx

mysql 사용 현황 파악 스크립트(mysql-status.sh)

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다