How Can I Shrink My MySQL's ibdata1 File and Improve Performance?

Shrinking MySQL's ibdata1 File: A Detailed Guide

MySQL's ibdata1 file can become extremely large over time, especially if users frequently create and drop databases and tables. While deleting the file and reloading MySQL can shrink it, a more practical approach is to enable separate storage for each table.

Enabling Separate File Storage

To configure MySQL to use separate files for tables, add the following line to your my.cnf file:

[mysqld]
innodb_file_per_table=1

This setting will ensure that each table and its indexes are stored in their own individual files, rather than in ibdata1.

Reclaiming Space from ibdata1

If you wish to reclaim space from the existing ibdata1 file, follow these steps:

1. Perform a mysqldump 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 from the dump.

Advantages of Separate File Storage

When using separate file storage, newly created tables will reside in their own ibd files. As these tables are typically dropped soon after creation, the ibd files are also deleted, preventing ibdata1 from expanding rapidly.

Additional Considerations

Note that dropping the information_schema database is neither necessary nor possible. It consists of read-only views and does not have associated files or a database directory.

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