Scaling MySQL

Why move MySQL?

MySQL contains:

• Student state data
• Users and Authentication
• Site Configurations
• Certificate Data
• eCommerce & Discovery data
• Much more. Anything not explicitly contained in Mongo is likely stored in MySQL.

When we scale up Open EdX and add multiple application servers, we must migrate our MySQL database to a shared location so that all application servers reference the same MySQL content.

Which databases should I move?

Open EdX installs several databases with a normal single-server installation. At a minimum, the following databases need to be moved:

• edxapp - The main EdX database
• edxapp_csmh - Stands for Courseware Student Module History. Contains the history of all student module/x-block states, and can get quite large.

Some databases must be moved if your Open EdX site uses those services

• ecommerce
• discovery
• analytics-api, dashboard, reports - For Insights
• xqueue - Unclear if this must be moved off the application server.

Choosing a MySQL Service

AWS, Azure, and Google Cloud all offer good managed MySQL services that can provide security and scaling for production installations. Things to consider when configuring your MySQL instance with your chosen cloud provider will be:

• MySQL Version
• Automated Backups
• Redundancy
• Encryption

Moving MySQL to remote

Note: Detailed MySQL Migration steps for AWS in 'Migrate MySQL' section.

1. Choose any relational database service to host the MySQL on a remote instance. The database services can be AWS RDS or Azure DB.
2. Select a compatible version of MySQL for a remote instance. The remote instance should be of the same version as of local MySQL. You can check the local version using the command mysql --version (for eg: 5.7.22).
3. Give the MySQL instance proper name, enter username (i.e master username) as admin and type a strong password for security reasons.
4. Choose the MySQL instance properties, backup plan and optimization objectives. Normally, these objectives can not be achieved using local MySQL.
5. After a MySQL instance is created, note down the hostname/end-point and credentials (i.e master username and its password). The hostname and credentials will be needed for either MySQL client or Open Edx instance to connect to the remote instance.
6. Create a dump of the local MySQL which includes edxapp and edxapp_csmh databases. You can use mysql dump command (i.e mysqldump) and use relevant users for creating dump (i.e edxapp001 for edxapp and edxapp_cmsh001 for edxapp_csmh). It will prompt for a password, which is defined in my-password.yml file (i.e EDXAPP_MYSQL_PASSWORD and EDXAPP_MYSQL_CSMH_PASSWORD).The edxapp database is used for storing user related data for both LMS and Studio. While edxapp_csmh used to record for each attempt that learners make to answer problem types.
7. Connect to the remote MySQL instance using mysql client to create edxapp and edxapp_csmh databases. Then restore each database with the dump created in step 6. For connecting to a remote MySQL instance, use the hostname and credentials created in step 5.
8. Use the same hostname and credentials to point your Open Edx instance to your remote MySQL instance. For this, open lms.auth.json and cms.auth.json and replace the HOST, USER and PASSWORD values. For instance:

    "DATABASES": {
        "default": {
            "ATOMIC_REQUESTS": true, 
            "CONN_MAX_AGE": 0, 
            "ENGINE": "django.db.backends.mysql", 
            "HOST": "demo-db.cdyyt8rxogtp.us-east-1.rds.amazonaws.com", 
            "NAME": "edxapp", 
            "OPTIONS": {}, 
            "PASSWORD": "V2dqr6JBHkhbTs2V", 
            "PORT": "3306", 
            "USER": "admin"
        }, 
        "read_replica": {
            "CONN_MAX_AGE": 0, 
            "ENGINE": "django.db.backends.mysql", 
            "HOST": "demo-db.cdyyt8rxogtp.us-east-1.rds.amazonaws.com", 
            "NAME": "edxapp", 
            "OPTIONS": {}, 
            "PASSWORD": "V2dqr6JBHkhbTs2V", 
            "PORT": "3306", 
            "USER": "admin"
        }, 
        "student_module_history": {
            "CONN_MAX_AGE": 0, 
            "ENGINE": "django.db.backends.mysql", 
            "HOST": "demo-db.cdyyt8rxogtp.us-east-1.rds.amazonaws.com", 
            "NAME": "edxapp_csmh", 
            "OPTIONS": {}, 
            "PASSWORD": "V2dqr6JBHkhbTs2Vn", 
            "PORT": "3306", 
            "USER": "admin"
        }
    }