How Does MySQL Store Data? Inside InnoDB Files, Pages, and Row Formats

1. Where MySQL Stores Data

MySQL stores data on disk, and the exact files used depend on the storage engine. MySQL supports multiple engines such as InnoDB and MyISAM. The default InnoDB engine places its files under /var/lib/mysql/ in a directory named after each database, containing the table definition and data files.

Inside a database directory you will find three files:

db.opt – stores the default character set and collation for the database.

table_name.frm – contains the table’s metadata (structure definition).

table_name.ibd – holds the table’s row data. If innodb_file_per_table is enabled (default since MySQL 5.6.6), each table gets its own .ibd file; otherwise data is stored in the shared tablespace ibdata1 . In MySQL 8 the .frm file is merged into the .ibd file.

2. Structure of the .ibd File

The tablespace consists of segments, extents, pages, and rows. A segment is made of multiple extents; an extent is a 1 MiB block containing 64 pages of 16 KiB each. Pages are the smallest unit that InnoDB reads from or writes to disk.

3. InnoDB Row Formats

InnoDB defines four row formats: Redundant (obsolete), Compact, Dynamic, and Compressed. Compact became the default in MySQL 5.1, while Dynamic is the default since MySQL 5.7. The Compact format is illustrated below.

4. Detailed Compact Format

The Compact row consists of three parts: a variable‑length field length list, a null‑value bitmap, and the record header.

4.1 Variable‑Length Field Length List

For VARCHAR, VARBINARY, TEXT, and BLOB columns, InnoDB stores the actual byte length of each value at the beginning of the record. Example table creation:

<code>CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` VARCHAR(20) DEFAULT NULL,
  `mobile` VARCHAR(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARACTER SET ascii ROW_FORMAT=COMPACT;</code>

When a row is inserted with name='a' (1 byte) and mobile='789' (3 bytes), the length list stores the bytes in reverse column order, resulting in 0301 .

If a variable‑length column is NULL, its length is omitted from the list.

4.2 Null‑Value Bitmap

Columns that allow NULL are represented by a bitmap where each bit corresponds to a column (ordered reverse). A bit value of 1 means the column is NULL, 0 means it is not.

1 – column is NULL

0 – column is not NULL

If no columns are nullable, the bitmap is omitted. The bitmap occupies whole bytes, padding the high bits with zeros when necessary.

Example with no NULLs:

Example with one NULL (mobile):

Bitmap value is 0x02 . When all columns are defined NOT NULL, the bitmap can be omitted to save at least one byte.

4.3 Record Header

The header contains several fields:

delete_mask – indicates if the row is logically deleted.

next_record – pointer to the next row in the linked list.

record_type – 0 for normal row, 1 for B+‑tree internal node, 2 for minimum record, 3 for maximum record.

Reserved bits (2 bits) – currently unused.

min_rec_mask – set to 1 for the smallest record in a non‑leaf node.

n_owned (4 bits) – number of owned records (max 15).

heap_no (13 bits) – position of the row within the page heap (max 8191).

4.4 Real Row Data

Beyond user‑defined columns, each row stores three hidden fields:

row_id – 6 bytes, present only if the table has no primary key or unique index.

trx_id – 6 bytes, the transaction ID that created the row.

roll_pointer – 7 bytes, pointer to the previous version of the row.

5. MySQL Data Size Limits

Except for TEXT and BLOB, the total byte length of all columns in a row (excluding hidden columns and the header) cannot exceed 65 535 bytes. For VARCHAR(n), the maximum n depends on the character set and the bytes taken by the variable‑length list and null bitmap. In ASCII, the effective maximum is 65 532 bytes; in UTF‑8 it is 21 844 characters.

6. Row Overflow

When a row’s data exceeds a 16 KB page, InnoDB stores the excess in overflow pages. In the Compact format, the main page keeps a 20‑byte pointer to the overflow page. Dynamic and Compressed formats use a fully‑overflow approach: the main page stores only the 20‑byte pointer, and the entire column value resides in overflow pages.