1. UnDROP tool for InnoDB

UnDROP tool for InnoDB

UnDROP tool for InnoDB

TwinDB data recovery toolkit is a set of tools that work with InnoDB tablespaces at low level.

Incredible Performance of stream_parser

stream_parser is a tool that finds InnoDB pages in stream of bytes. It can be either file such as ibdata1, *.ibd or raw partition.
stream_parser runs as many parallel workers as number of CPUs in the system.  The performance of stream_parser is amazing! Compare how stream_parser outperforms page_parser on a four-CPU virtual machine running on my laptop:

So, it takes almost 20 minutes to parse 18G partition.

Let’s check stream_parser

So, 18 minutes versus 31 seconds. 34 times faster! Impressive, isn’t it?

c_parser Improvements

c_parser is a tool that reads InnoDB page or many pages, extracts records and stores them in tab-separated values dumps. InnoDB page with user data doesn’t store information about table structure. You should tell c_parser what fields you’re looking for. Command line option -t specifies a file with CREATE TABLE statement.

This is how it works. Here’s the CREATE statement (I took it from mysqldump)

And now let’s fetch records of table actor from InnoDB pages:

The version 5.6 of MySQL introduced few format changes. Most of them were already supported. The c_parser fixes on top of that some bugs in processing temporal fields.

The new UnDROP tool for InnoDB is still no reason not to take backups :-), but at least you can be armed better if the inevitable happens.

How to Recover Table Structure

MySQL stores table structure in a respective .frm file. When the table is dropped the .frm file is gone. Fortunately InnoDB stores copy of the structure in the dictionary. sys_parser is a tool that can read the dictionary and generate CREATE TABLE statement. Check how you can Recover Table Structure From InnoDB Dictionary.

How to Install TwinDB Data Recovery Toolkit

Check out the source code from GitHub:

Compile the source code. But first install dependencies: make, gcc, flex, bison.

UPDATE:

The toolkit is tested on following systems:

  1. CentOS release 5.10 (Final) x86_64
  2. CentOS release 6.5 (Final) x86_64
  3. CentOS Linux release 7.0.1406 (Core) x86_64
  4. Fedora release 20 (Heisenbug) x86_64
  5. Ubuntu 10.04.4 LTS (lucid) x86_64
  6. Ubuntu 12.04.4 LTS (precise) x86_64
  7. Ubuntu 14.04 LTS (trusty) x86_64
  8. Debian GNU/Linux 7.5 (wheezy) x86_64

32 bit operating systems are not supported.

Have a question? Ask the experts!

Previous Post Next Post
  • very good,but how to get table Structure ?

    • An InnoDB index doesn’t carry information about the table structure indeed. MySQL keeps the structure in .frm files and InnoDB stores the structure in the dictionary. When the table structure isn’t available from external source (old backup, installation script etc) then possible way to recover the structure are:

      1) Recover from .frm files. There are some tools around available . I prefer to create a dummy table, replace the .frm file and run SHOW CREATE TABLE. This option however is useless when DROP TABLE happens, MySQL deletes the .frm file as well.

      2) Recover the structure from the InnoDB dictionary. InnoDB stores almost all necessary information about the table structure in the dictionary. When a user runs DROP TABLE the respective records are deleted from the dictionary tables, so when recover the dictionary tables you need to specify -D option to c_parser (-D recovers records that are marked as deleted). The tables you need are SYS_TABLES, SYS_INDEXES, SYS_FIELDS and SYS_COLUMNS. Then load everything into a live instance of MySQL. A tool sys_parser from the toolkit reads SYS_* tables from MySQL and generates CREATE TABLE statement.

  • Sorry to bother you, I managed to find the field, but could not find the definition of the field, such as similar to the id (int) not null.You can write a blog?

  • Hi Aleksandr ,

    Do you sell this tool? And can we become agent?

  • Matej

    Hi, I have a innodb mysql database with only ibdata (innodb_file_per_table is OFF) and when I follow the restore procedure, I successfully come to the data:
    COMPACT, Records list: Valid, Expected records: (442 442)
    but this 442 records number is extremely lower than the actual table row count (it was more than 1 million rows). Since there are multiple pages, I assume that the data for this table is stored in multiple pages. What to do in such a case? Or please correct me if I am totally wrong 🙂

    Thanks!

    • Matej,

      These numbers for one page only.
      Before processing a page c_parser counts expected number of records in the page. You see two numbers because there are two sources of records count.

      First source of the records count is the page header ( See field PAGE_N_RECS https://dev.mysql.com/doc/internals/en/innodb-page-header.html).
      The second source of records count is a unidirectional records list in the page. c_parser just counts number of items in the list.

      In the end of each page c_parser prints how many records were actually dumped.

      If a table is large enough then it’s stored in multiple pages, of course.

      I’m not sure I understand your question about what to do in this case. stream_parser finds all pages with same index_id and saves them in one file like 00000000123.page, where 123 is index_id. If you see only one page then probably stream_parser found just one page.

  • Oki Erie Rinaldi

    I can’t download it now. The download page is gone

  • Oki Erie Rinaldi

    Does it need root permission to run?

  • UPDATE (01/01/2017):

    We stopped further development of undrop-for-innodb and do not support its open source versions.

  • alfian syahroni

    i hope with this tools i can recover my deleted table, but when i follow step by step this articel i still cant recover my table, would you like to guidance me. cz it always error when i tried to retreive *.page file (doest match from parser)..