What are odds MySQL table can be recovered?

Posted by PDSERVICE on Nov 07, 2019 In

This is the most asked question. Every single customer asks if their MySQL table can be recovered. Although it’s not possible to answer that with 100% confidence there are ways to estimate recovery chances. I will describe few tricks.

Generally speaking, if data is on media there are high odds TwinDB data recovery toolkit can fetch it. Where to look for depends on accident type.

Online MySQL data recovery toolkit

On our Data Recovery portal you can upload an .ibd file and check if the InnoDB tablespace contains any good records. The table space may be corrupt. The tool should handle that.

MySQL data recovery portal

DROP TABLE or DATABASE with innodb_file_per_table=OFF

If innodb_file_per_table is OFF InnoDB stores all tables in one file ibdata1. When a table or database is dropped pages with data are marked as free. InnoDB may reuse the pages for new data. It’s important to stop writes to ibdata1 as soon as possible, but if MySQL was running a while InnoDB might overwrite some data.

Let’s take table actor from sakila database as an example:

CREATE TABLE `actor` (

`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`first_name` varchar(45) NOT NULL,

`last_name` varchar(45) NOT NULL,

`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`actor_id`),

KEY `idx_actor_last_name` (`last_name`)

) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8

Fields first_name and last_name are string and they come next to each other. In InnoDB page these fields also located next to each other. InnoDB does’t terminated strings with ‘\0’, so if first_name is WOODY and last_name is HOFFMAN in the InnoDB page you will find WOODYHOFFMAN string. So, take grep and try to find that string:

# grep WOODYHOFFMAN ibdata1

Binary file ibdata1 matches

So it’s likely the record is still in ibdata1 and not overwritten. However the string may be a remains of system buffers. To be sure the string comes from a good index page I use bvi. It stands for binary vi and works pretty much similar to vi. Particularly search works the same way. I can scroll down ibdata1 and see in what context WOODYHOFFMAN shows up. Here’s how InnoDB index page looks like.

0013C44C 00 00 00 00 07 0E 94 00 00 01 3D 02 ..........=.

0013C458 14 4A 55 4C 49 41 4D 43 51 55 45 45 .JULIAMCQUEE

0013C464 4E 43 F2 AF 59 07 05 04 00 E8 00 26 NC..Y......&

0013C470 00 1C 00 00 00 00 07 0E 94 00 00 01 ............

0013C47C 3D 02 1E 57 4F 4F 44 59 48 4F 46 46 =..WOODYHOFF

0013C488 4D 41 4E 43 F2 AF 59 05 04 00 00 F0 MANC..Y.....

0013C494 00 23 00 1D 00 00 00 00 07 0E 94 00 .#..........

0013C4A0 00 01 3D 02 28 41 4C 45 43 57 41 59 ..=.(ALECWAY

0013C4AC 4E 45 43 F2 AF 59 04 06 00 00 F8 00 NEC..Y......

0013C4B8 24 00 1E 00 00 00 00 07 0E 94 00 00 $...........

0013C4C4 01 3D 02 32 53 41 4E 44 52 41 50 45 .=.2SANDRAPE

0013C4D0 43 4B 43 F2 AF 59 08 05 00 01 00 00 CKC..Y......

0013C4DC 27 00 1F 00 00 00 00 07 0E 94 00 00 '...........

If you go up you’ll see the infimum and supremum records – those index page starts with:

0013C038 00 00 00 00 00 00 00 00 00 00 00 00 ............

0013C044 00 00 00 00 00 7D 00 00 00 00 00 00 .....}......

0013C050 01 EE 03 32 00 00 00 00 00 00 01 EE ...2........

0013C05C 02 72 01 00 02 00 1C 69 6E 66 69 6D .r.....infim

0013C068 75 6D 00 05 00 0B 00 00 73 75 70 72 um......supr

0013C074 65 6D 75 6D 07 08 00 00 10 00 29 00 emum......).

0013C080 01 00 00 00 00 07 0E 94 00 00 01 3D ...........=

0013C08C 01 10 50 45 4E 45 4C 4F 50 45 47 55 ..PENELOPEGU

0013C098 49 4E 45 53 53 43 F2 AF 59 08 04 00 INESSC..Y...

0013C0A4 00 18 00 26 00 02 00 00 00 00 07 0E ...&........

DROP TABLE or DATABASE with innodb_file_per_table=ON

The same principle applies if innodb_file_per_table is OFF.

The difference however is InnoDB deletes *.ibd file with data from file system when you DROP TABLE or DATABASE. That means the data maybe anywhere in free space of the file system. In this case I recommend to remount disk partition with MySQL data read-only as soon as possible. Otherwise not only MySQL but any process may overwrite the data.

To find the original records you can use grep:

# grep NICKWAHLBERG /dev/sda1

Binary file /dev/sda1 matches

bvi on large files works as bad as vi, so I use hexdump -C and less. Search however is less reliable because strings may be wrapped.

Corrupted InnoDB table

Depending on innodb_file_per_table you can look for the data in ibdata1 or respective *.ibd file. If records look good then the table is recoverable. Often corruption touches headers. For InnoDB it’s critical but data recover toolkit can ignore the corrupted bits and fetch what looks like good records.

You are here

What are odds MySQL table can be recovered?

What are odds MySQL table can be recovered?