1. How To Evaluate If MySQL Table Can Be Recovered

How To Evaluate If MySQL Table Can Be Recovered

What Are the Odds a MySQL Table Can Be Recovered?

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

Generally speaking, if data is on some sort of storage, odds are high that TwinDB data recovery toolkit can fetch it. Where to look for it 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 these 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 a table actor from sakila database as an example:

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

It’s likely the record is still in ibdata1 and not overwritten. However, the string may be what 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, the search works the same way. I can scroll down ibdata1 and see what context WOODYHOFFMAN shows up in. Here’s how an InnoDB index page looks like.

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

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 that InnoDB deletes the *.ibd file with data from the file system when you DROP TABLE or DATABASE. That means the data maybe anywhere in the free space of the file system. In this case I recommend to remount the 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:

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 data in ibdata1 or the respective *.ibd file. If records look good then the table is recoverable. Often corruption touches headers. For InnoDB it’s critical, but the data recover toolkit can ignore the corrupted bits and fetch what looks like good records.

Have a question? Ask the experts!

Previous Post Next Post