I would like to understand these ibdata files as these play vital role in the crash recovery procedure. I could not find proper resources over the web for this.
ibdata1 is the system tablespace for the InnoDB infrastructure.
It contains several classes for information vital for InnoDB
- Table Data Pages
- Table Index Pages
- Data Dictionary
- MVCC Control Data
- Undo Space
- Rollback Segments
- Double Write Buffer (Pages Written in the Background to avoid OS caching)
- Insert Buffer (Changes to Secondary Indexes)
You can divorce Data and Index Pages from ibdata1 by enabling innodb_file_per_table. This will cause any newly created InnoDB table to store data and index pages in an external
- datadir is
CREATE TABLE mydb.mytable (...) ENGINE=InnoDB;, creates
innodb_file_per_tableenabled, Data/Index Pages Stored in
innodb_file_per_tabledisabled, Data/Index Pages Stored in
Here are my past articles on separating table data and indexes from ibdata1
Oct 29, 2010: My Original Post in StackOverflow
Nov 26, 2011: ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full
Feb 03, 2012: Scheduled optimization of tables in MySQL InnoDB
Mar 25, 2012: Why does InnoDB store all databases in one file?
Apr 01, 2012: Is innodb_file_per_table advisable?
iblog files (a.k.a.
If you want to know what the
ib_logfile1 are for, they are the InnoDB Redo Logs. They should never be erased or resized until a full normal shutdown of mysqld has taken place. If mysqld ever crashes, just start up mysqld. It will read across
ib_logfile1 to check for any data changes that were not posted to the the double write buffer in
ibdata1. It will replay (redo) those changes. Once they are replayed and stored, mysqld becomes ready for new DB Connections.