Upgrading MySQL 5.7 to MySQL 8.0 on Oracle Cloud Infrastructure – An Enterprise Guide

Upgrading from MySQL 5.7 to MySQL 8.0 offers many benefits, including improved performance, enhanced security, and new features.

With MySQL 5.7 End of Life on October 31, 2023, it’s time to act. Here’s an overview of the MySQL upgrade planning process if you have an on-premises or cloud MySQL 5.7 deployment that you want to migrate to Oracle Cloud Infrastructure (OCI) MySQL Database Service or MySQL HeatWave.

MySQL on Oracle Cloud Infrastructure

Oracle offers two Database-as-a-Service (DBaaS) solutions for MySQL:

MySQL Database Service: This is a fully-managed MySQL 8.0 database running Enterprise Edition with InnoDB as the storage engine and commercial features included. It streamlines the process of deploying, managing, and scaling MySQL databases on OCI. You can opt for Standalone, which is a single database system, or High Availability, which consists of 3 MySQL instances.

MySQL HeatWave: A cloud-native MySQL 8.0 with an in-memory query accelerator designed to improve analytics performance and ML capabilities, while still providing transactional workload support.

It’s worth noting that both of these cloud solutions are first-party, as Oracle owns MySQL.

Preparing for a MySQL 5.7 to 8.0 OCI Upgrade

Before upgrading from MySQL 5.7 to MySQL 8.0, it’s important to assess your current database deployments and application dependencies.

Avoid MySQL Upgrade Disasters

The most important thing to understand when planning your OCI MySQL 5.7 to MySQL 8.0 upgrade strategy is that there are no easy downgrades or rollbacks following the process. Downgrading from MySQL 8.0 to MySQL 5.7, or from a MySQL 8.x minor release to a previous MySQL 8.0 release, is not supported.

The ONLY supported method for rolling back the upgrade is by restoring a backup taken BEFORE you upgrade to MySQL 8.x. Backing up your data before starting the upgrade process is critical to avoiding major disruptions in case something goes wrong. You’ll also need to test your backups to ensure that they’re working properly.

Preparing for Your OCI Move

Perform a database health check to ensure that your MySQL 5.7 databases don’t have issues that could cause issues during an upgrade. During this assessment process, you’ll want to cover the following:

• Current environment details, including usage peaks and valleys, concurrent sessions, and index usage.
• High Availability needs, if you have a replication or clustering environment for rolling upgrades.
• The backup and fault tolerance strategy.
• Platform and hardware details.
• Information on supported applications.
• Database security measures.
• Any recent database incidents.
• Blockages, I/O contention, offending queries, long-running queries, and similar details.

Along with this evaluation, you’ll want to assess the applications running on the target MySQL 5.7 databases to determine if code changes and connector driver updates are necessary. For example, MySQL 8.0 has changed some DB Authentication protocols, such as making caching_sha2_password the default. Future versions of MySQL could also potentially remove native_mysql_auth.

Check for Compatibility

Use the MySQL upgrade checker utility to generate MySQL 5.7 compatibility reports. This utility will notify you if you’re currently using features that are deprecated or otherwise need to be reconfigured to work properly in MySQL 8.0. If you’re not already using InnoDB as your MySQL storage engine, you will need to switch to it.

Additionally, not all versions of MySQL 5.7 have a direct upgrade path to MySQL 8.0. Your databases need to be on MySQL 5.7.9 for this to be possible. If you’re using earlier versions of 5.7, you’ll need to upgrade to 5.7.9 prior to upgrading to 8.0.

Test, Test, and Test Some More

A failproof and thorough testing of the applications and databases for functionality and performance is a must. You don’t want to take your MySQL 8.0 databases into production, only to find out your applications are throwing errors and your databases are not responding.

You have several tools to assist MySQL 5.7 migrations into OCI MySQL Database Service or MySQL HeatWave, such as Oracle GoldenGate or MySQL Shell.

Give Yourself a Generous Upgrade Window

Check your maintenance window availability during MySQL 8.0 upgrade planning so you know how much time you have to work with, the amount of allowable disruption to daily business operations, and whether you need your database administrators to come in outside of their normal hours to perform the upgrade.

Give yourself as much time as possible to double-check for any issues as you move into the production environment, so you can quickly address any unexpected problems within your planned maintenance window.

Want to learn more about MySQL 5.7 end of life planning? Download our guide How to Plan a MySQL 5.7 to MySQL 8.0 Upgrade Before End of Life.