Upgrading from MySQL 5.7 to MySQL 8.0: Benefits, Challenges, and Solutions
Published: 2023-10-14 09:35:45

**Introduction:**

MySQL is a popular open-source relational database management system that provides a reliable, efficient, and scalable way to store, manage, and retrieve data. In my organization, MySQL is a core database used to store data for a significant number of applications.

To maintain the security and functionality of the database, it is crucial to upgrade to MySQL 8.0, particularly since MySQL 5.7 is reaching its end of life. By upgrading to MySQL 8.0, we will be able to access the latest security features, performance enhancements, and bug fixes.

Furthermore, upgrading to MySQL 8.0 can future-proof our database infrastructure, ensuring compatibility with the latest hardware and operating systems.

Overall, upgrading to MySQL 8.0 is crucial to maintaining the security, stability, and performance of your database, particularly in light of the end-of-life of MySQL 5.7, which will occur in October 2023. Without support, any bugs related to MySQL 5.7 will not be resolved


**What it means for businesses still using MySQL 5.7**:

MySQL 8.0 introduces several new features that were not available in MySQL 5.7, such as window functions, common table expressions, and JSON enhancements.

MySQL 8.0 offers better performance than MySQL 5.7 such as improved index and query execution.

Better security policy, such as password expiration and policies, which help to protect your data from unauthorized access.

Better scalability such as improved resource management and support for more efficient queries.

Improved in mysql Replication such as group Multi source replication and improved the performance.


**Pre-upgrading steps**:

There are several pre-checks we should perform to ensure that Mysql 8.0 is compatible with the current database.

Check the version specific, review the syntax, check for deprecated functions and differences in default settings.

MySQL 8.0 includes a number of incompatibilities with MySQL 5.7. The following is a general list of these incompatibilities:

There must be no tables that use obsolete data types or functions.

There must be no orphan *.frm files.

Triggers must not have a missing or empty definer or an invalid creation context.

There must be no partitioned table that uses a storage engine that does not have native partitioning support.

There must be no keyword or reserved word violations. Some keywords might be reserved in MySQL 8.0 that were not reserved previously. For more information, see Keywords and reserved words in the MySQL documentation.

There must be no tables in the MySQL 5.7 mysql system database that have the same name as a table used by the MySQL 8.0 data dictionary.

There must be no obsolete SQL modes defined in your sql_mode system variable setting.

There must be no tables or stored procedures with individual ENUM or SET column elements that exceed 255 characters or 1020 bytes in length.

 Before upgrading to MySQL 8.0.13 or higher, there must be no table partitions that reside in shared InnoDB tablespaces.

There must be no queries and stored program definitions from MySQL 8.0.12 or lower that use ASC or DESC qualifiers for GROUP BY clauses.

Your MySQL 5.7 installation must not use features that are not supported in MySQL 8.0.For more information, see Features removed in MySQL 8.0(https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals) in the MySQL documentation.

There must be no foreign key constraint names longer than 64 characters.

For improved Unicode support, consider converting objects that use the utf8mb3 charset to use the utf8mb4 charset. The utf8mb3 character set is deprecated. Also, consider using utf8mb4 for character set references instead of utf8, because currently utf8 is an alias for the utf8mb3 charset. 

When you start an upgrade from MySQL 5.7 to 8.0, Amazon RDS runs prechecks automatically to detect these incompatibilities.

These pre-checks are mandatory. You can't choose to skip them. The pre-checks provide the following benefits:

They enable you to avoid unplanned downtime during the upgrade.

If there are incompatibilities, Amazon RDS prevents the upgrade and provides a log for you to learn about them.

