MySQL Upgrade 5.1 to MySQL 8.0.25 Version: Updated Guide

MySQL is the most popular open-source SQL Database Management System (DMS), developed, distributed, and supported by the Oracle Corporation. With the recent updates in MySQL Version 8, the DMS has reinstated its position as the best DMS in the market. The MySQL 8.0.25 upgrade comes with major performance and security improvements.

Some of the major highlights include:

Performance improvement
Security and account management
Table encryption management
InnoDB enhancements
JSON enhancements
Optimizer
Regular expression support
MySQL Document Store
Transactional Data Dictionary
SQL Roles
Default to utf8mb4
Common Table Expressions
Window Functions

Considering these improvements, it is advisable that your application’s DMS is upgraded to the latest MySQL server. Upgrading to the latest version of MySQL from the 5.1 version requires many minor as well as major edits in your enterprise application.

We recently took on a mandate to migrate our enterprise database to the latest version of MySQL. This enabled us to add new features to the platform and elevate security and data optimization capabilities.

We have documented our step by step journey and the lessons learned during the process.

The configuration of our enterprise application

To understand the migration process clearly, I have elaborated on the tech stack of the application:

Java, Maven, Spring, Hibernate, Tomcat, MySQL, Liquibase, with versions:

1.8, 2.2.1, 2.5, 3.2.6, 7.0.54, 5.1.34, 1.9.0.0.

Our main aim was to upgrade the DMS version of the platform.

The Migration Process

At the start of the migration process, we considered 3 parameters:

Reviewing what is new in MySQL 8.0
Taking note of the database/schema changes
Upgrading dependencies

As per the MySQL documentation, the upgrade map is 5.1 —> 5.5 —> 5.6 —> 5.7 —> 8.0. We followed the documentation for procedural upgrades.

A step-by-step process for MySQL Upgrade (Linux - Centos)

1. Take a backup of the current Database and its configurations

mysqldump -u****** -p****** --routines --triggers TEST_DB | gzip > /opt/db_bck.sql.gz

If your DB size is large, you can use the nohup command, which runs the backup process in the background. So the above dump command will be modified as:

nohup sh -c 'mysqldump -u****** -p****** --routines --triggers TEST_DB |gzip > /opt/db_bck.sql.gz' > /opt/bck_error_log.log &

If any error occurs during the dump process, it will be logged in bck_error_log.log file.

Now, backup the current MySQL configuration file located in /etc/my.cnf with this command.

cp /etc/my.cnf /opt/5.1_my.cnf

2. Uninstall MySQL 5.1 with the following commands:

Open terminal: Ctrl + Alt + T
Stop mysql service: systemctl stop mysql
Remove mysql-server: sudo yum remove mysql mysql-server
Remove mysql-libraries: sudo yum remove mysql mysql-server mysql-libs compat-mysql51
Find MySQL 5.1 files: find / -name mysql

The above command will list the following folder paths

/var/lib/mysql
/etc/selinux/targeted/active/modules/100/MySQL
/usr/bin/mysql
/usr/lib64/mysql
/usr/include/mysql

Remove the above listed folders. Also, remove my.cnf if it exists.

3. Install MySQL 8.0 (tested with 8.0.23) with the following commands

Set yum repo for MySQL 8.0: yum localinstall https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
Install MySQL 8.0: yum install mysql-community-server
Enable mysqld service: systemctl enable mysqld
Start mysqld service: systemctl start mysqld
Get a temporary password for the installed MySQL server: grep 'temporary password' /var/log/mysqld.log
Run secure installation for setting root password and removing test databases: mysql_secure_installation
Check MySQL version and other details by logging into MySQL server with the following command: mysql -u root -p

Note: Once you are logged in, create a new user and grant permission to that user. Avoid root user login.

4. Apply the old database backup and run migration for schema changes

gunzip -c /opt/db_bck.sql.gz | mysql -u ***** -p***** TEST_DB > dump_load.log &

Here’s a Nohup command for a large dump file:

