AWS - Migrate Joomla MySQL Database to Amazon RDS

Amazon RDS for MySQL offers many benefits over building and maintaining your own MySQL environments giving you time to focus on application development by managing time-consuming database administration tasks including backups, software patching, monitoring, scaling and replication.

This guide shows you how to move your Joomla MySQL database from an Ubuntu server to an AWS RDS MySQL Database, although this guide focuses around Joomla it could be applied to other MySQL to RDS migrations.

AWS - Create the RDS Database

Navigate to RDS within the AWS Console ensuring you are in the correct region you want the database to be created

Select Create Database

Select the MySQL engine and click Next

With Joomla not supporting version 8 at time of writing (See here), please select the latest version 7 available. (i.e. MySQL 5.7.25)

Select db.t2.micro for the instance class if you are using the free tier (Please be aware selecting greater classes or using Multi-AZ will incur costs)

Create a DB instance identifier with a master username and password and document for later use

On the next page, under Network and Security, specify your VPC

Select your subnet group or leave as "Create new DB subnet group"

Note: If you get the error "DB Subnet Group doesn't meet availability zone coverage requirement. Please add subnets to cover at least 2 availability zones. Current coverage: 1 (Service: AmazonRDS; Status Code: 400; Error Code: DBSubnetGroupDoesNotCoverEnoughAZs;" exit the RDS creation process and navigate to VPC then create another private subnet in another availability zone

Once this additional subnet has been created in another availability zone you can go back to RDS, then select subnet groups from the left hand side then Create DB subnet group giving it a logical name, description, correct VPC and you must specify at least 2 subnets in different availability zones.

Leave other options as default unless change is required

Enter a database name and document for later use

Leave the remaining values as default and create

Allow a few minutes for the RDS Database to be created

Make a note of the endpoint and port once the database has been created

Update the security group

You will have to update the security group that is attached to the RDS instance to allow port 3306 access from your EC2, i.e.

MySQL - Backup the existing database

At this point using your preferred MySQL method you need to disable write access to the old database to ensure the old and new are in sync. Depending on your Joomla setup it may be a requirement to put the site into maintenance mode, i.e.

In Joomla admin area go to Site > Global Configuration > Site Settings. Next to Site Offline click Yes

Ensure no admins are working in the Joomla administration section

SSH to your MySQL server and run the following command

mysql -u USERNAME -p

List the databases on the server

show databases;

Once you have confirmed your database name(s) type exit

Run the following command to take a backup of your database, replacing USERNAME and DATABASE with your own details

mysqldump -u USERNAME -p DATABASE > infraengineer_backup$(date +%F).sql

Verify the backup has been taken by running the command which will show the file and its size

ls -lh

Restore the database to RDS

Verify that you can talk to RDS by running the following command from your existing EC2

mysql -h ENDPOINTNAME.xx-xxx-x.rds.amazonaws.com -u USERNAME -p

List the databases on the server

show databases;

This proves connectivity to RDS and shows the database created earlier

Exit mysql by typing exit

The database backup can now be restored to RDS via the following command

mysql -h ENDPOINTNAME.xx-xx-x.rds.amazonaws.com -u USERNAME -p -D DATABASENAME < BACKUPNAME.sql

Depending on the size of the database it should now have been restored into RDS

You should be able to see the created tables by connecting to it and showing the tables, i.e.

use DATABASENAME

show tables;

Update the connection string to point to RDS

Edit your Apache configuration.php file located at /var/www/<site name>/configuration.php

vi /var/www/<site name>/configuration.php

The original values will look something like;

public $host = 'localhost';

public $user = 'root';

public $password = 'your_database_password';

public $db = 'your_database_name';

These need to be changed to your RDS values, i.e.

public $host = 'rds_database_endpoint';
public $user = 'rds_database_username';
public $password = 'rds_database_password';
public $db = 'rds_database_name';

Restart your Apache service for the new changes to come into effect

sudo /etc/init.d/apache2 restart

Within Joomla, put the site back into online mode

Test access to the website then monitor performance over the coming hours or days to see if the database instance class needs to be increased or decreased.