Below scripts are needed to find the Current Sessions live in Mysql instance:
Two sql scripts you need in the same folder as shell script:- which are in RED color in the script.
1) session_count.sql
tee ./logs/session.log
select count(*) from information_schema.processlist;
notee;
2) session_details.sql
tee ./logs/session_details.html
select user,SUBSTR(HOST,1,INSTR(HOST,':')-1) as "Host",db,command,state,count(*) as "Number of sessions" from information_schema.processlist group by user,SUBSTR(HOST,1,INSTR(HOST,':')-1),db,command,state;
notee;
Code for the Shell Script:-
**************************************************
#!/bin/ksh
#########################################################################
# PROGRAM NAME: session_count.sh for MySQL
#
# USAGE: ./session_count.sh
#
# Siva
#########################################################################
COMMAND=$0
PGM=${COMMAND##*/}
DATE=$(date "+%Y%m%d%H%M")
hostname=`hostname -s`
MAILTO=Name@domain.com
PWD=/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=root
BACKUP_PASS=`cat /oracle/.orapasswd/.backupusr`
Vald_sess_C=1 ---- Set the Valid sessions number for the mark
log_ret=15
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 root -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 > $Vald_sess_C ]]
then
mysql -H -u root -p$BACKUP_PASS < $repdir/session_details.sql >> $LOG/mysql_Session_"$hostname"_$DATE.log 2>&1
####### ******************** Email Output to Mail:
( echo "Subject: AUTO-Gen - HOST: $hostname - MySQL Sessions are High - Details: "
echo "To: OracleDBAs@unfi.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 "Action Needed: 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.
*********************************************************************
Hope this helps.................................................
No comments:
Post a Comment