October 24, 2014

How to recover deleted rows from an InnoDB Tablespace

In my previous post I explained how it could be possible to recover, on some specific cases, a single table from a full backup in order to save time and make the recovery process more straightforward. Now the scenario is worse because we don’t have a backup or the backup restore process doesn’t work. How can I recover deleted rows?

We’re going to follow the same example as in my previous post so we need to delete the records of the employee 10008 from the table “salaries”. After the “accidental” deletion of rows you should stop MySQL, take a copy of the salaries.ibd and start it again. Later, we’ll extract those deleted rows from the ibd file and import them into the database. The time between the deletion of rows and the database stop is crucial. If pages are reused you can’t recover the data.

The tool that we’re going to use to achieve this objetive is Percona Data Recovery Tool for InnoDB. This is the tool that we use in our data recovery service and it’s open source.

I’m going to explain the full process in four different steps to make it clearer:

1- Extract all the InnoDB pages from the tablespace:

First we need to download Percona Data Recovery Tool and compile all the tools using the “make” command. In this example I’m going to install the tools in /root/recovery-tool folder and the data like tablespaces and recovered rows in /root/recovery-tool/data.

After the compile process we need to copy the salaries.ibd tablespace to the recovery-tool’s data directory. In order to extract all the pages we’ll use the page_parser tool. This tool will find and extract all the pages of the tablespace to an output directory. We only need to specify the row format (-5) and where is our tablespace located (-f)

The row format can be -4 (REDUNDANT) or -5 (COMPACT). From 5.0.3 the default format is COMPACT. More information about row format on the following link:

http://dev.mysql.com/doc/refman/5.5/en/innodb-physical-record.html

You can also get the table row format from the Information Schema:

mysql (information_schema) > SELECT ROW_FORMAT from TABLES WHERE TABLE_SCHEMA='employees' AND TABLE_NAME='salaries';
+------------+
| ROW_FORMAT |
+------------+
| Compact |
+------------+

~/recovery-tool# ./page_parser -5 -f data/salaries.ibd
Opening file: data/salaries.ibd:
[...]
71.43% done. 2012-02-14 13:10:08 ETA(in 00:00 hours). Processing speed: 104857600 B/sec

All pages are stored in a single directory with some subdirectories inside, one for every single index in the table:

~/recovery-tool# ls pages-1329221407/FIL_PAGE_INDEX/
0-26 0-27

In this case, there are two indexes with the IDs 0-26 and 0-27. InnoDB has Clustered Primary Key, that is, the data is organized along with the primary key. Therefore if we want to extract the rows data we need to identify which of those two indexes is the Primary Key. This is our next step.

2- Identifying the Primary Key

There are different methods to find the correct index and here I’m going to explain three of them:

A) INNODB_SYS_INDEXES

Percona Server has some extra tables in INFORMATION_SCHEMA that can help us to find the different indexes and types.

mysql (information_schema) > select i.INDEX_ID, i.NAME FROM INNODB_SYS_INDEXES as i INNER JOIN INNODB_SYS_TABLES as t USING(TABLE_ID) WHERE t.NAME='salaries';
+----------+---------+
| INDEX_ID | NAME |
+----------+---------+
| 26 | PRIMARY |
| 27 | emp_no |
+----------+---------+

B) InnoDB Table Monitor

The index information can also be taken directly from MySQL using the InnoDB Tablespace Monitor. This monitor will write all the information related with tables and indexes (with their IDs) to the error log.

mysql (employees) > CREATE TABLE innodb_table_monitor (id int) ENGINE=InnoDB;

TABLE: name employees/salaries, id 18, flags 1, columns 7, indexes 2, appr.rows 2844513
[...]
INDEX: name PRIMARY, id 26, fields 2/6, uniq 2, type 3
root page 3, appr.key vals 2844513, leaf pages 6078, size pages 6120
FIELDS: emp_no from_date DB_TRX_ID DB_ROLL_PTR salary to_date
INDEX: name emp_no, id 27, fields 1/2, uniq 2, type 0
root page 4, appr.key vals 306195, leaf pages 2189, size pages 2212
FIELDS: emp_no from_date
[...]

The second method has the same result, 0-26 is our primary key. After identifying the Primary Key don’t forget to remove the innodb_table_monitor.

C) Check the size on disk of every indes

This is very dependent of the table schema, but normally the primary key will be larger on disk because it stores also the row itself.

~/recovery-tool/pages-1329221407/FIL_PAGE_INDEX# du -hs 0-26/
96M 0-26/
~/recovery-tool/pages-1329221407/FIL_PAGE_INDEX# du -hs 0-27/
35M 0-27/

In our examples 0-26 seems to be the Primary Key.

3- Extract the rows

We know in which index is the data so the next step is clear, extract the rows from it. To accomplish this task we’ll use constraint_parser command. In order to use it the tool needs to know the table schema structure, that is, column types, names and attributes. This information needs to be available on the header file recovery-tools/include/table_defs.h so it will be necessary to recompile the tool.

