Skip to content

RDS for MySQL: Moving table data from system tablespace to its own tablespace

0

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:

  1. How can I change the "innodb_file_per_table" parameter from "OFF" to "1" for an existing database?
  2. MySql - changing innodb_file_per_table for a live db
  3. 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:

  1. Dump and restore to a new instance potentially using this method to reduce downtime
  2. 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!

asked 2 years ago264 views

1 Answer
0
Accepted Answer

Relevant excerpt from MySQL file size limits in Amazon RDS:

When you have enabled or disabled InnoDB file-per-table tablespaces, you can issue an ALTER TABLE command to move a table from the global tablespace to its own tablespace, or from its own tablespace to the global tablespace as shown in the following example:

ALTER TABLE table_name TABLESPACE=innodb_file_per_table;

answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.