About SRGTechnical ArticlesRecruitment Information
About SRGTechnical ArticlesRecruitment Information
HOME/Technical Articles/Upgrading from MySQL 5.7 to 8.0 with rollback

Upgrading from MySQL 5.7 to 8.0 with rollback

This is Oniumi (@fat47) from the Service Reliability Group (SRG) of the Technology Headquarters.
#SRG(Service Reliability Group) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS.
This article is located in SRGDBWGWe will publish materials regarding the database provided by the DB Working Group for the entire company.
I hope this helps in some way.
 
 

overview

It is in-house studyPresentation at SRG StudyThis is a guidebook that focuses on the upgrade part based on the above.
This article introduces the steps to upgrade MySQL 5.7 to MySQL 8.0 and create an environment that allows you to revert to 5.7 in the event of an emergency.
 

About 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 8.0 version to use?

💡
Not the latest minor version8.0.28Consider using
8.0.29The INSTANT DROP COLUMN feature was added in , which significantly changed the default behavior of ALTER TABLE. As a result, many bug reports have been filed around this feature.
Currently, 8.0.29 is no longer available, and the official recommendation is to use 8.0.30 or later, but not all bugs have been fixed.
ALGORITHM=INPLACE/COPY;
Also, if there are any tables that have been INSTANT ADDed/DROPped even once, Percona Xtrabackup cannot take a backup.
In this case, you need to run OPTIME TABLE on the target table or run ALTER TABLE ALGORITHM=COPY; to update the table data.

What is Instant DDL?

This function allows you to complete updates by updating only the metadata when making additions involving ALTER TABLE.
It is very fast but there are limitations and restrictions on what can be done.
  • Changing indexing options
  • Renaming a table
  • MODIFY COLUMN
  • Adding columns (only the last column is possible until 8.0.28) From 8.0.29 onwards, you can add columns anywhere.
  • Delete column (from 8.0.29)
For usage examples up to 8.0.28, please see here.
Starting with 8.0.29, if ALGORITHM is not specified, INSTANT is used by default.
 

Points to note regarding changes from MySQL 8.0

Parameter difference check site

MySQL ParametersLet's use the site

Parameter differences to be particularly careful of

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
 

Overview of the upgrade process

DB replication configuration

Master DB (Source): DB01
Slave DB (Replica): DB02, DB03
All environments are MySQL 5.7.
 
Upgrade the slaves DB02 and DB03 first.
Then DB02 is promoted to master DB.
 
Before upgrading DB01, you can add it to the replication and use it as a fallback environment in case of an emergency. However, adding a full version from the master to the slave is not officially recommended by MySQL.
 

Detailed upgrade instructions

These are detailed steps for upgrading using DB01 to 03 above.
This time, DB01 will be included in the replication as a fallback environment.

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

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

There is no need to match the version with the MySQL main body, so install the latest mysql-shell.

3. [DB02,DB03] Run the upgrade checker

4. [DB02,DB03] Confirm that the upgrade checker results show 0 errors.

[DB02,DB03] Check that the upgrade checker results show 0 errors.
Items checked by the upgrade checker

5. [DB02,DB03] Stop MySQL

6. [DB02,DB03] Upgrade MySQL

7. [DB02,DB03] Edit my.cnf

As mentioned in the section on parameters to be aware of, the default collation is utf8mb4_0900_ai_ci from MySQL 8.0 onwards. This does not exist in MySQL 5.7, so in this test, which takes reversion into consideration, it is necessary to specify utf8mb4_general_ci in the my.cnf settings.

8. [DB02,DB03] Startup & Check

 

9. [DB02,DB03] Set default_collation_for_utf8mb4

default_collation_for_utf8mb4
This parameter cannot be set in my.cnf and uses SET PERSIST to make the variable changes persistent.
For more information on 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.
The character code is utf8mb4 and COLLATE is utf8mb4_general_ci.
 
However, in the following pattern, COLLATE is unintentionallyutf8mb4_0900_ai_ciis used.
This is the case when only the character code is specified and COLLATE is not specified.
 
 
 

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

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

 

Final configuration

This completes the following configuration:

Conclusion

SRG is looking for people to work with us. If you're interested, please contact us here.