Upgrading to MySQL 8

[ooshnoo]

We plan up on updating JamfPro to v10.11, and ulitmately MySQL 8.

Does anyone have any experience upgrading to MySQL 8? Did you use inplace upgrade install, or another method?

[koalatee]

Just upgraded dev from 5.7.24 > 8.0.15

Pretty much have instructions from this site that still work.

I take backup, uninstall old mysql, install new mysql, make modifications to config file as outlined in jamf article, create db/user, restore backup

I had some additional config modifications from previous threads/years, but I'm going to try without for now...

[luke_michelson]

I just upgraded from Jamf Pro 10.9 to 10.11.1 and MySQL 5.7 to MySQL 8.0.15. This is on a Windows 2012R2 server.

Steps I took (this was with a Jamf engineers direction):
1. Stop Tomcat
2. Take a backup with JSS Database Utility
3. Upgrade Jamf Pro (note I had already converted my DB to InnoDB)
4. Start Tomcat and verify Jamf Pro is operating as expected
5. Stop Tomcat
6. Take a backup with JSS Database Utility
7. Uninstall MySQL 5.7 - I had to disable the Tomcat and MySQL services from starting at boot-up, then rebooted server. Then I could uninstall MySQL
8. Install MySQL 8.0.15 - during install I did not create an additional user, but did after finishing the install through the command line. Note the Authentication Method used was "Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)"
9. Open the MySQL Command prompt and executed the following SQL:

create database <dbname>; #this is the jamfsoftware DB
create user '<user name>'@'localhost' identified by '<password> ';
grant all on <dbname>.* to '<user name>'@'localhost';

10. Open JSS Database Utility and restore backup, You'll have to point to the new DB location and give it the user name and password you created
11. Once the DB is restore, in MySQL Command prompt execute the following to revoke all privileges from the user created:

revoke all privileges, grant option from '<user name>'@'localhost';

12. Verify and flush privileges:

show grants for '<user name>'@'localhost';
flush privileges;

13. Grant, verify and flush privileges:

grant insert, select,update,delete,create,drop,alter,index,lock tables on <dbname>.* to '<user name>'@'localhost';
show grants for '<user name>'@'localhost';
flush privileges;

14. Enable Tomcat services to automatic and restart Tomcat.
15. Verify Jamf Pro is functioning as expected

Definitely test, test and re-test in a development environment first.

Edit to add: I needed to edit the DataBase.xml file located under Web-INFxml to update the database information stored in the file.

[ooshnoo]

Thanks guys. @koalatee can you clarify what Jamf article you were referring to?

[koalatee]

This one primarily: https://www.jamf.com/jamf-nation/articles/542/manually-creating-the-jamf-pro-database

[CypherCookie]

@luke.michelson what version of MySQL 5.7 did you upgrade from? I'm on 5.7.26 GA release and trying to run the MySQL 8.0.15 & 8.0.16 installers aren't allowing me to put an in place upgrade in :(

[luke_michelson]

@CypherCookie You can't do an in place upgrade of MySql 5.7 to 8.X. You have to uninstall MySQL 5.7 (I don't remember which version it was, it was installed when I installed Jamf Pro 10.9) then install MySql 8.0.15

[CypherCookie]

yeah I found that out the hard way! thanks for the info above though was very useful!

[benducklow]

I'd be curious for those using RHEL to host their MySQL instance, what they did as well. I would prefer to do an in-place upgrade if possible, but don't have any experience in this area..

[mark_mahabir]

I just upgraded to v8.0 successfully (from v5.7, on our Jamf Pro v10.12.0 instance) using a combination of the steps mentioned earlier in the thread, so many thanks all.

I used the v8.0.16.0 installer to remove the existing v5.7 instance, reboot, and then install a new v8.0 instance. I found that some of the changes I'd made to the my.ini file in the past were no longer appropriate, and those actually prevented v8.0 starting correctly - so I just went with the defaults that the installer created and all seems well so far. The only real issue I had was remembering what password I had originally set for the jamfsoftware DB!

[bradtchapman]

BTW Jamf has published a KB article for this:

https://www.jamf.com/jamf-nation/articles/698/upgrading-to-mysql-8-0

[Ken_Bailey]

Just curious on size of MySQL DB on 5.7 and how long it took you to restore to 8.0.

[Drewkennedy]

https://www.jamf.com/jamf-nation/articles/698/upgrading-to-mysql-8-0