cron – AntPace.com

A crashed database is a problem I’ve encountered across multiple WordPress websites. When trying to load the site you’re faced with a dreaded “Error establishing a database connection” message. Restarting the DB service usually clears things up. But, sometimes it won’t restart at all – which is why I started automating nightly data dumps to an S3 bucket.

Recently, one particular site kept going down unusually often. I assumed it was happening due to low computing resources on the EC2 t3.micro instance. I decide to spin up a a new box with more RAM (t3.small) and migrate the entire WordPress setup.

Since I couldn’t be sure of what was causing the issue, I needed a way to monitor the health of my WordPress websites. I decided to write code that would periodically ping the site, and if it is down send an email alert and attempt to restart the database.

The first challenge was determining the status of the database. Even if it crashed, my site would still return a 200 OK response. I figured I could use cURL to get the homepage content, and then strip out any HTML tags to check the text output. If the text did match the error message, I could take further action.

Next, I needed to programmatically restart MySql. This is the command I run to do it manually: sudo service mariadb restart

After doing some research, I found that I could use shell_exec() to run it from my PHP code. Unfortunately, Apache wouldn’t let the (non-password using) web server user execute that without special authorization. I moved that command to its own restart-db.sh file, and allowed my code to run it by adding this to the visudo file: apache ALL=NOPASSWD: /var/www/html/restart-db.sh

I also needed to make the file executable by adjusting permissions: sudo chmod +x /var/www/html/restart-db.sh

Once those pieces were configured, my code would work:

<?php

$url = "http://www.antpace.com/blog";
$curl_connection = curl_init();

curl_setopt($curl_connection, CURLOPT_URL, $url);

curl_setopt($curl_connection, CURLOPT_RETURNTRANSFER, true);
$curl_response = curl_exec($curl_connection);
$plain_text = strip_tags($curl_response);

if(strpos($plain_text, "Error establishing a database connection") !== false){
	echo "The DB is down.";
        
        //restart the database
        shell_exec('sudo /var/www/html/restart-db.sh');
        
        //send notification email
        import 'send-email.php';
        send_email();
}else{
	echo "The DB is healthy.";
}

?>

Create the cron job

A cron job is a scheduled task in Linux that runs at set times. For my PHP code to effectively monitor the health of the database, it needs to run often. I decided to execute it every five minutes. Below are three shell commands to create a cron job.

The first creates the cron file for the root user:

sudo touch /var/spool/cron/root

The next appends my cron command to that file:

echo "*/5 * * * * sudo wget -q 127.0.0.1/check-db-health.php" | sudo tee -a /var/spool/cron/root

And, the last sets the cron software to listen for that file:

sudo crontab /var/spool/cron/root

Alternatively, you can create, edit, and set the cron file directly by running sudo crontab -e . The contents of the cron file can be confirmed by running sudo crontab -l .

Tag: cron

Error establishing connection to database – WordPress solution

Create the cron job