Recovering databases from .frm & ibdata1 files

| March 3, 2014

Recently one of my VPS servers crashed, became unresponsive & needed to be reinstalled. The only backup of the database was one that I had from a few weeks ago, so that wasn’t of any use to me.  These instructions are made for Debian so may differ slightly depending on your distribution.

I asked my provider for a complete tar ball of my VPS so I could try and collect the lost data from it. If you are unable to access your VPS/server your provider may be able to provide you with a tar ball of your VPS or the files you need.

Prerequisites (ibdata + .frm files):

  • /var/lib/mysql containing ibdata1 and the databases you wish to recover. The name of the database will match the directory name, these should contain .frm files.
  • Another MySQL instance with access to the file system. This should be a new MySQL server setup just for this recovery. You should not attempt to transfer your database to an in use MySQL server.

Database Recovery Steps:

  1. Extract and locate the /var/lib/mysql directory that contains the files needed for recovery.
  2. Setup another MySQL server with the same version e.g. 5.5.
  3. Stop the MySQL server on your new instance. I issued the command: service mysql stop, however it may differ slightly.
  4. On your new mysql server locate the file ibdata1 in /var/lib/mysql and rename it to ibdata1bak. Just in case something goes wrong.
  5. Copy ibdata1 from /var/lib/mysql (this contains some of the data needed for recovery) to the new server in the same location.
  6. Copy the folders (not the .frm files inside) from /var/lib/mysql to /var/lib/mysql on the new server. You may have multiple databases you wish to recover so there will be multiple folders you need to copy.
  7. The permissions are may be wrong on the files now. I executed from the terminal: chown -R mysql:mysql /var/lib/mysql (this will change the permissions recursively to be the group and owner of mysql)
  8. Start the MySQL server again. In my case I used service mysql start.
  9. The databases should now be working and accessible. This may not be the case if they had become corrupted as well.