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
Login to your MySQL instance, replace USERNAME with your username and enter password when prompted
mysql -u USERNAME -p
List the databases on the server
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
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
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';
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.