#!/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