BUILDSTR's Cheat Sheet for MySQL Upgrade

Part 1 of 3: Upgrade Amazon RDS MySQL 5.7 to 8.0

Problem Statement

So, it turns out that when Standard Support for Amazon RDS MySQL 5.7 ended February of this year (2024), organizations still using the 5.7 Engine were automatically enrolled in Extended Support. Amazon RDS Extended Support is a paid offering (pricing can be found here: RDS for MySQL documentation ), and this option cannot be unselected. There are many customers “in the wild” (running MySQL 5.7 on their own hardware or a virtual machine e.g., an Amazon EC2 instance) that can continue to run that specific MySQL engine in an unsupported state, which means they carry the burden of patching and most importantly, fixing critical security issues. Therefore, it is important that the benefits of AWS customers getting support for an unsupported MySQL engine not go unmentioned.   

Amazon RDS Extended Support allows you to continue using MySQL 5.7 after the community end-of-life. Don’t get it twisted, this is not a solution per se, this is a band-aid that buys you time. By providing Extended Support, AWS provides the fixes for critical security issues and bugs through patch releases. In turn, AWS is providing you with the opportunity to upgrade to a new major version of the MySQL engine, in order to help you meet your business requirements with the right solution. 

It should also be noted that Aurora MySQL version 2 reached end of standard support as of October, 31 2024. We will address our recommendations for upgrading to version 3 in an upcoming post. The remainder of this post is dedicated to sharing the secrets of our strategy for upgrading your Amazon RDS MySQL 5.7 to 8.0.  

MySQL major version Community release date RDS release date Community end of life date RDS end of standard support date RDS start of Extended Support year 1 pricing date RDS start of Extended Support year 3 pricing date RDS end of Extended Support date MySQL 5.7* 21 October 2015 22 February 2016 October 2023 29 February 2024 1 March 2024 1 March 2026 28 February 2027 

Summary, Process, & Important Points 

There are 2 primary ways to make this upgrade:  Option 1) Blue/Green Deployments (AWS Documentation) Option 2) In-Place Upgrade (AWS Documentation). Option 1 is the AWS recommended approach that allows for minimal downtime, a fast rollback strategy, and allows testing/validation from anything connected to the database before switching over. Option 2 has additional technical details to be aware of and has a minimum of 4 hours downtime. We typically recommend customers to pursue Option 1, and therefore this document focuses on that approach. That said, when customers have technical requirements or prefer to use Option 2 we are happy to guide them through and implement that process for them.   

