ibdNinja 🥷

A powerful C++ tool for parsing and analyzing MySQL (.ibd) data files

中文

Contents

1. Key Features of ibdNinja

2. Examples of ibdNinja Usage

3. Highlight: Parsing Records with Instant Add/Drop Columns

4. Highlight: Inspecting JSON LOB Version Chains

5. Limitations

1. Key Features of ibdNinja

1. Parsing SDI Metadata

Extracts and analyzes the dictionary information of all tables and indexes contained in an ibd file from its SDI (Serialized Dictionary Information).

2. Dynamic Parsing of Records Across Multiple Table Definition Versions *

With the parsed dictionary information, ibdNinja supports parsing and printing any record from any page of any index* in any table* (supporting all column types). Moreover, it can dynamically adapt to parse records in tables with multiple coexisting schema versions caused by repeated instant add column and instant drop column operations.

Detailed explanations and examples are provided in Section 3

3. Multi-Dimensional Data Analysis

Powered by its record parsing capabilities, ibdNinja enables comprehensive data analysis across multiple levels, including Record, Page, Index, and Table levels. It computes and presents multi-dimensional statistics:

Record Level:

• Total size of the record (header + body), the number of fields, and whether the record contains a deleted mark.
• Hexadecimal content of the header.
• Detailed information for each field (including user-defined columns, system columns, and instant added/dropped columns), such as:
  • Field name
  • Field size in bytes
  • Field type
  • Hexadecimal content of the field value

Page Level:

• The number of valid records, their total size, and the percentage of page space they occupy.
• The count of records containing instant dropped columns and the size and page space percentage of these dropped but still allocated columns.
• The count, total size, and page space percentage of records marked as deleted.
• The space utilized internally by InnoDB (e.g., page header, record headers, page directory), along with its percentage of the page.
• The size and percentage of free space within the page.

Index Level:

• For a specific index, analyzes and aggregates statistics for all its pages starting from the root page.
• Statistics are presented separately for non-leaf levels and leaf levels, similar to the statistics provided at the page level.

Table Level:

• For a given table, starts from its primary index and analyzes each index to display its statistics

4. Printing Leftmost Pages of Each Index Level:

Allows users to print the leftmost page number of each level for a specified index, making it easier to manually traverse and print every record in the index page by page.

5. Inspecting External BLOB/JSON Fields (--inspect-blob, -I PAGE,REC)

For records containing externally stored fields (BLOB, TEXT, JSON, etc.), ibdNinja can inspect the LOB (Large Object) storage structure:

• LOB chain visualization: Shows all LOB index entries, data page locations, version numbers, and transaction IDs.
• Version chain traversal (JSON fields): Displays the full version history created by JSON_SET() partial updates, allowing you to view any historical version of a JSON document.
• Purge detection: Detects when old LOB versions have been purged by InnoDB, reports missing version numbers, displays the free list of recycled entries, and offers interactive fallback to the closest available version.

6. [TODO] Repairing Corrupted ibd Files

With ibdNinja's capability to parse records, it is possible to address ibd files with corrupted index pages. By removing damaged records from pages or excluding corrupted pages from indexes, the tool can attempt to recover the file to the greatest extent possible.

2. Examples of ibdNinja Usage

Compiling is straightforward—just run make in the current directory.

1. Display Help Information (--help, -h)

<img src="https://github.com/KernelMaker/kernelmaker.github.io/blob/master/public/images/ibdNinja-diagram/1-v2.png" alt="image-1" width="80%" />

2. List Tables and Indexes in the ibd File (--list-tables, -l)

Using the system tablespace file mysql.ibd as an example, after specifying the file with the --file or -f option, the output provides:

<img src="https://github.com/KernelMaker/kernelmaker.github.io/blob/master/public/images/ibdNinja-diagram/2.png" alt="image-2" width="60%" />

1. A summary of the ibd file, including the number of tables and indexes successfully parsed and loaded from the data dictionary.
2. The table IDs and names of all tables in the file.
3. For each table, all index IDs, root page numbers, and index names.

With this information, you can explore the ibd file further using other commands.

3. Parse and Print a Specific Page (--parse-page, -p PAGE_ID)

Continuing with mysql.ibd as an example, let’s parse the root page of the PRIMARY index for the mysql.innodb_index_stats table (its root page number is 7, as shown in the previous example).

Run the following command:

