Purging and Shrinking ibdata1 File in MySQL

While leveraging MySQL for data analysis in R, users may encounter an issue where the ibdata1 file size grows excessively despite having no data stored. This article addresses this problem and provides a comprehensive solution.

Cause of ibdata1 File Growth

The ibdata1 file contains data and indexes of tables stored in the shared tablespace. By default, MySQL stores all tables in this single file, causing it to expand continuously. Deleting databases and tables only removes their metadata from the server, but the file itself remains unchanged.

Solution: Enable Separate File-per-Table

To avoid the ibdata1 file from growing excessively, configure MySQL to store each table and its indexes as separate files. This is now enabled by default in MySQL 5.6.6 and later versions. If using an earlier version, add the following line to the my.cnf file:

[mysqld]
innodb_file_per_table=1

This will ensure that newly created databases and tables use separate ibd* files instead of ibdata1.

Reclaiming Space from ibdata1

To release the space occupied by ibdata1, follow these steps:

1. Dump all databases except mysql and performance_schema.
2. Drop all databases except mysql and performance_schema.
3. Stop MySQL.
4. Delete the ibdata1 and ib_log files.
5. Start MySQL.
6. Restore the dumped databases.

This process will delete all tables and data, so ensure you have backed up the necessary information before proceeding.

Note on Information Schema

The information_schema is a collection of read-only views, not actual tables. It does not occupy any files on the disk and is regenerated upon restarting MySQL. Therefore, dropping it has no effect on ibdata1 file size.