As you read through our cheat sheet, you will see that every upgrade begins with a set of pre-checks. Then we walk through the Blue/Green Deployment process. We want to caution everyone that as you go through this you will need to do a compatibility check at two stages along the way. At first, you will need to make sure that you have identified and mitigated any upgrade compatibility issues (e.g., table partitions that reside in shared InnoDB tablespaces, stored procedure refactoring, primary and foreign key constraints, etc., and then after your upgrade is complete you will need to check that your application(s) or downstream services do not have any compatibility issues with MySQL 8.0.  

Enjoy what our team put together and reach out to us (emeyer@buildstr.com) with any questions about this process or to find out how we can help you upgrade.    

Pre-checks 

• There must be no tables that use obsolete data types or functions. 

• There must be no orphan *.frm files.

• Triggers must not have a missing or empty definer or an invalid creation context. 

• There must be no partitioned table that uses a storage engine that does not have native partitioning support. 

• There must be no keyword or reserved word violations. Some keywords might be reserved in MySQL 8.0 that were not reserved previously. For more information, see Keywords and reserved words in the MySQL documentation. 

• There must be no tables in the MySQL 5.7 mysql system database that have the same name as a table used by the MySQL 8.0 data dictionary. 

• There must be no obsolete SQL modes defined in your sql_mode system variable setting. 

• There must be no tables or stored procedures with individual ENUM or SET column elements that exceed 255 characters or 1020 bytes in length. 

• Before upgrading to MySQL 8.0.13 or higher, there must be no table partitions that reside in shared InnoDB tablespaces. 

• There must be no queries and stored program definitions from MySQL 8.0.12 or lower that use ASC or DESC qualifiers for GROUP BY clauses. 

• Your MySQL 5.7 installation must not use features that are not supported in MySQL 8.0.For more information, see Features removed in MySQL 8.0 in the MySQL documentation. 

• There must be no foreign key constraint names longer than 64 characters. 

• For improved Unicode support, consider converting objects that use the utf8mb3 charset to use the utf8mb4 charset. The utf8mb3 character set is deprecated. Also, consider using utf8mb4 for character set references instead of utf8, because currently utf8 is an alias for the utf8mb3 charset.  

Blue/Green Deployment 

Pre-Requisites 

1. Blue/Green deployments for MySQL need to have automated Backups enabled 

   1. In most cases, this should be already enabled. To check via command line, run the following command: 
       
      aws rds describe-db-instance-automated-backups --db-instance-identifier <<db instance identifer/name >>  
       
      It should return the following JSON response: 
       
      aws rds describe-db-instance-automated-backups --db-instance-identifier upgradetest 
       
      { 
      "DBInstanceAutomatedBackups": [ 
      { 
      "DBInstanceArn": "arn:aws:rds:us-east-1:***************:db:upgradetest", 
      "DbiResourceId": "db-T64IMR7D7FSSRX5PK45KSPU4AQ", 
      "Region": "us-east-1", 
      "DBInstanceIdentifier": "upgradetest", 
      "RestoreWindow": { 
      "EarliestTime": "2024-10-18T14:14:37.628000+00:00", 
      "LatestTime": "2024-10-18T14:40:00+00:00" 
      }, 
      "AllocatedStorage": 20, 
      "Status": "active", 
      "Port": 3306, 
      "AvailabilityZone": "us-east-1b", 
      "VpcId": "vpc-0b3d52f51002b36be*******************", 
      "InstanceCreateTime": "2024-10-18T14:12:20+00:00", 
      "MasterUsername": "********buildstr", 
      "Engine": "mysql", 
      "EngineVersion": "5.7.44", 
      "LicenseModel": "general-public-license", 
      "Iops": 3000, 
      "OptionGroupName": "default:mysql-5-7", 
      "Encrypted": true, 
      "StorageType": "gp3", 
      "KmsKeyId": "arn:aws:kms:us-east-1:******************:key/7***************", 
      "IAMDatabaseAuthenticationEnabled": false, 
      "BackupRetentionPeriod": 7, 
      "DBInstanceAutomatedBackupsArn": "arn:aws:rds:us-east-1:************:auto-backup:ab-t64imr7d7fssrx5pk45kspu4aqtt5qnedkoe3ty", 
      "BackupTarget": "region", 
      "StorageThroughput": 125 
      } 

   2. If no response is returned for the selected database then automated backups are not enabled, and the following steps must be taken to enabled automated backups 

      
      

      Creating the Deployment 

      1. Create the Blue/Green Deployment Configuration 

         1. Creating a blue/green deployment 

            1. The above documentation includes creating both from the CLI and the Console. From the Console, select the Database you are performing the upgrade on 

            2. Then select Create Blue/Green Deployment 

            3. Select the Database Engine, that you will be upgrading to. we’ll be upgrading to the latest (8.0.39) 

            4. Also, enable optimized writes if desired for high-throughput databases  

            5. It will generate an estimated monthly cost for this Green (Staging Database) 

               1. Also, Notice the warning that IAM Policies are not copied over with this tool 

                  b. Viewing a blue/green deployment  

                  c. You will see that your blue/green deployment has been created. It will denote which database is in which state and have temporary names 

      2. Copy over IAM Roles/Polices to the Green Deployment 

         1. This will vary from customer to customer. This can be done via CloudFormation, Terraform, Console, Etc. 

         2. While this staging environment has been created. The Green deployment will have a unique endpoint that can be used for validating applications. Note. The switchover will update green deployment with the original endpoint name.  

      3. Switching a blue/green deployment 

         1. After selecting maintenance window and the best time for switch over 

         2. Specify the switch over timeout to 1 hour 

         3. There are some guardrails in place for switchover guardrails: 

            1. Replication Health 

            2. Replication lag 

            3. Active Writes 

            4. External Replication 

            5. Long-Running Active Writes 

            6. Long-Running DDL Statements 

            7. Unsupported PostgreSQL Changes 

               d. Confirming the switchover performs the following actions: 

               1. Runs guardrail checks to verify if the blue and green environments are ready for switchover. 

               2. Stops new write operations on the primary DB instance in both environments. 

               3. Drops connections to the DB instances in both environments and doesn't allow new connections. 

               4. Waits for replication to catch up in the green environment so that the green environment is in sync with the blue environment. 

               5. Renames the DB instances in the both environments. RDS renames the DB instances in the green environment to match the corresponding DB instances in the blue environment. For example, assume the name of a DB instance in the blue environment is mydb. Also assume the name of the corresponding DB instance in the green environment is mydb-green-abc123. During switchover, the name of the DB instance in the green environment is changed to mydb. RDS renames the DB instances in the blue environment by appending -oldn to the current name, where n is a number. For example, assume the name of a DB instance in the blue environment is mydb. After switchover, the DB instance name might be mydb-old1. RDS also renames the endpoints in the green environment to match the corresponding endpoints in the blue environment so that application changes aren't required. 

               6. Allows connections to databases in both environments. 

               7. Allows write operations on the primary DB instance in the new production environment. After switchover, the previous production primary DB instance only allows read operations until you set the read_only parameter to 0 and reboot the DB instance.  

      4. Validate Applications, Connections, Etc. 

         1. Have teams validate their applications 

         2. Check logs for database and applications 

         3. Check Cloudwatch Metrics and any other observability dashboards

      5. Clean-up 

         1. The final step will be to delete the Blue/Green Deployment 

         2. Deleting a blue/green deployment 

         3. After waiting for the agreed upon time with customer for the burn-in/transition time for the new database. You can power off the database instance, take a final backup of the old database, and then delete the database. 

            1. Clean-up of any Infrastructure as Code will also need to take place post-migration 

      6. CONGRATULATIONS!! You’ve completed an upgrade of MySQL from 5.7 to 8. Repeat this for as many databases as required.

         
         

         Documentation Glossary 

         1. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.MySQL.html 

         2. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments-creating.html#blue-green-deployments-creating-preparing-mysql 

         3. https://www.postgresql.org/docs/current/logical-replication-config.html 

         4. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithAutomatedBackups.Enabling.html

         5. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.Modifying.html 

         6. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithDBInstanceParamGroups.html 

         7. https://docs.aws.amazon.com/prescriptive-guidance/latest/tuning-postgresql-parameters/replication-parameters.html