About SRGArticlesRecruitment Information
About SRGArticlesRecruitment Information
HOME/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) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
This article is located in SRGDBWGWe will publish materials regarding the database that the (DB Working Group) provides to the entire company.
I hope this helps in some way.
 
 

overview

It is an internal studyPresentation at SRG StudyThis is a guide based on the above and focuses on the upgrade part.
This article introduces the steps to build an environment that allows you to upgrade from MySQL 5.7 to MySQL 8.0 and then revert back to 5.7 if an emergency occurs.
 

About MySQL 8.0 version

The development policy has changed significantly and new features have been added even to 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 and the default behavior of ALTER TABLE has changed significantly. As a result, many bug reports have been reported 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 updates such as additions that involve ALTER TABLE to be completed by updating only the metadata.
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, you can add anywhere
  • Delete column (from 8.0.29)
For usage examples up to 8.0.28, click here
Starting with 8.0.29, if ALGORITHM is not specified, INSTANT will be 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 pay special attention to

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 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 slaves DB02 and DB03.
Then promote DB02 to master DB.
 
Before upgrading DB01, you can add it to the replication so that you can 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~03 above.
This time, we will include DB01 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] Verify that the upgrade checker results show 0 errors.
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 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 verification, which takes reversion into account, you need 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 in particular.
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 without specifying COLLATE.
 
 
 

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 replicating

 

Final Configuration

This completes the following configuration:

Conclusion

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