Testing MySQL Connectivity From WordPress Docker Containers

cargo container lot

Moving forward with feature development for the Store Locator Plus® SaaS service includes moving forward with our tool kits and environments. Docker containers have become a bigger part of our development toolkit. They make for rapid deployment of isolated environments for testing various concepts without the overhead of virtual machines. While we have been working on Virtualbox virtualized machines for years, Docker is far faster to spin up and far less resource intensive.

Along the way we have found the need to perform deeper dives into the interplay of Docker, WordPress, and MySQL. The latest endeavor, discovering why our WordPress container is not communicating with our AWS hosted Aurora MySQL test data set.

While we can connect to our Aurora MySQL database using the credentials is our docker-compose YAML file, the WordPress instance running in docker on localhost is throwing connectivity errors.

Assumptions

For this article we assume a rudimentary knowledge of Docker, a baseline container with WordPress installed — likely from the default WordPress image, and a GUI interface such as Docker Desktop for managing instances and providing quick access to the shell command line of the container.

We also assume you have a docker-compose.yaml config file that is used to startup the container that looks similar to this:

version: '3'

services:

  wp:
    image: wordpress:6.0.1-php7.4-apache
    container_name: wp-slpsaas
    platform: linux/amd64
    ports:
      - '80:80'
    environment:
      WORDPRESS_DB_HOST: 'some_rds_instance.c0glwpjjxt7q.us-east-1.rds.amazonaws.com'
      WORDPRESS_DB_USER: wp_db_user
      WORDPRESS_DB_PASSWORD: '9aDDwTdJenny8675309'
      WORDPRESS_DB_NAME: wp_db_name

And you’ve started the container either via Docker Desktop or the command line with
docker-compose up -d

Installing MySQL Client On Docker WordPress Containers

Connect to the shell for the container after the container has been started. The easiest way to do this is with Docker Desktop and clicking the shell icon for the instance.

Our SLP SaaS container running WordPress in the Docker Desktop. The red arrow is the icon to connect to the container’s shell command line.

By default the WordPress Docker image does not contain very many tools. That is to be expected as the whole idea behind the containers is lightweight environments. As such we’ll need to install the MySQL client with apt:

apt-get update
apt-get install default-mysql-client

Checking Connectivity To The WordPress Database

With the MySQL client installed we can now test connectivity from within the container to our AWS database directly. This ensures our network configuration and docker-compose environment variables are set properly to communicate with the database.

mysql -A -h $WORDPRESS_DB_HOST -u $WORDPRESS_DB_USER --password=$WORDPRESS_DB_PASSWORD $WORDPRESS_DB_NAME

MySQL > show tables;

If you get a list of WordPress tables the connectivity is working.

Hosting WordPress On AWS

worms eye view of spiral stained glass decors through the roof

As fate would have it, Summer 2022 has come with some life changes that include a renewed focus on the long-established business at Charleston Software Associates (CSA). In the decade-plus that the company has been in place there have been several key shifts in focus including the launch of Store Locator Plus® as a multi-faceted location mapping software and SaaS platform. Now, as the Store Locator Plus® business is back as the center of focus for the company, it is time to rebuild the infrastructure that supports it; Including moving all non-locator related assets off of the primary server cluster that “host all things CSA”.

Among the many changes that are underway, moving websites hosted on a classic shared web server to independent hosting on AWS is being implemented.

Existing Website Environment

Many of the websites that are hosted on the shared CSA web presence servers are independent entities unrelated to the Store Locator Plus® project. These sites are hosted on a cluster of EC2 instances with a virtualized web service in place. The configuration works very much like most web hosting companies; One big server (or cluster) with many websites stored in different directories that are managed by the web services configuration.

As such, the details provided below should be nearly identical for any WordPress site hosted at a web presence provider. While some steps may be different, the details provided herein should provide insight into hosting WordPress on AWS.

WordPress On Lightsail

While there are several ways to get WordPress up-and-running on AWS, including using independent servers like EC2, Lightsail is by far the easiest to work while providing extra services every web presence should have.

WordPress instances on Lightsail start at a super-low price of $3.50/month (July 2022). This is vary competitive with nearly any self-managed WordPress solution. In reality, most people never utilize the additional services of a fully managed WordPress installation. The only feature some of those “fully managed” WordPress services offer that is not readily available on AWS Lightsail is a one-click “mirror this site on a staging site” option. For full production business sites this can be useful if your site appearance of software stack is often changing. For most sites hosting simple blogs (like this one) the service is never used.

