Migrate MySQL for Open edX to AWS RDS

Summary

If you’re looking for a general overview of how to scale the Open edX platform then you should read this first, “Scaling Open edX“. Also, bear in mind that this article presents a luxury alternative to the best practice that I outline in my article, “Scaling MySQL for Open edX” which you should definitely read if you haven’t already done so.

AWS provides an excellent managed relational database service named RDS that provides extremely high up-time and also takes care of all Linux and MySQL database administration activities for you. It costs roughly double the alternative of hosting your own Ubuntu EC2 instance with the MySQL service installed, but it’s a great alternative if you can afford it. Aside from taking care of all data backups and server snapshots for you, which is a true luxury, it also provides you with a console where you can dial up processing capacity literally with the click of a button. But having said all of that, very few of my clients actually use AWS RDS because MySQL for Open edX tends to be a low-maintenance service once it’s been migrated to a standalone server. But, if you can afford RDS you definitely will not be disappointed with it.

There are a couple of technical challenges to creating a MySQL database environment from scratch for use with Open edX. First, you’ll need to restore a backup of the “mysql” database in the existing local MySQL service of your Open edX platform. This database contains the usernames, passwords and permissions for the many MySQL accounts that are created by the Open edX native installation process. I provide exact instructions below on how to do this. And second, once migrated, you might need to upgrade this database depending on the precise MySQL version and release that runs on AWS’ RDS service. The exact nature and complexity of this upgrade(s) can vary depending on the version that is currently running on your existing Open edX platform, the version on your new AWS RDS service, and the number of major MySQL releases in between these two. However, beyond those two issues this article looks quite similar to my other article on migrating MySQL.

Regarding horizontal Vs vertical scaling strategies: note that you probably only need to focus on vertical scaling for MySQL on Open edX. Even though MySQL implements a publisher-subscriber model, I’ve found this to be complete overkill for all edX platforms on which I’ve ever worked. You can reasonably begin to scale MySQL using AWS’s default RDS service as further described below, increasing the processing capacity over time as-needed.

Note that OpenCraft also provides technical instructions for this operation in their Open edX Deployment ReadTheDocs pages.

1. Create an AMI from your Open edX instance

Follow the instructions in the AWS official documentation to create a clone (aka an “Amazon Machine Image, AMI”) of your existing Open edX instance, “Create an AMI from an Amazon EC2 Instance“. Keep in mind that you’re going to initialize your MySQL database from this AMI, so you need to ensure that no learners are interacting with your Open edX instance during the time that are migrating your MySQL data. You should conservatively estimate a 4-hour maintenance window for this activity. I generally perform this work during off hours.

1. Launch a new AWS RDS Instance from the AWS Console

First, check the distribution and version of the mysql service that is running locally on your Open edX instance by issuing the following command

mysql --version
mysql  Ver 14.14 Distrib 5.6.32, for debian-linux-gnu (x86_64) using  EditLine wrapper

In this example, the value “5.6.32” is the most important piece of information. Ideally, you’ll create an AWS RDS instance that exactly matches this version number but if that isn’t possible then get as close as you can, selecting a version that is equal to or greater than the version installed locally on your Open edX instance. In my example case above the best I could do was version 5.6.34

2. Important: Create a new EC2 Security Group for MySQL

You should create a separate EC2 Security Group for your new AWS RDS MySQL instance, as follows:

This firewall configuration limits remote access of the server to MySQL, regardless of whatever other services might still be installed and running internally on the server. Note that on the first row, SSH, you should try to limit access to your bastion server, if you use one.

Other AWS RDS Settings

I’m going to skip past other settings, but, I will share a few observations with you. First and most importantly, you probably do not need anything greater than t2.medium (2-CPU, 4Gb RAM) in terms of underlying processing power for your AWS RDS instance. Open edX runs on Django, and most of the queries that Django generates rely on a primary key and are therefore highly efficient. There is very little in the way of SQL aggregation queries in Open edX applications. Therefore, the mysql service itself requires minimal processing power. Second, You can mostly follow the suggested defaults for other settings with the one exception of “Availability” — you probably do not need a redundant instance operating in a different data center. This doubles your cost, and you’ll probably never need it.

3. Take Note of The AWS RDS Instance Host Name That Was Automatically Created

You’ll access your new AWS RDS MySQL service via the hostname, which is automatically assigned by AWS when you create the new RDS instance. Take note of this value, which will be titled, “Endpoint”

