Restore from a MySQL InnoDB backup on Windows
Beware this content is over 7 years old and considered stale. It may no longer be accurate and/or reflect the understanding of the author but remains here for reference only. Please keep this in mind as you use this content.
As part of a process to restore a clients WAMP server (Windows Server 2003), the original database needed to be imported once MySQL (5.5.25) was installed.
Before continuing I’ll assume you have a MySQL instance up and running without any problems before proceeding.
The first thing you’ll need to do is find the MySQL
On my server I could find it at:
C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data
On most Windows installs (unless you customised you’re install) you can find the
data folder in the
Documents and Settings folders and not in the MySQL
Program Files directory.
Provided you have a fresh MySQL install there shouldn’t be much in that
data folder, so copy the entire folder and rename it to something helpful like:
data-replace folder, you need to delete the following files (as it’s a fresh install there should be nothing of importance in this folder anyway!):
The files with the extension
.pid are prefixed with
[COMPUTER_NAME] which should match the name of the computer/server.
From you’re backup folder of the previous MySQL data folder, let’s call it:
data-backup, you’ll want to copy the following into the
data-replace folder: the
ibdata1 file along with any folders matching names of databases you want to keep (or restore).
You don’t need the entire contents of the
data-backup folder, so folders within it such as
test should be ignored (not restored).
Next, to avoid any possible permissions problems we can use the permission of the folder we copied,
data-replace to overrite the permissions of the files we restored from
data-replace folder and select the ‘Security’ tab and click ‘Advanced’. In the window that appears tick the box ‘Replace permission entries on all child objects with entries shown here that apply to child objects’. Then hit ‘Apply’ and ‘OK’ to confirm the new settings.
The remaining step is to switchover the live
data folder currently in use with
data-replace folder containing the MySQL databases we want to restore.
In command prompt (
Start > Run > "cmd") enter the following commands.
First stop the MySQL instance.
net stop MySQL
Then rename the live
data folder to
data-orig and rename
data (move to the location of the MySQL live
data folder if not working in the same directory!).
Once you’ve completed the switch. Restart the MySQL instance.
net start MySQL
If everything has worked, you’ll get no error message. You’re going to want to try and login to MySQL to double check either by the command line or using phpMyAdmin.
Any hitches, drop a comment. And remember to always make sure you backup properly before attempting the above.