The jtel Portal System is moving to use MySQL 8.x instead of the older MySQL 5.6.

Currently, the latest release of the portal 3.15 supports both versions of MySQL, however this is only an interim measure, and this will change in a coming release soon such that MySQL 8.x is required.

This procedure describes how to update the system to MySQL 8.x.

Pre-Requisites

• The jtel Portal must be updated to the latest version, currently 3.15
• The operating system must be updated to include all of the latest patches using yum -y update

• yum-utils must be installed using yum -y install yum-utils

Introduction

This is a multi-stage operation, since the MySQL system does not support missing out updates to versions - to move from 5.6 to 8.0 it is required to first update to 5.7.

Phase 1 - Ensure existing configuration is good

There are several configuration items which need to be set in the slave configuration before proceeding.

Unfortunately, mysql 5.7 will use a different default binary log and relay log file name during the upgrade process (this is probably a bug), unless these steps are completed. 

Complete this procedure for all slaves (also both masters in master-master replication).

# Master or slave?
if [ -e /etc/my.cnf.d/jtel-slave.cnf ] 
then
MY_CNF_FILE=/etc/my.cnf.d/jtel-slave.cnf
MASTER_UPDATE=FALSE
fi
if [ -e /etc/my.cnf.d/jtel-master.cnf ] 
then
MY_CNF_FILE=/etc/my.cnf.d/jtel-master.cnf
MASTER_UPDATE=TRUE
fi
if [ ! -z MY_CNF_FILE ] 
then
cat <<EOFF >> $MY_CNF_FILE
relay_log                       = mysqld-relay-bin
relay_log_index                 = mysqld-relay-bin.index
relay_log_info_file             = relay-log.info
EOFF
if [ "$MASTER_UPDATE" == "FALSE" ] 
then
cat <<EOFF >> $MY_CNF_FILE
skip-log-bin
EOFF
fi
fi
MY_CNF_FILE=

# Restart mysql
systemctl restart mysqld.service

Phase 2 - Update MySQL to 5.7

Remember - for replicated installations, start with the slaves first. Then update the masters, one after the other.

Perform clean shutdown of MySQL with innodb_fast_shutdown set to 0

mysql -u root -p<password> --execute="SET GLOBAL innodb_fast_shutdown=0"
mysqladmin -u root -p<password> shutdown

Remove Old yum repository, and install new yum repository

Remove the old MySQL yum repository.

yum remove mysql-community-release.noarch

Next, install the new MySQL yum repository, using the command relevant to the OS version being used.

You can determine the OS being used, with the following command:

cat /etc/centos-release

For CentOS 7:

yum -y localinstall https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

For CentOS 6:

yum -y localinstall https://dev.mysql.com/get/mysql80-community-release-el6-3.noarch.rpm

Update MySQL Server to 5.7

This involves clearing the version-lock table, and enabling the 5.7 update path.

Note, the yum command is issued without the -y option. You should check what the system is about to do before confirming this step.

# Clear versionlock table
yum versionlock clear

# Enable 5.7 
yum-config-manager --disable mysql56-community
yum-config-manager --enable mysql57-community
yum-config-manager --disable mysql80-community

# Update
yum update

Start the MySQL Server (WITHOUT REPLICATION FOR SLAVES AND MASTER-MASTER)

Start the mysql Server without replication.

# Start - IMPORTANT - WITHOUT REPLICATION
systemctl set-environment MYSQLD_OPTS="--skip-slave-start"
systemctl start mysqld.service

# Start - IMPORTANT - WITHOUT REPLICATION
mysqld_safe --skip-slave-start &

Run mysql_upgrade

Either change the password in the following command, or simply use the -p option without specifying a password, and entering it at the prompt.

mysql_upgrade -u root -p<password>

Check the output from this phase before proceeding - if this procedure is showing non-recoverable error messages, then the procedure has gone wrong, and you must rollback here.

Next restart the MySQL server.

systemctl stop mysqld.service
systemctl set-environment MYSQLD_OPTS=
systemctl start mysqld.service

service mysqld stop
service mysqld start

Check Database and Replication and Version are OK

mysql -u root -p<password> -e "SHOW SLAVE STATUS\G SELECT VERSION();"

Only proceed if no errors are found.

Phase 3 - Update MySQL to 8.0

Perform clean shutdown of MySQL with innodb_fast_shutdown set to 0

