Automatically Restarting Unresponsive MySQL Service

This blog and others that I run are all hosted on a cloud server with MySQL installed locally. Occasionally MySQL becomes unresponsive and I have to login to the server to kick it back into life by restarting msqld.

The most likely reason for MySQL becoming unresponsive is lack of resources so the best solution would be to increase the size of the server – however, that requires money that I don’t want to spend. Therefore, the next best option was to write a script to detect when MySQL was unavailable. Enter mysqladmin.

mysqladmin — A MySQL Server Administration Program

mysqladmin pretty much does what it says on the tin providing a range of administration features and one of the options is ping to check server availability. This returns 0 if the server is running and 1 if it is not (this seems backwards to me given that 1 would normally be true but hey-ho).

Therefore, putting this together I wrote the following script (When I say I wrote it actually ChatGPT did but that’s a story for another day):

#!/bin/bash

# MySQL connection parameters
MYSQL_USER="<Your username>"
MYSQL_PASSWORD="<Your Password>"
MYSQL_HOST="Your Hostname"

# MySQL ping command
MYSQL_PING="mysqladmin ping -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST"

# Execute MySQL ping and capture the output and exit code
ping_output=$($MYSQL_PING 2>&1)
exit_code=$?

# Check if the exit code indicates an unresponsive MySQL
if [ $exit_code -ne 0 ]; then
    echo "MySQL is unresponsive. Restarting..."

    # Restart MySQL using systemctl (modify this command based on your system's service name)
    systemctl restart mysqld.service

    # Check the exit code of the restart command
    if [ $? -eq 0 ]; then
        echo "MySQL has been successfully restarted."
    else
        echo "Failed to restart MySQL."
        # You can add additional handling here, like sending notifications.
    fi
else
    echo "MySQL is responsive."
fi

Schedule the Script

Save the above script to your server as restart_mysql.sh and update it to include your username, password and hostname. Ideally, these wouldn’t be held in the script but given they are also stored elsewhere, in the WordPress config file for example, the risk isn’t increased.

Finally, you need to schedule the script in cron. I would recommend doing this in sudo cron rather than your own user as that way the script will have the necessary permissions to restart MySQL.

*/5 * * * * /path/to/restart_mysql.sh

The above runs every five minutes but you can obviously adjust that to your needs.