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:
- 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 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 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 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
- 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 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.
About Ideas2IT,
Are you looking to build a great product or service? Do you foresee technical challenges? If you answered yes to the above questions, then you must talk to us. We are a world-class custom .NET development company. We take up projects that are in our area of expertise. We know what we are good at and more importantly what we are not. We carefully choose projects where we strongly believe that we can add value. And not just in engineering but also in terms of how well we understand the domain. Book a free consultation with us today. Let’s work together.
.png)
.png)
.webp)
