Migrating My WordPress Database from a Lightsail Instance to a Standalone Database

Last year, I moved this blog off of a EC2 instance running a too-old version of PHP to a Lightsail instance. I had to restart that instance in order to retrieve the images associated with all the prior posts so they looked exactly as they did before, but the end result was the same blog at a lower monthly cost. Since then, I installed and configured the WP Offload Media Lite plug-in to push all those images to an S3 bucket. Today I decided to move the WordPress database off the Lightsail instance to a standalone database.

Accomplishing this move required cobbling together instructions from Bitnami and AWS (and filling in any gaps with educated guesses). Here are the steps I took to get everything moved over, in the order I took them.

  1. Export the application database from the Lightsail instance. As of this writing, the Bitnami WordPress image still keeps database credentials in a bitnami_credentials file, so using that with the mysqldump command generated the file I would need to import to the new database (backup.sql).
  2. Download backup.sql to my local machine. Connecting to my Lightsail instance with sftp and my SSH key followed by “get backup.sql” pulled the file down.
  3. Download MySQL Workbench. Looking at these import instructions, I realized I didn’t have it installed.
  4. Create a Lightsail database. On the advice of co-workers who also do this with their side projects, I used us-east-2 as the region to setup in. I specified the database name to match the one in the backup.sql file to make things easier later when it was time to update wp_config.php.
  5. Enable data import mode. By default, data import mode is disabled and public mode is disabled. So I turned on data import mode and was puzzled for a second when I couldn’t connect to the database in order to import from backup.sql.
  6. Enable public mode. With public mode disabled, and my backup.sql file (and tools to import it) not already available in a us-east-2 hosted instance or other resource, I couldn’t load the backup data. Once I enabled public mode, I was able to use MySQL Workbench to connect and upload the data.
  7. Disable public mode.
  8. Update wp_config.php to use new database credentials.

To confirm that the post you’re reading now was written to the new database, I turned on the general query log functionality on the database instance to ensure that the server was writing to it. Having confirmed that, I turned off the general query log.

The additional cost of a standalone Lightsail database is worth it for the week’s worth of database backups you get with zero additional effort. Migrating to a newer WordPress instance in the future should be easier as well, now that both the database and media for the site are off-instance. The next step I need to take is upgrading from the lite version of WP Offload Media to the full one. This should offload all the media so I can safely remove it locally.

GenXJamerican.com Moves to Amazon Lightsail, A Follow-Up

One change I missed after migrating to Lightsail, was ensuring that all the posts with images in them were displaying those images on the new site the way they were on the old. A scroll backward through previous posts revealed the problem quickly enough, but life is busy so it took awhile until I had enough time fix it. The steps I expected I would need to take to resolve the missing images issue were roughly the following:

  • Start up the old EC2 instance
  • Download the old images
  • Upload the old images to the new instance on Lightsail

Because I only stopped the previous EC2 instance instead of terminating it, I was able to re-start it. To download the old images, I’d have to find them first. Having self-hosted WordPress for awhile, I knew the images would be in subfolders under wp-content/uploads, so the only real question remaining was where exactly the old Bitnami image rooted the install. Once I “sshed” into the instance, that location turned out to be ~/stack/apps/wordpress/htdocs/wp-content/uploads. Images were further organized by year and month of blog posts. To simplify the downloading of old images, I had to knock the rust off my usage of the tar command. Once I’d compressed all those years of images into a few archive files it was time to get them off the machine. I used this Medium post to figure out the right syntax for my scp commands.

Once the archive files were on my local machine, I needed to get them onto the Lightsail instance (and expand them into its uploads folder). But just as I did compressing and pulling the files down from the EC2 instance, I had to figure out where they were in the new Bitnami image. As it turned out, the path was slightly different in the Lightsail image: ~/stack/wordpress/wp-content/uploads. Once I uploaded the files with scp, I had to figure out how to move them into the years and months structure that would match my existing blog posts. Using the in-brower terminal, I was reminded that the tar command wouldn’t let me expand the files into an existing folder structure, so I created an uploads-old folder and expanded them there. Then I had to figure out how to recursively copy the files there into uploads. It took a few tries but the command that ultimately got me the result I wanted was this:

sudo cp -R ./uploads-old/<year>/* ./<year>

Now, every post with images has them back again.

GenXJamerican.com Moves to Amazon Lightsail

Before last year ended, I moved this blog off its EC2 instance running a too-old version of PHP to an Amazon Lightsail instance in a new region. The original rationale for hosting on EC2 was to have a project and a reason to do things in AWS other than whatever a certification course might teach. But having finally earned that AWS Certified Solution Architect Associate certification last spring (and paid more in hosting fees than a blog as small as this really merits), the switch to a simpler user experience and lower cost for hosting was overdue.

Lightsail made it simple to launch a single self-contained instance running the latest version of WordPress. The real work was getting that new instance to look like the old one. Getting my posts moved over wasn’t hard, since I make a regular habit of using Tools > Export > All Content from the dashboard to ensure I have a WordPress-compatible copy of my posts available. The theme I use however (Tropicana) recommends far more plugins than I remember when I first chose it. The Site Health widget nags you about using a persistent object cache, so I tried getting the W3 Total Cache plugin working. I kept seeing an error about FTP permissions that I couldn’t resolve so I got rid of the plugin and Site Health said the server response time was ok without it. Another plugin I got rid of was AMP. Something about how I had AMP configured was seemed to prevent the header image from loading properly. With AMP gone, everything worked as before. Akismet Anti-Spam and JetPack are probably the most important plugins of any WordPress install so I made sure to get those configured and running as soon as possible.

The last change I needed to make was the SSL certificate. The Lightsail blueprint for WordPress (the official image from Bitnami and Automattic) has a script which automatically generates certs using Let’s Encrypt. When the script didn’t work the first time (because I’d neglected to update my domain’s A record first), I went back and made that change then shut down the (now) old EC2 instance.

GenXJamerican 2.0 still needs some more changes. I used to have a separate blog just for photos, years ago when one of my best friends was hosting WordPress instances. The Social Slider Feed plugin lets you pull in content from Instagram and other social media sites, so I’ve added those to a Photos page. Once I figure out the photo gallery plugin, that should be the next update. I’ll also be looking into the ActivityPub and WebFinger plugins as part of my growing interest in Mastodon.

A long overdue upgrade

I’m finally running the latest version of WordPress (I’ve been way behind on upgrading).  I’d be curious to hear from those of you who visit regularly what you think of the new look.  I’d considered a couple others:

I ultimately chose this one for the random post feature that appears in the upper-right of the page.