Moving WordPress (3) – database and content

2023-09-23

This is the third part of my writeup of everything I did to move my site from one EC2 instance to another, and make a bunch of other changes. It’s mostly documentation for myself, so it is rough and may at times not make complete sense, though I hope anybody else who tries something similar finds it useful:
Moving WordPress (1) – what I wanted to do
Moving WordPress (2) – AWS setup
Moving WordPress (3) – database and content
Moving WordPress (4) – SSL, themes, etc.

Almost all WordPress and related instructions assume you are using a “standard” hosting service that includes the LAMP stack and a bunch of related utilities, all nicely packaged in something called “cpanel” that you use to manage them. I was not doing this, so a lot of the documentation didn’t apply. I was also using a database server that is separate from the “localhost” on which WordPress runs. So I was very much in uncharted territory and that’s part of the reason I’m documenting this.

Worth pointing out that one of the easier ways to set up WordPress on AWS would be to use Lightsail, which offers fully-configured hosting, including the option of cpanel. Several years ago I experimented with it and did not like the offering, but it’s been much improved by new management (including some people I was fortunate enough to work with during my time there) and would be something I would try again today if I hadn’t already gone in a different direction.

Database setup/migration

Setting up the database on the RDS instance was relatively painless. I installed a “clean” wordpress application and used it to create the database on the correct database server. This required making some minor changes to the standard installation that assumes database installation on “localhost.”

Table/schema setup

That created all the tables I needed, in exactly the form I needed them, with the right character set and collation, and with all the right indexes and other flags. For WordPress sites, the recommended charset is utf8mb4 and the recommended collation is utf8mb4_unicode_ci. RDS uses different defaults, so if you set things up by any mechanism other than the WordPress setup software, you may get them wrong. I found this guide to the WordPress database to be helpful: https://deliciousbrains.com/tour-wordpress-database/. (I did not use any of their tooling. I’d rather punish myself.)

An alternative I considered is using the full suite of AWS tools. The DMS (Database Migration Service) is simple enough, but only migrates the data and, depending on the specifics, sometimes the primary key for each table. It won’t create all the other keys, any other settings (WordPress depends on autoindexing the primary key for each table, which is a flag), or character sets/encodings. To capture all those, you need to use the AWS Schema Conversion Tool. I’ve never used that before, and since WordPress can easily create the schema for me, I did not see any point in trying.

DB Cleanup

I made sure the databases on the old site were clean, using the free version of the WP-Optimize plugin within the original WordPress environment. This allowed me to not only clean up the database, but also remove a bunch of old tables from a plugin I was no longer using, that were causing issues. (OK, I did this after screwing things up twice in a row).

Things to note:

Some plugins install their own tables in the WordPress database. They will mostly not remove them if you uninstall the plugins. My main site has moved around a few times and I’ve never completely wiped things clean, so I had a lot of old cruft that no longer served any purpose. “DROP TABLE” is your friend.
The WordPress application keeps a copy of every version of every post. A “post” also includes the metadata entry for every image or other object that you upload. This means that if you make regular updates to your post entries to correct things or keep them up-to-date, you could have dozens of previous versions stored unused. You can limit this by adding the following to wp-config.php:
define( 'WP_POST_REVISIONS', 3); where the number is how many previous versions to keep. I have it set to 3, but you may have different needs. Changing this will not automatically delete all the old ones, so going through and cleaning them out manually (or with the wp-optimize plugin) makes sense.
The wp-options table can get filled with junk and really, really, really slow things down. Mine contained option entries from old deleted plugins and themes that should have had expiration dates on them but were still taking up space in the database. My limited understanding is that all the options have to be read for a page to be displayed, so having a lot of them really slows things down, especially if they are defunct and point to things that no longer exist on the site. Cleaning this up was by far the most important thing I did for site performance. It may not be for everybody. As noted, my site has gone through a lot of changes over the years, so I probably had a lot more cruft in there than most.

DMS for data migration

I did use DMS to move data from the old database to the new, once the new tables were set up with the right schema. To accomplish this, I had to do a few things:

I had to pair the old and new VPCs. I had set up the IP range of the new VPC to not overlap, so this was easy. For simplicity during the transfer, I disabled the NACLs for both VPCs. Not something you want to do in a “real” production setting, but as with much I did in this exercise, I decided to violate that rule.

Setting up the DB Migration endpoints in DMS is pretty straightforward. The source endpoint is the old MariaDB on EC2, target is new RDS. Those endpoints can be used in both serverless and migration server modes of DMS. I chose serverless. There are a number of options and selection criteria, and I won’t address the details here because it’s very well documented. I was moving multiple databases one at a time so was using the same migration setup with different selection criteria. What MySQL/MariaDB call a “database” is what AWS calls a “schema,” and that’s the only thing that changed from one database to the next.

Overall, once I had a clean database prior to migration it went very easily, though some of the problems I encountered that I thought were related to the database were caused by other incompatibilities. I would recognize and address those later.

Site content migration

Moving the site content should have been easy. In the past it always has been.

I installed WordPress and used it’s setup scripts to create the database and everything else. Typically, it should be possible to just move the wp-content directory that sits inside the main directory for each site.

I used scp to move files between the two peered VPCs. To do this it is necessary to exchange RSA keys. This is explained in this article.

scp -r ec2-user@<original ip address>:/var/www/html/michaelgatwebsite/wp-content/. .

You need that dot at the end of the source to get scp to behave the way everything else does in Linux. Without it, the command won’t copy the hidden files like htaccess that are needed for WordPress to function. I did it wrong once, realized the error when things didn’t work, then did it again.

Still no luck in getting the site to run. I was getting an error page.

It’s the content stupid

I started digging around, trying to find any files that might have been missed that would cause the error. I made sure all the file and directory permissions were correct. I was stumped. The site worked on my previous host, why would it not work on a new, upgraded one?

The obvious answer was that the upgrade had broken something, and the most obvious initial culprit was one of the several WordPress plugins. I wiped the /wp-content directory, pointed my browser back at the original site, and made modifications. I turned off all the plugins, then removed them entirely, just in case. They’re easy enough to re-install if needed. Then I move the directory using the scp commands again.

Still no luck. Just a vague WordPress error message.

I looked around again and figured out how to turn on debugging mode in a config file, then tried again. I noticed a lot of warnings around functionality not being supported in PHP 8. Most of these were coming from the WordPress theme files, which I had purchased a while back from a third party. With a bit more digging it became clear that the theme seller had removed their website around the time PHP 8 came out. Probably decided that a legacy business generating minimal income from now-outdated themes wasn’t worth the trouble of the upgrades that would be needed by the dozens of themes they had once sold.

What’s particularly annoying about this is that to this day, the free versions of those themes are still available on the WordPress site. As far as I can tell, those themes have not been modified since PHP 7.2 (which ended most support in 2019, and has been end of life since 2020) and WordPress software version 5.2. Why they are still available for download with the apparent endorsement of WordPress, is a mystery to me.

Skip the Themes

I switched the old site to the latest WordPress “generic” theme and copied files again.

This time it worked. Not perfectly, as I hadn’t installed an SSL certificate yet, so some links and photos were failing to load properly, but I was familiar with that problem from the past. With the problem addressed, I moved the remainder of the sites, verified that they too were working, and moved on the the final tasks, including SSL setup and finding a new theme.