Edit once you have copied to Vi Editor
1) session_count.sql
***************************************
tee ./logs/session.log
select count(*) from information_schema.processlist;
notee;
**********************************
2 ) Session_count.sh
**********************************************************************
#!/bin/ksh
#########################################################################
# PROGRAM NAME: session_count.sh for MySQL
#
# USAGE: ./session_count.sh
#
#########################################################################
COMMAND=$0
PGM=${COMMAND##*/}
DATE=$(date "+%Y%m%d%H%M")
hostname=`hostname -s`
MAILTO=USER_SIVA@ABC.com
PWD=/opt/siva/scripts/sessions # put script dir path if you want to run from "crontab"
repdir=$PWD
LOG=$repdir/logs
if [ ! -d $LOG ]; then
mkdir $LOG
else
rm $LOG/session.log
if [ -f $LOG/session_details.html ]; then
rm $LOG/session_details.html
fi
fi
####### ******************** Below list of changes are req:
conf_file=/etc/my.cnf
BACKUP_USER=TEST_USER # can be root.
BACKUP_PASS=`cat /opt/.orapasswd/.backupusr` # to hide password
Vald_sess_C=200 # max value limit to email.
log_ret=30 # retention for logfiles
Logfile=mysql_Session_"$hostname"_$DATE.log
echo " started $DATE" >>$LOG/$Logfile
#### Changes are done:
####### ******************** run Active file:
if [ -f $LOG/bkp_active_file ]
then
echo "previous mysql session_count.sh script is still running. confirm before running again" | mailx -s "Mysql-Session Status: $hostname - Failed to execute" $MAILTO
exit 0
fi
###
touch $LOG/bkp_active_file
mysql -Ns -u TEST_USER -p$BACKUP_PASS < $repdir/session_count.sql >> $LOG/mysql_Session_"$hostname"_$DATE.log 2>&1
####### ******************** Check the session count:
Check_Sess_C=`cat $LOG/session.log`
if [ $Check_Sess_C -gt $Vald_sess_C ];
then
mysql -H -u TEST_USER -p$BACKUP_PASS < $repdir/session_details.sql >> $LOG/mysql_Session_"$hostname"_$DATE.log 2>&1
####### ******************** Email Output to Mail:
( echo " HOST: $hostname - MySQL Sessions are High - Details: "
echo "To: USER_SIVA@ABC.com"
echo "From: <$hostname> "
echo "MIME-Version: 1.0"
echo "Content-Type: text/html; charset=US-ASCII"
echo "<html>"
echo "<body>"
echo "<font size="+2">" "<font color="red">"
echo "<u>"
echo "Mysql sessions are high - Count - $Check_Sess_C ."
echo "</u>"
echo "<br/>"
echo "<font size="-1">" "<font color="black">"
echo "<pre>"
cat $LOG/session_details.html
echo "<pre>"
echo "Successful end of $repdir/session_count.sh"
echo "</body>"
echo "</html>"
) | /usr/sbin/sendmail $MAILTO
### End of Email
fi
####### ******************** Purge Log directory:
if [ ! -d $LOG ]; then
mkdir $LOG
else
/usr/bin/find $LOG -type f -mtime +$log_ret -exec rm -f {} \;
fi
####### ******************** Remove run active file:
if [ -f $LOG/bkp_active_file ]
then
rm $LOG/bkp_active_file
echo " removed run-active file" >> $LOG/$Logfile
fi
exit
### All steps are Done.
No comments:
Post a Comment