Recover MySQL Databases – Handling Corruption on Windows Server

MySQLrecoverywindows

I have a (Virtualized/Hyper-V) MySQL 8 (8.0.16.0) DB server whose installation has become corrupt and will no longer start the service, or report any errors as to why it won't start (this is a virtual Windows 2008R2 server).

So I went and created a brand-new, duplicate, VM from scratch, installed (the same version of) MySQL on it, and am now trying to figure out how to get the tables from the old server into the new one.

If I simply copy the entire Data folder over, as is the process described in virtually every article I've found on the subject, the new instance also won't start (exactly like the old one won't) but if I then copy a pristine MySQL.ibd file (from the clean installation) into the migrated Data folder, the service will, once-again, start normally, but none of the copied Databases/Tables will exist in it.

All I want/need to do is get the shiny, new, clean, MySQL to see and incorporate all of the old databases from their original files, but after more than a day of reading many guides on how to do this (from here and elsewhere), I have not been able to find a solution that works.

So here I am, asking again, in 2020, for MySQL 8(.0.16.0), on Windows 2008R2, what exactly do I need to do in order to fully recover these tables into a new server when some elements of the base structure are corrupt/invalid?

Thanks!

Best Answer

Well, after three days of fighting with this, I have found a means of recovering at least some of the data:

With a lot of searching, I was able to locate an old virtual machine in the archives that still had a working copy of MySQL (5.x) that contained a version of one of the databases that I need to recover. From that resource I was able to generate all of the appropriate CREATE statements for all of the associated Schemas and Tables. So then, armed with my fresh server, and a new installation of MySQL, and all of the CREATE statements, I was finally able to regenerate all of the schemas and (empty) tables that this customer uses. So, upon shutting down the MySQL server service, and copying only the specific directories for that customer's databases into the new Data folder, then re-starting the service, the databases were, miraculously, fully restored!

IDK if it matters or not, but all of the schemas this particular customer had were MyISAM tables; I haven't yet tried this technique with any InnoDB tables, but this, at least, is progress, of a sort! Also, I don't know that I'll be able to find legacy functioning VM's for all of the schemas that I need to recover (and that are up to date/accurate!) so there's still a lot of work ahead...

I find it stunning that there's no simple tool for analysing a backed-up raw MySQL Data folder and then integrating the contents thereof onto an active server. You'd think that such a tool would have tremendous value for innumerable people in situations like this!?

Anyway, back into the archives!

Related Question