My ‘hourly’ MySQL monitor script

Following my article Everything fails, Monitor Everything , and some inquiries, I’ve made some small modifications to my initially hourly script. This script is still a quick and dirty trial of what I’m wanting to develop, but in true Guy Kawasaki terms “5. Don’t worry, be crappy”. It works for now, and enables me to determine what works and what doesn’t.

My goals are Data Collection, Data Analysis and Data Presentation. This is the start of Data Collection. So now I get the following files:

• os.vmstat.070524.122054.log
• os.ps.070524.122054.log
• mysql.innodbstatus.070524.122054.log
• mysql.processlist.070524.122054.log
• mysql.status.070524.122054.log
• mysql.tablestatus.070524.122054.log
• mysql.tablestatus.vertical.070524.122054.log
• mysql.variables.070524.122054.log

#!/bin/sh
#  Name:    hourly
#  Purpose: Script to 'cron' hourly to run for monitoring
#  Author:  Ronald Bradford

error() {
  echo "ERROR: $1"
  exit 1
}

MYSQL_AUTHENTICATION=".mysql.authentication"
[ ! -f $MYSQL_AUTHENTICATION ] && error "You must specific MySQL Authentication in $MYSQL_AUTHENTICATION"
[ -z `which mysqladmin` ] && error "mysqladmin must be in the PATH"

DATETIME_FORMAT="+%y%m%d.%H%M%S"
DATETIME=`date $DATETIME_FORMAT`
DATABASE="test"

AUTHENTICATION=`cat $MYSQL_AUTHENTICATION`
# run vmstat every second for 1 hour
# normally this is an overkill 5 seconds is acceptable
# but need to monitoring any spike

VMSTAT_OPTIONS="1 3600"
LOG_FILE="os.vmstat.$DATETIME.log"
echo "INFO:  Logging vmstat $VMSTAT_OPTIONS to $LOG_FILE"
vmstat $VMSTAT_OPTIONS > $LOG_FILE 2>&1 &

LOG_FILE="os.ps.$DATETIME.log"
echo "INFO:  Logging ps to $LOG_FILE"
ps -ef > $LOG_FILE 2>&1 &

LOG_FILE="mysql.variables.$DATETIME.log"
echo "INFO:  Logging mysqladmin variables to $LOG_FILE"
echo "| date_time                        | $DATETIME |" > $LOG_FILE
mysqladmin $AUTHENTICATION variables >> $LOG_FILE 2>&1 &

LOG_FILE="mysql.tablestatus.vertical.$DATETIME.log"
mysql $AUTHENTICATION $DATABASE -e "SHOW TABLE STATUSG" > $LOG_FILE 2>&1 &
LOG_FILE="mysql.tablestatus.$DATETIME.log"
mysql $AUTHENTICATION $DATABASE -e "SHOW TABLE STATUS" > $LOG_FILE 2>&1 &

COUNT=0
MAX_COUNT=60
SLEEP_TIME=60
LOG_FILE1="mysql.status.$DATETIME.log"
LOG_FILE2="mysql.processlist.$DATETIME.log"
LOG_FILE3="mysql.innodbstatus.$DATETIME.log"
> $LOG_FILE1
> $LOG_FILE2
> $LOG_FILE3
echo "INFO:  Logging mysqladmin extended-status per $SLEEP_TIME secs for $MAX_COUNT times to $LOG_FILE1"
echo "INFO:  Logging mysqladmin processlist per $SLEEP_TIME secs for $MAX_COUNT times to $LOG_FILE2"
echo "INFO:  Logging mysql show innodb status per $SLEEP_TIME secs for $MAX_COUNT times to $LOG_FILE3"
while [ $COUNT -lt $MAX_COUNT ]
do
  NOW=`date $DATETIME_FORMAT`
  echo "| date_time                        | $NOW |" >> $LOG_FILE1
  echo $NOW >> $LOG_FILE2
  echo $NOW >> $LOG_FILE3
  mysqladmin $AUTHENTICATION extended-status >> $LOG_FILE1
  mysqladmin --verbose $AUTHENTICATION processlist >> $LOG_FILE2
  mysql $AUTHENTICATION $DATABASE -e "SHOW INNODB STATUS\G" >> $LOG_FILE3 2>&1 &
  COUNT=`expr $COUNT + 1`
  sleep $SLEEP_TIME
done
exit 0

So from here, I need to:

• Put into my standard sh script framework which provides correct logging, message management and true parameterization.
• Additional pre-checks for the correct security requirements
• Revised Parameterised settings including database
• Host and Instance logging
• Additional file parsing for later Data Analysis and Data Presentation.