Migrating MySQL Database off of Single-Server

These instructions will help you move the MySQL Database off of a standard single-server installation onto an RDS instance.

1. Go to Amazon RDS service and create database there

2. Choose MySQL

3. Choose your edition and version

Edition = MySQL Community Version

Version = MySQL 5.7.22

From the Templates, choose accordingly (for Production, select Production):

4. Give your DB Instance Identifier to the name of your choice.

We have given demo-db for demonstration purpose.

Keep the Master username as admin or choose other username. We have chosen admin in our case.

Select the Master Password (Strong Password).

Input same in Confirm Password.

5. Choose the Defaults:

6. Choose the Defaults:

7. Choose the Defaults:

8. Review the Additional configuration.

Here, you can enable and disable the encryption as well.

DB configuration can be modified afterwards as well. Also, review the cost estimate and then click on "Create database".

9. Creation of database will take few minutes.

10. Take a backup of the edxapp database on the application server

SSH into the server where your edX is deployed.

Execute the following command to take dump of the edxapp database:

sudo mysqldump -u edxapp001 -p edxapp > edxapp.sql

It will ask for the password which you can see under the lms.auth.json file for db edxapp and user edxapp001:

Give the password as per configuration found in above.

11. Take a backup of the edxapp_cmsh database on the application server

Execute the following command to take dump of the edxapp_csmh database:

sudo mysqldump -u edxapp_cmsh001 -p edxapp_csmh > edxapp_csmh.sql

12. Check that the backups are created successfully.

13. Now, create the new databases on the RDS

Issue the following command

sudo mysql -h demo-db.cdyyt8rxogtp.us-east-1.rds.amazonaws.com -P 3306 -u admin -p -e "create database edxapp";

Give the password that you set while creating the RDS on AWS:

Then, restore the db by issuing the following command:

sudo mysql -h demo-db.cdyyt8rxogtp.us-east-1.rds.amazonaws.com -P 3306 -u admin -p edxapp < edxapp.sql

Create db edxapp_csmh on RDS:

sudo mysql -h demo-db.cdyyt8rxogtp.us-east-1.rds.amazonaws.com -P 3306 -u admin -p -e "create database edxapp_csmh";

Restore db edxapp_csmh on RDS:

sudo mysql -h demo-db.cdyyt8rxogtp.us-east-1.rds.amazonaws.com -P 3306 -u admin -p edxapp_csmh < edxapp_csmh.sql

14. To confirm the changes, connect to the remote RDS Instance

Issue the following command:

sudo mysql -h demo-db.cdyyt8rxogtp.us-east-1.rds.amazonaws.com -P 3306 -u admin -p

15. Point edX MySQL to RDS end-points:

Issue the following commands to be able to update the lms.auth.json and cms.auth.json:

sudo -H -u edxapp bash
source /edx/app/edxapp/edxapp_env
cd

Open with nano editor or any other of your choice:

nano lms.auth.json
CTRL+W (Search) 3306

Set the following for all the entries of 3306 (MySQL Db):

HOST: demo-db.cdyyt8rxogtp.us-east-1.rds.amazonaws.com (Your RDS End-point)
PASSWORD: V2dqr6JBHkhbTs2V (Your RDS password)
USER: admin (Your RDS Master Username)

After making the changes in the file save (CTRL+O)

Repeat the same for cms.auth.json

Then, press CTRL+D and restart LMS and CMS by issuing the following command:

sudo /edx/bin/supervisorctl restart lms cms

See below screenshot for reference:

16. Test application, it will be working.