Dealing with MySQL issues in the Cloud: Automating restart on error

MySQL is the mainstay of most Cloud Applications (including this WordPress Blog !), however if MySQL has an issue, either through number of connections maxing out, or MySQL being locked and not available it can result in site outages. We’ve seen clients who have ended up with their SQL DB down from a couple of hours to a couple of days before they suddenly realised there was an issue.

To that end we wrote a small script that can be used to automate the restarting of MySQL in such scenarios.

The script is called mysqlrestart.sh and is listed below. You need root access to be able to use it. If you use it and ever reboot the server you will need to login as root and run nohup ./mysqlrestart.sh & to restart it.

Set the script to run every 30 seconds using Cron. It will then check for a number of connections and if it cannot get a connection or the number of connections is greater than the number defined (defined as 90 in the example below), it will restart mysql.

#!/bin/bash

SQLCONNECTION_THRESHOLD=90

echo `date` sqlrestart started >> run.out

while true; do

sqlconnections=`mysql –skip-column-names -s -e ”SHOW STATUS LIKE ‘Threads_connected’” -u root | awk ‘{print $2}’`

#exclude myself from the number of thread connections

sqlconnections=$((sqlconnections – 1))

echo `date` sqlrestart connections $sqlconnections >> run.out

if [ $sqlconnections -gt $SQLCONNECTION_THRESHOLD ] || [ $sqlconnections -lt 0 ]

then

echo `date` restarting mysql server $sqlconnections >> restart.out

service mysql restart >> restart.out 2>&1