Overview
The next Jive HOP release (9.17) supports MySQL 8 and some customers will want to migrate (from MySQL 5.6). Below is a guide on how to do this.
Once this is complete please follow this guide to upgrade to 9.17
Guide
Steps for Upgrade Existing MySQL Server
- Setup Jive application to Maintenance
- Backup databases and MySQL config files
- Upgrade MySQL Server
- Restore config files
- Restore databases
- Run upgrade scripts
- Start Jive Application and verify
Steps for Setting Up New MySQL Server
- Prepare a separate server/cluster with MySQL 8.x
- Setup Jive application to Maintenance
- Backup databases and MySQL config files
- Restore databases to new MySQL Server
- Run upgrade scripts
- Re-configure Jive application to new MySQL server
- Start Jive Application and verify
Important: config property lower_case_table_names=1 must be set for case sensitive file systems like Linux, Unix and MacOS platforms i.e., in my.cnf
Setup Jive application to Maintenance
Setup Jive application to maintenance and stop services by using Jive CLI
jive stop
Backup Databases
Backup all databases
| mysqldump -u [username] -p --all-databases --single-transaction > alldatabases$(date +%Y%m%d%H%M%S).sql |
Or Individual database
| mysqldump -u [username] -p jive > jive-$(date +%Y%m%d%H%M%S).sql mysqldump -u [username] -p eae > eae-$(date +%Y%m%d%H%M%S).sql |
Backup cnf files
Backup my.cnf and mysql.cnf files which is usually /etc/my.cnf
| sudo find / -name my*.cnf 2>/dev/null |
Copy all files to a backup folder
Make sure to restore config file(s) after upgrade and before starting service
Upgrade MySQL Server
Shutdown Database
Shutdown database and stop mysql service
| mysqladmin -u root -p shutdown sudo service mysqld stop sudo service mysqld status |
Upgrade MySQL Server Installation
Upgrading MySQL server varies based on platform. I have tested at Amazon Linux and followed these steps Jive - MySQL Upgrade 5.x to 8.x
Restore config files
- Copy config files back to original locations if upgrade process followed re-installation of MySQL
- Run the following script for each MySQL config file (my.cnf) Runbook: Jive - MySQL Upgrade 5.x to 8.x
Restore Databases
Restore all databases
| mysql -u [username] -p < alldatabases.sql |
Or Individual databases
| CREATE DATABASE IF NOT EXISTS `your jive database name`; CREATE DATABASE IF NOT EXISTS `your eae database name`; |
example;
| mysql -u [username] -p jive < jive-20240125200656.sql mysql -u [username] -p eae < eae-20240125200656.sql |
Run Upgrade Scripts
- Download file change_to_utf8mb4.sql
- Find and replace database names as
- `jive` -> your jive database name
- `eae` -> your eae database name
- Run the sql after login to mysql
| mysql -u [username] -p source <path>/change_to_utf8mb4.sql; |
Update mysql-connector-j
MySQL-connector-j now uses the com.mysql.cj.jdbc.Driver class name instead of the old com.mysql.jdbc.Driver, and HOP 9.17.0 expects this. Therefore, make sure the version of mysql-connector-j you have installed is up-to-date (in our testing, we used 8.2.0).
Additionally, if the path to mysql-connector-j has changed (or if you installed the new version in a different path), update the jive properties using the following commands BEFORE starting Jive 9.17.0 for the first time after the upgrade:
| # replace with actual paths jive set webapp.custom_ld_library_path_additions /usr/local/jive/mysql jive set webapp.custom_classpath_additions /usr/local/jive/mysql/mysql-connector-j.jar jive set eae.custom_ld_library_path_additions /usr/local/jive/mysql jive set eae.custom_classpath_additions /usr/local/jive/mysql/mysql-connector-j.jar |
Start Jive Application and verify
- Start Jive application
jive start
- Verify login
If facing issue in login then re-configure application to database server as
- Take backup of jive_startup.xml
- Set up as per KB Configuring Web Application servers for high-availability
Rollback
- Uninstall MySQL 8
- Install MySQL 5 (previous version)
- Set lower_case_table_names=1 for case sensitive filesystem platforms
- Restore databases
---------------------------------------------- Platform Specific ---------------------------------------------
Upgrade MySQL Server - Amazon Linux
Upgrading MySQL server varies based on platform. I have tested at Amazon Linux and followed these steps
Uninstall MySQL 5.x
List installed packages
| sudo yum list installed | grep ^mysql |
Uninstall all packers listed above, I found these
| sudo yum remove mysql-community-server sudo yum remove mysql-common sudo yum remove mysql-libs sudo yum remove mysql-client |
Install MySQL 8.0.36
| sudo yum update |
Setup Key
sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2023
Download RPM
| sudo wget https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm ls -lrt mysql80* |
Install MySQL
| sudo dnf install mysql80-community-release-el9-1.noarch.rpm -y sudo dnf install mysql-community-server -y |
Edit my.cnf and set lower case table property
| sudo vi /etc/my.cnf |
Add following line under
| [mysqld] lower_case_table_names=1 |
Start MySQL service
| sudo systemctl start mysqld |
If service fail to start due to lower_case_table_names property then recreate databases as
| sudo systemctl stop mysqld sudo rm -rf /var/lib/mysql sudo mkdir /var/lib/mysql sudo chown mysql:mysql /var/lib/mysql sudo chmod 700 /var/lib/mysql sudo mysqld --defaults-file=/etc/my.cnf --initialize --lower_case_table_names=1 --user=mysql --console sudo systemctl start mysqld |
| sudo grep 'temporary password' /var/log/mysqld.log |
Shell script to comment-out removed variables
Script commentOutRemovedVariables.sh is attached to this article.
Priyanka Bhotika
Comments