Modify the version of the instance will bring the downtime but it works for small instances. I Like to follow the right stapes to get the upgrade done with Minimum/Zero downtime.
Best, Solution to get the MySQL version upgrade in Amazon Web Services RDS is to create a Read replica and upgrade it multiple version until you got your right version. this process will keep you existing instance up and running while you working on an upgrade. isn’t it cool?
Let’s see what those steps are:
- Select RDS instance and create a read replica with Same VPC/subnet group, parameter group, security group etc.
- Select the Read Replica and Modify the version to 5.6.x. This time you cannot select Automatic backup or Multi-AZ. ( this can be done later). Keep performing this until you behind 1 version to your final required version of MySQL
- Create a custom parameter group of type MySQL Community 5.7 or one which is your final version, Use this custom group at last upgrade you give to your Read Replica i.e. MySQL Community 5.7.
- The final version you are modifying: Once Read Replica status is “Available”, select it again and Modify to Version 5.7.x. Along with selecting the Multi-AZ option as of the original Instance and Backup retention to a non-zero value ( Selecting in these steps will change the time required later for promoting Read Replica considerably).
- Once Read Replica status is “Available”. Verify the version as 5.7.x or your required last version. Check the Replica Lag in RDS console. This should be zero to proceed with next steps.
- Make the Primary RDS instance as read-only by setting parameter “read_only” to “1” in the parameter group associated with Primary RDS instance, if you are storing the Session in the DB then your site gets down by this step.
- Promote Read replica so that it will become a Writable.
- Change the Endpoint in your application and test whether this new upgrade is working with your application code?
- Modify (Rename DB instance identifier) the Original Primary to any arbitrary name. Or change the End Point in the Application
- Modify (Rename DB instance identifier) the Original Replica to the name of primary RDS instance.
- Take the RDS snapshot of the New version RDS Instance.
- take the RDS snapshot of the old version RDS Instance.
- Shutdown old version RDS instance.
Declaimer: You can implement this steps for your stage environment, Make sure you app is working with new upgraded DB then upgrade your production server along with your code if required.