Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted
  • 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.
share|improve this answer
    
thankzz mahesh. –  Anto Aug 23 '12 at 4:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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