Monday, January 05, 2009

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.

3 comments:

  1. I recently cut over from Ghost to Acronis True Image Home for managing systems. Not sure if it solves this MySQL problem well, but it does certainly ease system migration.

    ReplyDelete
  2. Anonymous5:22 PM

    Funny...I've done this a bunch of times and never ran into this. Stay away from my computer, please.

    ReplyDelete
  3. Bens -

    With the luck I've been having lately with my laptop, I think I should stay away from all computers.

    ;-)

    -Ben

    ReplyDelete