./ibdNinja -f ../innodb-run/mysqld/data/mysql.ibd -p 7

The output consists of three parts:

1. Page Summary: Information such as sibling page numbers (left and right), the index the page belongs to, the page level, etc.

<img src="https://github.com/KernelMaker/kernelmaker.github.io/blob/master/public/images/ibdNinja-diagram/3.png" alt="image-3" width="60%" />

2. Record Details: For each record in the page, details like:

<img src="https://github.com/KernelMaker/kernelmaker.github.io/blob/master/public/images/ibdNinja-diagram/4.png" alt="image-4" width="60%" />

• Total length of the record (header + body), field count, and whether it has a delete mark.
• A hexadecimal dump of the record header.
• Detailed information for each field (e.g., name, length, type, and the hexadecimal value).

3. Page Analysis Summary:

Includes statistics such as:

<img src="https://github.com/KernelMaker/kernelmaker.github.io/blob/master/public/images/ibdNinja-diagram/5.png" alt="image-5" width="60%" />

• Number, total size, and space usage percentage of valid records.
• Number and size of records with instant dropped columns, as well as their space usage percentage.
• Number and size of delete-marked records, and their space usage percentage.
• Space used by InnoDB internal components (e.g., page headers), along with their percentages.
• Free space size and percentage.

4. Analyze a Specific Index (--analyze-index, -i INDEX_ID)

Using mysql.ibd again, first obtain the table and index information using the --list-tables (-l) command.

<img src="https://github.com/KernelMaker/kernelmaker.github.io/blob/master/public/images/ibdNinja-diagram/6.png" alt="image-6" width="60%" />

For example, the mysql.tables table has an ID of 29 and contains 10 indexes. To analyze the PRIMARY index (ID 78), run:

./ibdNinja -f ../innodb-run/mysqld/data/mysql.ibd -i 78

<img src="https://github.com/KernelMaker/kernelmaker.github.io/blob/master/public/images/ibdNinja-diagram/7.png" alt="image-7" width="60%" />

ibdNinja traverses the PRIMARY index from its root page, analyzing it level by level and page by page, then summarizes the statistics:

1. Overview: Includes the index name, number of levels, and number of pages.
2. Non-Leaf Levels Statistics: Provides page count, record count, and various space usage details.
3. Leaf Level Statistics: Similar to the above, but specific to the leaf level.

5. Analyze a Specific Table (--analyze-table, -t TABLE_ID)

Using mysql.ibd again, first run the --list-tables (-l) command to get table and index information. For the mysql.tables table with an ID of 29, execute:

./ibdNinja -f ../innodb-run/mysqld/data/mysql.ibd -t 29

This command analyzes all 10 indexes of the mysql.tables table and outputs their statistics. Each index's structure is similar to the output of --analyze-index.

6. List the Leftmost Page Number for Each Level of an Index (--list-leftmost-pages, -e INDEX_ID)

Continuing with the mysql.ibd example, the PRIMARY index of the mysql.tables table has an ID of 78. Run the following command:

./ibdNinja -f ../innodb-run/mysqld/data/mysql.ibd -e 78

<img src="https://github.com/KernelMaker/kernelmaker.github.io/blob/master/public/images/ibdNinja-diagram/8.png" alt="image-8" width="60%" />

The output shows the leftmost page number for each level of the index. For example:

• Level 1 (non-leaf) has a leftmost page number of 82.
• Level 0 (leaf level) has a leftmost page number of 161.

You can then use the --parse-page (-p PAGE_NO) command to print detailed information for these pages. From the sibling page numbers, you can continue parsing the left and right pages to traverse the entire index.

Note: To skip printing record details for a page (e.g., to avoid excessive output), use the --no-print-record (-n) option along with -p, as in:-p 161 -n

7. Inspect External BLOB/JSON Fields (--inspect-blob, -I PAGE,REC)

For records with externally stored fields (BLOB, TEXT, JSON), use --inspect-blob to examine the LOB storage structure and version history. First use --parse-page (-p) to find the leaf page and identify the record number (1-based position in the page), then pass them to -I:

./ibdNinja -f test.ibd -I 4,1

The tool will:

1. List all external fields in the record and let you select one.

2. Display the LOB chain visualization showing index entries, data pages, version chains, and free list entries.

3. Offer an interactive menu:

   For JSON fields:

   • [1] Print current version (hex)
   • [2] Print current version (JSON text)
   • [3] Save curr