Monitor MySQL And Auto Recover From A Crash

Since starting this blog in July of 2014, I have had an unexpected amount of growth. So much, that a lot of strain has been placed on my virtual private server (VPS).

A common issue that has plagued me and many other WordPress users is the awful MySQL crash due to exhausted resources. This is because the server has run out of resources due to traffic spikes or other anomalies. The worst part for me is that MySQL tends to crash while I’m sleeping, putting my blog out of commission for up to six hours at a time. This is not good when you’re trying to be a reliable source of information on the internet.

Luckily, I’ve developed a script that will elegantly resolve these related problems.

At this point I’m going to assume you’re running Debian or Ubuntu Linux. Other Linux operating systems will work with minor tweaking, but Windows will not.

#!/bin/bash

if (( $(ps -ef | grep -v grep | grep mysqld | wc -l) <= 0 ))
then
    echo "MySQL is currently not running and will be restarted!" | mail -s "MySQL may have crashed" -r from@example.com to@example.com
    /etc/init.d/mysql restart
else
    echo "Running"
fi

The above script will see how many process are running similar to mysqld. Notice I am using mysqld and not mysql in the command.

If zero results come back in the process search, that means MySQL is not currently running, whether it be from a crash or something else. We don’t really care the specifics. If MySQL is not running, we want an email to be sent to us notifying us that it happened, and then we want to restart the service. The email might be useful so you can track how often something like this happens. For me it happens about once a week.

For this script to be effective, it needs to be run as a cron. As the systems root user we need to access the crontab. This must be the root user, not sudo. From your Terminal, switch to the root user by running sudo su and entering your password.

At this point, you should find yourself at /root if your run pwd. Make sure the script resides in this directory with execute permissions. For purposes of this tutorial, we’re going to call the script mysql_monitor.sh.

As the root user, run crontab -e and enter the following:

*/5    *    *    *    *    /root/mysql_monitor.sh

The above line will run the script as the root user every five minutes, forever. So if you use the above, the longest your database will be down for is five minutes, provided that it has crashed and isn’t just frozen.

Nic Raboy

Nic Raboy is an advocate of modern web and mobile development technologies. He has experience in C#, JavaScript, Golang and a variety of frameworks such as Angular, NativeScript, and Unity. Nic writes about his development experiences related to making web and mobile development easier to understand.