To help us in the task of converting the schema definition to a C header file there is another tool with the name create_defs.pl. This one will connect to the database in order to examine the table and create the table_defs.h content.

~/recovery-tool# ./create_defs.pl --host 127.0.0.1 --port 5520 --user root --password msandbox --db employees --table salaries > include/table_defs.h

More information about the table_defs.h format in the following link:

http://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:generating_a_table_definition

With the table definition on table_defs.h it’s the moment to compile again all the tools with “make” command. After the compile process we can use constraints_parser to recover the rows in a human readable format.

~/recovery-tool# ./constraints_parser -5 -D -f pages-1329221407/FIL_PAGE_INDEX/0-26/ > data/salaries.recovery

With -D option we are asking to the constraints_parser to recover only deleted pages. -5 and -f are the same options that we’ve used before with page_parser.

In the salaries.recovery you can find lot of deleted rows, not only rows deleted accidentally. You should manually find the data you need to recover and save it in another file. This is the output of our example:

~/data-recovery# cat data/salaries.recovery
salaries 10008 "1998-03-11" 46671 "1999-03-11"
salaries 10008 "1999-03-11" 48584 "2000-03-10"
salaries 10008 "2000-03-10" 52668 "2000-07-31"

4- Import the rows

With the data in our hands the last step is to import it in our database:

mysql (employees) > LOAD DATA INFILE '/root/recovery-tool/data/salaries.recovery' REPLACE INTO TABLE `salaries` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'salaries\t' (emp_no, from_date, salary, to_date);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql (employees) > select * from salaries where emp_no=10008;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10008 | 46671 | 1998-03-11 | 1999-03-11 |
| 10008 | 48584 | 1999-03-11 | 2000-03-10 |
| 10008 | 52668 | 2000-03-10 | 2000-07-31 |
+--------+--------+------------+------------+

Data recovered! 10008 has recovered his salary again :)

Conclusion

With InnoDB the deleted rows are not lost. You can recover them from the original tablespace and also from an ibd file if you have a binary backup. Just use constraint_parser without the -D option (deleted) and you will recover all the data that exists inside that tablespace

About Miguel Angel Nieto

Miguel joined Percona in October 2011. He has worked as a System Administrator for a Free Software consultant and in the supporting area of the biggest hosting company in Spain. His current focus is improving MySQL and helping the community of Free Software to grow. Miguel's roles inside Percona are Senior Support Engineer and Manager of EMEA Support Team.

Comments

  1. Shenglin says:

    it’s good to have a delay slave if TCO is acceptable :)

  2. Mohit Bumb says:

    nice tutorial miguel

  3. Alice Wang says:

    I deleted one row in a innodb table to test how to recovery the data with file_per_table setting and followed your instruction. Every step seems to be correct (see the final step below) but the table1.recovery file is 0 byte and I can’t recovery the deleted row.

    {{{
    ./constraints_parser -5 -D -f pages-1337640335/FIL_PAGE_INDEX/0-10847/>data/table1.recovery
    LOAD DATA INFILE ‘/var/tmp/percona-data-recovery-tool-for-innodb-0.5/dumps/default/table1′ REPLACE INTO TABLE `table1` FIELDS TERMINATED BY ‘\t’ OPTIONALLY ENCLOSED BY ‘”‘ LINES STARTING BY ‘table1\t’ (district_id, account_id, password, account_uuid, person_uuid, last_name, first_name, roles, district_uuid, school_uuid);

    }}}

  4. Raymond Boswel says:

    Hi Miguel,

    I used this tool in an attempt to recover deleted table data. The primary key index is 143, but there is no 0-143 sub directory created by the page_parser. Does this mean the data is permanently lost?

    Kind regards,
    Raymond Boswel

  5. Nice tools and instruction are very clearly defined
    Thanks so much for better blogs there/.
    Best
    Sonu Sindhu

  6. I selected all table and choise delete button, when i releaze this is big mistake it too late, show i want to retrieve all table i had. it, anybody can help me. i using myphpadmin.
    best regarrd

  7. Hi Truong, I’m Percona’s community manager. Thanks for the comment, however, this is an old post and your question is much better suited for our MySQL discussion forums. You’ll be able to connect with peers as well as Percona experts there. It’s free and easy to join the discussions – just register and you can post your question(s) there: http://www.percona.com/forums/

    I look forward to seeing you there!
    Tom

  8. Luis Fernando Murara says:

    You saved my life! :)

    Thank you!

  9. Hang Zhou says:

    Hello, Miguel: what I need is that I delete one row from a table, and nobody can recover this row at any time. I think the only way is to rewrite the correspond physical unit with other data. Now my question is: should I wait other records for long enough time or I can programmatically rewrite this unit immediately?

    Thanks!

Speak Your Mind

*