About SRGTechnical articleRecruitment Information
About SRGTechnical articleRecruitment Information
⚠️⚠️⚠️ STG SRG Portal ⚠️⚠️⚠️/Technical article/Upgrading from MySQL 5.7 to 8.0 with rollback considerations

Upgrading from MySQL 5.7 to 8.0 with rollback considerations

This is Onikai (@fat47) from the Service Reliability Group (SRG) of the Technology Division.
#SRGThe Service Reliability Group primarily provides comprehensive support for the infrastructure surrounding our media services, focusing on improving existing services, launching new ones, and contributing to open-source software (OSS).
This article is located within SRG.DBWGThe DB Working Group is releasing database-related materials that it provides to the entire company.
I hope this is of some help.
 
 

overview

This is an in-house training program.Presentation at the SRG StudyThis is a guide specifically focused on the upgrade portion, based on the previous document.
This document describes the procedure for upgrading MySQL 5.7 to MySQL 8.0 and setting up an environment that allows for a rollback to 5.7 in case of any issues.
 

Regarding the MySQL 8.0 version

The development policy has changed significantly, and new features have been added even in the minor version 8.0.
Therefore, even minor version upgrades require careful verification.

Which version of 8.0 should I use?

💡
Not the latest minor version8.0.28Consider using
8.0.29The addition of the INSTANT DROP COLUMN feature has significantly altered the default behavior of ALTER TABLE. Consequently, numerous bug reports have been filed regarding this feature.
Currently, version 8.0.29 is no longer officially supported, and the use of version 8.0.30 or later is officially recommended, although not all bugs have been fixed.
ALGORITHM=INPLACE/COPY;
Furthermore, if a table has been instant add/dropped even once, Percona Xtrabackup cannot take a backup.
In that case, you will need to either execute OPTIME TABLE on the target table or update the table data by executing ALTER TABLE ALGORITHM=COPY;.

What is INSTANT DDL?

This feature allows you to complete updates by updating metadata only when making additions or other operations involving ALTER TABLE.
It's extremely fast, but there are limitations and constraints on the operations it can perform.
  • Changing index options
  • Rename table
  • MODIFY COLUMN
  • Adding columns (only the last column was possible until 8.0.28). From 8.0.29 onwards, columns can be added anywhere.
  • Column deletion (from 8.0.29)
Examples of use up to version 8.0.28 can be found here.
Starting with version 8.0.29, if no ALGORITHM is specified, it defaults to INSTANT.
 

Points to be aware of when changing from MySQL 8.0

Parameter difference checking site

MySQL ParametersLet's make use of this website.

Parameter differences that require particular attention.

Parameter name5.78.0
default_authentication_pluginmysql_native_passwordcaching_sha2_password
default charsetlatin1utf8mb4
default collationutf8mb4_general_ciutf8mb4_0900_ai_ci
sql_modeONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
default_authentication_plugin
default charset
default collation
sql_mode
default_collation_for_utf8mb4
 

General overview of the upgrade process

DB Replication Configuration

Master DB (Source): DB01
Slave DB (Replica): DB02, DB03
All environments are MySQL 5.7.
 
The upgrade will be performed first on the slave devices, DB02 and DB03.
After that, DB02 will be promoted to the master DB.
 
By adding DB01 to the replication before upgrading, it can be used as a rollback environment in case of an emergency. However, adding a full version from the master to the slave is not recommended by MySQL's official documentation.
 

Detailed upgrade instructions

Here are the detailed steps for performing an upgrade using DB01-03 as described above.
For this project, DB01 will be included in the replication process as a rollback environment.

1. [DB02,DB03] Replacing the MySQL yum repository and importing the GPG key

2. [DB02,DB03] Install mysql-shell to use the upgrade checker.

Since there's no need to match the MySQL core version, we'll install the latest mysql-shell.

3. Run the Upgrade Checker [DB02, DB03]

4. [DB02, DB03] Confirm that there are 0 errors in the upgrade checker results.

[DB02,DB03] Verify that there are 0 errors in the upgrade checker results.
Items checked by the upgrade checker

5. [DB02, DB03] Stopping MySQL

6. [DB02, DB03] MySQL Upgrade

7. [DB02,DB03] Edit my.cnf

As mentioned in the section on parameters to be careful about, the default collation in MySQL 8.0 is utf8mb4_0900_ai_ci. This does not exist in MySQL 5.7, so in this test, which considers rollback, you need to specify utf8mb4_general_ci in the my.cnf settings.

8. [DB02, DB03] Startup & Check

 

9. Set [DB02,DB03]default_collation_for_utf8mb4

default_collation_for_utf8mb4
This parameter cannot be set in my.cnf; you must use SET PERSIST to persist changes to the variable.
For a detailed explanation of default_collation_for_utf8mb4, click here.
If this parameter is not specified, the following will occur.
utf8mb4_general_ci
Let's try creating a table without specifying anything in particular.
The character encoding is correctly set to utf8mb4 and the COLLATE setting is utf8mb4_general_ci.
 
However, COLLATE may unintentionally occur in the following patterns.utf8mb4_0900_ai_ciThis will be used.
This occurs when only the character encoding is specified and COLLATE is not specified.
 
 
 

10. Stop writing from the application and fail over DB02 to the master DB.

11. [DB01] Change the replication master reference to DB02 and start replication.

 

Final configuration

This completes the following configuration.

In conclusion

SRG is looking for new team members. If you are interested, please contact us here.