• Posted in

WordPress AWS Lightsail Load Balancer prep – mariaDB Migration to MYSQL

Operating a WordPress blogsite brings the joy of content creation, but with increased visitors, the demand on your hosting infrastructure grows. Recently, I encountered downtimes on my WordPress blog, hosted on AWS Lightsail, due to spikes in traffic. To ensure a seamless experience for my audience, I decided to optimise my setup by implementing AWS Lightsail Load Balancer and deploying multiple instances.

Setup Overview:

My current setup involves Bitnami WordPress running on a Lightsail instance, featuring a Linux distribution, Apache, and MariDB. Additionally, all static content is stored in an S3 bucket with CloudFront distribution, ensuring efficient content delivery.

Improving Performance:

To address performance issues, I opted to utilize the Lightsail Load Balancer and deploy multiple instances. However, simply creating new instances can lead to database synchronisation complications. Therefore, I chose to utilize a single RDS instance with a Lightsail database, selecting MySQL as the database engine.

Step1: Migration to MySQL Lightsail Database:

1.a Backup WordPress Database:

Utilise the following steps to back up the WordPress database:

  • Create a MySQL instance from Lightsail Database.
  • Generate a WordPress database backup using the command:
    $ mysqldump -u root -p bitnami_wordpress > backup.sql
  • Copy the backup file to the S3 bucket:
    $ aws s3 cp /home/bitnami/backup.sql s3://<bucket-name> --acl bucket-owner-full-control

1.b Restore WordPress Backup to MySQL:

  • Download MySQL Workbench.
  • Connect to MySQL Workbench to the Lightsail database instance (ensure public connection is enabled).
  • Use MySQL Workbench to restore the data into the Lightsail database.

1.c Update WordPress Configuration:

Modify the WordPress configuration file to use the new database settings:
$ vi /opt/bitnami/wordpress/wp-config.php

Update the following based on the new database setup:

   /** The name of the database for WordPress */
   define( 'DB_NAME', 'dbxxxxx' );

   /** Database username */
   define( 'DB_USER', 'xxxxxxxxx' );

   /** Database password */
   define( 'DB_PASSWORD', 'xxxxxxxxxxxx' );

   /** Database hostname */
   define( 'DB_HOST', 'ls-xxxxxxxxxxxxxxxxxxxx.yyyyy-2.rds.amazonaws.com:3306');

Restart Instance and Database:
Restart the instance to apply changes and stop the MariaDB Database inside the Lightsail instance:
$ sudo /opt/bitnami/ctlscript.sh stop mariadb

1.d Snapshot Creation:

Create a snapshot of the instance for future use in creating other instances.

Conclusion:

By migrating to a Lightsail MySQL database and deploying multiple instances with the help of Lightsail Load Balancer, I’ve significantly improved the performance and scalability of my WordPress blogsite. Now, even with increased traffic, my website runs smoothly, ensuring a seamless experience for visitors.