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

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! 🙂

Share this post

Comments (2)

  • CreateIT

    Thanks for sharing with the info! It helped me a lot! Regards

    March 21, 2017 at 2:39 pm
  • M. Keijzer

    Maybe it is possible to explain when a DROPPING the table is not working… (doesn’t exist in engine, or ‘doesn’t exist’, in case of a corrupted table:

    1. stop mysql, delete the frm and ibd-files corresponding the table
    2. copy a backup-frm table
    3. start mysql
    4. NOW dropping had to work, then you can CREATE TABLE

    maybe give the statement UNLOCK TABLES at the end

    January 18, 2019 at 12:07 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.