David's Blog

I have a production WordPress site on Amazon Lightsail that I need to migrate away from. These are notes on how to migrate over only the stuff I want to keep.

Backstory: Amazon Lightsail was very inexpensive, at under $5 per month for hosting on their smallest machine, and it did fine. Two things became problems, however:

• Bitnami WordPress is super easy to spin up, and everything just works. But upgrading to a newer version of something (say PHP or MySQL or something) is a non-starter. The only way to upgrade is to spin up a new machine and do a migration to a new machine.
• Amazon recently did a price increase. Now, I can get a Linode machine with double the RAM for only $2 more, and that will include backups.

Okay, so I need to migrate, but over the years, I’ve tried different plugins, and even though many of them were uninstalled, the installation routine left crap in the database. How to migrate to a new server, but leave behind the crap? This will be the topic of this post.

First, I installed WP-CLI, instructions can be found here.

Then, on the new machine, I installed only those Plugins which I know I need.

I took a snapshot backup at this point, simply because it seems prudent.

On the new machine, I logged in with ssh and ran this:

wp --path='/var/www/html/wordpress' db query "SHOW TABLES" --skip-column-names --allow-root

This gives me a list of the tables in the new machine that I want from the old machine.

+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_termmeta           |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+

This is a pretty minimal list; the old machine has a list 362 tables long! Matomo was a particularly egregious offender here.

With this information, I can use a script written by Mike Andreasen over on the WP Bullet website to dump the databases on the old machine:

# set WP-CLI flags
WPFLAGS="--allow-root"

# define path to the database dumps without trailing slash
DBSTORE="/tmp"
# get the name of the database
DBNAME=$(wp config get DB_NAME ${WPFLAGS})

# list all of the tables regardless of database prefix
TABLELIST=(wp_posts wp_postmeta)

# create the temporary directory for storing the dumps
mkdir -p ${DBSTORE}/${DBNAME}

# loop through tables and export, log details to /tmp/mysqlexport-<database>.txt
for TABLE in ${TABLELIST[@]}
do
    # export the table
    wp db export ${DBSTORE}/${DBNAME}/${TABLE}.sql --tables=${TABLE} ${WPFLAGS} | tee /dev/stderr
done > /tmp/mysqlexport-${DBNAME}.txt

With this done, I scp the files from the old machine to my local machine. Then I scp them up to the new machine. The next script assumes they are in the sql directory in the wordpress folder.

I tried it, but I should have taken a snapshot, first. 😉

I need to search-and-replace all instances of the old domain name in the MySQL dump files, and put in the new domain name. Technically, once the actual switch happens, the new machine will be found at the old name, so this shouldn’t be necessary. But, the whole reason for migrating to a development machine is to test out this migration process. And the new machine does have a different domain name.

The script to upload the MySQL dumps looks like this:

# define WordPress path
WPPATH="/var/www/html/wordpress"

# loop through all of the 
for DUMP in /var/www/html/wordpress/sql/*.sql;
do
    wp db import ${DUMP} --allow-root --path=${WPPATH}
done

But, until the data is cleaned up, the new WordPress website gets the dreaded white-screen-of-death.