How Can I Control the Size of My MySQL ibdata1 File?

How to Manage Ibdata1 File Size in MySQL

MySQL's ibdata1 file stores essential data, but can grow excessively under certain usage patterns. This can become problematic, especially when using MySQL for specific purposes like query tool applications.

Causes of Ibdata1 File Growth

When using MySQL as a query tool, creating and dropping databases and tables frequently can lead to rapid ibdata1 file growth. This is because MySQL typically stores data and indexes of tables within the ibdata1 file by default.

Options for Managing Ibdata1 File Size

• Enable Separate Table Files:

As of MySQL version 5.6.6, MySQL supports the use of separate files for each table. Enabling this feature prevents the ibdata1 file from growing excessively. To do this, add the following line to your my.cnf file:

innodb_file_per_table=1

• Purge Ibdata1 File:

To reclaim space used by ibdata1, follow these steps:

1. Dump all databases and relevant server objects, excluding MySQL and performance_schema.
2. Drop all databases except MySQL and performance_schema.
3. Stop MySQL.
4. Delete ibdata1 and ib_log files.
5. Start MySQL.
6. Restore data from the dump.

After restarting MySQL, ibdata1 and ib_log files will be recreated.

• Extract Data and Index Pages:

Using the ALTER TABLE or OPTIMIZE TABLE commands, you can extract data and index pages from ibdata1 to separate files. However, ibdata1 will not shrink unless you perform a full purge as described above.

Note on Information_Schema

Information_schema cannot be dropped and does not require its own file storage, as it utilizes the memory db-engine and is created/destroyed upon MySQL start/stop.

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