EmergencyEMERGENCY? Get 24/7 Help Now!

How to Restore a Single InnoDB Table from a Full Backup After Accidentally Dropping It

 | March 15, 2017 |  Posted In: InnoDB, Insight for DBAs, Percona XtraBackup, Quality Assurance

PREVIOUS POST
NEXT POST

InnoDBIn this blog post, we’ll look at how to restore a single InnoDB table from a full backup after dropping the table.

You can also see an earlier blog post about restoring a single table from a full backup here: How to recover a single InnoDB table from a full backup.

The idea behind the actions in that blog is based on the “Transportable Tablespace” concept, which was introduced in MySQL 5.6. So when you have deleted the data from a table, you are going to quickly restore this table as follows:

  • Prepare the backup
  • Discard the tablespace of the original table
  • Copy .ibd from the backup to the original table path
  • Import the tablespace

Of course, you need to test it using the process in production, even though it is relatively straightforward.

But how about when you drop a table? It is still a great process because you will lose the table structure and the datafiles.

The actions mentioned in the previous blog will not work here, simple because it is impossible to discard a non-existing tablespace. 🙂

Instead, one solution scenario could be something like:

  • Prepare the backup
  • Extract the original table structure from the backup (i.e., extract the create statement from the backup .frm file)
  • Create a new empty table
  • Apply some locks
  • Discard the newly created tablespace
  • Copy back .ibd from the backup
  • Import the tablespace

And now you can continue to be happy!

Let’s test this scenario. In this test, I am not going to use real world tables. I will instead use good old “t1”.

Take a backup:

Prepare a backup:

Drop the table:

Extract the create statement from the .frm file using the mysqlfrm tool. Please read the #WARNING and #CAUTION sections below, and also review the documentation to figure out how to use this tool:

Create an empty table using this create statement:

Apply write lock, or take another action to ensure safety:

Discard the tablespace:

Copy back the .ibd file from backup:

Apply the proper owner:

Import the tablespace:

You can ignore the warnings.

Now check if your table is restored or not:

With our simple test, this method worked well. It should theoretically work in a production environment. However, you will need to test this first. Ensure it works for your test environment and test tables.

Thanks! 🙂

PREVIOUS POST
NEXT POST
Shahriyar Rzayev

Shako from Azerbaijan/Baku. My hobby is cooking kebap.
Bug lover by design.

One Comment

Leave a Reply