MySQL data recovery via Shadow Copy

Situation: Your data has been corrupted, or accidentally/maliciously modified and now you are facing the possibility of losing your sites data that has been modified or added since your nightly backup.

Good News Everyone...

...your data can be saved!

In order to set yourself up to be able to use this method of data retrieval, you first need to have shadow copying enabled on your server. I won't go into details here on how to set this up, but if you would like to set this up on a windows server here is a helpful guide.

Another step that will help you to use this recovery process, is to configure your MySQL server to use innodb_file_per_table. This way each table you need has its own file for it's data.

The first step in your recovery process is to get a copy of the best shadow you have for recovering the data you need. On a windows server, this can be done by right clicking the file and selecting the shadow copy tab, then selecting the shadow and clicking copy.

The next step is to create a database where you will load your shadow.

mysql> CREATE DATABASE recovery;
mysql> USE recovery;

In your new database, create the structure for your table that you are trying to recover;

mysql> CREATE TABLE foo (
foo_id bigint(20) unsigned NOT NULL auto_increment,
bar_id int(10) unsigned default NULL,
foobar varchar(255) NULL
PRIMARY KEY (foo_id)
) ENGINE=InnoDB;

Now we will discard the tablespace associated with the table

mysql> ALTER TABLE recovery.foo DISCARD TABLESPACE;

Once this has been discarded, your .ibd file for your table will no longer exist in your %mysql server folder%/data/recovery folder. Now you can copy in your .ibd file which was restored from the shadow. Once this has been completed you will attach the table to this tablespace

mysql> ALTER TABLE recovery.foo IMPORT TABLESPACE;

If you are lucky, your data will now be accessible by querying the table on your recovery database.

While looking into this myself, I found multiple resources. Taking the steps I took, it ended up being easier than others, but here is the best reference I found, incase you need further assistance.