mysql -u root -p<password> --execute="SET GLOBAL innodb_fast_shutdown=0"
mysqladmin -u root -p<password> shutdown

Enable 8.0 repoitories and update the server

Note, the yum command is issued without the -y option. You should check what the system is about to do before confirming this step.

# Enable 8.x
yum-config-manager --disable mysql56-community
yum-config-manager --disable mysql57-community
yum-config-manager --enable mysql80-community

# Update to 8.x
yum update

Start the MySQL Server

Start the mysql Server.

systemctl start mysqld.service

service mysqld start

Note, that starting the mysql server may take some time. It may even appear to be hung, but wait for a few minutes and let it complete.

Check Database and Replication and Version are OK

mysql -u root -p<password> -e "SHOW SLAVE STATUS\G SELECT VERSION();"

Phase 4 - Configure Other Components

Portal

Run the Portal update script again. This will optimise the scripts for MySQL 8.x.

sudo /home/jtel/shared/JTELCarrierPortal/Update/update_release.sh

JBOSS Server

EITHER: Install new Version of JBOSS Server

You can simply install the new version of the JBOSS Server, at least jboss-as-7.1.1.FINAL.14.tar.gz from cdn.jtel.de.

This contains the required components, but will require that the hazelcast.xml and standalone-full.xml files are updated as described in the installation of the webserver, see here: Role WEB

OR: Update the JBOSS Server

The JBOSS server requires a new connector to attach to MySQL 8.x.

This can be copied to the JBOSS server instance, using the following command (run for example from /home/jtel/shared/JTELCarrierPortal/Update), as the jtel user:

./update_jboss.sh

Change stanalone-full.xml

The standalone-full.xml configuration file requires some modifications.

A new connection property must be added to the datasource configurations for MySQL. This must be added to the datasource configurations for JTELWeb, JTELWebSlave, JTELStats, JTELLog and JTELReports.

See the following example:

...
<datasource jndi-name="java:jboss/datasources/JTELLog" pool-name="JTELLog" enabled="true">
    <connection-url>jdbc:mysql://testdb2:3306/JTELLog</connection-url>
        #
        # THIS SECTION NEEDS TO BE ADDED
        #
        <connection-property name="useSSL">
            false
        </connection-property>
...

In the drivers section, make sure that the driver class line is included for the mysql driver.

This will only be missing on very old jboss installations, if this is the case, it might be better to update the jboss server to a new release completely.

...
<drivers>
    <driver name="com.mysql" module="com.mysql">
        #
        # MAKE SURE THIS NEXT LINE IS PRESENT.
        #
        <driver-class>com.mysql.jdbc.Driver</driver-class>
        ...               

Reconfigure 8-Server ODBC Connections

The Rollback string for MySQL Versions 8.x (in fact MySQL 5.7) and higher has changed, and will need to be reconfigured in 8-Server. 

The new rollback string is:

DO RELEASE_ALL_LOCKS(); ROLLBACK;

8-Server should be restarted after making this change:

Troubleshooting

jtel Portal DB Script fails with "Failed to load caching_sha2_password authentication plugin"

The DB scripts are being run from a different server than the updated MySQL server(s). 

This is good! You should run them from the role STORE.

However, you will need to update the mysql client on these machines too. Do this (change the second line for CentOS 6.x - see above for the RPM required):

yum remove mysql-community-release.noarch
yum -y localinstall https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
yum update mysql*

Now run the scripts again.

jtel Portal DB Script fails with "Duplicate Entry"

Try running the DB script again.

Authentication Failed with some DB Tools

For example, earlier versions of DB Forge will fail when connecting to MySQL 8.x.

Update the tool.

Slave Fails to Start with Error ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

The following series of commands issed in mysql might sometimes help to restart the slave, without rebuilding replication from scratch:

RESET SLAVE;
START SLAVE IO_THREAD;
STOP SLAVE IO_THREAD;
RESET SLAVE;
START SLAVE;

Upgrade to MySQL 8.x Fails

If the upgrade to 8.x fails, try rolling back to 5.7 as follows, and resolve the problem in 5.7 before proceeding.

# Enable 5.7
yum-config-manager --disable mysql56-community
yum-config-manager --enable mysql57-community
yum-config-manager --disable mysql80-community


# Uninstall
yum -y remove mysql-community-server
yum -y remove mysql-community-client
yum -y remove mysql-community-libs
yum -y remove mysql-community-common


# Install
yum -y install mysql-community-server