Author Andriy Lysyuk.

Introduction

Unfortunately, human mistakes are inevitable. That’s how life is. Wrong DROP DATABASE or DROP TABLE may destroy critical data on the MySQL server. Obviously, backups would help, however they’re not always available. This situation is frightening but not hopeless. In many cases it’s possible to recover almost all the data that was in the database or table.
Let’s look at how we can do it. The recovery plan depends on whether InnoDB kept all data in a single ibdata1 or each table had its own tablespace. In this post we will consider the case when innodb_file_per_table=OFF. This option assumes that all tables are stored in a common file, usually located at /var/lib/mysql/ibdata1.

One Wrong Move, And The Table’s Gone

For our scenario, we use …

First step of successful MySQL data recovery is to find InnoDB pages with your data. Let’s call it first, because prerequisite steps are already done.

InnoDB page type is a two bytes integer stored in the header of a page. For MySQL data recovery two are important:

• FIL_PAGE_INDEX. Pages of this type are nodes of B+ Tree index where InnoDB stores a table.
• FIL_PAGE_TYPE_BLOB. So called external pages, where InnoDB keeps long values of BLOB or TEXT type.

stream_parser reads a stream of bytes, finds InnoDB pages and sorts them per type, per index or page id. It applies sophisticated algorithms tailored for particular page type. Of course, it assumes that page type in the header corresponds to the content of the page, otherwise it will ignore the page.

…

Introduction

Human mistakes are inevitable. Wrong “DROP DATABASE” or “DROP TABLE” may destroy critical data on the MySQL server. Backups would help however they’re not always available. This situation is frightening but not hopeless. In many cases it is possible to recover almost all the data that was in the database or table.
Let’s look how we can do it. The recovery plan depends on whether InnoDB kept all data in a single ibdata1 or each table had its own tablespace . In this post we will consider the case innodb_file_per_table=OFF. This option assumes that all tables are stored in a common file, usually located at /var/lib/mysql/ibdata1.

Wrong action – table deletion

For our scenario we will use test database sakila that is shipped together with the tool.
Suppose we drop my mistake table actor:

mysql> SELECT * FROM actor LIMIT 10; …