Mysql – How to append a new datafile in an existing InnoDB tablespace


I want to add a new data file in an existing InnoDB tablespace so that entry of data is smooth in case it hits its 4GB limit.

Best Answer

Plan A: Reformat the disk to be NTFS. Caution: This destroys all data, so you would need to do a major backup and restore.

Plan B: Turn off innodb_file_per_table, go into my.cnf and configure the main tablespace in a way I have not seen used in more than a decade -- namely something like ibdata1:3G,ibdata2:3G,ibdata3:3G,ibdata4:3G,ibdata5:3G. That way, you are pre-specifying the partitions and InnoDB (unless it has forgotten how) will be able to handle tables of arbitrary size (> 4GB) if they are in that tablespace. Finally, ALTER TABLE foo ENGINE=InnoDB to move the table into that tablespace.

Yuck. Turn back the clock because of Windows.