Note: Read this entire set of information multiple times before actually doing anything – you can really do some damage if you don’t know what you’re doing. If in doubt, speak to an expert.
Well, this isn’t something you have to do on a regular basis (thankfully!) but you’ll probably need to do this at some point when things go a little wrong.
So, a bit of setting the scene, for myself using Windows 10 for a development machine, all of a sudden something odd happened which looked like either a temporary hard drive failure / blip and / or an automatic Windows Update that didn’t quite work as expected (more than likely…). After not being able to boot onto the machine at all, this resulted in me having to completely re-install Windows 10 from a bootable USB. Thankfully when you do this, Microsoft copies all of your data to a ‘Windows.old’ folder so at least you still have access to everything on your machine. I mean, you’ve got everything backed up in the cloud anyway, so it doesn’t really matter… right?
Anyhow, you now have to task of getting your local MySQL Server back up and running in the exact state they were in before everything went into meltdown. And this is where things get a little interesting.
Step 1 – Find Your Old MySQL Server Information
Firstly you need to understand what version of MySQL Server you were running previously as you’ll need to download and install that exact version to avoid any version mismatches. To find this out, open the folder C:\Program Files\MySQL\MySQL Server X.X. Note, this is the original file path where it likely lives. If you had a similar issue to myself, then this will now be within C:\Windows.old\Program Files\MySQL\MySQL Server X.X.
Step 2 – Re-Install MySQL Server X.X
Now you know what version of MySQL Server you need to re-install, head over to Microsoft and download that. There is an archive page where you can download different versions of the MySQL installer which may come in handy, https://downloads.mysql.com/archives/installer/.
Step 3 – Find your Old Databases and Data
For localhost development, you’ve likely build this up with both genuine, temporary, test and junk databases and data over a period of time. And while you’ll have the schema creation files somewhere as you’ve been building genuine things, along with and migration files – the reality is that to go from nothing then through a number of migrations (for every database) over a period of time – this is going to be a nightmare to do and it highly unlikely to work 100%. Hey, if you have handy full database backup scripts that you run on your local machine on a nightly basis – you’re awesome – but let’s be honest – most people don’t have this (no-matter what they say…). So you’re back to restoring your databases by manipulating the core underlying files that MySQL Server runs from – yay!
So, before we get started, let’s first understand how MySQL Server works and actually stores the data on a Windows 10 machine. There are a few bits of information that you need to understand;
- C:\Program Files\MySQL\MySQL Server X.X: This is where all of the files live that MySQL Server requires to run. Your actual databases and data aren’t stored within here.
- C:\.Program Data\MySQL\MySQL Server X.X\data: This is a hidden file and this is where all of your databases and data are stored. It is these files that we’ll be using to restore your entire MySQL Server databases and data.
- C:\.Program Data\MySQL\MySQL Server X.X\data\{your_database};
- {your_database}.frm: FRM is a file extension for formatting used with MySQL. FRM stands for FoRMat. FRM files are used to define the format of a table used with MySQL. MySQL is a cross-platform relational database.
- {your_database}.ibd: The IBD file type is primarily associated with MySQL – InnoDB by Sun Microsystems, Inc. InnoDB tables and indexes can be stored in their own file (a feature called “multiple tablespaces” since in this way each table uses its own tablespace).
- {your_database}.opt: opt is just overall database options file. They contain no data.
Step 4 – Stop the MySQL Server Windows Background Service
If you’ve just installed MySQL Server again, it has likely automatically started and/or been configured to start as part of the Windows startup process. Since you’ll be changing one of the files and importing the old ones, this step just stops things failing along the way.
To do this, type;
- CTRL + R (to open the Run box)
- msc (to open the Windows Services)
- Find ‘MySQL Server X.X
- Right Click
- Select: Stop
The service will now be stopped.
Step 5 – Rename your New ‘Data’ File
Remember I said that your databases are stored in C:\.Program Data\MySQL\MySQL Server X.X\data? Well, since you’ve just done a fresh install, it’s likely that you’ll have one of these folders created already. So go ahead and rename that to ‘data-old’ – You know, just in case anything goes wrong with the next step.
Step 6 – Move All Your Old Databases and Data
Next, simply move the entire /data/ folder that you want to restore and place it in C:\.Program Data\MySQL\MySQL Server X.X\data. Simple.
Step 7 – Start the MySQL Server Windows Background Service
Now you’ve copied all of your old databases and data over into the current installed folders, then you’re good to start the Windows service again now.
Step 8 – Verify the Restored Databases and Data in MySQL Workbench
Now you’ve restored the databases and data, let’s just double check that everything is there as you’re expecting. Open up MySQL Workbench, connect to your MySQL Server and verify the databases are listed and there is data in the tables where you would expect to see them.
Depending on how you installed MySQL Workbench, it is likely that you also installed the newest version of MySQL Server – so make sure you’re connecting to the correct version of MySQL Server that is running on your system as you may have multiple versions.
You can download older versions of MySQL Workbench from here and use the installer to set it all up, https://downloads.mysql.com/archives/workbench/, although if you’ve installed the latest version of MySQL Server already and likely the latest version of MySQL Workbench too, then before you install an older version of MySQL Workbench, I’d just try connecting to the older version of MySQL Server on your machine – just put it on a different port than the standard 3306, put it on 3307 or something like that if you aren’t using that port for anything else.
Notes
The above steps clearly aren’t going to work for everyone – but hopefully this is a starter for 10 if you’ve come across this problem and are in the process of trying to resolve.
There is also MySQL Utilities which may come in handy for more challenging restorations, https://downloads.mysql.com/archives/utilities/, you’ll need to have Python 2.6 installed (it doesn’t work on newer versions of Python), so download that from here if you need to, https://www.python.org/download/releases/2.6/. And you’ll also need Microsoft Visual C++ 2005 Redistributable Package (x64) installed too if you’re using MySQL Utilities, so download that here, https://www.microsoft.com/en-us/download/confirmation.aspx?id=21254. If you try to run MySQL Utilities once it’s installed without having Python 2.6 and the C++ package mentioned, then you’ll likely get the error “The command line MySQL Utilities could not be located. To use them, you must download and install the utilities package for your system from www.mysql.com. Click on the Download button to proceed.”
Good luck and feel free to comment with any tips / pointers you’ve found helpful along the way.
Michael Cropper