AWS’ Relational Database Service (RDS) is a superior alternative to running MySQL locally on your Open edX platform. This simple how-to guide shows you how to migrate the service.

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.

I. Snapshot Your Open edX Server

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.

II. Backup Your Existing Open edX MySQL Databases

IMPORTANT! Configure Your MySQL User Accounts To Allow Remote MySQL Connections

Prior to backing up your databases you first need to reconfigure the MySQL user accounts to allow remote connections. Otherwise, you’ll be unable to login to your new AWS RDS instance after migrating your databases. To allow your Open edX instance to be able to connect to the new RDS MySQL service remotely via port 3306 you’ll need to enable remote connections on the local mysql service on your Open edX server. The native installation limits the scope/visibility of all MySQL users to ‘localhost’, and so you’ll need to update the database table mysql.user to expand the allowed hosts from ‘localhosts’ to ‘%’ (which means ‘any’ host).

# Note: beginning with Koa, the root mysql password is preset
# and as of this writing i have not located where this password is set. As a workaround,
# you can alternatively use the command 
#            mysql -u admin -p
# where the password value for 'admin' is stored in /home/ubuntu/my-passwords.yml
# under the yml key tag, "COMMON_MYSQL_ADMIN_PASS" on or around row 22.
mysql -u root -p
mysql> UPDATE mysql.user SET host = '%';
mysql> FLUSH PRIVILEGES;

# NOTE: (February 2021) I had an isolated case where I lost all of the 
# user-level permissions after running this UPDATE query. If this happens 
# then you have two choices: 
# a.) use the mysql command line in your original EC2 instance to generate
#     a script of the user-level permissions, or
# b.) start the entire process from the beginning, and then use a tool like
#     MySQL Workbench (https://www.mysql.com/products/workbench/) to
#     manually update the host setting for each user.
# results should look like the following:
mysql> select host, user from mysql.user;
+------+------------------+
| host | user             |
+------+------------------+
| %    | admin            |
| %    | api001           |
| %    | debian-sys-maint |
| %    | discov001        |
| %    | ecomm001         |
| %    | edxapp001        |
| %    | edxapp_cmsh001   |
| %    | migrate          |
| %    | read_only        |
| %    | reports001       |
| %    | root             |
| %    | rosencrantz      |
| %    | xqueue001        |
+------+------------------+
13 rows in set (0.01 sec)
mysql> exit

More detailed instructions are available here, “MySQL: How to allow remote connection to mysql

Create The Backup File

My blog article, “Open edX Complete Backup Solution“, explains most of what you need. Additionally however, you’ll need to include the “my