MYI Files

What is a .MYI file?

File .MYI refers to the MyISAM table index used by MySQL databases, which the name stands for MySQL Index. The MyISAM system has been designed by MySQL AB to replace previous versions using ISAM tables, and Oracle together with MariaDB project keep supporting it in current versions 8.4 of MySQL and 11. MariaDB although the default engine remains InnoDB.

As a whole, a full MyISAM table uses one common base filename among several files where .MYI holds index pages with table information, .MYD has row information while usually, a third file .frm has information about table structure. One can find files .MYI among the other MySQL data, inside databases of MySQL hosting account, in backups or on files restored after crash from MySQL database server.

File is not Excel file and cannot be considered as independent data export because of meaninglessness for single file except of presence of relevant schema (.frm), row file (.myd) or MySQL database itself for new installations.

Extends are uniquely correlated with MYISAM, thus there are no other file formats currently used with MYISAM extension. To determine if this true you have to make sure if your file is binary and starts with FE FE bytes at offset 0. Also you should look around if there are files of your table with .MYD and .frm extensions.

How to open a .MYI file

Technical details

A MySQL index file, .MYI, does not store plain text, but its content is binary data that requires a MySQL or MariaDB engine to be accessed properly. For example, in hex viewer or browser, the only possible read without any risks would be raw data view without any attempt to interpret the contents. MyISAM files share one general signature FE FE at offset 0, and the index header contains engine state options, keys data, record count, options, repair state. Structure may change between versions of MySQL AB/Oracle and MariaDB software packages. Since there is no any kind of standard for such on-disk file representation, cross-version recovery can hardly be done except through myisamchk tool or CHECK TABLE command on some compatible server rather then through an invented structure description parser. Actually, a latest implementation, present in version 8.4 of MySQL, uses exactly the same file format.

The file keeps B-tree pages for indexes of primary, UNIQUE, ordinary, and fulltext keys, information about the table state. Row storage is separated, it is located in corresponding .MYD table files. The only stored information in index file consists in row pointers and index keys (column data is not stored); the only encoding used is the one that defined by MySQL table definition (charset/collation); MyISAM stores indexes only in fixed row-length form; it does not compress the .MYI indexes themselves (the feature of packed table in case of compressed rows only). Size depends on the table size (number or rows and length of index keys, number of indexes etc.) - may be few kilobytes for small tables and many gigabytes otherwise. There is no special MIME type associated to .MYI, just application/octet-stream (by definition of file-based databases like web servers).

How to convert .MYI files

Portable table-to-table conversion procedure: run the utility mysqldump with access to a running MySQL server, which has access in turn to the required table files, for example: mysqldump -u USER -p DATABASE TABLE > table.sql. This way of saving table data produces an output table definition file (.sql), from which you would be able to create a database table again elsewhere.

Query output should be saved as .csv or .tsv using mysql command, workbench, phpMyAdmin, DBeaver or Navicat. A sample mysql command is: mysql -u USER -p -e “SELECT * FROM DATABASE.TABLE” --batch --raw > table.tsv. Exporting to Excel format .xlsx is practical for business users, although no database objects such as indexes, constraints and triggers are included.

To bring table engine up to date in MySQL/MariaDB: run ALTER TABLE DATABASE.TABLE ENGINE=InnoDB; InnoDB has different locking rules and improved data protection capabilities in addition to using B-Tree-based indexes like ISAM and MyISAM.

.MYI file (table indexing information file) is uninformative without both the related MYD (MYTable Data) data file and SQL table creation information (SQL schema). If you have only an MYI file at disposal, all the data from the file you could get would be fragments of indexed fields only. The procedure for PostgreSQL/SQLite is: Start from a SQL/CSV file and perform auto_increment/unsigned integers/datetime/collations and indexes mapping manually.

Raw files are physically archived using tar and zip utility, so it is more appropriate for a data backup.

Common problems and fixes

