Upgrading to RDS MySQL 8.0 with minimum downtime

We have been using RDS MySQL 5.7 at Halodoc extensively since the past 5 years. Since we constantly look at upgrading our tech stack, we evaluated migrating to MySQL 8.0 because of the significant benefits it provides, some of which are listed below.

1.Security: We can create roles which are named collections of privileges, for granting and denying permissions to groups of users.

2.Indexing : Function based index is the most wanted feature in MySQL8 now date(start_date) type index can be created and invisible indexes are available.

3.Manageability: Adding a new column instantly in a table without locking is very helpful in schema modification.

4.JSON:Functions for converting JSON to relational tables, merging JSON objects, and aggregation.

5.Performance: 2x higher performance versus MySQL 5.7, InnoDB performance optimisation for read-write and IO-bound replication performance improvements.

6.Character set: utf8mb4 as the default character set

7.CTE Support : Window Functions and Common Table Expressions (WITH queries) to reduce query complexity.

Major version upgrades can contain database changes that are not backward-compatible with existing applications. As a result, Amazon RDS doesn't apply major version upgrades automatically, we must manually modify our DB instance.

Steps for migration:

Based on our extensive research and analysis from various documentation online regarding MySQL 8 upgrade, we arrived at the following steps to perform an upgrade to our RDS servers in order to achieve minimal downtime (about 30 seconds) for each of the RDS upgrades.

Firstly, it is  recommended that you thoroughly test any upgrade before applying it to production instances. Hence we have upgraded our stage RDS to MySQL 8, so developers can test all existing use cases and can make any changes if necessary. This step was also helpful in determining any code level incompatibility issues.

When we start upgrading from MySQL 5.7 to 8, Amazon RDS runs pre-checks automatically to detect any incompatibilities. If there are incompatibilities, Amazon RDS prevents the upgrade and provides a log with the details. We can use the log to prepare our database for the upgrade to MySQL 8 by eliminating the incompatibilities.

Some of the incompatibilities checks we need to look into are:

• Views must not have a missing or empty definer or an invalid creation context.
• java.sql.SQLException: Unknown system variable 'query_cache_size'  we get this error as query_cache_size is removed in MySQL 8 to fix this upgrade mysql connector to the latest 8.x version.
• Usage of old temporal type.
• Usage of db objects with names conflicting with new reserved keywords.
• Foreign key constraint names longer than 64 characters.
• ENUM/SET column definitions containing elements longer than 255 characters.
• Tables recognised by InnoDB that belong to a different engine.
• Tables with dangling FULLTEXT index reference.
• The definer column for mysql.events cannot be null or blank.
• Usage of partitioned tables in shared tablespaces.

After the pre-checks steps there are two ways to upgrade Amazon RDS instances.

1. Direct upgrade which will involve a downtime of 10 to 15 minutes. Some upgrades might take longer because of the DB instance class size.

2. External replication based upgrade with minimal downtime of 30 seconds.

Since we wanted to have minimal downtime we chose to go with option 2 above.

Upgrade via the External Replication route

The following steps were followed to achieve upgrade via external replication.

Step 1 :  Created a new DB parameter group with our existing settings that is compatible with the new major version.

Step 2: On Master RDS perform the steps 2.a and 2.b below.

a.  Check binlog rotation time, increase to 24 hours minimum in Prod

call mysql.rds_show_configuration();

call mysql.rds_set_configuration('binlog retention hours', 24);

b. Create a user to set up replication to an external RDS in the new RDS

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'REPL_USER'@'%' IDENTIFIED BY 'password';

Step 3. Create a new replica of Master RDS and once there is no lag, stop replication between Master and Replica RDS and make note of  the Binlog positions in the replica.

a.Take a snapshot of that new replica

b. Restore new RDS from snapshot and once the RDS is up, upgrade RDS MYSQL 8

Step 4. Create external replication between Master and new MySQL 8 RDS by following the steps below for creating replication.

a. Setup replication using binlog points (from step 3)

CALL mysql.rds_set_external_master('RDS endpoint', port,'replication user','replication password','binlog file', 'binlog position', 0);

CALL mysql.rds_start_replication;

b. Validate if there is no replica Lag between the master and new RDS

show slave status\G;

Step 5. Create a replica of your new RDS if required, make sure replica lag is 0.

Step 6. Once everything is confirmed during cutover follow the steps below

a. Make your current master as read_only = 1 , to avoid new writes

Approach 1:  Update the end points in route 53 to point to the new RDS Master

Approach 2: If there is no DNS endpoint, update the new MySQL 8 RDS endpoints in your application configuration so that it starts fetching data from new MySQL 8 RDS instance.

Step 7. CALL mysql.rds_reset_external_master reset on new RDS after activity.

Summary

Also as a good practice, we should always ensure to keep our RDS instances system upto date in order to get multiple benefits like new features, enhanced security management, enhanced performance.

Based on our observations with running RDS MySQL 8 in our production environment in the last 3 months, we have not observed any issues post upgrade and we are utilising the benefits of MySQL 8.

Interested in exploring with us ?

We are always looking out for top engineering talent across all roles for our tech team. If challenging problems that drive big impact enthral you, do reach out to us at careers.india@halodoc.com

About Halodoc

Halodoc is the number 1 all around Healthcare application in Indonesia. Our mission is to simplify and bring quality healthcare across Indonesia, from Sabang to Merauke. We connect 20,000+ doctors with patients in need through our Tele-consultation service. We partner with 3500+ pharmacies in 100+ cities to bring medicine to your doorstep. We've also partnered with Indonesia's largest lab provider to provide lab home services, and to top it off we have recently launched a premium appointment service that partners with 500+ hospitals that allow patients to book a doctor appointment inside our application. We are extremely fortunate to be trusted by our investors, such as the Bill & Melinda Gates Foundation, Singtel, UOB Ventures, Allianz, GoJek, Astra, Temasek and many more. We recently closed our Series C round and In total have raised around USD$180 million for our mission. Our team works tirelessly to make sure that we create the best healthcare solution personalised for all of our patient's needs, and are continuously on a path to simplify healthcare for Indonesia.

References

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.MySQL.html

https://aws.amazon.com/premiumsupport/knowledge-center/rds-required-maintenance/