Challenges for planning:(why didn't we choose same RDS box for upgrade process)

Current DB setup was additional storage, which needs to be reduced.
Database should be Encryption enabled.




**Different Methods of Upgrade**:


**Method 1: Blue green Deployment.**

Blue-green deployment is a software deployment strategy that allows you to upgrade your MySQL database from version 5.7 to 8.0 with minimal downtime.

a. In this method, you have to create the new cluster of mysql 8.0 from 5.7 by clicking on the Blue green deployment option.
b. Blue cluster will be old version mysql 5.7 and Green cluster will be the 8.0 and make replication between them.
c. When ready, switch over to promote the staging environment to be the new production environment. For instructions, see Switching a blue/green deployment.

The switchover results in downtime. The downtime is usually under one minute, but it can be longer depending on your workload. 

The following are limitations for changes in a blue/green deployment:

You can't change an unencrypted DB instance into an encrypted DB instance.

You can't change a blue environment DB instance to a higher engine version than its corresponding green environment DB instance.

The resources in the blue environment and green environment must be in the same AWS account.

During switchover, the blue primary DB instance can't be the target of external replication.

**Method 2: In-place Upgrade.**

We can do the upgrade process on mysql 5.7 replica do the upgrade from mysql 5.7 to 8.0. Then do the master switch.

The following are limitations:

a. There will be no option to decrease the storage space.
b. There is no easy way to roll back the upgrade if something goes wrong.
c. The downtime is required during the upgrade process.




**Method 3: Customised Upgrade Method (per our requirement)**	

Creation of new replica with same config as mysql 5.7, with below changes 
Storage should be resized to the desired size.
DB replica instance created with encrypted.
After replica creation with Mysql 8.0 make replication between mysql 5.7.


**Planning for the migration:**


Below is the system requirements for Mysql RDS 8.0


1. Use version Mysql 8.0.
2. Create a new mysql 8 parameter group.
3. Binlog format should be Row based.
4. Storage should be resized to the desired size.
5. Auto extend enabled.
6. Encrypted enabled.
7. log_bin_trust_function_creators should 1.
8. Slow query enable.
9. Long_query time=1 sec.
10. Output should be File format.








**Upgrade process:**

We have to take a backup of the current Mysql 5.7 database with MYSQLDUMP utility.
Plan to restore the backup to the New Mysql 8.0 database.


**Before Mysql Upgrade Architecture:**



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/71jetb6o080pj9ptun5e.png)















**After Mysql Upgrade Architecture:**




![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qfctbik25homemwtl5xo.png)








**Challenges we have faced during the Migration:**
We have faced below errors while restoring the mySQL 5.7 to 8.0:
Store Procedure:
Avoid reserve Keywords
Deprecated Functions
Charactersets
Select queries with non standard group behavior
Grouping functions

 a. Store Procedures has the definer, so we have to create the same user in the new database to restore the backup successfully.
 
 b. Check for the replacing the reserved Keyword like "TYPE", "SHOW COLUMNS", "USE" etc.

 c. Check for the functions which are deprecated in mysql 8.0 like SQL_CALC_FOUND_ROWS, REGEXP, REFERENCES etc.

  d. We have faced the Error for 'Character set '#255' which was resolved by change of character_set_server, character_set_database,collation_server, collation_database.

  e. We have cross-checked all the select queries which rely on non-standard grouping behavior, because Mysql 8.0 has default mode: "ONLY_FULL_GROUP_BY".

  f. MySQL 8.0 has removed support for some grouping functions in the SELECT query like
     GROUP_CONCAT(DISTINCT), ANY_VALUE(), GROUP BY with BLOB or TEXT columns etc. 

  By rectifying the above issues we have successfully restored mysql 5.7 DB data to MYSQL 8.0.   

**Replication Setup:**

After successfully restoring the DB Data in Mysql 8.0 RDS, we have set up the replication between MYSQL 5.7 and 8.0.


**Application switchover:**

After mysql 8.0 replication setup with 5.7 successfully sync, we have to redirect the application connection to newly created Mysql 8.0 RDS.

Below are the checklist for Application switchover from DBA end:

a. Verify the DB connections user details in the current DB master before the Migration.
   
   select count(*),user,db,host from information_schema.processlist group by user,db;

b. update the end points in route 53 to point to the new RDS Master Mysql 8.0 for the minimal downtime.

c. Review the db connections to the old database and making sure it drained to zero and sametime binlog position should not be changing. If there are any read connections from the application we can kill manually.

d. Once the above steps are validated, please make sure to change the parameter for read_only=ON in the 5.7 db server, so that it’s restricted for write.

f. Stop replication between mysql 5.7 and 8.0.

CALL mysql.rds_stop_replication; CALL mysql.rds_reset_external_master;

g. Once the DNS switch complete, then start the applications.

h. Review the database connections details after the application point to the new database.

  Example:  select count(*),user,db,host from information_schema.processlist group by user,db;

i. Then handover to the Application team for a sanity check.



**Rollback Plan:**

If the Application team faced any challenges during the sanity check, then we will be executing the following steps to rollback to Mysql 5.7.

    1.  Read_only OFF in the old DB(5.7) and make it primary.

    2. Then DNS changes for Application with old DB host.




**Conclusion:**

Upgrading to the latest version of MySQL is a significant achievement that provides several benefits, such as improved performance, better security features, and access to new functionalities.This may include testing applications and modifying them to ensure compatibility with the new version of MySQL 8.0 thoroughly code test and select queries test.

Once the upgrade is complete, it is worth exploring the new features and enhancements that come with MySQL 8.0, such as better scalability, improved security, and the MySQL Document Store, which allows you to store JSON documents directly in the database.

Overall, upgrading to MySQL 8.0 is a significant step for any organization that depends on MySQL for their database requirements. With careful planning and execution, businesses can ensure a smooth transition and leverage the benefits of this robust database system.