1. How to evaluate if MySQL table can be recovered

How to evaluate if MySQL table can be recovered

What are odds 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 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:

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:

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.

If you go up you’ll see the infimum and supremum records – those 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 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:

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.

Have a question? Ask the experts!

Previous Post Next Post