AWS Lightsail provides a simplified interface for managing your web instance. It includes the ability to add advanced features from the AWS services with a few clicks. The initial configuration sets up a basic web server that is deployed on AWS container services; Don’t worry you don’t need the details as they take care of the setup and management for you. The default configuration will install and configure the services (web server, database, installing WordPress, the OS) needed for a full WordPress hosting presence within a few minutes of creating an instance.

One the service is up and running you can add extra features such as a static IP address to make DNS routing easier. If you business grows you can easily add things like cloud front distribution, a shared database instance and load balancer if the site gets popular enough to need multiple servers. Lightsail walks you through it. If the website gets features on a runaway Tik Tok viral video you can even convert it to the full-fledged EC2-based service stack with minimal effort.

Moving An Existing WordPress Site To Lightsail, Export Tool

AWS provides some great documentation to assist with moving an existing WordPress site over to Lightsail. However, this methodology does NOT copy all the software onto the new server and thus requires extra work to get things “exactly the same”.

The basic steps:

  1. Export your existing site content and download the XML file
  2. Create a Lightsail instance for WordPress
  3. Fetch your WordPress login (username: user , password: stored in a file on the server you get to with a web-based SSH console)
  4. Login to the new instance and import the content
  5. Optional (sort of) assign a static IP to the new lightsail instance
  6. Update the website’s DNS record to point the domain to your new lightsail instance.

Necessary additional steps:

While the instructions above will bring over all of the data you need for the site, what it does NOT do is install all the secondary software that was on the main site. As such you’ll need to:

  1. Install themes from the original site
  2. Install plugins from the original site
  3. Drop any unused themes or plugins that came pre-installed on the Bitnami Lightsail stack

Additional steps to consider:

Since the images provided by Bitnami are generic, there are some tweaks that are common before publishing the site to the world.

  1. Add an SSL certificate via the SSH login
  2. Remove the Bitnami badge from the site via the SSH login
  3. Add a second admin login/password only known to the site owner
  4. Deactivate the user account

Moving An Existing WordPress Site, All-In-One by Servmask

An alternative route for copying over the website is to use the All-In-One WP Migration plugin by Servmask. This tool comes with the default Bitnami image that is installed by AWS Lightsail, saving an additional step on the new server. (Note: I am not affiliated with Servmask or this plugin. Use at your own risk.)

Basic steps:

  1. Install the All-In-One WP Migration plugin on your original site.
  2. Export the site, I prefer to a download file but they have paid extensions to export to many places like S3 or Dropbox.
  3. Create your new Lightsail instance
    1. Assign the instance a static IP once is is running.
  4. Login via SSH to get your password for the admin user “user” on WordPress
  5. While you are there you will likely need to increase the PHP file size limit if your export is more than the 80MB limit all Bitnami WordPress Lightsail images come with and restart the services.
  6. Import your site via the download file — this method also gets all plugins and themes from the original site
  7. Point your domain name to the new static IP.
  8. Configure the SSL certificate.
    1. Login to the Lightsail instance with SSH
    2. sudo /opt/bitnami/bncert-tool

Summary

While AWS Lightsail may not be perfect for every WordPress-based website, it does a great job at providing a solid web presence for most businesses. It can easily be backed up and replicated for testing purposes. The container spin up very quickly. It is low cost. And despite the container-based-all-in-one solution, it performs extremely well even under high volumes of traffic.

Unless you are running a SaaS service, update your website frequently, or a running a web presence for a company with millions of page views every month — Lightsail may work for you. Anyone can get started with this service with minimal technical skills required.

Hosting WordPress on AWS Lightsail is worth a try next time you need a WordPress site.

Cleaning Up WordPress – Large wp_options.idb

Did your tiny AWS Bitnami WordPress server run out of disk space? The culprit may be unoptimized data tables in MySQL. Finding a 4.9GB wp_options.idb file on your server is not as uncommon as you may think. What makes it surprising is when you look at your wp_options entries and discover there are only 300 rows in that table with limited text in the option_value column.

