Upgrading from MySQL 5.7 to 8.0 on Windows

As you may know, I’m using MySQL exclusively on GNU/Linux. To be honest for me it’s almos 20 years that the year of Linux on the desktop happened. And I’m very happy with that.

But this week-end, I got a comment on an previous post about upgrading to MySQL 8.0, asking how to proceed on Windows. And in fact, I had no idea !

So I spent some time to install a Windows VM and for the very first time, MySQL on Windows !

The goal was to describe how to upgrade from MySQL 5.7 to MySQL 8.0.

So once MySQL 5.7 was installed (using MySQL Installer), I created some data using MySQL Shell:

Of course I used latest MySQL Shell, 8.0.18 in this case. Don’t forget that if you are using MySQL Shell or MySQL Router, you must always use the latest 8.0 version even with MySQL 5.7.

Before upgrading, I ran MySQL Upgrade Checker to be sure everything is compatible with MySQL 8.0:

No problem, I’m good to go !

Don’t forget, now it’s the right time to perform a backup or a snapshot.

The first step is to stop the MySQL Service:

When done, you have to launch once again the MySQL Installer and use Modify your MySQL 5.7 Server product features to only leave the Sever data files checked:

When all is proceeded, you return to the MySQL Installer Product Overview and you Add a new product:

We select the latest MySQL 8.0 and there is no need to select Server data files, as we will upgrade our current data:

When is done, please stop the new MySQL80 service and modify the my.ini (of MySQL 8.0!) that is located in System\ProgramData\MySQL\MySQL Server 8.0 by default:

In that file, we modify the actual value of datadir and we point it to where was located the datadir of MySQL 5.7. In this example I only used the default values:

And now it’s the most tricky part of the upgrade process, when you save this file, you must specified the ANSI encoding:

If you don’t use the right encoding, when you will start the MySQL Service, in the error log you will have a message like this: [ERROR] Found option without preceding group in config file ... at line 1

When saved, you can start the service. It will take some times as MySQL will proceed to the upgrade of the system tables and other files as you can see in the error log:

When the service is running again, you can connect and you should have access to all the data that was in you previous MySQL 5.7:

As you can see the test schema is there and of course we can check the content too:

I hope this post answers the questions of those that were experiencing issues when trying to perform an in-place upgrade from MySQL 5.7 to MySQL 8.0 in Microsoft Windows.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

22 Comments

  2. Excellent post, thank you so much for taking time covering an upgrade of MySQL running on Windows. I was wondering whether I can use similar approach to convert existing Community Edition to Standard Edition. Same rersion, 5.7.12. I have 2 MySQL running on Windows as services (different ports) with numerous databases under each. Can I install standard edition as 3rd MySQL service and modify my.ini for it to put datadir of existing databases? Ports swap too, if possible, since those community edition databases are used by many apps specifying that particular port in config connection string. Many thanks!

  3. Thanks for the article. Would overwriting the Backup, Data, and Uploads folders from ProgramData\MySQL\MySQL Server 8.0 with the Backup, Data, and Uploads folders from ProgramData\MySQL\MySQL Server 5.7 work? If so, would that mean there would be no need to edit my.ini in the MySQL Server 8.0 folder? Just looking for a way to make this cleaner so if there is a version upgrade again in the future I won’t have to keep pointing to older and older versions of the server folder. Thanks!

  4. Thank you for this excellent article. Could you please clarify if I need to go thru the configuration part of the installation or just Cancel that part and make the changes on the my.ini file?

    I successfully tried your steps on two servers (Windows 2012R2 5.7 and Windows 2008R2 5.6 all the way to 8) but when I went to upgrade the production (Windows 2008R2&2012R2 5.5.23) server I received the 1067 error. I made sure the service is running as Local Account and the my.ini file with the skip-grant-tables is pointing to the right folders.

    I successfully ran the mysql_upgrade –user=root –password command with the skip-grant-tables in the my.ini but the Control Panel doesn’t show the MySQL 5.5.60 installation.

    Are you available for hire to help me with this migration?

    Gratitude

    • I found out the issue. The instance I had was implemented nine years ago and the my.ini file was missing the lower_case_sensitive = 1. Make sure this is set for Windows.

  5. Excellent step by step guide to the MySql 5.7 to 8.0 upgrade process on Windows. We recently upgraded production to 8.0 (Linux VM) which required an ODBC update to Win10 to support MsAccess legacy frontend and the update of a local MySql Win10 instance from 5.7 to 8.0. It may be worth noting the V8 installer may hang on Win10 (use the PowerShell command-line eg .\mysql-installer-community-8.0.11.0.msi). I would also avoid editing my.ini but by doing so you can avoid moving datafiles.

  6. Hi,
    Thanks a lot for this article. The steps worked like a charm.
    I’ve this question. Is it possible to delete Server data files of MySQL upon upgrade and when MySQL 8 is already running and using old databse?

    Thanks!

    • It depends of your package manager. If you use yum/dnf it will replace the old binaries. If you don´t, after upgrade, you can delete them.

  7. This is a very amazing step by step guide upgrade mysql from 5.7 to 8.0 for windows 10. I just follow your steps and I successfully upgraded mysql to 8.0. Actually I immediately need to execute those queries in which PARTITION is used. So now happy to execute those queries even without losing my data…

    Thank You…!!!!

  8. works like a charm! this is very practical and clear as a child can do a migration with your instructions mate. Thanks a lot !

  9. Hi.
    Your tutorial helps me to upgrade from 5.7.33 to 8.0.23 Thank you very much. But now i would like to upgrade to 8.0.31 and it’s impossible. Can you help? I know the datafiles are on 5.7.33. but when i try to upgrade mysql to 8.0.31 at the end the process is failing and there are no files anymore. (Sorry for my english, i’am fromGermany).
    Cheers, Fabinello

      • Thank you for your Reply. I had an openssl issue with hmailserver and i upgraded it to 1.1.1q(same version with mysql 8.0.31). I still get trouble at the end of the upgrade, i stop the mysql service and it does not start autom. in the upgrade process so the upgrade fails. Same if i do it manually. Do i have to upgrade mysql 5.7.33 to 5.7.40 to(before while open ssl 1.1.1q)? I would like to have the server to 8.0.31 and the data files to 5.7.40 but the upgrade process does not finish properly and so i can’t start hmailserver. Do i have to change the my.ini with the data directory file to upgrade to 8.0.31 and rechange it after upgrade? i will copy some logs an post them soon. Cheers,

        • Back again. FYI. I had after upgrading from 8.0.23 to 8.0.31 SSL connection problems. The very simple workaround is to update the openssl binaries and set the environment path(forgot it). After a reboot all connections was successfull and green. Cheers,

  10. I just upgraded mysql 5.7 to 8.0.32 following your steps and it worked like a charm. Thanks so much.
    However after upgrade, I’m unable to connect from remote connections. None of the users can. I believe there is some step missing in the end to configure connections as per version 8

      • None of the remote connetions could be possible after the upgrade, there was no specific error just usual unable to connect. It turned out the plugin authentication_windows was missing. Once installed it was fixed.
        mysql> INSTALL PLUGIN authentication_windows SONAME ‘authentication_windows.dll’;
        Ive done a lot of upgrades lately and most of them have run into some kinda connection issues. Someone may need to write a thorough article to get all of it checked out once mysql 8 installation/upgrade is complete.

Leave a Reply

Your email address will not be published. Required fields are marked *

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.

Continue