How to upgrade Amazon RDS MySQL from 5.7 to 8.0

I've been ignoring all of my Amazon AWS Emails about major/minor version upgrades for quite some time now (they always seem rather frivolous), but the email that did catch my eye was my monthly BILL (jumping 4x in costs in the last month or so). Turns out RDS Extended Support for old versions of MySQL costs $0.10 per hour ($75/month) for each end-of-life database. Now, I have not touched my AWS configurations for over 5 years, so it definitely took me some time to get reacquainted to all of the options and new UI in the console. My frustration is knowledge for the rest of y'all, so please enjoy my struggle notes.

Here's my step-by-step guide on how to successfully upgrade your instances and check your DB connections are still working.

  1. If you're like me and forgot where your DB credentials are located, I started off my trying to connect to it within the AWS RDS -> DB Instances -> [Select DB Name]. From here, I was able to find the connection details of Endpoint (Host), Port under Connectivity & security, and Username, Password under Configuration.
  2. I then took this information and attempted to connect using MySQLWorkbench by entering all of the Hostname, Port, Username, and Password and clicking "Test Connection". You should see a "Success" if everything is configured properly.

  3. Mine wasn't successful on the first try lol. Things I had to reconfigure on AWS: VPC Security Group (made sure my security group allowlisted my new public internet IP. for port 3306 on my Inbound connection rules. I also forgot my password for my username... whoops! Turns out you can reset your master password from RDS Dashboard when you enter (click) "Modify" -> Master password and Confirm master password. Apply changes immediately and wait for the DB Status to turn green again
  4. After I successfully verified my original connection, I made a manual backup of my database in MySQLWorkbench via Data Export into a Self-Container File.. just in case things went poorly.
  5. Next, I tried updating straight to 8.036 also via "Modify" -> DB engine version (please note, if this weren't my personal blog, I would have used an Amazon Blue Green Deployment to minimize Production downtime), but yet again, I ran into another dreaded AWS error:
  6. Current Option Group (default:mysql-5-7-db-randomhash-upgrade) is non-default. You need to explicitly specify a new Option Group in this case (default or custom)
  7. To solve this error above, I first changed the "Modify" -> Option group from "default:mysql-5-7-db-randomhash-upgrade" to default:mysql-5-7 and also unchecked
    Enable auto minor version upgrade, which created this invalid option group in the first place. Apply changes and wait for the Status to turn green.
  8. Finally, we're ready to do the final step of upgrading from 5.7 to 8.0 on step 5. This process took my around 15 minutes but may take longer for larger databases, so be prepared with a maintenance plan!!
  9. Once the upgrade is complete, your DB will reboot and Status will hopefully turn green again. Revalidate your connection from MySQLWorkbench or your preferred CLI command. It should all work as before!

Amazon indicates that you should stop getting charged for extended support as soon as you upgrade to 8.0, but double check your Billing $$$ after the upgrade to be sure. Hope that helped!