This is one of the most controversial topics I have ever dealt with over the years as a MySQL DBA and in the DBA StackExchange.
To put it mildly, there is simply no other way to shrink ibdata1. With innodb_file_per_table disabled, every time you run
OPTIMIZE TABLE on an InnoDB table, ibdata1 grows rapidly. Data that are dropped using
DROP TABLE and
DROP DATABASE cannot be rolled back because they are DDL, not DML. I believe Oracle and MSSQL can rollback DDL. MySQL cannot do that.
There are several classes of information that reside in ibdata1
- Table Data
- Table Indexes
- Table MetaData
- MVCC Control Data
- Double Write Buffer (Background write to prevent reliance on OS caching)
- Insert Buffer (Managing changes to non-unique secondary indexes)
innodb_file_per_table=1 will allow you to create new tables with table data and table indexes being created outside ibdata1. You could extract any tables still inside ibdata1 using
ALTER TABLE ... ENGINE=InnoDB; or
OPTIMIZE TABLE but that will leave that big gaping unused space in ibdata1.
Notwithstanding, you must clean up the InnoDB infrastructure. I already wrote StackExchange posts on how and why to do this:
You only have to dump the data, reload one more time and never revisit this issue again. Running
OPTIMIZE TABLE afterwards will indeed shrink the
.ibd tablespace file for any InnoDB table.