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.
Caution
This is a non trivial operation. Only proceed if you have adequate rollback mechanisms in place, such as snapshots of your virtual machines and a backup of the database.
ALL INSTALLATIONS
- MAKE SURE THE JTEL PORTAL IS AT LEAST 5.15 BEFORE STARTING.
- MAKE SURE THE OPERATING SYSTEM IS UP TO DATE
INSTALLATIONS WITH REPLICATION
- MAKE SURE THAT REPLICATION IS WORKING CORRECTLY BEFORE STARTING.
- COMPLETE PHASE 1 FIRST FOR ALL SERVERS, STARTING WITH THE SLAVES.
- WHEN UPDATING MASTER-MASTER CONFIGURATIONS, STOP THE OTHER MASTER BEFORE STARTING THE UPDATE PROCESS with service mysqld stop.
- MAKE SURE THAT BOTH MASTERS ARE 8.x BEFORE RESTARTING BOTH AT THE SAME TIME.
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.
Caution
Do not proceed unless the Pre-Requisites are done. The procedure WILL NOT WORK with an earlier version of the jtel Portal installed.
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 fi if [ -e /etc/my.cnf.d/jtel-master.cnf ] then MY_CNF_FILE=/etc/my.cnf.d/jtel-master.cnf 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 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.
Caution
For Master-Master Replication, perform this simulataneously on both masters.
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 are OK
mysql -u root -p<password> -e "SHOW SLAVE STATUS\G"
Only proceed if no errors are found.
Note
The jtel Portal is NOT compatible with MySQL 5.7 - you must continue the upgrade path to 8.x at this point.
Upgrade ALL Servers to 5.7 before proceeding.
Phase 3 - Update MySQL to 8.0
Caution
Proceeding after this point without Version 3.15 or later of the jtel Portal installed, will break the database, and a rollback will be necessary.
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.
mysql_upgrade
mysql_upgrade is run automatically by all current 8.x versions of MySQL Server.
The output of mysql_upgrade can be found in /var/log/mysqld.log and errors will be in /var/log/messages
Check these files for errors.
Check Database and Replication are OK
mysql -u root -p<password> -e "SHOW SLAVE STATUS\G"
Phase 4 - Configure Other Components
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
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: