AWS RDS MySQL Upgrade Your Step-by-Step Path from 5.7 to 8.0 

Upgrading MySQL on AWS RDS can seem daunting, but with the right steps, the process can be smooth and manageable. The upgrade process involves creating backups, modifying parameter settings, running compatibility checks, altering table structures, and applying changes to ensure that the application works as expected on MySQL 8.0. In this post, we will outline the steps you need to follow, from the initial snapshot creation to testing and finalizing the upgrade.

Step 1: Create a New Snapshot and Restore It

Before making any changes to the production environment, it is critical to back up the existing database.

• Create a new snapshot from the production DB in the AWS Management Console.
• Restore the snapshot to a new DB instance with the same instance type and MySQL version (5.7).
• Once the DB is restored, modify the inbound rules of the DB instance to allow access to it.
• Connect to the new DB instance using MySQL Workbench.

Step 2: Modify the DB Instance and Apply Parameter Changes

Once the DB instance is restored, the next step is to update the parameter group.

• In the AWS console, go to the DB instance and click Modify.
• Change the parameter group to mysql5-7-to-upgrade.
• Apply changes immediately to ensure the new parameter group is used.
• Reboot the instance for the changes to take effect.

Step 3: Check the Compatibility Log

After modifying the DB instance, the next important step is to review the compatibility check log for MySQL 8.0. This log will highlight any potential issues that could affect the database’s functionality.

Some of the most common compatibility issues are:

• Deprecated or Removed Features: Certain features or syntax may no longer be supported in MySQL 8.0. The log will indicate which ones need attention.
• Incompatible Table Formats: Older table formats may need to be updated to DYNAMIC or COMPRESSED to ensure compatibility with MySQL 8.0.
• Trigger Issues: Triggers that rely on deprecated attributes may need to be recreated.
• Stored Procedure Issues: Some stored procedures may require adjustments, such as replacing unsupported SQL syntax (e.g., GROUP BY DESC).

Be sure to follow the steps outlined below to resolve these issues before proceeding with the upgrade

Step 4: Alter Table Row Format to DYNAMIC

A common task during the upgrade is modifying table row formats to DYNAMIC.

• Use the following query to check the row format for a specific table:

SHOW TABLE STATUS LIKE 'notificationList';

• For tables with millions of records, run the following query one by one for each table:

ALTER TABLE dbname.TableName1 ROW_FORMAT=DYNAMIC;
ALTER TABLE dbname.TableName2 ROW_FORMAT=DYNAMIC;

• For tables with fewer rows, you can run the ALTER command in bulk:

ALTER TABLE dbname.TableName3 ROW_FORMAT=DYNAMIC;
ALTER TABLE dbname.TableName4 ROW_FORMAT=DYNAMIC;
ALTER TABLE dbname.TableName5 ROW_FORMAT=DYNAMIC;
ALTER TABLE dbname.TableName6 ROW_FORMAT=DYNAMIC;

Step 5: Fix Trigger Errors

While reviewing the compatibility log, you might encounter errors related to triggers, which typically look like:

dbname.TableName1 - Trigger dbname.TableName1.TableName1_AFTER_UPDATE does not have CREATED attribute.
dbname.TableName2 - Trigger dbname.TableName2.TableName1_BEFORE_UPDATE does not have CREATED attribute.

To resolve these errors, you need to recreate the triggers to remove the error messages.

Step 6: Fix Procedure Errors

Similar to triggers, you might also see errors related to stored procedures, such as:

dbname.procedure_1 - PROCEDURE uses removed GROUP BY DESC syntax
dbname.procedure_2 - PROCEDURE uses removed GROUP BY DESC syntax

To resolve these errors, recreate the procedures to ensure compatibility with MySQL 8.0.

Step 7: Modify DB Instance to Upgrade MySQL Version

Once the compatibility issues have been resolved, the next step is to actually upgrade MySQL to version 8.0.

• Go to the Modify DB instance page for the new DB instance.
• Change the MySQL version to 8.0.36 or the latest available version.
• Apply the changes immediately.
• Wait for the upgrade process to complete.

Step 8: Connect to the DB and Perform Sanity Tests

After the upgrade, it’s crucial to verify that everything is working as expected.

• Connect to the DB using MySQL Workbench.
• Perform sanity tests to ensure that the database is functioning properly.
• Test the entire application to confirm that it works seamlessly with the upgraded MySQL version.
• Update the production server environment to reflect any necessary changes and verify that everything is operating smoothly.

Step 9: Finalize the Upgrade

Once you have verified that everything is working correctly, you can finalize the upgrade process.

• If necessary, stop the old database instance.
• Optionally, take a new snapshot of the old DB instance for backup purposes.
• The DB upgrade is now complete!

Conclusion

Upgrading MySQL on AWS RDS from version 5.7 to 8.0 is a comprehensive process that involves creating backups, modifying configurations, resolving compatibility issues, and verifying the application’s functionality. By following these steps, you can ensure a smooth transition to the latest MySQL version, improving both performance and security for your application.