Thankfully there is an easy fix as long as you can get enough disk space to manage the task. Start by looking for any log files or other files you are CERTAIN you don’t need so you can shut down web, php, and mysql services.

Finding Large Files

# sudo find / -xdev -type f -size +50M -print | sudo xargs ls -lh | sudo sort -k5,5 -h -r

This command finds all files on the drive over 50M in size.

Look for specific MySQL raw data files (wp_options.idb for example) in that list. Remember the table name for later.

Stopping System Services

Stop the services. On an AWS Bitnami WP server they have their own special control scripts in place of the standard Linux services library.

bitnami@ip-172-31-87-127:/opt/bitnami/mysql$ sudo /opt/bitnami/ctlscript.sh stop mysql


usage: /opt/bitnami/ctlscript.sh help
       /opt/bitnami/ctlscript.sh (start|stop|restart|status)
       /opt/bitnami/ctlscript.sh (start|stop|restart|status) mysql
       /opt/bitnami/ctlscript.sh (start|stop|restart|status) php-fpm
       /opt/bitnami/ctlscript.sh (start|stop|restart|status) apache

help       - this screen
start      - start the service(s)
stop       - stop  the service(s)
restart    - restart or start the service(s)
status     - show the status of the service(s)

Stop all the services, then start MySQL only.

Cleaning Up MySQL

If you are doing system admin commands you should know how to find your data access credentials in the wp-config.php file. No need to go into that here. Find the credentials and login to MySQL.

No OPTIMIZE that table (and any others that you suspect have unusually large .idb files).

mysql> OPTIMIZE NO_WRITE_TO_BINLOG TABLE wp_options;

That’s it. One simple command may easily recover 4.9GB of disk space , making a 4.9GB file on a 9GB drive get back to a more reasonable 10MB.

Why WordPress core or cron jobs are not doing this on a regular basis is a question for another day…

Hate Spam? Turn Off Jetpack Email Sharing

jetpack publicize spam

The past few days have been spent diagnosing various email delivery issues from the AWS web cluster that is running our WordPress plugin store as well as our SaaS locator platform. During this process email routing was pushed from the servers through the AWS Simple Email System. SNS notifications were enabled to monitor the progress and provide some insight as to what was happening on the send mail side of things.

Not far into the mission something odd was showing up. Email delivery notifications were being transmitted from our documentation server — a basic WordPress install with almost no plugins running and a simplified theme. Yet in the “delivered” stack of SNS notifications there were random email addresses being spammed every 30-60 seconds.

The Culprit? Jetpack

Turns out the documentation site has Jetpack installed. It also has the default settings for the publicize sharing enabled. This includes email sharing.

After a good bit of research it was found that the mailing subsytem was being exploited through the front end interface for sharing a post via email. The Postfix mail logs provide immediate evidence of this.

Read More

AWS LEMP Stacks and EFS Issues

Lesson learned — if you are using EFS on production systems you want to be using provisioned throughput mode.

But, before we get into that, let’s go over the details of this implementation…

Service Configuration

We utilize AWS EC2 instances to run multiple WordPress sites hosted in different directories. The configuration is fairly standard: 2+ servers configured as part of an load-balanced cluster. The servers run from the same image meaning they use the same underlying software stack.

Part of that image includes a mounted EFS (Elastic File Storage) directory , used to share WordPress resources between all nodes in the cluster. The original architecture was designed to host not only the typically-shared wp-content/uploads folder of WordPress via this EFS mount but also the code. The thought was that sharing the code in this way would allow a system admin to easily update WordPress core, plugins, or themes from the typical wp-admin web login. Any code updates would immediately be reflected across all nodes.

EFS Web App Code Hosting – A Bad Idea

Turns out this is a bad idea for a few reasons. First of all, EFS volumes are mounted using the NFS4 (network file storage) protocol — this defines how the operating system handles file read/write operations for a network mounted drive. While NFS4 is fairly robust, the throughput of ANY network drive, even on a high speed AWS data center backbone, is much slower than a local drive such as an EBS volume.

That means that even on a good day every PHP file, JavaScript file, or anything else needed to serve up that WordPress web page are going to be a bit slower than normal.

However, the bigger problem comes to light if you happen to choose the default, and pushed as “the mode to use” by Amazon, EFS throughput mode known as “Burst mode”.

Read More
%d bloggers like this: