I am new to mysql administration and I came to know about the data storage in myisam is through .frm, .myd and .myi. I could not find the data storage mechanism in InnoDB rather than its storage in tablespace.

Could anyone help me to sort out the storage mechanism in innodb storage machine ?


1 Answer 1

  • Table format files (.frm files) that contain a description of table structure. Every table has its own .frm file, located in the appropriate database directory. This is true no matter which storage engine manages the table.
  • The InnoDB storage engine has its own tablespace and log files. The tablespace contains data and index information for all InnoDB tables, as well as the undo logs that are needed if a transaction must be rolled back. The log files record information about committed transactions and are used to ensure that no data loss occurs. By default, the tablespace and log files are located in the data directory. The default tablespace file is named ibdata1 and the default log files are named ib_logfile0 and ib_logfile1. (It is also possible to configure InnoDB to use one tablespace file per table. In this case, InnoDB creates the tablespace file for a given table in the table's database directory.)
  • Each InnoDB table is represented on disk by an .frm format file in the database directory, as well as data and index storage in the InnoDB tablespace. The InnoDB tablespace is a logical single storage area that is made up of one or more files or partitions on disk. By default, InnoDB uses a single tablespace that is shared by all InnoDB tables. The tablespace is stored in machine-independent format. It is implemented such that table sizes can exceed the maximum file size allowed by the filesystem. It is also possible to configure InnoDB to create each table with its own tablespace.
  • MySQL represents each MyISAM table using three files: a format file that stores the definition of the table structure, a data file that stores the contents of table rows, and an index file that stores any indexes on the table. These files are distinguished from one another by their suffixes. For example, the format, data, and index files for a table named mytable are called mytable.frm, mytable.MYD, and mytable.MYI.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.