Sometimes it becomes necessary to put a simple monitoring service in place for MySQL databases that crash occasionally due to a slim system configuration or other hard to control factors. This script and CRON job can monitor your database for just such an occurrence.

First SSH into your server instance. Create a new Bash script:

[ec2-user@ip-999-99-9-99 ~]$ sudo nano /bin/monitorsql

Of course you can use VI if you like punishment but I prefer Nano. Next paste in the below Bash script. In the if line we are simply checking if the service is running by matching the output of the mysqld status command. Don’t forget to change the email to your email!

#!/bin/bash

if [[ ! "$(sudo service mysqld status)" =~ "is running..." ]]
then
    echo "YourSite.com MySQL stopped running and has restarted " | sudo sendmail -s "YourSite.com  mysql restarted unexpectedly" youremail@gmail.com
    sudo service mysqld start > /dev/null
fi

Now save the new script and make sure it is executable by running this command.

[ec2-user@ip-999-99-9-99 ~]$ sudo chmod +x /bin/monitorsql

I would suggest testing the script to make sure it does what you expect. To do this, turn off your MySQL service, then run this script. It should email you and turn on the service immediately

[ec2-user@ip-999-99-9-99 ~]$ sudo service mysqld stop
Stopping mysqld:                                           [  OK  ]
[ec2-user@ip-999-99-9-99 ~]$ sudo /bin/monitorsql

Finally, add a CRON record to run this script every 5 minutes

[ec2-user@ip-999-99-9-99 ~]$ export VISUAL=nano; crontab -e

Cron record

*/5 * * * * /bin/monitorsql

Remember, this is a stopgap measure meant to get things back up and running in the event something does happen. This should NOT be used as a solution for a faulty MySQL setup. If your database is crashing on a regular basis, look at the logs and make the necessary configuration changes!