How Can I Clean Up and Shrink My MySQL InnoDB Storage Engine?

MySQL InnoDB Storage Engine Cleanup

In order to cleanse a MySQL InnoDB storage engine and remove data from deleted tables, follow these steps:

Understanding InnoDB Architecture

InnoDB stores six types of information in the ibdata1 file:

• Table Data
• Table Indexes
• MVCC (Multiversioning Concurrency Control) Data
• Table Metadata (Data Dictionary)
• Double Write Buffer
• Insert Buffer

Can You Run OPTIMIZE TABLE?

Using OPTIMIZE TABLE on an InnoDB table stored in ibdata1 has two effects:

• It makes the table's data and indexes contiguous within the file.
• It makes ibdata1 grow by appending the modified data and indexes.

Can You Run OPTIMIZE TABLE with innodb_file_per_table?

With this option enabled, OPTIMIZE TABLE creates separate .ibd files for each InnoDB table. While this segregation separates data and indexes from ibdata1, it does not remove data dictionary entries from ibdata1.

InnoDB Infrastructure Cleanup

To shrink ibdata1 and remove unnecessary data:

1. Dump all databases into an SQL text file (SQLData.sql).
2. Drop all databases except mysql and information_schema. Preserve user grants.
3. Set innodb_fast_shutdown to 0 to flush transactional changes.
4. Shutdown MySQL.

5. Modify my.cnf (or my.ini) with these settings:

   • innodb_file_per_table
   • innodb_flush_method=O_DIRECT
   • innodb_log_file_size=1G (ensure it's 25% of innodb_buffer_pool_size)
6. Delete ibdata and ib_logfile files.
7. Start MySQL.
8. Import SQLData.sql.

After this process, ibdata1 will contain only table metadata, and individual InnoDB tables will be stored in separate files with .frm and .ibd extensions. OPTIMIZE TABLE can then be used to shrink the table's .ibd file.

The above is the detailed content of How Can I Clean Up and Shrink My MySQL InnoDB Storage Engine?. For more information, please follow other related articles on the PHP Chinese website!