Updating MySQL Windows Server to new release version.

I spent a lot of time researching how to upgrade our MySQL windows server from version 5.5 to 5.7 there are indeed many resources spouting the best way to achieve it.

The biggest problem with this type of upgrade is the fact that the underlying system tables can (and do ) vary between the different versions.

We initially followed the advice and documentation from MySQL using the Windows Zip distribution option. This being an abject failure we made further searches to find more and more complicated solutions.

The failure was after copying the files over the existing files the MySQL service would not restart.

It all got very complicated so in the end we used another server to set up a test environment to ensure we could carry out the upgrade without major service interruption.

After various attempts we found the following procedure completed the upgrade successfully. This process would work for either moving servers of upgrading the same server.

Thanks to the thread at Serverfault  and Tom (his comment is buried way down list of solutions) for pointing us in the right direction (I’ve not got enough points on their website so so can’t comment directly, their loss!)

Using a command prompt take a backup of all databases, use this option as it also backs up the tables in mysql  database which includes the users and their security settings:  “mysqldump -u root -p –all-databases > dbdump.sql”

Stop MySQL service (if updating same server)

Use MySQL Installer to add new version of MySQL, this step will fail if you attempt it without stopping any running MySQL on the server. Use the same root password as currently set on the old version. this process will leave you with a running MySQL service.

Import the exported Databases using “mysql -u root -p < dbdump.sql”

We stopped and restarted the MySQL server after this import although not sure if this step is required. Use “Net Stop MySQL57” and then “Net Start MySQL57”

Our testing showed this imported all the databases and users with their associated privileges however the User table is effectively corrupt at this point.

This it is essential to run “mysql_upgrade -u root -p” on the new instance of MySQL. This runs through the tables and updates any inconsistencies in the table structures.

Thus in brief do the following

• mysqldump -u root -p –all-databases > dbdump.sql
• Net Stop MySQL
• Run MySQL Installer use Add and select MySQL Server appropriate version, as part of the MySQL Installer routine add user root with existing password
• mysql -u root -p < dbdump.sql
• Restart MySQL
• mysql_upgrade -u root -p

Doing this meant our production server was offline for no more than 10 minutes tops.

Hope this helps you, I’ve documented it so I’ll remember it for future releases.

Potential gotchas:

• When you install the new MySQL using the installer it does not appear to add any statements to the server PATH variable thus mysql statements will fail unless you are on the MySQL bin directory (C:\Program Files\MySQL\MySQL Server 5.7\bin) or you’ve added this to the PATH statement.