0

I had a mysql database stored on a USB thumb drive which has irreparably lost its file allocation table. Therefore, I cannot get to the ibdata1 file as a whole. I can, however locate the record pages which were used using a hex editor.

All the data is there, but I have to read each record myself and play back new SQL statements to a database restored from a 6 month old backup.

Because I have a backup, I know the table structure. and can find a record in the new database that I know roughly equates to a small block of binary data. However, I am having trouble determining exactly where the record starts and decoding the record data.

The CREATE statement for the table is: CREATE TABLE ExpenseTransactions ( idExpenseTransactions int(11) NOT NULL AUTO_INCREMENT, TransactionDate datetime NOT NULL, DollarAmount float DEFAULT NULL, PoundAmount float DEFAULT NULL, Location varchar(255) DEFAULT NULL, MinorCategory int(11) NOT NULL, Comment varchar(255) DEFAULT NULL, Recurring bit(1) NOT NULL DEFAULT b'0', Estimate bit(1) NOT NULL DEFAULT b'0', PRIMARY KEY (idExpenseTransactions), KEY MinorCategory (MinorCategory) ) ENGINE=InnoDB AUTO_INCREMENT=4687 DEFAULT CHARSET=utf8;

A clean record looks like this: '2924', '2013-11-01 00:00:00', '60', NULL, 'George', '66', 'Lawn Maintenance', '1', '0'

The hex bytes associated with this record are next. I am pretty certain have more bytes than necessary to recreate the record, but I have marked what I believe is the id field to try to give some reference point. 10 06 02 00 01 70 00 41 80 00 0B 6C 00 00 00 00 07 05 86 00 00 01 4A 0E B1 80 00 12 4F 23 1F C1 40 00 00 70 42 47 65 6F 72 67 65 80 00 00 42 4C 61 77 6E 20 4D 61 69 6E 74 65 6E 61 6E 63 65 01 00

I can fathom out the strings easily enough and I can pick out the 4 bytes making up the MinorCategory. The last 2 bytes should represent the 2 bit values. The rest is more difficult.

jeremycole
  • 2,741
  • 12
  • 15
James Sudbury
  • 3
  • 1
  • The tool you're writing is already written https://launchpad.net/undrop-for-innodb – akuzminsky May 22 '15 at 23:43
  • undrop-for-innodb helped to pull the pages out of my raw disk data (from ddrescue dump). This at least keeps me from having to track down the pages by hand. Thanks for the link! – James Sudbury May 23 '15 at 14:02
  • A good move with ddrescue. A dying disk may not be readable second time. If you got to `stream_parser` that finds InnoDB pages then check `c_parser`, too. It will fetch records from the pages. See examples on https://twindb.com/recover-corrupt-mysql-database/ – akuzminsky May 23 '15 at 14:57
  • c_parser is definitely awesome for this task. It spits out a tab-separated list of everything I am currently missing. Thank you for the usage link! – James Sudbury May 23 '15 at 18:55

1 Answers1

0

The record in question is correctly identified, and per my blog post The physical structure of records in InnoDB, here's how it decodes:

Header:
10                          Length of Comment = 16 bytes
06                          Length of Location = 6 bytes
02                          Nullable field bitmap (PoundAmount = NULL)
00                          Info flags and number of records owned
01 70                       Heap number and record type
00 41                       Offset to next record = +65 bytes

Record:
80 00 0B 6C                 idExpenseTransactions = 2924
00 00 00 00 07 05           TRX_ID
86 00 00 01 4A 0E B1        ROLL_PTR
80 00 12 4F 23 1F C1 40     TransactionDate = "2013-11-01 00:00:00"
00 00 70 42                 DollarAmount = 60.0
                            (No data, PoundAmount = NULL)
47 65 6F 72 67 65           Location = "George"
80 00 00 42                 MinorCategory = 66
4C 61 77 6E 20 4D 61 69     Comment = "Lawn Maintenance"
6E 74 65 6E 61 6E 63 65     (Comment continues...)
01                          Recurring = 1
00                          Estimate = 0
jeremycole
  • 2,741
  • 12
  • 15