I am performing a MySQL upgrade on a system that had MySQL installed using the noinstall Zip archive approach. (It's installed in c:\mysql). In order to keep with this approach, we are avoiding the MySQL Installers and MSI's. More information on noinstall Zip Archives can be found here.
We were upgrading from a lower 5.5.* version to 5.5.52, and the upgrade was a success for the most part. As directed (and recommended) in this documentation, we simply copied the 5.5.52 install over the existing c:\mysql directory.
In doing so, we copied over (overwrote) everything, including the \data\mysql\* files that were in the 5.5.52 installation package, which includes the default system files, such as user table files (i.e. user.frm, user.MYD, user.MYI), and many other various tables, and artifacts.
Due to these system tables and artifacts being written over, we lost our existing database MySQL users. i.e. the only users in the table are the root users with empty passwords.
There's probably other system level details that were stored in the MySQL tables that we lost.
(Thankfully this was our QA environment, and we did take a mysqldump beforehand)
Question:
Is there a way to avoid this data loss (i.e. not copy over the data directory), and still maintain the integrity of the upgrade?
For example could we skip overwriting the data directory altogether? Or perhaps skip it, and run the mysql_upgrade tool (which we need to run anyways), though I'm not sure if this is relevant to this context.
Best Answer
After talking to some MySQL admins, I came to a conclusion on how to go about this. We can do the below steps:
mysql_upgradeHowever, you may notice that there's no conceptual difference between the steps above, and simply not copying over the data file as part of the installation.
If you avoid copying over the data file, the steps become the following:
datafolder.mysql_upgrade