Finally. How to verify if all MySQL records were recovered

How to verify if all MySQL records were recoveredAfter nearly every recovery case the same question arises: How many MySQL records were recovered and how many were lost.

Until now there was no way to answer the question without manual investigation. As it turned out a small change can make a big difference.

There are two ways to know how many records an InnoDB page stores. The index page has a header PAGE_N_RECS – this is a number of records the page stores. Obviously it doesn’t count any deleted records. The second method to confirm how many records are in the page is to follow records pointers – and count them.

As you might know, records inside an InnoDB page are organized in an unidirectional list. The list is sorted by primary key and starts with the internal record infinum and ends with another internal record supremum. If you follow record pointers from the infinum and end up with the supremum record then two conclusions can be make:

  1. The page structure is not corrupt
  2. You know how many records to expect

This is what constraints_parser does before it tries to find any records in the page:

./constraints_parser -5f pages-actor/FIL_PAGE_INDEX/0-1599/00000000-00000003.page -V
Checking a page
Infimum offset: 0x63
Supremum offset: 0x70
Next record at offset: 0x7F (127)
Next record at offset: 0xA8 (168)
Next record at offset: 0xCE (206)
Next record at offset: 0x70 (112)
Page is good
Format: COMPACT, Records list: Valid, Expected records: (200 200)

Here you can see “Expected records”: 200 in the header and 200 records were found by pointers.

Then the contraints_parser tries to recover the records. It checks whether fields have permitted values, etc. In the end it knows how many records were actually recovered.

This statistics are printed along with the recovered records:

-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (200 200)
000000001917    DB000001720110  actor   1       "PENELOPE"      "GUINESS"       "2006-02-15 04:34:33"
000000001917    DB00000172011B  actor   2       "NICK"  "WAHLBERG"      "2006-02-15 04:34:33"
000000001917    DB000001720126  actor   3       "ED"    "CHASE" "2006-02-15 04:34:33"
000000001917    DB000001720131  actor   4       "JENNIFER"      "DAVIS" "2006-02-15 04:34:33"
000000001917    DB0000017209E4  actor   200     "THORA" "TEMPLE"        "2006-02-15 04:34:33"
-- Page id: 3, Found records: 200, Lost records: NO, Leaf page: YES

From the output above we know that 200 records were expected, 200 – recovered. So, no records were lost.

It’s worth noting that records are stored in the leaf pages only, so if an index has more than one page no records will be found in the root page.

For the root page it will print “Leaf page: NO” and it can be ignored.

The post Finally. How to verify if all MySQL records were recovered appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com