When attempting to move non-system table data from the system tablespace (datafile: ./ibdata1) to its own tablespace (datafile: ./dbname/tablename.ibd), RDS for MySQL [8.0.35] is not moving the table data as expected after issuing ALTER TABLE <tablename> ENGINE=InnoDB; with innodb_file_per_table set to 1.
Is this due to a limitation of the RDS-specific implementation of MySQL or some other reason?
This has become necessary as the RDS instance was inadvertently created with innodb_file_per_table set to 0 and used storage is not reducing after truncating partitions. The above is an attempt to avoid having to do a mysqldump and restore (not being able to reclaim free space in ibdata1 is Ok).
Some posts suggesting that the above should work in a native / non-managed MySQL installation:
- How can I change the "innodb_file_per_table" parameter from "OFF" to "1" for an existing database?
- MySql - changing innodb_file_per_table for a live db
- How do I shrink the innodb file ibdata1 without dumping all databases?
If ALTER TABLE or other similar methods are not an option with RDS for MySQL, the known alternatives are:
- Dump and restore to a new instance potentially using this method to reduce downtime
- Use AWS DMS
Please suggest other, possibly less onerous methods to address this issue. Blue-Green deployment and restoring from snapshots are not suitable for this purpose.
Thanks!