nohup sh -c 'gunzip -c /opt/db_bck.sql.gz | mysql -u ***** -p***** TEST_DB’ > /opt/dump_error_log.log &

If there are any errors during the loading dump process, it will be logged in the dump_error_log.log file. After a successful dump process, Run maven clearchecksum and it will clear check sums that were calculated with older versions of Liquibase and a new checksum will be calculated on the next migration command.

5. Testing

Once the upgrade was completed, we ran comprehensive tests on all the parts of the application. We ensured focused testing was done for Liquibase to check if it migrates all the changesets and loads metadata properly.

The application was also tested using multiple web browsers like Google Chrome, Firefox, and Microsoft Edge.

Database Changes

During the upgrade process, there will be certain changes in the database configuration. It is essential to take note of those changes to ensure that the application runs smoothly after the DMS upgrade. The major changes will be:

The default character set has changed from latin1 to utf8mb4
InnoDB INFORMATION_SCHEMA views were renamed
New reserved keywords have been added
Binary logs were enabled by default. Know more.

Upgrading Dependencies

Like I just said, there will be a few major changes in the database and we need to upgrade dependencies accordingly. There might be a few challenges for the existing tech stack to connect with the new version of the Database Management System.

In the case of our application, we use Liquibase for managing database schema changes and the dbunit plugin for seeding meta changes. When we tried to connect our application with the MySQL 8.0 database server using mysql-connector/J-8.0 for running schema and data migration, we faced quite a few issues with creating and fetching tables. This was because our application had an older version of Liquibase 1.9.0.0, which was not fully compatible with MySQL 8.0. So we upgraded to a higher version of Liquibase, which was fully supported by MySQL 8.0. Once we upgraded Liquibase, we realized we needed to upgrade the corresponding dependencies as well.

Upgrading Liquibase to a higher version led to upgrading the Ant library and its dependencies too. Once done, our schema change log system for the migration process was ready.

There were a few challenges faced during the migration process. To ensure that you do not run into those, we have listed them below with appropriate solutions.

Challenges & Solutions

Liquibase Version Compatibility

Problem with Liquibase-1.9.0 on DATABASECHANGELOG and DATABASECHANGELOGLOCK table creation when using mysql-connector-java-8.0 or higher.

Solution: Upgrade Liquibase to the version that supports MySQL 8.0. I have upgraded Liquibase from 1.9.0.0 to 3.6.3

Ambiguous Table Exception

Fetching tables without matching schema/DB name, which leads to an Ambiguous Table exception.

Solution: This happened because the JDBC connection generated with the MySQL Connector/J 8.0.25 was generic. So, multiple tables with the same filename from different schema/DB were fetched. Update the POM configuration for dbunit to fetch a table with schema/DB name.

<schema>TEST_DB</schema>

<dataTypeFactoryName>org.dbunit.ext.mysql.MySqlDataTypeFactory</dataTypeFactoryName>

<metadataHandlerName>org.dbunit.ext.mysql.MySqlMetadataHandler</metadataHandlerName>

Deprecated Tags in Changesets

We have <modifyColumn> tag in our changesets, which was deprecated in Liquibase 2.0.

Solution: We can use the modifyColumn tag with Liquibase-modify-column.jar along with <ext:modifyColumn> tag, instead of using the <modifyColumn> tag. See the Liquibase documentation to choose the right tags for your application.

Compatibility with Ant Library

Liquibase 3.6.3 cannot log changesets with Ant library 1.6.5.

Solution: Upgrade Ant libraries from 1.6.5 (in our project) to 1.7.0, it will be compatible with Liquibase 3.6.3.

Additional Changes Required

The type of boolean was changed from java.sql.Types.BIT to java.sql.Types.BOOLEAN
Use ext databaseChangeLog xsd in Changeset xmls to use <ext:modifyColumn> tag.

Note: New reserved keywords have been added in MySQL 8.0.25. If your changeset tables contain any of them, use (`) as an escape pattern, which was internally handled in Liquibase 3.6.3 (ex: RANK -> `RANK`)