Gotcha Of The Day: Moving MySQL's data directory between machines

I'm suffering from some laptop woes, and a as a result, I'm working this morning on a different laptop than my usual one.

To save time getting up to speed on the temporary laptop, I thought I would copy the database files from the old machine to the new one. That way, I wouldn't have to recreate the databases for the apps I was working on.

This turned out to be slightly easier said than done. But, after putting up with some arcane error messages, I was off an running.

Here's what I went through...

• I shut down the MySQL service on the temporary laptop
• I moved my current data directory out of the way to something like C:/Program Files/MySQL/MySQL Server 5.0/data.orig
• Using cygwin, I copied the files in place from the currently broken laptop. You'll notice I had the broken laptop's hard drive mounted on F:/

   cd /cygdrive/f/Program\ Files/MySQL/MySQL\ Server\ 5.0/
   tar cf - data | (cd /cygdrive/c/Program\ Files/MySQL/MySQL\ Server\ 5.0/ ; tar xvf - )
  

• I then attempted to start up the MySQL service and it failed.

This is when things started getting annoying. For some reason, MySQL wasn't writing a .err file to the data directory, so I couldn't even get a lot message explaining what was broken. After much poking around, I realized that I had a permission problem - apparently, the above tar command worked too well, writing files as user Ben instead of Administrator. I then ran the following commands:

 cd /cygdrive/c/Program\ Files/MySQL/MySQL\ Server\ 5.0/
 chmod -R a+rwX data
 chown -R administrator data

This, of course, explains why a .err file wasn't being written to the data directory. The process lacked the permissions to do so. Duh.

Now when I attempted to start up MySQL, it failed, but gave me the following error message:

InnoDB: Error: log file .\ib_logfile0 is of different size 0 25165824 bytes
InnoDB: than specified in the .cnf file 0 26214400 bytes!
090105  9:01:30 [ERROR] Default storage engine (InnoDB) is not available
090105  9:01:30 [ERROR] Aborting

Huh?

I looked through my.ini (my guess as to the .cnf file it was talking about) and the only value that jumped out at me was:

 innodb_log_file_size=25M

I updated this value to be:

  innodb_log_file_size=25165824

And Holy Smokes! the server started up. I'm able to get into the database and all looks Kosher.

Bottom line: you can copy around the data directory in MySQL (at least between Windows boxes and the same server version), you just need to be willing to chase around a handful of issues before it can be expected to all work.