Document toolboxDocument toolbox

Upgrade AWS Aurora MySQL Cluster

Owned by Dwayne Jeng (Unlicensed)
Last updated: Feb 14, 2023

  1. Ensure that your database cluster has the binlog_format set to ROW. To do this, go to the RDS console in AWS and click on your database cluster to open the details page. In the Configuration tab, DB Cluster Parameter Group and click on the name to view the Parameter Group. Search for binlog_format and verify that it has value ROW.

  2. If the value isn’t ROW, edit the DB Cluster Parameter Group (or create a new one, if it’s the default group) and set the binlog_format to ROW. Edit the database configuration to use your new DB Cluster Parameter Group. To make the settings take effect, you will need to reboot the databases. The fastest way to do so is to go back to the database cluster details page, select the Writer instance, click the Actions dropdown, and click Failover. This will sync the settings to both instances in the cluster. This should only take about a minute.

  3. Log into your database cluster and run CALL mysql.rds_show_configuration; If the value for binlog retention hours is less than 24 (or is null), run CALL mysql.rds_set_configuration('binlog retention hours', 24); This ensures that your binlogs are retained long enough to be replicated to all replicas.

  4. Make sure there is a DB Cluster Parameter Group of the target MySQL version that also has the binlog_format set to ROW. If it doesn’t exist, create one.

  5. In your database, make some table changes to ensure the binlog is flowing. For example:

    Create database testdb1; use testdb1; CREATE TABLE `big` ( `A` int(11) primary key, `B` int(11) DEFAULT NULL, UNIQUE KEY `big_idx` (`B`) USING BTREE, KEY `COL_A_B` (`A`,`B`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=latin1; delimiter $$ CREATE PROCEDURE myInsert() BEGIN DECLARE i INT DEFAULT 1; WHILE (i<=5000) DO INSERT INTO testdb1.big VALUES(i,i); SET i=i+1; commit; END WHILE; END$$ DELIMITER ; call myInsert;

  6. Wait about 10 minutes for changes to propagate.

  7. Back on the database cluster details page, click the Actions dropdown, and click Create Blue/Green Deployment.

  8. Under Blue/Green Deployment Settings, fill in a meaningful but unique ID, such as “bridge-uat-2022-01-20”. Select the MySQL version you want to upgrade to and select the DB Cluster Parameter Group with the binlog_format=ROW. (The DB Instance Parameter Group doesn’t matter.) Click Create Staging Environment. This next step can take anywhere from 45 to 80 minutes.

  9. In the database clusters list in RDS, click on your blue/green deployment. Click the Actions dropdown, and click Switch Over. Verify the details and click Switch Over. This switches your green deployment for your blue one, effectively upgrading your database. This takes up to 4 minutes. Note that your database will be unavailable during this time.

  10. In the database clusters list in RDS, delete the Blue/Green deployment and the cluster labeled “old”. (You’ll need to delete the instances before the cluster. You might need to disable delete protection first.) might Deleting the deployment is pretty quick. Deleting the old cluster takes 10-20 minutes.