Error 1016 (file is unavailable or missing). Usually this issue implies that the file containing indexes in the file MyISAM table could be either lost or corrupted, or there can be any problems with permissions and access privileges. Check the owner and permission for the .MYI file; check that index files stay with your data (.MYD) and with your table’s metadata files, respectively. Make sure you got all these files during your backup procedure because the restoration will make sense only from exactly the same snapshot in case if you are restoring several tables.

Error 126 ("wrong key file for the table"). This message means that indexes are corrupted but in some cases it could happen even if there is not enough free space on the filesystem containing temporary or MySQL data files or on some data filesystem. Check the availability of the free diskspace and try running CHECK TABLE or myisamchk on your table offline (or if your MySQL service is running - stop it).

Error 145 ("crashed" tables). Most of the cases it happens either after unexpected power-off, crash, etc. Try to start with table’s repair, if MySQL doesn’t let you do - stop your server, mysqld, use myisamchk --recover command for your offline table .MYI file.

"The table is not existing" - if the error happens and at least two other files (usually it is .MYI and .MYD files) exists on your file system then either the problem with your schema’s metafile and it’s missing or simply those two were put under another folder, wrong folder. The other possibility (if you’re restoring) is a case sensitivity problem (your MySQL has to allow to have the tables in mixed-case while another one allows tables with lowercase names only. Use mysqldump utility.

Also keep in mind if you are copying .MYI tables while your mysql server still writes something - it might lead to inconsistent .MYI backups; use locks, shutdown or use database aware backup. As for version problems (from newer MySQL/MariaDB -> older one; or older <-> MySQL/MariaDB), use the closest possible version of the source to dump logical dump with mysqldump utility.

Security considerations

The MYI files lack any form of macro, script or even ordinary executables thus the risks posed by them are not great as compared to the office files or installations. Nonetheless, they may still result in crashes, as well as corruption of a parser of MariaDB/MySQL database. This is especially important as the utilities for use during a repair of MyI files such as myisamchk needed handling of corrupted index files since the beginning of the MyISAM era; hence you should open unknown files using a patched server preferably inside the disposable VM/container. Since you can obtain indexed values through a .MYI file, then you can get key parts and pointers contained inside the index pages that might include names, emails, IDs, tokens etc. You may obtain information on table names, indexes, columns, number of rows as well as other details that will be used for database mapping by the hacker.

History and versions

MyISAM was developed by MySQL AB in 1995 in replacement of ISAM storage table handler. The structure and file layout soon became distinguishable in which index was put into .MYI format while rows into .MYD format. Around the time of release of 3.23 line in circa 2001, MyISAM began to be used widely as the default storage engine in the market and is common in legacy web application. Its success is contributed to its simplicity of file structure, performance speed when the database is often read by users, FULLTEXT indexes which are available before those from InnoDB engine, and simplicity in using table recovery tools including myisamchk. However, when MySQL 5.5 released in 2010 made transaction system available by changing it to be InnoDB by default, new adoption MyISAM reduced due to transaction. Following that, MySQL 8.0 stopped using .frm file in most cases for data dictionary while MyISAM is still useful in handling legacy tables and even systems-like database. MariaDB supported both MyISAM and offers Aria database engines as an alternative, thus MyISAM still requires to be recover in hosting environment due to the need to use .MYI format.

Related formats

When all columns of an existing MyISAM table are occupied, row-level information will be stored using the .MYD files and indexing as well as other properties are recorded using .MYI files. Both .MYD files and .MYI files usually include an obsolete .frm metadata file from previous versions of MySQL below the 8 series versions. It becomes difficult to recover data when this metadata does not exit since indexes do not provide information about schema. Although InnoDB has the better design for transactions and recovering from any crashes than MyISAM but this engine has complex process compared to copying all three files. Aria uses the .MAI and .MAD and provides the crash protection solution to MyISAM under specific circumstances. It can be observed that .sql files are easy for portable solutions whereas others store the textual data information. The single SQLite database files such as.sqlite have .db extension which is not a solution for MyISAM tables.