Error establishing connection to database – WordPress solution

solutions for wordpress database errors

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.

warning message when a website can't connect to 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.";
}

?>

You can read more about how to send a notification email in another post that I wrote on this blog.

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 .

 

Migrate a WordPress site to AWS

WordPress migration

In a previous article I discussed launching a website on AWS. The project was framed as transferring a static site from another hosting provider. This post will extend that to migrating a dynamic WordPress site with existing content.

Install WordPress

After following the steps to launch your website to a new AWS EC2 instance, you’ll be able to connect via sFTP. I use FileZilla as my client. You’ll need the hostname (public DNS), username (ec2-user in this example), and key file for access. The latest version of WordPress can be downloaded from wordpress.org. Once connected to the server, I copy those files to the root web directory for my setup: /var/www/html

Make sure the wp-config.php file has the correct details (username, password) for your database. You should use the same database name from the previous hosting environment.

Data backup and import

It is crucial to be sure we don’t lose any data. I make a MySql dump of the current database and copy the entire wp-content folder to my local machine. I’m careful to not delete or cancel the old server until I am sure the new one is working identically.

After configuring my EC2 instance, I install phpMyAdmin so that I can easily import the sql file.

sudo yum install php-mbstring -y
sudo systemctl restart httpd
sudo systemctl restart php-fpm
cd /var/www/html
wget https://www.phpmyadmin.net/downloads/phpMyAdmin-latest-all-languages.tar.gz
mkdir phpMyAdmin && tar -xvzf phpMyAdmin-latest-all-languages.tar.gz -C phpMyAdmin --strip-components 1
rm phpMyAdmin-latest-all-languages.tar.gz
sudo systemctl start mariadb

The above Linux commands installs the database management software on the root directory of the new web server. It is accessible from a browser via yourdomainname.com/phpMyAdmin. This tool is used to upload the data to the new environment.

phpMyAdmin import screen

Create the database and make sure the name matches what’s in wp-config.php from the last step. Now you’ll be able to upload your .sql file.

Next, I take the wp-content folder that I stored on my computer, and copy it over to the new remote. At this point, the site homepage will load correctly. You might notice other pages won’t resolve, and will produce a 404 “not found” response. That error has to do with certain Apache settings, and can be fixed by tweaking some options.

Server settings

With my setup, I encountered the above issue with page permalinks . WordPress relies on the .htaccess file to route pages/posts with their correct URL slugs. By default, this Apache setup does not allow its settings to be overridden by .htaccess directives. To fix this issue, the httpd.conf file needs to be edited. Mine was located in this directory: /etc/httpd/conf

You’ll need to find (or create) a section that corresponds to the default document root: <Directory “/var/www/html”></Directory>. In that block, they’ll be a AllowOverride command that is set to “None”. That needs to be changed to “All” for our configuration file to work.

apache config settings found in the HTTPD conf file

Final steps

After all the data and content has been transferred, do some smoke-testing. Try out as many pages and features as you can to make sure the new site is working as it should. Make sure you keep a back-up of everything some place secure (I use an S3 bucket). Once satisfied, you can switch your domain’s A records to point at the new box. Since the old and new servers will appear identical, I add a console.log(“new server”) to the header file. That allows me tell when the DNS update has finally resolved. Afterwards, I can safely cancel/decommission the old web hosting package.

Don’t forget to make sure SSL is setup!

Update

AWS offers an entire suite of services to help businesses migrate. AWS Application Migration Service is a great choice to “simplify and expedite your migration while reducing costs”.

Secure a website with SSL and HTTPS on AWS

SSL, certbot, https

My last post was about launching a website onto AWS. This covered launching a new EC2 instance, configuring a security group, installing LAMP software, and pointing a domain at the new instance. The only thing missing was to configure SSL and HTTPS.

Secure Sockets Layer (SSL) encrypts traffic between a website and its server. HTTPS is the protocol to deliver secured data via SSL to end-users.

In my last post, I already allowed all traffic through port 443 (the port that HTTPS uses) in the security group for my EC2 instance. Now I’ll install software to provision SSL certificates for the server.

Certbot

Certbot is free software that will communicate with Let’s Encrypt, an SSL certificate authority, to automate the management of encryption certificates.

Before downloading and installing Certbot, we’ll need to install some dependencies (Extra Packages for Enterprise Linux). SSH into the EC2 instance that you want to secure, and run this command in your home directory (/home/ec2-user):

sudo wget -r --no-parent -A 'epel-release-*.rpm' http://dl.fedoraproject.org/pub/epel/7/x86_64/Packages/e/

Then install it:

sudo rpm -Uvh dl.fedoraproject.org/pub/epel/7/x86_64/Packages/e/epel-release-*.rpm

And enable it:

sudo yum-config-manager --enable epel*

Now, we’ll need to edit the Apache (our web hosting software) configuration file. Mine is located here: /etc/httpd/conf/httpd.conf

You can use the Nano CLI text editor to make changes to this file by running:

sudo nano /etc/httpd/conf/httpd.conf

Scroll down a bit, and you’ll find a line that says “Listen 80”. Paste these lines below (obviously, changing antpace.com to your own domain name)

<VirtualHost *:80>
    DocumentRoot "/var/www/html"
    ServerName "antpace.com"
    ServerAlias "www.antpace.com"
</VirtualHost>

Make sure you have an A record (via Route 53) for both yourwebsite.com AND www.yourwebsite.com with the value set as your EC2 public IP address.

After saving, you’ll need to restart the server software:

sudo systemctl restart httpd

Now we’re ready for Certbot. Install it:

sudo yum install -y certbot python2-certbot-apache

Run it:

sudo certbot

Follow the prompts as they appear.

Automatic renewal

Finally, schedule an automated task (a cron job) to renew the encryption certificate as needed. If you don’t do this part, HTTPS will fail for your website after a few months. Users will receive an ugly warning, telling them that your website is not secure. Don’t skip this part!

Run this command to open your cron file:

sudo nano /etc/crontab

Schedule Certbot to renew everyday, at 4:05 am:

05 4 * * * root certbot renew --no-self-upgrade

Make sure your cron daemon is running:

sudo systemctl restart crond

That’s it! Now your website, hosted on EC2 will support HTTPS. Next, we’ll force all traffic to use it.

* AWS Documentation Reference