mysql 5.5 to 5.7 upgrade - sapphireims · mysql 5.5 to 5.7 upgrade page 7 3.4 installing odbc 5.2...
Post on 26-Aug-2020
4 Views
Preview:
TRANSCRIPT
Tecknodreams Software Consulting Pvt. Ltd.
Manual steps to Upgrade MySQL 5.5 to
MySQL 5.7
Document Version – 1.0 [July24, 2017]
Last Modified:24/07/2017
Abstract:
This document provides steps by step procedure to manually upgrade from MySQL 5.5 to MySQL 5.7 and
also describes the database migration steps.
Aashick John [aashick@sapphireims.com]
MySQL 5.5 to 5.7 Upgrade
Page 1
Copyright
Copyright © 2017 SapphireIMS All Rights Reserved Tecknodreams Software Consulting Pvt. Ltd.
Restricted Rights Legend
This documentation is subject to and made available only pursuant to the terms of the Tecknodreams
License Agreement and may be used or copied only in accordance with the terms of that agreement. It is
against the law to copy the documentation except as specifically allowed in the agreement. This
document may not, in whole or in part, be copied photocopied, reproduced, translated, or reduced to
any electronic medium or machine readable form without prior consent, in writing, from Tecknodreams.
Information in this document is subject to change without notice and does not represent a commitment
on the part of Tecknodreams.
MySQL 5.5 to 5.7 Upgrade
Page 2
Table of Contents 1. Overview .................................................................................................................................................3
2. Prerequisites ...........................................................................................................................................4
3. Upgrade Steps (Windows) ......................................................................................................................5
3.1 Backing up my.ini Configuration Settings ......................................................................................5
3.2 Backing up MySQL 5.5 Schema ......................................................................................................5
3.3 Installing MySQL Community .........................................................................................................6
3.4 Installing ODBC 5.2 .........................................................................................................................7
3.5 Un-installing MySQL 5.5 .................................................................................................................9
3.6 Installing MySQL 5.7 Server ............................................................................................................9
3.7 Restoring Time Zone Changes ......................................................................................................13
3.8 Restoring my.ini Configuration Settings .......................................................................................13
3.9 Restoring Database Backup ..........................................................................................................13
3.10 Securing MySQL Server ................................................................................................................15
3.11 Executing Post Installation Steps .................................................................................................18
4. Upgrade Steps (Linux) ...........................................................................................................................19
4.1 Backing up my.cnf Configuration Settings ...................................................................................19
4.2 Backing up MySQL 5.5 Schema ....................................................................................................20
4.3 Executing Pre Installation Steps ...................................................................................................21
4.4 Installing MySQL 5.7 Server ..........................................................................................................22
4.5 Restoring my.cnf Configuration Settings .....................................................................................22
4.6 Creating database users ...............................................................................................................22
4.7 Restoring Time Zone Changes ......................................................................................................23
4.8 Restoring Database Backup ..........................................................................................................23
4.9 Securing MySQL Server ................................................................................................................24
4.10 Executing MySQLUpgrade.bat .....................................................................................................25
MySQL 5.5 to 5.7 Upgrade
Page 3
1. Overview
MySQL database upgrade from 5.5 to 5.7 can be done by executing the MySQLUpgrade.bat provided as
part of SAPP_4097 patch and is located under <SapphireIMS Installed
path>\ConsoleManagement\ExternalWar\SoftwareRepository.war\MySQL5.7Upgrade\MySQLUpgrade\
folder. During the batch file execution if you encounter any issues or the script fails to upgrade MySQL
Server, then follow the steps mentioned in Chapter 3. Upgrade Steps (Windows) to install MySQL 5.7
Server manually.
The database upgrade is done in stages and personnel involved in upgrade should check the error log
(<mysql_upgrade_folder>/logs/sims_mysqlupgrade_log.txt) to determine the cause of the failure and the
step at which the error occurred. Once the failure step is known, then proceed with the manual upgrade
from that step directly and skip all other previous steps.
For example, in the event of MySQLUpgrade.bat batch file failing, if after analysing the log file it is
established that the script failed during Step 4, it means that batch file successfully executed up to Step 3
and you may skip Steps 1, 2 and 3 and directly jump to Step 4.
This document also includes steps to be followed to secure MySQL server by restricting global access to
the database and enable access to the SapphireIMS server alone.
MySQL 5.5 to 5.7 Upgrade
Page 4
2. Prerequisites
❖ The SapphireIMS Patch version should be SAPP_4097
❖ MySQL 5.7 installer is downloaded by the user and is ready for installation
Note: Windows version of MySQL 5.7 installer can be downloaded from the following link
https://downloads.mysql.com/archives/get/file/mysql-installer-community-5.7.17.0.msi
❖ .NET Framework 4.0 or above needs to be installed before installing MySQL Server 5.7
MySQL 5.5 to 5.7 Upgrade
Page 5
3. Upgrade Steps (Windows) Follow the steps mentioned below to manually upgrade MySQL Server 5.5 to 5.7.
3.1 Backing up my.ini Configuration Settings Note down the values of the following variable values from the MySQL Server 5.5 my.ini file. Post MySQL 5.7 installation and before restoring the database backup, update the respective variable values in MySQL Server 5.7 my.ini file.
1. max_connections 2. tmp_table_size 3. key_buffer_size 4. innodb_log_buffer_size 5. innodb_buffer_pool_size
Note : Apart from above changes, if any other parameters are customized in MySQL 5.5 my.ini file then similar changes must be done in MySQL 5.7 my.ini as well.
3.2 Backing up MySQL 5.5 Schema This step involves taking a schema backup of MySQL 5.5 server.
Note: In Cluster member / SapphireIMS standalone setups, ignore this step.
➢ In case of Professional setup, you need to backup ‘ims’ DB alone.
➢ If the setup is MSP, then you need to backup both ‘ims’ and Customer Schema one by one.
Note: Follow similar steps for add-on MSP Server.
➢ Before taking the backup, source AlterScript.sql file if ODBC 5.1 is installed. If ODBC 5.2 is installed
then ignore sourcing AlterScript.sql file.
Note: AlterScript.sql can be found in the <mysql_upgrade_folder>\MySQL Msi\ folder.
Where to source AlterScript.Sql
▪ In Professional / MSP Probe/ Cluster Server, on system where MySQL server is installed,
need to source this file in ‘ims’ schema.
▪ In MSP Server setup, on system where MySQL server is installed, source this file in
customer schema only (i.e. ‘ims’ schema must be excluded).
Backup ‘ims’ database:
MySQL 5.5 to 5.7 Upgrade
Page 6
Fig1: Backing up ims database
• Execute the following command to backup ims database. Make sure that entire schema is backed up
and keep the backup file in a safe place.
mysqldump -uims -pims123 --events -R --triggers ims > ims_db_Dump.sql
Backup customer schema:
• Execute the following command to backup customer database. Make sure that entire schema is
backed up and keep the backup file in a safe place.
mysqldump -uims -pims123 --events -R --triggers [customer schema name] > [customer schema
name]_db_Dump.sql
E.g.mysqldump -uims -pims123 --events -R --triggers customer_1 > customer_1_db_Dump.sql
3.3 Installing MySQL Community This step involves installing MySQL community edition. Follow the steps given below.
➢ Open folder MySQLUpgrade\MySQL Msi\ and look for a file named mysql-installer-community-
5.7.17.0.msi
➢ Open a command prompt in Administrative mode and execute the following command as shown
in the Fig 2: MySQL Community installation
msiexec /i mysql-installer-community-5.7.17.0.msi /passive
Fig 2: MySQL Community installation
MySQL 5.5 to 5.7 Upgrade
Page 7
3.4 Installing ODBC 5.2 This step involves installing MySQL ODBC 5.2 driver. Follow the steps given below.
➢ Find the version of the MySQL ODBC driver installed on the server using any of the 2 methods listed
below.
Method 1:
Look for “MySQL ODBC Connector” folder in SapphireIMS installed location. You may find the version
number suffixed to the folder name.
E.g. If SapphireIMS server installed location contains a folder “MySQL ODBC Connector 5.1” then it
means that MySQL server ODBC driver 5.1 is installed.
Method 2:
Check Control panel for version of the MySQL ODBC driver installed.
➢ If ODBC driver 5.1 is installed on server
• Perform utf8 conversion and definer changes by executing the following command.
Note : MySQL Upgrade will change only DEFINER=`ims`@`` to DEFINER=`ims`@`localhost`,
remaining possibilities like some other definers found then need to handle accordingly by the end user.
cd <mysql_upgrade_folder>\MySQL Msi\SedUtility
sed.exe -e "s/latin1_swedish_ci/utf8_general_ci/" -e "s/latin1/utf8/" -e
"s/DEFINER=`ims`@``/DEFINER=`ims`@`localhost`/" <
"<mysql_upgrade_folder>\MySQLBackup\ims_DB_Dump.sql">"<mysql_upgrade_folder>\MyS
QLBackup \ims_DB_utf8.sql"
Note: sed.exe can be found in the <mysql_upgrade_folder>\MySQL Msi\SedUtility\ folder.
After successful execution, it will create utf8 converted file by the name ims_DB_utf8.sql. Keep
this file in a safe place for future reference.
• Manually un-install the ODBC driver5.1 from the SapphireIMS Server hosted machine.
• Install ODBC 5.2 (ignore if ODBC 5.2 is already installed) support files by executing
vcredist_x86_2010.exe. Make sure to run the executable by selecting Run as Administrator
option.
Note: vcredist_x86_2010.exe can be found in the <mysql_upgrade_folder>\MySQL Msi\folder.
• Install the ODBC driver 5.2 on SapphireIMS Server is installed machine for below mentioned
setup types.
▪ Professional
▪ MSP Server
▪ MSP Probe
MySQL 5.5 to 5.7 Upgrade
Page 8
▪ Cluster Server
▪ Cluster Member
• Open Regedit to update the ODBC driver 5.2 path by editing the Driver key for all underlying
schema as shown in the Fig 3.1.
Fig 3.1: Configure ODBC driver key
• Similarly update the ODBC Data Sources to point to the ODBC 5.2 driver as shown in the Fig 3.2.
Fig 3.2: Configure ODBC Data Sources
➢ If ODBC driver 5.2 is installed on the server Utf8 changes are already applied on SapphireIMS database and hence utf8 data conversion is not
required, but need to make definer changes by executing the following command.
MySQL 5.5 to 5.7 Upgrade
Page 9
Note : MySQL Upgrade will change only DEFINER=`ims`@`` to DEFINER=`ims`@`localhost`, remaining
possibilities like some other definers found then need to handle accordingly by the end user.
cd <mysql_upgrade_folder>\MySQL Msi\SedUtility
sed.exe -e "s/DEFINER=`ims`@``/DEFINER=`ims`@`localhost`/"
<"<mysql_upgrade_folder>\MySQLBackup\ ims_DB_Dump.sql"
>"<mysql_upgrade_folder>\MySQLBackup \ims_DB_Backup.sql"
Note: Post MySQL 5.7 Server installation, you need to restore either <Schema Name>_DB_utf8.sql or
<Schema Name>_DB_backup.sql file present in the MySQLBackup folder.
3.5 Un-installing MySQL 5.5 Next step is to uninstall MySQL 5.5 Server. Follow the steps given below.
• Go to control panel and Un-install MySQL 5.5 Server as shown in Fig 4: Un-installing MySQL 5.5.
Fig 4: Un-installing MySQL 5.5
• After successful un-installation, go to SapphireIMS installed path and data folder path to clean up the
following MySQL Server 5.5 folder traces.
Note : This step is preferable after successful up-gradation of MySQL Server 5.7.
1. C:\Program Files (x86)\SapphireIMS\MySQL Server 5.5\
Note: This path may vary. Check the relevant MySQL Server installed path on the local machine.
2. C:\ProgramData\MySQL\MySQL Server 5.5\
3.6 Installing MySQL 5.7 Server • If a previous installation MySQL 5.7 Server is failed, then the MySQL installer may have left some
traces. Clean up the traces by deleting all the files and folders present in the following folders,
including the folders and then proceed with the server installation.
Note: Un-install MySQL Server 5.7 via control panel if an option exists. Post un-installation, delete the
following folders including their contents.
1. C:\Program Files (x86)\SapphireIMS\MySQL Server 5.7\
MySQL 5.5 to 5.7 Upgrade
Page 10
Note: This path may vary. Check the relevant MySQL Server installed path on the local machine.
2. C:\ProgramData\MySQL\MySQL Server 5.7\
• If MySQLUpgrade.bat file is failed to install MySQL Server 5.7, it means that MySQLUpgrade.bat file
had already installed the MySQL Community.
• So un-install the MySQL Community manually via control panel to avoid unwanted errors. Post un-
installation, delete the following folders including their contents.
1. C:\Program Files (x86) or Program Files\MySQL\MySQL Installer for Windows\
2. C:\ProgramData\MySQL\ MySQL Installer for Windows\
• Post that install MySQL Community Manually by referring the 3.3 Installing MySQL Community
section.
• Collect the information about the below mentioned parameters that will be used in the section to
follow.
Open the SIMS_MysqlUpgrade_log.txt file present in the <SapphireIMS Installed
path>\ConsoleManagement\ExternalWar\SoftwareRepository.war\MySQL5.7Upgrade\MySQLUpg
rade\Logs\ folder to find the information about MySQL Bit, MySQL Port, MySQL install directory,
MySQL Service Name and MySQL Data Directory as shown in the Fig 5.
Fig 5: SIMS_MysqlUpgrade_log.txt file
Example
MySQL Bit: x86 or x64
MySQL Port: 3306
MySQL Install Directory: C:\Program Files\SapphireIMS\MySQL Server 5.7
MySQL Service Name: SapphireMySQL
MySQL Data Directory: C:\Program Files\SapphireIMS\MySQL Server 5.7
MySQL 5.5 to 5.7 Upgrade
Page 11
For Professional, MSP Server, MSP Probe, Cluster Server and MySQL Stand-alone system
Install and configure the MySQL Server 5.7 as shown in the Fig 6: MySQL 5.7 installation and
configuration.
For Cluster Member and SapphireIMS Server without MySQL database
Simply install MySQL without configuration as explained in the following section.
Note: Choose either x86 or x64 bit mysql-5.7.17-xxx.msi installer available in
C:\ProgramData\MySQL\MySQL Installer for Windows\Product Cache\ folder depending on the
MySQLBit parameter available in the SIMS_MysqlUpgrade_log.txt file as shown in the Fig 5.
MySQL Bit: x86
msiexec /imysql-5.7.17-win32.msi INSTALLDIR=<Install Directory path> /passive
MySQL Bit: x64
msiexec /i mysql-5.7.17-winx64.msi INSTALLDIR="Install Directory path" /passive
➢ Run the following commands by replacing the variables with appropriate values as shown in the
Fig 6 to install and configure MySQL 5.7 server.
cd "C:\Program Files or Program Files (x86)\ MySQL\ MySQL Installer for Windows"
MySQLInstallerConsole.exe community install Server;5.7.17;<MySQL
Bit>:*:type=config;port=<MySQL Port>;slowlog=false;rootpasswd=ims123;installdir=<MySQL
Install Directory>;ServiceName=<MySQL Service Name>;datadir=<MySQL Data Directory> -
silent
➢ Upon successful installation of MySQL 5.7 server, you will see a success message printed on the
command prompt as shown in Fig 6.
MySQL 5.5 to 5.7 Upgrade
Page 12
Fig 6: MySQL 5.7 installation and configuration
➢ Upon successful installation of MySQL 5.7, edit the <SapphireIMS Installed path>/
ConsoleManagement/Database/Config.bat file and update the parameters imsMySQLPath and
imsMySQLPwd as described in the following section.
• Go to line containing imsMySQLPath and change to set imsMySQLPath="<SapphireIMS
installed path> \MySQL Server 5.7\bin"
• Go to line containing imsMySQLPwd and change to set imsMySQLPwd="ims123"
• Go to line containing imsMySQLDriver and change to set imsMySQLDriver="<SapphireIMS
installed path>\MySQL ODBC Connector 5.2.7\myodbc5w.dll"
After making above changes a sample config.bat will look as follows.
set imsMySQLPath="C:\Program Files (x86)\SapphireIMS\MySQL Server 5.7\bin"
set imsMySQLServer="localhost"
set imsMySQLUsrName="root"
set imsMySQLPwd="ims123"
set imsMySQLPort="3306"
set imsMySQLSchema=ims
set imsMySQLDriver="C:\Program Files (x86)\SapphireIMS\MySQL ODBC Connector
5.2.7\myodbc5w.dll"
Note: In MySQL Server 5.7 the default root password is ims123
MySQL 5.5 to 5.7 Upgrade
Page 13
3.7 Restoring Time Zone Changes Time zone changes can be restored by executing the following command as shown in the Fig 7.
Note: In Cluster member / SapphireIMS standalone setups ignore this step
cd "C:\Program Files or Program Files (x86)\SapphireIMS\MySQL Server 5.7\bin"
mysql-uims -pims123 mysql< "<mysql_upgrade_folder>\MySQL Msi\timezone_posix.sql"
Fig 7: Restoring time zone changes
3.8 Restoring my.ini Configuration Settings • Copy the following variable values backed up from MySQL Server 5.5 my.ini file and update the
respective variable values in MySQL Server 5.7 my.ini file and then restart SapphireMySQL service.
E.g. <SapphireIMS server installed path>/SapphireIMS/MySQL server 5.7/ folder.
1. max_connections 2. tmp_table_size 3. key_buffer_size 4. innodb_log_buffer_size 5. innodb_buffer_pool_size
• Apart from above changes, if any other parameters are customized in MySQL 5.5 my.ini file then similar changes must be done in MySQL 5.7 my.ini as well.
Note:
➢ In case of Cluster Setup, copy the my.ini from the cluster server and place it inside the "MySQL Server 5.7 directory" of the Cluster member.
➢ If MySQL installed in remote machine then copy the my.ini file from MySQL Server and place it inside the "MySQL Server 5.7 directory" of the SapphireIMS Server machine.
Location of MySQL Server 5.7 is " <SapphireIMS Installation Location>\ MySQL Server 5.7"
3.9 Restoring Database Backup All SapphireIMS database backup files can be found in the MySQLBackup folder as shown in the Fig 9.1:
Database backup files.
MySQL 5.5 to 5.7 Upgrade
Page 14
Fig 8.1: Database backup files
➢ Before starting the database restore, set max_allowed_packet variable by executing the
following command. This setting is mandatory until entire database restoration is completed.
set global max_allowed_packet = 1073741824;
Note: If you restart the MySQL Service then this variable will be revert back to its old value.
➢ Create database (schema) by executing the following command.
CREATE DATABASE ims;
➢ Restore the ims DB by executing either of the following commands as shown in Fig 9.2. If you
find that ims_DB_utf8 file is present (Refer to Fig 9.1), then need to restore
ims_DB_uft8.sqlinstead of ims_DB_backup.sql file.
mysql -uims -pims123 –f ims< "<folder path>\MySQLBackup\ims_DB_uft8.sql";
OR
mysql -uims -pims123 –f ims< "<folder path>\MySQLBackup\ims_DB_backup.sql";
Fig 8.2: Restoring ‘ims’ schema backup
MySQL 5.5 to 5.7 Upgrade
Page 15
➢ Similarly for MSP Setup, all customer files backed up during the Step 1 must be restored.
3.10 Securing MySQL Server MySQLUpgrade.bat will create pre-shipped users (ims, imsagent, imsweb and imshealth) without any
access restrictions. In order to restrict global access to the database, follow the steps given below.
Note: In Cluster member / SapphireIMS standalone setups ignore this step
There are two ways to restrict user access to the database.
▪ Configuring the bind address
▪ Adding access restrictions to the database users
• Configuring the bind address
This method is applicable for Professional setup alone. By configuring the bind address, we can apply
network restrictions on MySQL server. Follow the steps given below to configure the bind address.
➢ Choose bind-address
Correct bind address must be chosen by referring to the below section. This bind-address must
be used in all subsequent step below.
Professional setup (SapphireIMS Server and MySQL Server on same machine)
bind-address=127.0.0.1
Professional setup (SapphireIMS Server and MySQL Server on different machines)
bind-address=<IPAddress of the MySQL Server installed machine>
Enterprise setup
On Enterprise setups we cannot configure bind-address, so kindly proceed with the user
level restriction section described below.
➢ Update my.ini
Add a new line or update the existing line containing bind-address in the [mysqld] section of
my.ini file and restart the SapphireMySQL Service. In case SapphireIMS and MySQL server is
installed on different machines, after updating the bind address kindly proceed with the user level
restrictions.
Fig 9.1: Updating Bind-address in my.ini
➢ Update config.bat
Open <SapphireIMS installed path>/ConsoleManagement/Database/config.bat file and set
imsMySQLServer variable value to either localhost or <IPAddress>of the MySQL Server installed
machine.
MySQL 5.5 to 5.7 Upgrade
Page 16
E.g. If bind-address = 127.0.0.1 then set imsMySQLServer variable to localhost or if bind-address
= <IPAddress of MySQL Server installed machine> then set imsMySQLServer variable to
<IPAddress>
➢ Update standalone.xml
Open <SapphireIMS installed path>/WebManagement/standalone/configuration/standalone.xml
file and change lines containing jdbc:mysql to above selected bind-address.
E.g.: If bind-address = 127.0.0.1, change the line jdbc:mysql://172.16.11.1:3306 to
jdbc:mysql://localhost:3306
Fig 9.2: Updating Standalone.xml
➢ Update System DSN
Update System DSN using ODBC Data Source Administrator as shown in Fig 7(by running the
command C:\Windows\SysWOW64\odbcad32.exe). Select SystemDSN and select the System
Data Sources(‘ims’or Customer schema if any) and press Configure. Change TCP/IP Server as
either localhost or <IPAddress of MySQL Server installed machine>.
E.g. If bind-address = 127.0.0.1 then set TCP/IP Server to localhost or if bind-address = <IPAddress
of MySQL Server installed machine> then set TCP/IP Server to <IPAddress>
Fig 9.3: Updating System DSN
• User level Restrictions
By applying user level restrictions, we can restrict database access to only known hosts and there by
securing the database. Follow the steps given below to apply access restrictions on database users.
MySQL 5.5 to 5.7 Upgrade
Page 17
Note: Following commands must be executed by connecting to MySQL server as root user.
➢ Delete existing users by executing the following command.
DELETE FROM mysql.user WHERE USER IN ('ims', 'imsweb', 'imsagent', 'imshealth');
➢ Flush the privileges by executing the following command.
FLUSH PRIVILEGES;
➢ Create the users ims, imsweb, imsagent and imshealth by executing the following commands
after replacing the <IP Address>and<hostname>with appropriate IP address and host name of
the SapphireIMS server installed machine respectively.
➢ If SapphireIMS Server and MySQL Server both are in same machine then create users as given
below.
CREATE USER 'ims'@'localhost' IDENTIFIED BY 'ims123';
CREATE USER 'imsweb'@'localhost' IDENTIFIED BY 'ims123';
CREATE USER 'imsagent'@'localhost' IDENTIFIED BY 'ims123';
CREATE USER 'imshealth'@'localhost' IDENTIFIED BY 'ims123';
CREATE USER 'ims'@'<IP Address>’ IDENTIFIED BY 'ims123';
CREATE USER 'imsweb'@'<IP Address>’ IDENTIFIED BY 'ims123';
CREATE USER 'imsagent'@'<IP Address>’ IDENTIFIED BY 'ims123';
CREATE USER 'imshealth'@'<IP Address>' IDENTIFIED BY 'ims123';
CREATE USER 'ims'@'<hostname>' IDENTIFIED BY 'ims123';
CREATE USER 'imsweb'@'<hostname>’ IDENTIFIED BY 'ims123';
CREATE USER 'imsagent'@'<hostname>' IDENTIFIED BY 'ims123';
CREATE USER 'imshealth'@'<hostname>' IDENTIFIED BY 'ims123';
GRANT ALL ON *.* TO 'ims'@'localhost' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'imsweb'@'localhost'WITH GRANT OPTION;
GRANT ALL ON *.* TO 'imsagent'@'localhost'WITH GRANT OPTION;
GRANT SELECT,EXECUTE,PROCESS ON *.* TO 'imshealth'@'localhost';
GRANT ALL ON *.* TO 'ims'@'<IP Address>'WITH GRANT OPTION;
GRANT ALL ON *.* TO 'imsweb'@'<IP Address>' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'imsagent'@'<IP Address>' WITH GRANT OPTION;
GRANT SELECT,EXECUTE,PROCESS ON *.* TO 'imshealth'@'<IP Address>';
GRANT ALL ON *.* TO 'ims'@'<hostname>' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'imsweb'@'<hostname>' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'imsagent'@'<hostname>' WITH GRANT OPTION;
GRANT SELECT,EXECUTE,PROCESSON *.* TO 'imshealth'@'<hostname>';
MySQL 5.5 to 5.7 Upgrade
Page 18
➢ If SapphireIMS Server and MySQL Server are on different machines, then give access to the
SapphireIMS installed machine by executing the following commands.
CREATE USER 'ims'@'<IP Address of SapphireIMS installed machine>’ IDENTIFIED BY 'ims123';
CREATE USER 'imsweb'@'<IP Address of SapphireIMS installed machine>’ IDENTIFIED BY
'ims123';
CREATE USER 'imsagent'@'<IP Address of SapphireIMS installed machine>’ IDENTIFIED BY
'ims123';
CREATE USER 'imshealth'@'<IP Address of SapphireIMS installed machine>' IDENTIFIED BY
'ims123';
GRANT ALL ON *.* TO 'ims'@'<IP Address of SapphireIMS installed machine>'WITH GRANT
OPTION;
GRANT ALL ON *.* TO 'imsweb'@'<IP Address of SapphireIMS installed machine>' WITH GRANT
OPTION;
GRANT ALL ON *.* TO 'imsagent'@'<IP Address of SapphireIMS installed machine>' WITH
GRANT OPTION;
GRANT SELECT,EXECUTE,PROCESS ON *.* TO 'imshealth'@'<IP Address of SapphireIMS installed
machine>';
➢ Give access to all cluster member machines for all the users (ims, imsagent, imsweb, imshealth) by
executing the following commands.
CREATE USER 'ims'@'<IP Address>’ IDENTIFIED BY 'ims123';
CREATE USER 'imsweb'@'<IP Address>’ IDENTIFIED BY 'ims123';
CREATE USER 'imsagent'@'<IP Address>’ IDENTIFIED BY 'ims123';
CREATE USER 'imshealth'@'<IP Address>' IDENTIFIED BY 'ims123';
GRANT ALL ON *.* TO 'ims'@'<IP Address>'WITH GRANT OPTION;
GRANT ALL ON *.* TO 'imsweb'@'<IP Address>' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'imsagent'@'<IP Address>' WITH GRANT OPTION;
GRANT SELECT,EXECUTE,PROCESS ON *.* TO 'imshealth'@'<IP Address>';
Note: Where <IPAddress> is the IPAddress of the cluster member installed machines.
3.11 Executing Post Installation Steps After successful installation of MySQL Server 5.7, execute the steps given below.
▪ Delete the MySQL Server 5.5 folders
➢ Go to SapphireIMS installed path.
➢ Delete the MySQL Server 5.5 folder as shown in the Fig 10.
E.g. C:\Program Files (x86)\SapphireIMS\MySQL Server 5.5\
Note: This path may vary. Check the relevant MySQL Server installed path on the local machine.
➢ Delete the MySQL Server 5.5 folder from ProgramData folder.
E.g.C:\ProgramData\MySQL\MySQL Server 5.5\
MySQL 5.5 to 5.7 Upgrade
Page 19
▪ Uninstall MySQL community using control panel
Fig 10: Delete MySQL Server 5.5 folder
▪ Post database restore, in case of MSP Setup, update the customermaster table by executing the
following query in both ims and customer databases
update customermaster set DBPWD = 'aW1zMTIz' where DBUID = 'root';
▪ Post Restoration of database backup, run the mysql_upgrade.exe as given below. Open Command
Prompt as Run as Administrator, and execute the following command.
<MySQL Bin Path>\mysql_upgrade.exe -u <UserName> -p <Password>
4. Upgrade Steps (Linux) Follow the steps mentioned below to manually upgrade MySQL Server 5.5 to 5.7 on a Linux system.
4.1 Backing up my.cnf Configuration Settings Note down the following variable values from MySQL Server 5.5 my.cnf file. Post MySQL 5.7 installation and before restoring the database backup, update the respective variable values in MySQL Server 5.7 my.cnf file.
1. max_connections 2. tmp_table_size 3. key_buffer_size 4. innodb_log_buffer_size 5. innodb_buffer_pool_size
Note : Apart from above changes, if any other parameters are customized in MySQL 5.5 my.ini file then similar changes must be done in MySQL 5.7 my.ini as well.
MySQL 5.5 to 5.7 Upgrade
Page 20
4.2 Backing up MySQL 5.5 Schema This step involves taking a schema backup of MySQL 5.5 server.
Note: In Cluster member / SapphireIMS standalone setups ignore this step
• In case of Professional setup, you need to backup ‘ims’ DB alone.
• If the setup is MSP, then you need to backup both ‘ims’ and Customer Schema one by one.
• Before taking the backup, source AlterScript.sql file if ODBC 5.1 is installed. If ODBC 5.2 is installed
then ignore sourcing AlterScript.sql file.
Note: AlterScript.sql can be found in the <mysql_upgrade_folder>\MySQL Msi\ folder.
▪ In Professional / MSP Probe/ Cluster Server, on system where MySQL server is installed,
need to source this file in ‘ims’ schema. Open Command Prompt as Run as Administrator
and execute the following commands.
cd <SapphireIMS installed path>/MySQL Server 5.5/bin/
mysql -h<MySQL Server installed machine IPAddress>-u ims -p<ims password>ims
<<mysql_upgrade_folder>/MySQL Msi/AlterScript.Sql
▪ In MSP Server setup, on system where MySQL server is installed, source this file in customer
schema only (i.e. ‘ims’ schema must be excluded). Open Command Prompt as Run as
Administrator and execute the following commands.
cd <SapphireIMS installed path>/MySQL Server 5.5/bin/
mysql -h<MySQL Server installed machine IPAddress> -uims -p <ims password> <Customer
Schema Name> < "<mysql_upgrade_folder>/MySQL Msi/AlterScript.Sql"
Backup ims database:
• Execute the following command to backup ims database by connecting to MySQL Server remotely
from SapphireIMS installed machine. Make sure that entire schema is backed up and keep the
backup file in a safe place.
mysqldump -h<IPAddress of the MySQL Server installed machine>-uims -pims123 --events -R --
triggers ims > ims_db_Dump.sql
Backup customer schema:
• If the setup is MSP, then you need to backup Customer Schema one by one.
• Execute the following command to backup customer database. Make sure that entire schema is
backed up and keep the backup file in a safe place.
MySQL 5.5 to 5.7 Upgrade
Page 21
mysqldump -h<IPAddress of the MySQL Server installed machine>-uims -pims123 --events -R --
triggers [customer schema name] > [customer schema name]_db_Dump.sql
E.g. mysqldump -h172.11.12.01-uims -pims123 --events -R --triggers customer_1
>customer_1_db_Dump.sql
4.3 Executing Pre Installation Steps This step involves preparing the setup for MySQL Server 5.7 installation.
➢ Find the version of the MySQL ODBC driver installed on the server using any of the 2 methods listed
below.
Method 1:
Look for “MySQL ODBC Connector” folder in SapphireIMS installed location. You may find the version
number suffixed to the folder name.
E.g. If SapphireIMS server installed location contains a folder “MySQL ODBC Connector 5.1” then it
means that MySQL server ODBC driver 5.1 is installed.
Method 2:
Check Control panel for version of the MySQL ODBC driver installed.
➢ If ODBC driver 5.1 is installed on server
• Perform utf8 conversion changes by executing the following command.
Note : MySQL Upgrade will change only DEFINER=`ims`@`` to DEFINER=`ims`@`localhost`, remaining
possibilities like some other definers found then need to handle accordingly by the end user.
cd <mysql_upgrade_folder>\MySQL Msi\SedUtility
sed.exe -e "s/latin1_swedish_ci/utf8_general_ci/" -e "s/latin1/utf8/" -e
"s/DEFINER=`ims`@``/DEFINER=`ims`@`localhost`/" <
"<mysql_upgrade_folder>\MySQLBackup\ims_DB_Dump.sql">"<mysql_upgrade_folder>\MyS
QLBackup \ims_DB_utf8.sql"
Note: sed.exe can be found in the <mysql_upgrade_folder>\MySQL Msi\SedUtility\ folder.
After successful execution, it will create utf8 converted file by the name ims_DB_utf8.sql. Keep
this file in a safe place for future reference.
➢ If ODBC driver 5.2 is installed on the server Utf8 changes are already applied on SapphireIMS database and hence utf8 data conversion is not
required, but need to make definer changes by executing the following command.
Note : MySQL Upgrade will change only DEFINER=`ims`@`` to DEFINER=`ims`@`localhost`, remaining
possibilities like some other definers found then need to handle accordingly by the end user.
MySQL 5.5 to 5.7 Upgrade
Page 22
cd <mysql_upgrade_folder>\MySQL Msi\SedUtility
sed.exe -e "s/DEFINER=`ims`@``/DEFINER=`ims`@`localhost`/"
<"<mysql_upgrade_folder>\MySQLBackup\ ims_DB_Dump.sql"
>"<mysql_upgrade_folder>\MySQLBackup \ims_DB_Backup.sql"
Note: Post MySQL 5.7 Server installation, you need to restore either <Schema Name>_DB_utf8.sql or
<Schema Name>_DB_backup.sql file present in the MySQLBackup folder.
4.4 Installing MySQL 5.7 Server • Manually Un-install MySQL 5.5 Server from Linux machine.
• Install MySQL 5.7.17 Server manually on Linux machine according to the flavour of the Linux machine.
Note: Currently SapphireIMS supports MySQL Server Version 5.7.17.
4.5 Restoring my.cnf Configuration Settings • Copy the following variable values backed up from MySQL Server 5.5 my.cnf file and update the
respective variable values in MySQL Server 5.7 my.cnf file.
1. max_connections 2. tmp_table_size 3. key_buffer_size 4. innodb_log_buffer_size 5. innodb_buffer_pool_size
Note : Apart from above changes, if any other parameters are customized in MySQL 5.5 my.ini file then similar changes must be done in MySQL 5.7 my.ini as well.
• Restart the SapphireMySQL Service.
4.6 Creating database users
• Connect to MySQL Server 5.7 as root user on Linux machine and then execute the following commands
CREATE USER 'root'@'<IPAddress of MySQL Server installed machine>' IDENTIFIED BY 'ims123';
CREATE USER 'root'@'<HostName of MySQL Server installed machine>' IDENTIFIED BY 'ims123';
GRANT ALL ON *.* TO 'root'@'<IPAddress of MySQL Server installed machine>' WITH GRANT
OPTION;
GRANT ALL ON *.* TO 'root'@'<HostName of MySQL Server installed machine>' WITH GRANT
OPTION;
CREATE USER 'ims'@'%' IDENTIFIED BY 'ims123';
CREATE USER 'imsweb'@'%' IDENTIFIED BY 'ims123';
CREATE USER 'imsagent'@'%' IDENTIFIED BY 'ims123';
CREATE USER 'imshealth'@'%' IDENTIFIED BY 'ims123';
GRANT ALL ON *.* TO 'ims'@'%' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'imsweb'@'%'WITH GRANT OPTION;
MySQL 5.5 to 5.7 Upgrade
Page 23
GRANT ALL ON *.* TO 'imsagent'@'%'WITH GRANT OPTION;
GRANT SELECT,EXECUTE,PROCESS ON *.* TO 'imshealth'@'%';
4.7 Restoring Time Zone Changes Time zone changes can be restored by executing the following command as shown in the Fig 12.
cd "C:\Program Files or Program Files (x86)\SapphireIMS\MySQL Server 5.7\bin"
mysql-uims -pims123 mysql< "<mysql_upgrade_folder>\MySQL Msi\timezone_posix.sql"
Fig 12: Restoring time zone changes
4.8 Restoring Database Backup All SapphireIMS database backup files can be found in the MySQLBackup folder.
• Before starting the database restore, set max_allowed_packet variable by executing the following
command. This setting is mandatory until entire database restoration is completed.
set global max_allowed_packet = 1073741824;
Note: If you restart the MySQL Service then this variable will be revert back to its old value.
• Create ‘ims’ schema by executing the following command.
mysql -h<IPAddress of MySQL Server installed machine> -uims -pims123 -e "CREATE DATABASE
ims"
• Restore ‘ims’ Schema by executing either of the following commands.
mysql –h<IPAddress of MySQL Server installed machine> -uims -pims123 –f ims < "<Backup file
path>\ims_DB_uft8.sql";
OR
mysql -h<IPAddress of MySQL Server installed machine>-uims -pims123 –f ims< "<Backup file
path>\ims_DB_backup.sql";
• Similarly for MSP Setup, restore all customer backed up files by executing the following steps.
mysql –h<IPAddress of MySQL Server installed machine>-uims -pims123 –f <customer schema
name> < "<Backup file path>\<customer schema name>_DB_uft8.sql";
OR
MySQL 5.5 to 5.7 Upgrade
Page 24
mysql -h< IPAddress of MySQL Server installed machine> -uims -pims123 –f <customer schema
name> < "<Backup file path>\<customer schema name>_DB_backup.sql";
• Post Restoration of database backup, run the mysql_upgrade.exe as given below. Open Command
Prompt as Run as Administrator, and execute the following command.
<MySQL Bin Path>\mysql_upgrade.exe -u <UserName> -p <Password>
• Post restoration of Database backup Run MySQLUpgrade.bat in SapphireIMS installed machine.
4.9 Securing MySQL Server In order to restrict global access to the database and enable access to the SapphireIMS server alone,
follow the steps given below.
There are two ways to restrict user access to the database. Choose either of the following methods based
on the SapphireIMS setup type installed.
▪ Configuring the bind address
▪ Adding access restrictions to the database users
• Configuring the bind address
This method is applicable for Professional setup alone. By configuring the bind address, we can apply
network restrictions on MySQL server. Follow the steps given below to configure the bind address.
➢ Choose bind-address
Correct bind address must be chosen by referring to the below section. This bind-address must be
used in all subsequent step below.
Professional setup
bind-address = <IPAddress of the MySQL Server installed machine>
Enterprise setup
On Enterprise setups we cannot configure bind-address, so kindly proceed with the user
level restriction section described below.
• Update my.cnf
➢ Add a new line or update the existing line containing bind-address in the [mysqld] section of
my.cnf file and restart the MySQL Service. After updating the bind address kindly proceed with
the user level restrictions.
• User level Restrictions
By applying user level restrictions, we can restrict database access to only known hosts and there by
securing the database. Follow the steps given below to apply access restrictions on database users.
Note: Following commands must be executed by connecting to MySQL server as root user.
MySQL 5.5 to 5.7 Upgrade
Page 25
➢ Delete existing users by executing the following command.
DELETE FROM mysql.user WHERE USER IN ('ims', 'imsweb', 'imsagent', 'imshealth');
➢ Flush the privileges by executing the following command.
FLUSH PRIVILEGES;
➢ Create the users ims, imsweb, imsagent and imshealth by executing the following commands
after replacing the <IP Address>with appropriate IP address of the SapphireIMS server installed
machine.
CREATE USER 'ims'@'<IP Address>’ IDENTIFIED BY 'ims123';
CREATE USER 'imsweb'@'<IP Address>’ IDENTIFIED BY 'ims123';
CREATE USER 'imsagent'@'<IP Address>’ IDENTIFIED BY 'ims123';
CREATE USER 'imshealth'@'<IP Address>' IDENTIFIED BY 'ims123';
GRANT ALL ON *.* TO 'ims'@'<IP Address>'WITH GRANT OPTION;
GRANT ALL ON *.* TO 'imsweb'@'<IP Address>' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'imsagent'@'<IP Address>' WITH GRANT OPTION;
GRANT SELECT,EXECUTE,PROCESS ON *.* TO 'imshealth'@'<IP Address>';
4.10 Executing MySQLUpgrade.bat Follow the steps given below to execute MySQLUpgrade.bat on SapphireIMS Server installed machine.
• Unzip the MySQLUpgrade.zip file.
• Download mysql-installer-community-5.7.17.0.msi from the following link and copy
to<MySQLUpgrade Folder path>\MySQLUpgrade\MySQL Msi\ folder.
(https://downloads.mysql.com/archives/get/file/mysql-installer-community-5.7.17.0.msi)
• Open a new command prompt as "Run as administrator", then execute the MySQLUpgrade.bat batch
file.
E.g.: <MySQL5.7Upgrade Folder path>\MySQL5.7Upgrade\MySQLUpgrade\MySQLUpgrade.bat
• Post Upgrade to MySQL 5.7, Copy the my.cnf file from Linux (MySQL Server installed) machine and
paste it inside the "MySQL Server 5.7 directory" of the SapphireIMS Server machine and change the
extension to .cnf to .ini .
top related