It is also POSSIBLE to avoid any downtime during the upgrade.
The way to do it is by briefly launching a new RDS from a read replica snapshot and configure it as active/active Master to Master replication. Once it's configured, you can switch application traffic one APP server at the time without any downtime.
We use the approach every time AWS announces RDS maintenances to avoid downtime as well as during our scheduled maintenances.
Here are the details:
M1 - Orignal Master
R1 - Read Replica of the M1
SNAP1 - Snapshot of the R1
M2 - New Master
M2 creation sequence:
M1 → R1 → SNAP1 → M2
Since we can’t use SUPER privilege on RDS, we don’t use mysqldump with
— master_data2 option on the M1. Instead, we launch R1 to obtain the binlog position of the M1 from it. Then create a snapshot (SNAP1) from the R1 and then launch M2 from the SNAP1.
Create two separate RDS parameters groups with the followingt offsets to avoid PK conflcts:
M1: auto_increment_ increment = 4 and auto_increment_offset = 1
M2: auto_increment_ increment = 4 and auto_increment_offset = 2
Create replication user on M1
GRANT EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO
‘repl’@’%’ IDENTIFIED BY PASSWORD <secret>;
1. Create R1 from M1
-- Connect to the R1 and stop replication
-- Obtain M1’s (!!) current binlog file and position
`mysql> show slave status\G
2. Create SNAP1 from R1
4. Setup M/M replication
-- Configure M2 as a slave of M1
CALL mysql.rds_set_external_master ('m1.xyxy24.us-east-1.rds.amazonaws.com', 3306, 'repl', 'mypassword', 'mysql-bin.000622', 9135555, 0);
-- Connect to M2 and obtain its current binlog file and position
mysql> show master status\G
-- Connect to M1 and configure it to be a slave of the M2
CALL mysql.rds_set_external_master ('m2.xyxy24.us-east-1.rds.amazonaws.com', 3306 , 'repl', 'mypassword', 'mysql-bin.004444', 6666622, 0);
5. Delete R1 and SNAP1 as they’re no longer needed
6. Upgrade M2 via AWS Console
Use the standard procedure to Modify the Instance as per your needs.
7. Perform Graceful Switchover to M2
As M/M replication is set up successfully, we are ready to proceed with DB maintenance without downtime by gracefully switching App servers one at the time.
Here are more details on how it works.