MySql monitoring and automatic restart script

[WebMaster]

Hi!
If you want to monitor MySQL Server service and will restart automatically then you can follow the script which is given bellow :

#!/bin/bash

# MySQL Server root/admin username
MUSER="root"

# MySQL Server admin/root password
MPASS="SET-ROOT-PASSWORD"

# MySQL Server hostname
MHOST="localhost"

#Shell script to start MySQL server i.e. path to MySQL daemon start/stop script.
MSTART="/etc/init.d/mysql start"

# Email ID to send notification
EMAILID="you@your-email.com"

# path to mail program
MAILCMD="$(which mail)"

# path mysqladmin
MADMIN="$(which mysqladmin)"

#### DO NOT CHANGE anything BELOW ####
MAILMESSAGE="/tmp/mysql.fail.$$"

# see if MySQL server is alive or not
# 2&1 could be better but i would like to keep it simple and easy to
# understand stuff
$MADMIN -h $MHOST -u $MUSER -p${MPASS} ping 2>/dev/null 1>/dev/null
if [ $? -ne 0 ]; then
echo "" >$MAILMESSAGE
echo "Error: MySQL Server is not running/responding ping request">>$MAILMESSAGE
echo "Hostname: $(hostname)" >>$MAILMESSAGE
echo "Date & Time: $(date)" >>$MAILMESSAGE
# try to start mysql
$MSTART>/dev/null
# see if it is started or not
o=$(ps cax | grep -c ' mysqld$')
if [ $o -eq 1 ]; then
sMess="MySQL Server MySQL server successfully restarted"
else
sMess="MySQL server FAILED to restart"
fi
# Email status too
echo "Current Status: $sMess" >>$MAILMESSAGE
echo "" >>$MAILMESSAGE
echo "*** This email generated by $(basename $0) shell script ***" >>$MAILMESSAGE
echo "*** Please don't reply this email, this is just notification email ***" >>$MAILMESSAGE
# send email
$MAILCMD -s "MySQL server" $EMAILID < $MAILMESSAGE
else # MySQL is running and do nothing
:
fi
# remove file
rm -f $MAILMESSAGE

You just have to do some small modification in this script (mysql password, email ID etc). Then set a cron to run according to your requirement for the above script.
Such as :

*/15 * * * * ./(path to script) > /dev/null 2>&1

It will run the script in every 15 mins.

[66carolin]

Hi webmaster!
I am not able to monitoring of MySQL Server and httpd with my WHM - do you know if these will send email alerts as well, as your script seems to do?

[25DORIS]

Hi 66carolin!

No, you will not receive any warn mails as the above mentioned script does.

But, you can also check the "CPU/Memory/MySQL" Usage with the following steps :

WHM >> Server Status >> CPU/Memory/MySQL Usage and apache load from WHM >> Server Status >> Apache Status.

[66carolin]

webmaster can you confirm that the script really works?

[10NANCY]

may I know, will that SQL Server question work on a database which is hosted by trap17,or will I have to edit it in another process?

[77wmaster]

Hi,

This is a great script and good to monitor the mysql service. But, you require to make two modification in the script in order to work.

o=$(ps cax | grep -c ' mysqld$')
if [ $o -eq 1 ]; then

Edit these two lines to the below.

o=$(ps cax | grep -c ' mysqld')
if [ $o -ge 1 ]; then

It will work as expected.