Salta al contenuto

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!

posta 2 anni fa312 visualizzazioni

1 Risposta
0
Risposta accettata

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;

con risposta 2 anni fa

Accesso non effettuato. Accedi per postare una risposta.

Una buona risposta soddisfa chiaramente la domanda, fornisce un feedback costruttivo e incoraggia la crescita professionale del richiedente.