How Can I Reclaim Wasted InnoDB Storage Space in MySQL?

Reclaiming InnoDB Storage Space

MySQL InnoDB engine manages data in a file called ibdata1, where it stores table data, indexes, metadata, and other infrastructure components. Over time, deleted tables' data can accumulate in ibdata1, causing storage space bloat.

Method for Cleaning InnoDB Storage Engine

To shrink ibdata1 and free up space, follow these steps:

1. Dump and Drop Databases: Export all databases to a SQL file (SQLData.sql) and drop all databases except MySQL and information_schema.
2. Flush Transactional Data: Execute SET GLOBAL innodb_fast_shutdown = 0; to clear all transactional changes from log files.
3. Configure MySQL: Add appropriate settings to my.cnf file, including innodb_file_per_table, innodb_flush_method=O_DIRECT, innodb_log_file_size=1G, and innodb_buffer_pool_size=4G.
4. Delete Ibdata and Log Files: Remove ibdata* and ib_logfile* files from the data directory.
5. Restart MySQL: Start MySQL, which creates a new ibdata1 with a smaller size.
6. Import SQL Data: Import SQLData.sql to restore the database.

Benefits of This Method

• Frees up storage space previously occupied by deleted tables.
• Improves performance by reducing the size of ibdata1, enhancing I/O performance.
• Provides better control over data storage by segregating table data and indexes into separate files.

Note:

Ensure that user grants are preserved by copying the mysql schema directory and restoring it after the cleanup process. Additionally, optimize log file size based on server requirements and workload characteristics.

The above is the detailed content of How Can I Reclaim Wasted InnoDB Storage Space in MySQL?. For more information, please follow other related articles on the PHP Chinese website!