Changes to a MySQL InnoDB table - which files are involved and why?

Question

When changing the content of an InnoDB MySQL table the following files on the file system appears to be involved/changed:

• /path/to/mysql/data/[database]/[table].ibd (due to innodb_file_per_table)
• /path/to/mysql/data/data/ib_logfile0
• /path/to/mysql/data/data/ib_logfile1
• /path/to/mysql/data/data/ibdata1

For each of these files:

1. When is the file created?
2. When will the file be written to?
3. When will the file be read from?
4. What would be the consequence if the file is corrupt or deleted?

Answer 1

/path/to/mysql/data/[database]/[table].ibd (due to innodb_file_per_table)

This is where you data are stored. They are created when you create the tables.

/path/to/mysql/data/data/ib_logfile0
/path/to/mysql/data/data/ib_logfile1

These are logfiles.

All data changes are written into the logfiles sequentially, which allows write-ahead logging (crucial for transactions)

/path/to/mysql/data/data/ibdata1

This is where system data and UNDO data are stored.

If ibdata is not found, MySQL will think that InnoDB engine is not initialized and just create the new ibdata. Same with logfiles.

If a query is issued against a table, and .ibd file is not found, MySQL will fail with this message:

Cannot find table database/table from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database?