Jive Support

Articles in this section

See more
Back

Jive - MySQL Upgrade 5.x to 8.x

Author: Colin Guilfoyle

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

 

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.

Was this article helpful?
0 out of 0 found this helpful

Related articles

Comments

0 comments

Please sign in to leave a comment.

Still Have Questions?

Our customer care team is here for you!

Submit a request