How Can I Purge and Shrink the ibdata1 File in MySQL?

Purge and Shrink Ibdata1 File in MySQL

Despite deleting databases in MySQL, the ibdata1 file can accumulate in size, causing performance issues. To address this, MySQL allows you to configure individual tables and indexes to use separate files instead of storing them in ibdata1.

Configuring Individual Table Files

Add the following setting to your my.cnf file:

[mysqld]
innodb_file_per_table=1

Restart MySQL for the changes to take effect. This will ensure that new tables are created with their own ibd* files.

Reclaiming Space from Ibdata1

To reclaim space from ibdata1, follow these steps:

1. Create a dump of 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 database from the dump.

Upon restart, MySQL will create a new ibdata1 and ib_log files. When you create new databases for analysis, they will utilize individual ibd* files rather than ibdata1.

Optimizing Table Size

Using the ALTER TABLE ENGINE=innodb or OPTIMIZE TABLE commands can move data and index pages from ibdata1 to separate files. However, ibdata1 will not shrink without performing the steps mentioned above.

Additional Notes

• The information_schema database cannot be dropped as it contains read-only views that are regenerated upon MySQL restart.
• While MySQL Enterprise Edition provides additional features for shrinking ibdata1, they are not available in the Community Edition.

The above is the detailed content of How Can I Purge and Shrink the ibdata1 File in MySQL?. For more information, please follow other related articles on the PHP Chinese website!