MySQL uses a number of different file formats for the storage of information. Regardless of the storage engine, every MySQL table in an existing database is represented, on disk, by a .frm file, which describes the definition of the table. The .frm file is named after the table. The .frm format is the same on all platforms.
As an example, I create a new table:
Immediately, a .frm file is created. The .frm file related to this new table is stored in the data directory. You can easily find the location of this directory with a query:
As you can see, in my case the data directory is c:\xampp\mysql\data
Browse to this data directory and open the subdirectory of your database:
Inside the data directory you see subdirectories for all databases that exists on your server. If you want to know which database you're currently working with, you can use:
As you can see, I'm currently working in the "nieuw" database. Now I know this, it's easy for me to find the .frm file, because it has to be c:\xampp\mysql\data\nieuw\table1.frm
Use a hex viewer (for example, FTK Imager) to look at the contents of the .frm file:
OFFSETS
FILE HEADER
The header of the .frm file always starts with FE 01 (offset 0000)
offset 0002 shows the frm_ver, offset 0003 shows the database type (for example 09 is DB_TYPE_MYISAM, 14 is MYISAM with partitioning)
offset 0005 is always 00
offset 0006 shows the IO_SIZE
offset 0010 shows the length
offset 0014 shows the 'tmp_key_length' based on the key_length
offset 0016 shows the rec_length
offset 0022 shows the create_info->max_rows
offset 0024 shows the create_info->min_rows
offset 0027 is always 02, which means "use long pack-fields"
offset 0028 shows the key_info_length
offset 0030 shows create_info->table_options, also known as db_create_options. One possible option is HA_LONG_BLOB_PTR
offset 0032 is always 00, offset 0033 is always 05 (which means "version 5 frm file")
offset 0033 shows create_info->avg_row_length (length: 4)
offset 0038 shows create_info->default_table_charset
offset 0039 is always 00
offset 0040 shows create_info->row_type
offset 0041 is always 00..00 (formerly used for RAID support)
offset 0047 shows the key_length
offset 0051 shows the MYSQL_VERSION_ID
offset 0055 shows create_info->extra_size
offset 0059 is reserved for extra_rec_buf_length
offset 0061 is reserved for default_part_db_type
offset 0062 shows create_info->key_block_size
offset 0064 shows the end of the header
KEY_INFO
offset 4096 is always 00 when there are no keys / indexes
In case the table has a primary key the text PRIMARY will appear in the .frm file. In case there's also an index (in my example: blabla) it will also be shown (after PRIMARY)
offset 4122 shows MYISAM, the name of the storage engine (please not that the exact offset can differ)
offset 8238 shows the length of the comment
offset 8239 shows the string in the COMMENT clause (length: 40). During the creation of my test table I wrote:
In offset 8239 you can see the * that I added in my comment!
COLUMNS
offset 8448 is always 01
offset 8450 shows share->fields (number of columns)
offset 8452 shows pos ("length of all screens"). Goes up if column-name length increases. Doesn't go up if add comment.
offset 8454 is based on number of bytes in row.
offset 8460 shows n_length. Goes up if row length increases
offset 8462 shows interval_count. Number of different enum/set columns
offset 8464 shows interval_parts. Number of different strings in enum/set columns
offset 8466 shows int_length
offset 8474 shows share->null_fields. Number of nullable columns
offset 8476 shows com_length
offset 8530 shows the length of column-name including '\0' termination
offset 8531 shows the column name, because I used:
the column name in the .frm file is "column1\0"
offset 8541 shows the number of bytes in the column
offset 8542 shows the number of bytes in the column
offset 8547 shows flags for zerofill, unsigned, etc.
offset 8548 shows additional flags, and scale if decimal/numeric
offset 8552 shows the data type of the column (fe=char, 02=smallint, 03=int etc.)
offset 8553 shows the character set or geometry type
.MYI FILE
As soon as the table has been created, a .myi file is added to the data directory of the database. A .myi file for a MyISAM table contains the table's indexes.
The .MYI file has two parts: the header information and the key values.
The .MYI Header
A .MYI file begins with a header, with information about options, about file sizes, and about the "keys". In MySQL terminology, a "key" is something that you create with CREATE [UNIQUE] INDEX.
These are the main header sections:
state (occurs 1 time)
base (occurs 1 time)
keydef (occurs one for each key)
recinfo (occurs once for each field)
a blogpost about MySQL indexes
.MYD FILES
MYD is the associated signature to MySQL MyISAM data files. As soon as the table is created, a .myd file is added to the data directory of the database. In this .myd file you can find the actual records of the database table. As long as the table contains no records, the .myd file is empty.
Variable length records are contained in "frames". A record can be put in one or more frames, also called the record "parts" or "blocks". The sense of the frames is to allow reusage of the space of deleted records. Starting with an empty data file, records are put in a single frame each, unless a record is bigger than the maximum frame size (16MB - 4). When a record is deleted, its frame is marked deleted. When a record is inserted after this, it reuses the old frame. If the new record is smaller, the frame is split. The unused part is marked deleted. If a new record is bigger than the old frame, the frame is filled with the record as much as fits. The rest is inserted in other old frames, or, if non is available, in a new frame at end of file.
Comments