Data storage in innodb
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.