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.
- 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).
- 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.
- Download MySQL Workbench. Looking at these import instructions, I realized I didn’t have it installed.
- 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.
- 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.
- 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.
- Disable public mode.
- 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.