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:
- Extract and locate the /var/lib/mysql directory that contains the files needed for recovery.
- Setup another MySQL server with the same version e.g. 5.5.
- Stop the MySQL server on your new instance. I issued the command: service mysql stop, however it may differ slightly.
- On your new mysql server locate the file ibdata1 in /var/lib/mysql and rename it to ibdata1bak. Just in case something goes wrong.
- Copy ibdata1 from /var/lib/mysql (this contains some of the data needed for recovery) to the new server in the same location.
- 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.
- 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)
- Start the MySQL server again. In my case I used service mysql start.
- The databases should now be working and accessible. This may not be the case if they had become corrupted as well.