Beware this content is over 4 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.

Due to the fact that I was restoring an InnoDB backup from the actual MySQL data folder instead of a MySQL dump file it made things a little more complicated.

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 data folder.

On my server I could find it at:

1
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.

In the 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!):

1
2
3
4
ib_logfile0
ib_logfile1
[COMPUTER_NAME].err
[COMPUTER_NAME].pid

The files with the extension .err and .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 mysql, performance_schema and 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-backup.

Right-click the 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.

1
net stop MySQL

Then rename the live data folder to data-orig and rename data-replace to 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.

1
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.

Additional Reading:

  • How to safely change MySQL innodb variable ‘innodb_log_file_size’? (StackExchange)
  • Issue changing innodb_log_file_size (StackOverflow)
  • InnoDB: Error: log file ./ib_logfile0 is of different size (ServerFault)