MySQL 5.1 to MySQL 8.0.25 Upgrade: A step-by-step guide

MySQL 5.1 to MySQL 8.0.25 Upgrade: A step-by-step guide to keep your projects updated

Share This

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 stepwise journey and lessons learnt 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

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:

  1. Reviewing what is new in MySQL 8.0
  2. Taking note of the database/schema changes
  3. 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.

Step-by-step process for 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

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

The above command will list 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

  1. Set yum repo for MySQL 8.0: yum localinstall https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
  2. Install MySQL 8.0: yum install mysql-community-server
  3. Enable mysqld service: systemctl enable mysqld
  4. Start  mysqld service: systemctl start mysqld
  5. Get a temporary password for the installed MySQL server: grep 'temporary password' /var/log/mysqld.log
  6. Run secure installation for setting root password and removing test databases: mysql_secure_installation
  7. Check MySQL version and other details by logging into mysql server with 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 larger size 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 changes 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 

Challenge #1

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

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

Challenge #2

Fetching table 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>

Challenge #3

We have <modifyColumn> tag in our changesets which was deprecated from 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.

Challenge #4

Liquibase 3.6.3 cannot log changesets with Ant library 1.6.5.

Solution: Upgraded Ant libraries from1.6.5 (in our project) to 1.7.0, it will be compatible with Liquibase 3.6.3.

Additional Changes Required

  1. Type of boolean was changed from java.sql.Types.BIT to java.sql.Types.BOOLEAN
  2. 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 changesets tables contain any of them use (`) as an escape pattern, which was internally handled in Liquibase 3.6.3 (ex: RANK -> `RANK`)

Here is the Updated Final Configuration of our Application

Java

Maven

Spring

Hibernate

Tomcat

MySQL

Liquibase

1.8

2.2.1

2.5

3.2.6

7.0.54

8.0.23

3.6.3

Like our step-by-step guides? Then check out our step-by-step guide to upgrade from Angular 9 to Angular 11.