If you run for example WordPress or something else that’s heavily depending on a lot of MySQL resources to run, you don’t want to wake up every morning and notice that your MySQL database has crashed again…. So to make sure the server would restart MySQL every time it fails I started searching for a bash script to check the status for MySQL and restart if needed.
After some Googling I came across this script from TopTechieTimes.com. The script didn’t work effeminately on my EC2 so I had to do a few changes. For example you need to run mysqladmin as sudo on an Amazon EC2 and you also need to provide a user name and password (if you’ve set a password for the database).
After some testing I discovered that the code below would work on an EC2 since trying to use sudo in a script would just cause errors like “sudo: sorry, you must have a tty to run sudo“. But by replacing sudo with visudo instead you would be able to run commands from any shell, like for example using cron like I wanted 😉
#!/bin/bash /usr/bin/mysqladmin --user=root --password=PASSWORD ping| grep 'mysqld is alive' > /dev/null 2>&1 if [ $? != 0 ] then visudo /etc/init.d/mysqld stop visudo /etc/init.d/mysqld start echo Starting at `date` fi
Also instead of just printing “Starting at 2013-09-23 19:00:01” I want to have the starting dates saved to a log file in my Dropbox folder so I can review how often MySQL is restarted every night (good to know if you need to upgrade to another EC2 or get a second instance).
To achieve this I replaced the echo line in the script above with this line:
echo Starting at `date` >> /home/ec2-user/Dropbox/mysql.nightly.log
The double >> means it’s going to append the message sent to the file. If you just use a single > the previous messages in the file will be replaced by the new message.
So the final script looks something like this:
#!/bin/bash /usr/bin/mysqladmin --user=root --password=PASSWORD ping| grep 'mysqld is alive' > /dev/null 2>&1 if [ $? != 0 ] then visudo /etc/init.d/mysqld stop visudo /etc/init.d/mysqld start echo Starting at `date` >> /home/ec2-user/Dropbox/mysql.nightly.log fi
By saving the script as mysqlcheck.sh in the ec2-user folder I could use crontab with this command line to run every 5 minutes and check if MySQL was still running, and if not restart:
*/5 * * * * /bin/sh -x /home/ec2-user/mysqlcheck.sh &> /dev/null
A big thanks to Harijith R for his original script!