Restoring MySQL Databases

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.

Background

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.

Problem

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:

$ ls -lh /Backup/mysql/data/
-rw-rw----    1 brian  staff   1.0G May 21 10:05 ibdata1

Yeah, oops. So InnoDB stores things a bit differently than MyISAM.

Solution

The solution turns out to be simple and is what I should have done in the first place.

First, shutdown the mysql server:

$ mysqladmin -u root -p shutdown

Next, startup mysqld with the –datadir option pointing to your old data directory:

$ mysqld --datadir=/Backup/mysql/data/

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):

$ mysqldump -u root -p database_name > database_name.sql

Lastly, shutdown mysqld again, start mysqld normally and then do the import as usual:

$ mysql -u root -p database_name < database_name.sql

(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?

This entry was posted on Wednesday, May 21st, 2008 at 11:48 am and is filed under MySQL.

1. Cassidy Says:
   June 3rd, 2008 at 4:55 pm
   You should have been able to tar up the whole data directory and extract it in its new home. Assuming the paths were the same between installs, (if not, edit your my.cnf) innodb should happily come up just fine without missing a beat. Of course you’d want to have done the tarring with MySQL cleanly shutdown, otherwise you’d probably have some issue with ibdata files and probably the MyISAM files too.
   Another gotcha is if you change architectures and try and copy the ibdata files to a new box.
2. brian Says:
   June 9th, 2008 at 12:27 pm
   Cassidy – ah, thanks for the tip. I still probably needed to use the method I did since I wasn’t moving an entire data directory to an empty new one. I was only moving part of the old into my “new” (been using it for 8 months) dev environment.