When it comes to performance and security upgrading your MySQL DB is a must. MySQL 5.7 reached community end of life in October 2023. Even though AWS is still offering RDS extended support on version 5.7.44, upgrading your database is still highly recommended.

When upgrading a MySQL database from version 5.7.x to 8.0.x, you must update the character sets of your tables and columns from utf8mb3 to utf8mb4.

Steps to Upgrade

1. Backup Your Database

Ensure you create a backup of your database before starting the upgrade process.

2. Update Tables from character set utf8mb3 to utf8mb4

Run the Tables Command

If using MySQL Workbench, you may need to adjust the row limit to display all rows before executing your commands.

Use the following SQL query to generate the necessary ALTER TABLE commands:

SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.tables
WHERE table_schema = 'YourDBName'
AND table_collation LIKE 'utf8%'
AND table_collation <> 'utf8mb4_unicode_ci';

Copy the generated commands to a new SQL tab and execute them. You may need to run these commands multiple times due to potential database connection issues.

3. Update Columns character set from utf8mb3 to utf8mb4

Run the Columns Command

Use the following SQL query to generate the necessary ALTER COLUMN commands:

SELECT CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ', column_type, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.columns
WHERE table_schema = 'YourDBName'
AND character_set_name LIKE 'utf8%'
AND collation_name = 'utf8_general_ci';

Copy the generated commands to a new SQL tab and execute them. You may need to run these commands multiple times due to potential database connection issues.

4. Run the Database Upgrade

The upgrade process may take about 20–30 minutes, depending on the size of your database.

Handling Zero Date, Datetime, and Timestamp Values

1. Create a Parameter Group

In the navigation pane, choose Parameter groups.

• If you already have a custom parameter group, note its name. If not, create a new one.
• Choose Create parameter group.
• Give your parameter group a name and description.
• Engine Type: MySQL Community Edition
• In the Parameter group family list, select the DB engine and version (mysql8.0).
• Click on Create.

2. Modify the Parameter Group

In the Parameter Groups list, select the parameter group you just created.

• Choose Edit.
• Search for sql_mode.
• Modify the sql_mode parameter to remove NO_ZERO_IN_DATE and NO_ZERO_DATE from the current value.
• Click Save Changes.

3. Apply the Parameter Group to Your RDS Instance

• In the navigation pane, choose Databases.
• Select your RDS DB instance.
• Choose Modify.
• In the DB parameter group section, select the custom parameter group you created.
• Review the changes and choose Continue.
• Apply the changes immediately or during the next maintenance window (applying immediately will cause a brief downtime).

4. Reboot the RDS Instance

For the changes to take effect, you need to reboot the RDS instance:

• In the Databases section, select your DB instance.
• Choose Actions, and then choose Reboot.

This process will help in performing a clean and efficient upgrade of your AWS RDS MySQL database. Upgrading your MySQL database version 5.7 to 8.0 offers several performance benefits and more importantly security enhancements.