How to upgrade Mysql 5 to MySQL 8

Upgrading MySQL 5 to MySQL 8 is a major version upgrade that requires careful planning and execution. Here's a general process to follow:

1. Preparation:

• Thoroughly test the upgrade in a non-production environment first.
• Check for incompatibilities between your current version and MySQL 8.0.
• Ensure there are no obsolete data types, functions, or orphan files.
• Verify that triggers have valid definers and creation contexts.
• Check for any keyword or reserved word violations that may have changed in MySQL 8.0.
• Review and update any custom SQL modes that might be obsolete.

2. Backup:

• Create a full backup of your existing MySQL 5 database.

3. Upgrade process: For Amazon RDS:

• Use the AWS Management Console, AWS CLI, or RDS API to initiate the upgrade.
• Choose MySQL 8.0 as the target version.
• Schedule the upgrade during a maintenance window or initiate it immediately.

For self-managed databases:

• Install MySQL 8.0 on your target system.
• Use mysqldump to export data from your MySQL 5 database.
• Import the data into the new MySQL 8.0 database.

4. Post-upgrade:

• Verify that all data has been successfully migrated.
• Test your applications thoroughly with the new MySQL 8.0 database.
• Update any application code that may be affected by changes in MySQL 8.0.

5. Finalize:

• Once testing is complete and successful, update your production environment.
• Monitor the upgraded database closely for any issues.

Remember that the upgrade process may cause downtime, so plan accordingly. Also, be aware that MySQL 8.0 includes significant changes that may affect your existing applications, so thorough testing is crucial before applying the upgrade to your production environment.
Sources
Migrate data from a MySQL 5.6 database to a newer version in Lightsail - Amazon Lightsail
Major version upgrades for RDS for MySQL - Amazon Relational Database Service
Upgrading a DB instance engine version - Amazon Relational Database Service