Upgrade from MySQL 5.7 to 8.0

PDF

This topic explains how to upgrade from MySQL 5.7 to 8.0 using the Windows ZIP Distribution method. This method enables retailers to easily upgrade MySQL without the need to export and later reimport the database. After performing the cross-version upgrade from 5.7 to 8.0 using the Windows ZIP distribution method, you can upgrade to a more recent version of 8.0 by running the installer for that version (e.g., 8.0.35). Important! The most current version of MySQL (8.2) is not compatible with Prism. More information can be found at https://dev.mysql.com/doc/refman/8.0/en/windows-upgrading.html 

Considerations and Limitations
The process described here does not rename the root folder containing the files or remove registry keys. For example, after a successful upgrade, users will  see a "C:\Program Files\MySQL\MySQL Server 5.7" folder in Windows Explorer and references to 5.7 in MySQL Workbench.
Before you Begin

• Unlink replication profiles in Prism Connection Manager. After the process completes, relink the replication profiles.
• Download the Windows ZIP Archive Distribution for a version of 8.0.  In our example, we use MySQL 8.0.30. Link: MySQL :: Download MySQL Community Server (Archived Versions)
• Download Microsoft Visual C++ 2015-2019 Redistributable x64 from https://aka.ms/vs/16/release/vc_redist.x64.exe

Sample MySQL Archived Versions download page:

Step 1. Stop MySQL and Prism services
Stop the MySQL service and all Retail Pro Prism services.

Sample Retail Pro Prism Services being stopped:

Step 2. Extract Windows ZIP Archive Files
Extract all the files from the downloaded Windows ZIP archive. Copy all the files to the existing MySQL installation folder (C:\Program Files\MySQL\MySQL Server 5.7\) and replace (overwrite) the existing files. Because the files you are adding have the same name as existing files, a prompt asks whether to replace or skip those files. Select "Replace the files in the destination."

If prompted to provide administrator permission, select the "Do this for all current items" checkbox and click Continue.
 
Step 3. Edit my.ini File
Certain options in the existing my.ini file are not compatible with the MySQL version 8.0 you just installed. Modify the my.ini file located at C:\ProgramData\MySQL\MySQL Server 5.7\
Comment out character-set-server, query_cache_type, query_cache_size entries (put a # in front of each setting; not all settings may be present).
#character-set-server=utf8
#query_cache_type=1
#query_cache_size=1M

Next, remove the NO_AUTO_CREATE_USER option in the sql-mode entry.
Before:
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
After:
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

Finally, the "innodb_buffer_pool_size" param in mysql.ini SHOULD NOT be empty. The comments within the mysql.ini file provide detailed information about the InnoDB buffer.
Save and close my.ini file.

Step 4. Install Visual C++ Redistributable
Install Microsoft Visual C++ 2015-2019 Redistributable x64. If you didn't download it already, it can be downloaded here: https://aka.ms/vs/16/release/vc_redist.x64.exe

Step 5. Restart Prism Services.

Step 5. Restart MySQL Service
Restart the MySQL service. This process may take some minutes and may even fail with an error stating that the MySQL service failed to start in a timely manner. If the process fails, click OK to close the error. The service needs more time to start.

Wait a minute and then start MySQL Workbench.
Click the desired connection (e.g., localhost).
Enter MySQL login credentials and click OK.

You should see the rpsods database schema on the left, an indication of a successful connection.

Click Server Logs and scroll to near the end of the log. You should see a confirmation that the upgrade from 5.7 to 8.0 is complete.

Click Server Status to verify the version and other information.

Step 6. Verify MySQL Server operates smoothly
BEFORE upgrading Prism or running the Prism Server installer:
Make sure the MySQL Server runs smoothly without any errors. Stopping & starting MySQL server 8.0 should take the normal time (less than 30 secs). If the service doesn't start in 30 seconds then the Prism installer may fail to establish connection with MySQL server during DB update process.

Step 7. Restart Prism Services.

Silent Install of Prism 2.1 Server (MySQL) via Command Line
(The Silent Install feature is available for Prism 2.0 and above. It is not available for Prism 1.14.7 and earlier.)

You can install Prism in "silent" mode by creating a scheduled task that runs the install scripts from the command line. Each Prism component has its own installer; therefore, you will have to create a scheduled task for each component that you want to install. The installers are found in the "Prism_[version}\[component]" folder of the Prism setup files. The following table lists the installers.

Installer Files

Example Script
MySQL
C:\Win32PrismServerInstaller.exe  /SkipVCInstall=Yes/UseMySQL=Yes/MySQLRootUsername=root /MySQLRootPassword=root /MySQLServerFolder="C:\mysql-5.7.29-win32\" /MySQLServerIni="C:\ProgramData\MySQL\MySQL Server 8.0\" /VERYSILENT /SUPPRESSMSGBOXES /LOG="c:\Win32PrismServerInstaller.log" /ClientID=12345 /RegKey="[SA Key]"

General Installer Parameters

Additional MySQL Parameters