How To Auto Restart MySQL Service if it Crashes

How To Auto Restart a Crashed MySQL Service with Cron

Last updated on | 4 replies

MySQL may crash when it runs out of memory or for some other issue. In most cases, you can resolve this by simply restarting the MySQL service.

To see if MySQL is running or not, run:

sudo service mysql status

If MySQL is down, you can restart it with:

sudo service mysql restart

But, what if you’re away on vacation while your client’s website goes down? Surely there’s a way to automate this entire process In Linux?

Why, yes there is! Cron is a time-based job scheduler for Linux that can run scripts in your absence.

Create a Script to Auto Restart MySQL

You can configure cron to automatically check the status of the MySQL server and restart it if it crashes. Of course, this is not a permanent fix for your leaky MySQL server, but it can buy you time until you are able to investigate further.

We first need to create a simple BASH script and store it in the home folder (or anywhere you want), and then instruct cron to run this BASH script once a minute.

You can put this script anywhere, but in this example, we will put it in the home folder.

Change directory to the home folder.

cd /home/

Create a new directory here called scripts.

sudo mkdir scripts

Change to this directory.

cd scripts

Create a new file in this directory called mysqlmon.sh using the nano text editor.

sudo nano mysqlmon.sh

Paste in the following script.

/home/scripts/mysqlmon.sh
#!/bin/bash

# Check if MySQL is running
sudo service mysql status > /dev/null 2>&1

# Restart the MySQL service if it's not running.
if [ $? != 0 ]; then
    echo -e "MySQL Service was down. Restarting now...\n"
    sudo service mysql restart
else
    echo -e "MySQL Service is running already. Nothing to do here.\n"
fi

Save and exit (press CTRL + X, press Y and then press ENTER)

Make the script executable.

sudo chmod +x mysqlmon.sh

Test the Script to Auto Restart MySQL

Test the script by running:

sudo ./mysqlmon.sh

If MySQL is up and running, you should see:

MySQL Service is running already. Nothing to do here.

If you want to see what happens when the script detects if MySQL is down, stop the MySQL service, but only do this if your web server isn’t live! If your server is live but not busy, stopping and starting the MySQL server should only take a few seconds.

sudo service mysql stop

Now test the script again by running:

sudo ./mysqlmon.sh

Output:

MySQL Service was down. Restarting now..

The MySQL service should be up again. You can check with:

sudo service mysql status

Add the MySQL Auto Restart Script to Crontab

By adding this script to crontab, the server will check the MySQL service once a minute, and if it isn’t running, it will restart it.

Open crontab (if asked to select a text editor, choose nano).

sudo crontab -e

In crontab, add the following line to the bottom of the file.

crontab
* * * * * /home/scripts/mysqlmon.sh > /dev/null 2>&1

Save and exit (press CTRL + X, press Y and then press ENTER)

Testing Crontab

To test if crontab is running the script once a minute, you will need to stop the MySQL service temporarily.

sudo service mysql stop

You can now test if MySQL is running with the following command.

sudo service mysql status

If MySQL is down, wait for at least a minute until the crontab runs. If after two minutes the MySQL service is still down, something has gone wrong with your cron script. Start MySQL again with sudo service mysql start.

Let me know if this helped. Follow me on Twitter, Facebook and YouTube, or 🍊 buy me a smoothie.

4 replies

Leave a reply

Your email address will not be published. Required fields are marked *

  1. In my case, `service mysql status` always returns 0 to the stderr, so the bash script in the how to does not work. Here’s a working alternative:

    #!/bin/bash

    # Check if MySQL is running
    status=$(sudo service mysql status)

    # Check the output for the running status
    if [[ $status == *”start/running”* ]]; then
    echo -e “MySQL Service is running already. Nothing to do here.\n”
    else
    echo -e “MySQL Service was down. Restarting now…\n”
    sudo service mysql restart
    fi