Upgrading a MySQL DB on AWS RDS

The database is considered as a heart of the application. Keeping their versions up to date is an inevitable requirement. Upgrading a Database itself is not bigger of a deal, but, planning the upgrade to have minimal impact on the application downtime is critical.

Before we get into the upgrade part, lets actually get us familiar with a few terminologies here.B

1. AWS RDS– Amazon service for Relational Database is called AWS RDS.
2. Master RDS – This is the RDS that is a target to get all the write operations from the application.
3. Read Replica – This RDS is a replica of the Master RDS. All the read operations from the application are performed on this.
4. Lag Time – This time indicates the read replica’s lag w.r.t the Master RDS.

Let’s now plan on upgrading our MySQL RDS from a 5.6 version to the 5.7 version. Let’s get started here 🙂

1. Take a snapshot of the rds before performing the upgrade. This snapshot is 5.6.33 compatible. If the upgrade fails, we can’t rollback rather we should spin up a rds with this snapshot.
2. There is a datetime format change from 5.6 to 5.7. Upgrade the database date and time on the table –
   You can use the following query to find all tables in your database that have columns of type datetime, time, or timestamp and to create an ALTER TABLE FORCE; command for each table:

SELECT DISTINCT CONCAT(‘ALTER TABLE ', REPLACE(is_tables.TABLE_SCHEMA, '‘, ‘'), '`.`', REPLACE(is_tables.TABLE_NAME, '`', '‘), ‘` FORCE;’)
FROM information_schema.TABLES is_tables
INNER JOIN information_schema.COLUMNS col ON col.TABLE_SCHEMA = is_tables.TABLE_SCHEMA
AND col.TABLE_NAME = is_tables.TABLE_NAME
LEFT OUTER JOIN information_schema.INNODB_SYS_TABLES systables ON
SUBSTRING_INDEX(systables.NAME, ‘#’, 1) = CONCAT(is_tables.TABLE_SCHEMA,’/’,is_tables.TABLE_NAME)
LEFT OUTER JOIN information_schema.INNODB_SYS_COLUMNS syscolumns ON
syscolumns.TABLE_ID = systables.TABLE_ID AND syscolumns.NAME = col.COLUMN_NAME
WHERE col.COLUMN_TYPE IN (‘time’,’timestamp’,’datetime’)
AND is_tables.TABLE_TYPE = ‘BASE TABLE’
AND is_tables.TABLE_SCHEMA NOT IN (‘mysql’,’information_schema’,’performance_schema’)
AND (is_tables.ENGINE = ‘InnoDB’ AND syscolumns.MTYPE = 6);

After this –

1. https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html -> Go through the changes in the in 5.7 MySQL
2. Check if the 5.6 parameter group for the DB is compatible with 5.7.
3. Now upgrade using the console -> https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.Upgrading.html#USER_UpgradeDBInstance.Upgrading.Manual
4. Run as many of your quality assurance tests against the upgraded DB instance as needed to ensure that your database and application work correctly with the new version. Implement any new tests needed to evaluate the impact of any compatibility issues. Test all stored procedures and functions. Direct test versions of your applications to the upgraded DB instance.

How about upgrading with minimal downtime –

1. As always create a snapshot. If anything goes wrong we can rollback.
2. Now, Create a read replica for the master rds.
   https://aws.amazon.com/rds/details/read-replicas/
3. Upgrade the read replica to 5.7.
4. Make sure the lag time on the read replica is zero, and perform all your tests on the read replica DB.
5. If everything seems good, promote the read replica as the new master.

You can always think of different ways to do a rds upgrade with minimal downtime. Always make sure you first test out the plan on a test rds and make sure you perform all tests to check if the upgrade was successful.