After a hiatus of several months the site is back up. The site was unfortunately victim to some script kiddies and fell under the scrutiny of my hosting provider. After a failed attempt to rewind to a previous backup, which turned out to be corrupt, I decided that rather than spend a bunch of time that I did not have spare trying to fix it, I would simply turn the server off and get back to it later. Of course I did not consider that 'later' may stretch to be several months but that's just the way it panned out to be.

So I finally got some free time and decided to take a look at getting the server back up and running. The original issue with the original server was that due to a kernal issue with the server itself, the backup service provided by my host generated corrupted backups, so reinstalling from a backup invariably led to a bricked server. I spent some time working with the host to get the server repaired and whilst I did get a server that I could SSL into I unfortunately did not get any further as the SQL service would not run due to toasted database files.

So with renewed enthusiasm to get the site back up the first thing I did was to create a new server instance. Might as well start afresh, especially with the kernel issue - a problem I did not want to walk back into. I then grabbed all of the database files from the original server which were located at /var/lib/mysql/"DB-Name"

MySql is great in that you can simply physically copy the files from one database to another and the database will become available, however there are some caveats to this. 

You can then run check tables / repair tables to fix any issues, however, and rather unfortunately, this will not work on innodb tables. Bugger.

With my database the innodb tables were the tables that corrupted. On inspection I could see in the database folder the tables were present by not in the same format as the normal tables. Normally you will find three files for each table - table.frm table.MYD and table.MYI. In the case of innodb tables there were only two files - table.frm and table.IBD.

It is easy enough to generate an insert statement from the FRM file which will allow you to recreate the table structure, but to recover the contents requires a little messing about.

 

To recover the table structure

install mysqlfrm

CD into the database directory and then run the following statement

mysqlfrm --server=username:password@localhost --port=3307 yourtablename.frm > yourtablename.txt

This will create a text file with the SQL to create the table.

However - Before you run the SQL code you will need to do the following...

Rename the yourtablename.ibd file to yourtablename.ibd.old

Delete the yourtablename.frm file

Then you can run the SQL statement. 

 

To recover the table contents

Once the table structure has been created you will note that you now have two new files in the database folder

yourtablename.frm

yourtablename.ibd

 

To recover the data you need to discard the exisitng ibd file and replace it with the old one. You can do this as follows:

Run the following SQL statement

ALTER TABLE yourtablename DISCARD TABLESPACE;

This will discard the new ibd file. You will note it is no longer in the folder

Rename the original yourtablename.ibd.old file to yourtablename.ibd

Then run the following SQL

ALTER TABLE yourtablename IMPORT TABLESPACE;

This will bring the contents into the database. Browse to the table using phpmyadmin and check.

Repeat for the other tables.

 

This will work 90% of the time, however there are some occasions where an error will be thrown and the tablespace will not be imported. For me it allowed me to recover enough information to be able to recreate the site. Fortunately the tables that contained the main content were able to be saved, the tables that I had an issue with were able to be simply recreated with the new installation, of course your mileage may vary.