Mysql Uptime Check Script

Published on Oct 1, 2012 by Jamie Munro

I've been pretty vocal recently over my move to Amazon EC2 and switching to LEMP stack and then switch to a LEPP stack. This has certainly seen some great improvements in speed, traffic, and Google indexing. Unfortunately as many of you may know who run their own dedicated server, it's not all white sandy beaches and beautiful sunsets! When something goes wrong, it's up to you to fix it.

So far the only issue that I see keep cropping up is my Mysql database seems to crash every several days. From what I can see, it appears to be a lack of memory issue it appears my EC2 server is just shy on a bit of memory.

At this point, I don't think it's worth upgrading; instead I've built a simple Mysql uptime script that will ensure it's always running for me.


Mysql server provides a nice tool called mysqladmin. By executing the following command:


mysqladmin ping


This code returns me a message indicating the status of the Mysql server. Since I'm not running the largest traffic sites in the world, I've taken this simple command and created a script around it, that if it doesn't return the message "mysqld is alive" I restart the mysql daemon inside of a shell script.

Please note: I'm not certain that this is the best approach, but it certainly is serving my purposes right now.

Here is the full script below; it includes emailing me as well so I can keep track of how often this is happening.


#!/bin/bash

result=`/usr/bin/mysqladmin ping`
expected='mysqld is alive'

if [[ "$result" != "$expected" ]]
then
echo "It's dead - restart mysql"

# email subject
SUBJECT="[MYSQL ERROR] - Attempting to restart service"

# Email To ?
EMAIL="info@endyourif.com"

# Email text/message
EMAILMESSAGE="/tmp/emailmessage.txt"
echo "$result was received"> $EMAILMESSAGE
echo "when we were expected $expected" >>$EMAILMESSAGE
# send an email using /bin/mail
mail -s "$SUBJECT" "$EMAIL" < $EMAILMESSAGE

sudo /etc/init.d/mysql restart
fi


Once I finished creating this script, I also added a cronjob that will run every 5 minutes executing this command:


*/5 * * * * /<path_to>/scripts/mysql.sh


This is my simple 5 minute solution to this problem, is there a better way to do this?

Tags: SQL | Theory | mysql | mysqladmin | crontab | uptime check

Related Posts

blog comments powered by Disqus