​Understanding MySQL's ibdata File: Core Functions Explained

The ibdata file is the core system tablespace file for the InnoDB storage engine. InnoDB stores metadata and data here, primarily including: the data dictionary, Change Buffer, Doublewrite Buffer, Undo Logs, and table data.

The ibdata file (typically named ibdata1, though multiple can exist) is a composite file responsible for storing the following:

1. ​Data Dictionary​
   This is the most critical metadata. It records structural information for all InnoDB tables, columns, and indexes. For example, if you create a table like users, details such as its schema, field types, and indexes are stored in the data dictionary. Without this, InnoDB cannot locate or interpret your data.
2. ​Change Buffer​
   Often translated as "Change Buffer," this is a specialized data structure that caches changes to non-unique secondary indexes (e.g., from INSERT, DELETE, or UPDATE operations). When these index pages are not in memory, changes are first cached in the Change Buffer. Later, when the pages are loaded into the buffer pool, the changes are merged in. This reduces disk I/O and improves write performance.
3. ​Doublewrite Buffer​
   Commonly called "Doublewrite Buffer," this is a data safety mechanism. Before writing data pages to their actual disk locations, InnoDB first writes them to the Doublewrite Buffer. If a power failure or crash causes partial page writes, InnoDB can use the copy in the Doublewrite Buffer to repair corrupted pages during recovery. This ensures data integrity and consistency.
4. ​Undo Logs​
   Referred to as "Undo Logs," these store information needed for transaction rollback and multi-version concurrency control (MVCC). When you execute a ROLLBACK, Undo Logs are used to reverse changes. For other transactions requiring older versions of data (to support isolation levels like Repeatable Read or Read Committed), the logs provide access.
5. ​Table Data (under specific configurations)​​
   This depends on your MySQL setup. In the default configuration (innodb_file_per_table=OFF), all InnoDB table data and indexes are stored in the shared ibdata file. However, in modern MySQL versions (5.6 and later), the default is innodb_file_per_table=ON. With this setting, each InnoDB table has its own .ibd file for data and indexes. In this case, the ibdata file mainly stores items 1–4 above and no longer holds user table data.

​Common Issues: Large ibdata1 File​
This often occurs in older systems still using shared tablespaces (innodb_file_per_table=OFF), such as those running MySQL 5.6.

• ​Cause: All table and index data are packed into ibdata1, causing the file to grow continuously. Even if you delete large amounts of data or tables, the disk space occupied by ibdata1 isn't automatically released. InnoDB marks the space as available for future use but doesn't return it to the operating system.
• ​How to Free Space: Typically, follow these steps:
  1. Back up all databases completely using mysqldump.
  2. Stop the MySQL service.
  3. Delete all ibdata and ib_logfile files.
  4. Modify the configuration file to ensure innodb_file_per_table=ON.
  5. Start the MySQL service, generating a new, clean ibdata1 file.
  6. Restore data from the backup.
     For this reason, it's recommended to always set innodb_file_per_table=ON. This way, each table has its own file, and when you drop a table (DROP TABLE), MySQL can delete the corresponding .ibd file, immediately freeing space for the OS.

​Article Summary