Updated: September 28, 2022 10:25am

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,  users can upgrade to the most current version of MySQL 8.0 (8.0.29 at current time), by running the installer for that version. 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

Sample MySQL Archived Versions download page:
MySQL archived version download

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

Sample Retail Pro Prism Services being stopped:
Prism service stop

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."
replace files message
If prompted to provide administrator permission, select the "Do this for all current items" checkbox and click Continue.
Grant folder access 
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.

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.
MySQL Server fail to start
Wait a minute and then start MySQL Workbench.
Click the desired connection (e.g., localhost).
Enter MySQL login credentials and click OK.
MySQL workbench login
You should see the rpsods database schema on the left, an indication of a successful connection.
rpsods schema on left

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.
MySQL Server log showing upgrade to 8.0 complete
Click Server Status to verify the version and other information.
MySQL Server status

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.

restart prism services

Silent Install of Prism 2.1 Server (MySQL) via Command Line
You can install Prism 2.1 in "silent" mode by creating a scheduled task that runs the install scripts from the command line. Each Prism component has its own installer. 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

Installer Description
ApacheInstaller.exe rism_[version}\Apache has the Apache web server installer.
Win32PrismServerInstaller.exe Prism_[version}\Server has the Prism Server installer.
Win32PrismProxyInstaller Prism_[version}\PrismProxy has the Prism Proxy installer.
Win32PrismDocumentDesignerInstaller.exe Prism_[version}\DocumentDesigner has the Document Designer installer.

Example Scripts
C:\Win32PrismServerInstaller.exe  /SkipVCInstall=Yes/UseMySQL=Yes/MySQLRootUsername="root" /MySQLRootPassword="root" /MySQLServerFolder= /MySQLServerIni= /VERYSILENT /SUPPRESSMSGBOXES /LOG: "c:\Win32PrismServerInstaller.log"

  • The path to the 32-bit binaries is typically something like /MySQLServerFolder="C:\mysql-5.7.29-win32\"
  • The path to the mysql.ini file is typically something like /MySQLServerIni="C:\ProgramData\MySQL\MySQL Server 8.0\"

MySQL Command Line Parameters

Parameter Description
/MySQLRootUsername="SomeUserName" Valid MySQL username
/MySQLRootPassword="SomePassWord" Password for the specified username.
/MySQLServerFolder={Path to .MySQL folder} Default = C:\Program Files (x86)\MySQL\MySQL Server 5.6\"    Substitute the path to the /MySQL folder, if different
/MySQLServerIni={Path to .ini file.} Path to MySQL.ini file. Substitute the correct path if in a different location than the default. Default = C:\ProgramData\MySQL\MySQL Server 5.7\
/DELETEPRISMCERTIFICATE=YES This parameter should be included if running the Prism Server Installer or Prism Proxy Installer on an installation that has the deprecated Retail Pro self-signed SSL certificates installed. Those certificates should be removed and replaced with your own SSL certificates (added via SSL Manager in Tech Toolkit).
/ClientID=""  If the ClientID is set that value is automatically added to the Licensing page.
/RegKey="" If the RegKey is set that value is automatically added to the Licensing page.
/SkipEULA=Yes  If the SkipEULA param is set to Yes the EULA page is skipped.