Summary steps:
- Database must be exported from MySQL 5.7 by Buymanager or mysqldump.exe
- Both old MySQL (5.7) and new MySQL 8 will be running on the same machine.
- Buymanager will be disconnected from MySQL 5.7 and reconnected to MySQL 8
- Database will be imported to MySQL 8 by Buymanager or mysql.exe
- Connect MySQL Workbench to MySQL 8
- Update to Buymanager latest release (at least 5.3 full 64-bit)
Prequisite:
- Make sure Users should not use Buymanager once the database is extracted, as it will not contain any updates made after that.
- Check the database and server size: the server space needs to be 3 times bigger than the database: You can do so by right-clicking on the database folder and then properties.
- Computer/Server CANNOT go on sleep mode/ screen saver during the export/import of DataBase.
MySQL database default location: C:\ProgramData\MySQL\MySQL Server 5.7\Data
- If you can’t find your database in the MySQL default folder, you can open Workbench >Option file > datadir [Please check]
- Make sure you have the administrator rights for the whole process.
- Downloads:
1/ Download MySQL 8.4.0: https://dev.mysql.com/get/Downloads/MySQL-8.4/mysql-8.4.3-winx64.msi
OR:
Go to https://dev.mysql.com/downloads/mysql/
Choose version 8.4.2 LTS, and download
Click on the link for downloading (no need log in or sign up)
2/ Download MySQL Workbench 8.0 CE: https://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-workbench-community-8.0.40-winx64.msi
OR
Go to: https://dev.mysql.com/downloads/workbench/
3/ Download the latest version of Buymanager: http://www.pertilience.net/buymanager/wp-content/Version/5_3/
- Allow the addition of "network service" rights on the Buymanager and MySQL directories on the servers. The server network infrastructure must allow Buymanager to:
- Connect to the internet to access the Webquote server Webquote http://rqt.buymanager.biz/ Port: 443
- Send emails directly by Outlook or via an SMTP server
- Optional: Install the newest version of Excel and Outlook on the new server.
Upgrade process:
- Data export
- With BuyManager:
- With CMD and mysqldump.exe
Open CMD in admin mode, navigate to ExeMySQL folder (cd C:\Program Files\Buymanager) and run : mysqldump --max_allowed_packet=1024M --default-character-set=utf8 -uBuymanager -pBuymanager [database name] > [file path].sql
- MySQL 5.7 port modification
The standard port for MySQL is 3306, and 2 versions of MySQL can't cohabit if they have the same port. According we want to give the default port to the new version of MySQL, we must change the old MySQL port.
Steps:
- Change old MySQL port to 3305: Change my.ini (typically in C:\ProgramData\MySQL\MySQL Server X.Y\) => port=3305 (maybe several places in the my.ini)
- Restart service MySQL
- Modify MySQL Workbench settings (change access port and rename to "Local instance MySQL 5.7")
- Test workbench
Please note: at this step, Buymanager can't work anymore with MySQL 5.7, that's why we exported data before
- MySQL 8 installation
In MySQL Server Installations, choose the second option and then browse to choose a folder for databases or keep the default folder.
Keep the default port 3306
Configuration type :
- Dedicated Computer if only MySQL is installed on this VM/Server
- Server Computer it hosts MySQL and other softwares, as Buymanger / Remote desktop
Root password : Buymanager
Create a new user Buymanager, password: Buymanager
- Modify MySQL engine parameters
The my.ini file is usually located here: C:\ProgramData\MySQL\MySQL Server 8.4
Please note: It must be opened in administrator mode to be able to modify and save it.
- max_allowed_packet=100M (instead of 64M)
- innodb_buffer_pool_size=2048M (instead of 128M)
- innodb_redo_log_capacity=512M
- Sql-mode="STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION" (remove ONLY_FULL_GROUP_BY from the list)
After modification, MySQL 8 service must be restarted.
- MySQL workbench upgrade
After downloading MySQL workbench, you have to execute the installation file.
When the installation is completed, a new connection must be created:
- Name the connection and fill in the hostname (IP adress) and port number (3306)
- You can test if the connection is successful by clicking on “Test connection”.
- Data import
- With BuyManager:
On the opening window, click on the roll down menu and choose “New connection”
Make sure to check “MySQL Version >= 8” and hit next.
- With MySQL Workbench / CMD
- Open MySQL Workbench and connect to the instance MySQL 8.4
- Create new query table
- Type : create database [Database name]
- Execute
- Open CMD, navigate to ExeMySQL folder (cd C:\Program Files\Buymanager) and run :
mysql --max_allowed_packet=1024M -uBuymanager -pBuymanager --default-character-set=utf8 [Database name] < [file path].sql
Please note: If you update 8.2 > 8.4 and you have password issue "Failed to Connect to MySQL at 127.0.0.1:3306 with user root" Plugin 'mysql_native_password' is not loaded, you have to add this line to “my.ini” just under [mysqld] :
Mysql_native_password=ON