MySQL Internals File Formats
← Back to MySQL Internals overview page
[edit] File Formats
MySQL uses a number of different file formats for the storage of information. This section covers the different file formats and how to read, write and understand the contents.
[edit] MySQL .frm File Format
Regardless of the storage engine you choose, every MySQL table you create is represented, on disk, by a .frm file, which describes the table's format (i.e. the table definition). The file bears the same name as the table, with a .frm extension. The .frm format is the same on all platforms but in the description of the .frm format that follows, the examples come from tables created under the Linux operating system.
First, let's create an example table, using the mysql client:
mysql> CREATE TABLE table1 (column1 CHAR(5)) ENGINE=MYISAM COMMENT '*'; Query OK, 0 rows affected (0.00 sec)
The .frm file associated with table1 can be located in the directory that represents the database (or schema) to which the table belongs. The datadir variable contains the name of this directory:
mysql> SHOW VARIABLES LIKE 'datadir'; +---------------+-----------------------+ | Variable_name | Value | +---------------+-----------------------+ | datadir | /usr/local/mysql/var/ | +---------------+-----------------------+ 1 row in set (0.00 sec)
The DATABASE() function contains the name of the relevant database:
mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | ff | +------------+ 1 row in set (0.00 sec)
Since MySQL stores .frm files in datadir/database_name, it's a simple matter to locate the corresponding .frm file for table1. For example, within a Linux shell:
shell> su root shell> cd /usr/local/mysql/var/ff shell> ls table1.*
You'll see a response like the following:
shell> su root shell> cd /usr/local/mysql/var/ff shell> ls table1.* table1.frm table1.MYD table1.MYI shell> ls -l table1.* -rw-rw---- 1 root root 8566 2006-09-22 11:22 table1.frm -rw-rw---- 1 root root 0 2006-09-22 11:22 table1.MYD -rw-rw---- 1 root root 1024 2006-09-22 11:22 table1.MYI
The .MYD and .MYI files are not our concern here; they are described elsewhere in this MySQL internals manual. To understand the .frm format, let's look at table1.frm using a hexadecimal-dump utility; the contents are show below.
shell> hexdump -v -C table1.frm 00000000 fe 01 09 09 03 00 00 10 01 00 00 30 00 00 10 00 |...........0....| 00000010 06 00 00 00 00 00 00 00 00 00 00 02 08 00 08 00 |................| 00000020 00 05 00 00 00 00 08 00 00 00 00 00 00 00 00 10 |................| 00000030 00 00 00 c0 c3 00 00 10 00 00 00 00 00 00 00 00 |................| 00000040 2f 2f 00 00 20 00 00 00 00 00 00 00 00 00 00 00 |//.. ...........| ... | (many 0s) | 00001000 00 00 00 00 02 00 ff 00 00 00 00 00 00 00 00 00 |................| 00001010 ff 20 20 20 20 20 00 00 06 00 4d 79 49 53 41 4d |. ....MyISAM| ... | (many 0s) | 00002000 6c 01 00 10 00 00 00 00 00 00 00 00 00 00 00 00 |l...............| 00002010 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00002020 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 2a |...............*| ... 00002100 01 00 01 00 3b 00 05 00 00 00 06 00 0a 00 00 00 |....;...........| 00002110 00 00 00 00 00 00 50 00 16 00 01 00 00 00 00 00 |......P.........| 00002120 3b 00 02 01 02 14 29 20 20 20 20 20 20 20 20 20 |;.....) | 00002130 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 | | 00002140 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 00 | .| 00002150 04 00 08 63 6f 6c 75 6d 6e 31 00 04 08 05 05 00 |...column1......| 00002160 02 00 00 00 80 00 00 00 fe 08 00 00 ff 63 6f 6c |.............col| 00002170 75 6d 6e 31 ff 00 |umn1..| 00002176
The details shown above might change, especially since there is a transition underway from an old ("binary") format to a new ("text based") .frm format. You can confirm that the details are correct by comparing this description with the statements in sql/table.cc, create_frm(). The table below explains the meaning of each byte in the hexadecimal dump shown in the preceding example. The Offset column shows the byte position in the file; Length is the number of bytes; Value is what's in that byte position for that length (remember that storage is "low byte first" so 0010 means 1000!); and Explanation provides a brief explanation of the contents.
| Offset | Length | Value | Explanation |
| header | ?? | ?? | ?? |
| 0000 | 1 | fe | Always |
| 0001 | 1 | 01 | Always |
| 0002 | 1 | 09 | FRM_VER (which is in include/mysql_version.h) +3 +test(create_info->varchar)
|
| 0003 | 1 | 09 | See enum legacy_db_type in sql/handler.h. e.g. 09 is DB_TYPE_MYISAM, but 14 if MyISAM with partitioning.
|
| 0004 | 1 | 03 | ?? |
| 0005 | 1 | 00 | Always |
| 0006 | 2 | 0010 | IO_SIZE
|
| 0008 | 2 | 0100 | ?? |
| 000a | 4 | 00300000 | Length, based on key_length + rec_length + create_info->extra_size
|
| 000e | 2 | 1000 | "tmp_key_length", based on key_length
|
| 0010 | 2 | 0600 | rec_length
|
| 0012 | 4 | 00000000 | create_info->max_rows
|
| 0016 | 4 | 00000000 | create_info->min_rows
|
| 001b | 1 | 02 | Always (means "use long pack-fields") |
| 001c | 2 | 0800 | key_info_length
|
| 001e | 2 | 0800 | create_info->table_options also known as db_create_options? one possible option is HA_LONG_BLOB_PTR
|
| 0020 | 1 | 00 | always |
| 0021 | 1 | 05 | Always (means "version 5 frm file") |
| 0022 | 4 | 00000000 | create_info->avg_row_length
|
| 0026 | 1 | 08 | create_info->default_table_charset
|
| 0027 | 1 | 00 | Always |
| 0028 | 1 | 00 | create_info->row_type
|
| 0029 | 6 | 00..00 | Always (formerly used for RAID support) |
| 002f | 4 | 10000000 | key_length
|
| 0033 | 4 | c0c30000 | MYSQL_VERSION_ID from include/mysql_version.h
|
| 0037 | 4 | 10000000 | create_info->extra_size
|
| 003b | 2 | 0000 | Reserved for extra_rec_buf_length
|
| 003d | 1 | 00 | Reserved for default_part_db_type, but 09 if MyISAM with partitioning
|
| 003e | 2 | 0000 | create_info->key_block_size
|
| key_info | ?? | ?? | ?? |
| 1000 | 1 | 00 | Always 00 when there are no keys i.e. indexes |
| ... | ?? | ?? | ?? |
| 101a | 6 | "MyISAM" | Name of engine. If partitioning, the partition clauses are here |
| comment | ?? | ?? | ?? |
| 202e | 1 | 01 | Length of comment |
| 202f | 40 | "*" | The string in the COMMENT clause |
| columns | ?? | ?? | ?? |
| 2100 | 2 | 01 | Always |
| 2102 | 2 | 0100 | share->fields (number of columns)
|
| 2104 | 2 | 3b00 | pos ("length of all screens"). Goes up if column-name length increases. Doesn't go up if add comment.
|
| 2106 | 2 | 0500 | Based on number of bytes in row. |
| 210c | 2 | 0500 | n_length. Goes up if row length increases
|
| 210e | 2 | 0000 | interval_count. Number of different enum/set columns
|
| 2110 | 2 | 0000 | interval_parts. Number of different strings in enum/set columns
|
| 2112 | 2 | 0000 | int_length
|
| 211a | 2 | 0100 | share->null_fields. Number of nullable columns
|
| 211c | 2 | 0000 | com_length
|
| 2152 | 1 | 08 | Length of column-name including '\0' termination |
| 2153 | 3 | "column1\0" | column-name |
| 215b | 1 | 04 | ?? |
| 215c | 1 | 03 | ?? |
| 215d | 1 | 05 | Number of bytes in column |
| 215e | 1 | 05 | Number of bytes in column |
| 215f | 4 | 00020000 | ?? |
| 2163 | 1 | 00 | Flags for zerofill, unsigned, etc. |
| 2164 | 1 | 80 | Additional flags, and scale if decimal/numeric |
| 2168 | 1 | fe | Data type (fe=char, 02=smallint, 03=int, etc.) see enum field_types in include/mysql_com.h
|
| 2169 | 1 | 08 | Character set or geometry type |
| (later) | ?? | ?? | Column names again, defaults, enum/set strings, column comments ... at end of row. not shown. |
The .frm file for a partitioned table contains partition information, in clear text, in addition to the usual table definition details. Let's create a partitioned table and do a hexadecimal dump of its .frm.
mysql> CREATE TABLE table2 (column1 INT) ENGINE=MYISAM COMMENT '*'
PARTITION BY HASH(column1) PARTITIONS 2;
Query OK, 0 rows affected (0.00 sec)
The hexadecimal dump from table2 is shown below:
00000000 fe 01 09 14 03 00 00 10 01 00 00 30 00 00 10 00 |...........0....| 00000010 05 00 00 00 00 00 00 00 00 00 00 02 08 00 08 00 |................| 00000020 00 05 00 00 00 00 08 00 00 00 00 00 00 00 00 10 |................| 00000030 00 00 00 c0 c3 00 00 3d 00 00 00 00 00 09 00 00 |.......=........| 00000040 2f 2f 00 00 20 00 00 00 00 00 00 00 00 00 00 00 |//.. ...........| 00000050 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| ... 00001000 00 00 00 00 02 00 ff 00 00 00 00 00 00 00 00 00 |................| 00001010 ff 00 00 00 00 00 00 09 00 70 61 72 74 69 74 69 |.........partiti| 00001020 6f 6e 2a 00 00 00 20 50 41 52 54 49 54 49 4f 4e |on*... PARTITION| 00001030 20 42 59 20 48 41 53 48 20 28 63 6f 6c 75 6d 6e | BY HASH (column| 00001040 31 29 20 50 41 52 54 49 54 49 4f 4e 53 20 32 20 |1) PARTITIONS 2 | 00001050 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| ... 00002000 76 01 00 10 00 00 00 00 00 00 00 00 00 00 00 00 |v...............| 00002010 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00002020 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 2a |...............*| 00002030 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| ... 00002100 01 00 01 00 3b 00 0b 00 00 00 05 00 0a 00 00 00 |....;...........| 00002110 00 00 00 00 00 00 50 00 16 00 01 00 00 00 00 00 |......P.........| 00002120 3b 00 02 01 02 14 29 20 20 20 20 20 20 20 20 20 |;.....) | 00002130 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 | | 00002140 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 00 | .| 00002150 04 00 08 63 6f 6c 75 6d 6e 31 00 04 08 0b 0b 00 |...column1......| 00002160 02 00 00 1b 80 00 00 00 03 08 00 00 ff 63 6f 6c |.............col| 00002170 75 6d 6e 31 ff 00 |umn1..| 00002176
In the example output, notice that position 00001010 and following contains the clear text of the CREATE TABLE ... PARTITION clause and not just the MYISAM engine information, as in table1, which shows the .frm of a non-partitioned table.
Finally, CREATE VIEW also causes creation of a .frm file, but a view .frm bears no resemblance to a base table .frm; it's purely textual. Here's an example of a .frm for a view made with:
mysql> CREATE VIEW v AS SELECT 5; Query OK, 0 rows affected (0.00 sec)
Just looking at the text will tell you what it's about. For example let's do another hexadecimal dump; the contents are shown below.
linux:/usr/local/mysql/var/d # hexdump -v -C v.frm 00000000 54 59 50 45 3d 56 49 45 57 0a 71 75 65 72 79 3d |TYPE=VIEW.query=| 00000010 73 65 6c 65 63 74 20 35 20 41 53 20 60 35 60 0a |select 5 AS `5`.| 00000020 6d 64 35 3d 38 64 39 65 32 62 62 66 64 35 33 35 |md5=8d9e2bbfd535| 00000030 66 35 37 39 64 34 61 39 34 39 62 39 65 62 37 64 |f579d4a949b9eb7d| 00000040 32 33 34 39 0a 75 70 64 61 74 61 62 6c 65 3d 30 |2349.updatable=0| 00000050 0a 61 6c 67 6f 72 69 74 68 6d 3d 30 0a 64 65 66 |.algorithm=0.def| 00000060 69 6e 65 72 5f 75 73 65 72 3d 72 6f 6f 74 0a 64 |iner_user=root.d| 00000070 65 66 69 6e 65 72 5f 68 6f 73 74 3d 6c 6f 63 61 |efiner_host=loca| 00000080 6c 68 6f 73 74 0a 73 75 69 64 3d 32 0a 77 69 74 |lhost.suid=2.wit| 00000090 68 5f 63 68 65 63 6b 5f 6f 70 74 69 6f 6e 3d 30 |h_check_option=0| 000000a0 0a 72 65 76 69 73 69 6f 6e 3d 31 0a 74 69 6d 65 |.revision=1.time| 000000b0 73 74 61 6d 70 3d 32 30 30 36 2d 30 39 2d 32 32 |stamp=2006-09-22| 000000c0 20 31 32 3a 31 34 3a 34 38 0a 63 72 65 61 74 65 | 12:14:48.create| 000000d0 2d 76 65 72 73 69 6f 6e 3d 31 0a 73 6f 75 72 63 |-version=1.sourc| 000000e0 65 3d 73 65 6c 65 63 74 20 35 0a |e=select 5.|