May 21st, 2008 By: brian
This is a quick post to possibly save someone some time when they google for restoring mysql data files. The gist of it is, don’t try to copy InnoDB files directly into your data directory, it won’t work.
Sometime back I wiped my development box clean and reinstalled the OS from scratch. It’s a wonderful feeling to start fresh and get rid of all the old whacky configs and random junk that collects on a filesystem. Prior to this cleansing, I backed up all the junk I considered save-worthy. One thing I saved was they MySQL data directory. Many of the databases were old projects and at the time I didn’t think I’d need to look at them anytime soon. Well, anytime soon arrived yesterday in the form of me really wanting to see how I had implemented a particular feature in an old project. The database component of that project was essential, so I couldn’t just glance at the code, I wanted to get it running again with the database backend.
I probably should have known better, but I decided to just try to shutdown mysql and copy the files directly to my new data directory. After some permission and user adjusting, that seemed to work… until I actually tried to access the restored database I needed, which used mostly InnoDB tables. They were not showing up or working properly. A quick glance revealed a file I hadn’t copied which seemed like it *might* be something important:
Yeah, oops. So InnoDB stores things a bit differently than MyISAM.
The solution turns out to be simple and is what I should have done in the first place.
First, shutdown the mysql server:
Next, startup mysqld with the –datadir option pointing to your old data directory:
It may complain about some issue with writing to log files, but that doesn’t matter for this quick export.
Now just dump your database as you normally would (or should have originally):
Lastly, shutdown mysqld again, start mysqld normally and then do the import as usual:
(you’ll likely need to create the database first)
There you have it – the way to get your old database files restored into your current setup. There is probably some better easier way to do this, and if someone knows it, clue me in will ya?