To identify which yml files require modification you can use the Linux command, ‘grep’ to search for the Linux port number corresponding to the service you are scaling. For example, MySQL is assigned to Linux port number 3306, thus we can execute the the following command to identify all Open edX configuration files that contain MySQL configuration parameters:

sudo grep -rn '3306' /edx/etc/

We can see that in total there are seven yml files containing MySQL configuration parameters, and furthermore that some of these yml files contain multiple references to MySQL, as follows:

/edx/etc/insights.yml:26:        PORT: 3306
/edx/etc/discovery.yml:26:        PORT: 3306
/edx/etc/discovery.yml:38:        PORT: 3306
/edx/etc/lms.yml:209:        PORT: '3306'
/edx/etc/lms.yml:218:        PORT: '3306'
/edx/etc/lms.yml:227:        PORT: '3306'
/edx/etc/studio.yml:167:        PORT: '3306'
/edx/etc/studio.yml:176:        PORT: '3306'
/edx/etc/studio.yml:185:        PORT: '3306'
/edx/etc/xqueue.yml:16:        PORT: '3306'
/edx/etc/analytics_api.yml:24:        PORT: '3306'
/edx/etc/analytics_api.yml:34:        PORT: '3306'
/edx/etc/ecommerce.yml:36:        PORT: 3306

For example, the MySQL configuration for lms.yml looks like the following:

DATABASES:
    default:
        ATOMIC_REQUESTS: true
        CONN_MAX_AGE: 0
        ENGINE: django.db.backends.mysql
        HOST: localhost
        NAME: edxapp
        OPTIONS: {}
        PASSWORD: vU2MEmYcj7cSp79hZGUCWav6FbQT3W1v6kS
        PORT: '3306'
        USER: edxapp001
    read_replica:
        CONN_MAX_AGE: 0
        ENGINE: django.db.backends.mysql
        HOST: localhost
        NAME: edxapp
        OPTIONS: {}
        PASSWORD: vU2MEmYcj7cSp79hZGUCWav6FbQT3W1v6kS
        PORT: '3306'
        USER: edxapp001
    student_module_history:
        CONN_MAX_AGE: 0
        ENGINE: django.db.backends.mysql
        HOST: localhost
        NAME: edxapp_csmh
        OPTIONS: {}
        PASSWORD: igSQb0Wxfrnf6FYXso9NuEfj9y7eNgtzpOK
        PORT: '3306'
        USER: edxapp_cmsh001

To reconfigure the LMS to use a remote MySQL server we would modify the ‘HOST’ parameter from ‘localhost’ to the Internal IP address of the newly-created remote MySQL server. Note that we MUST address remote servers using internal IP addresses because otherwise our network traffic would leave and re-enter our Virtual Private Cloud which would not only be inefficient from a performance point of view but also insecure.

Do a full reboot of your Ubuntu server when you finish.

sudo reboot

Ok, that’s it for MySQL. If you can login to your Open edX instance as a normal user, and also login to the Django admin console then your migration was successful.

Testing your platform is easier than it might seem.

• If you can login to the LMS then the remote mysql service is correctly configured for this application.
• If you can navigate to the Course Management Studio, and you can see the course list on the home page then the remote mysql service is correctly configured for this application.
• If you can see course cards in the “Explore New Courses” page of the LMS then the remote mysql service is correctly configured for the Discovery service.
• If you can navigate to Course Management Studio and browse the course list on the home page then the mysql service is correctly configured for this application
• If you use the E-commerce service and you can browse the page https://[yourdomain.com]:18130/courses/ then the remote mysql service is correctly configured for this subsystem.

On the original Open edX we’re only going to shut down the mysql service and prevent it from launching after reboots. We’ll leave the system files and the legacy data in place since these (probably) do not consume significant file system storage space.

# Execute these commands on your Open edX instance.
# terminate the mysql service.
sudo service mysql stop
# prevent mysql from automatically starting after reboot.
sudo systemctl disable mysql
# reboot the server
sudo reboot

If you have a cron job for scheduled data backups then disable this on your Open edX instance via the following command.

cron -e

Run the following command if you’re curious and want to know the exact size of the legacy mysql data files that remain on your Open edX instance

sudo du -sh /var/lib/mysql

If you followed my blog post, “Open edX Complete Backup Solution“, then the only thing you’ll have to do is edit the bash script edx.backup.sh to remove the MongoDB backup. The AWS CLI should work on your new remote MySQL server, assuming that you’d configured this for remote backups to AWS S3 on your original Open edX platform.