Restoring an InnoDB database from raw files (on Ubuntu)

Recently I had to replace my computer’s hard drive as the old one was getting close to dying. In the chaos that comes from copying files from one drive to another, I completely forgot to  back up the databases on the old hard drive. I was still able to access the raw files, however, and after some hair-pulling and a lot of googling, I was able to restore the tables I needed like so:

  1. On the old hard drive, I navigated to the /var/lib/mysql folder
  2. I copied the database folder I needed to the new hard drive (this contains a bunch of  files ending in .frm; the names should be those of the tables on that database). For convenience I copied them to the Desktop.
  3. I then copied the ibdata1, ib_logfile0, and ib_logfile1 files to the Desktop
  4. On the command line I stopped mySQL – sudo service mysql stop
  5. I backed up the ibdata1, ib_logfile0, and ib_logfile1 files by appending -bak to them. To do this (since you can’t exactly browse the /var/lib/mysql  folder using cd) I entered sudo mv /var/lib/mysql/<filename> /var/lib/mysql/<filename>-bak (replacing <filename> with the respective file names mentioned above)
  6. I then copied the 3 ib files from the Desktop to the /var/lib/mysql folder – sudo cp <filename> /var/lib/mysql/<filename>
  7. Make sure all 3 of those  files are owned by mysql. If not – sudo chown mysql:mysql /var/lib/mysql/<filename>
  8. Copy the database folder – sudo cp -R <database> /var/lib/mysql/<database>  (where <database> is the database name)
  9. Give mysql ownership – sudo chown -R mysql:mysql /var/lib/mysql/<database>
  10. Restart mysql – sudo service mysql start
  11. Check phpMyAdmin/Adminer/Database tool of choice to ensure everything shows up properly!

Hopefully this helps someone else!

Advertisements