MySQL ALTER TABLE on tables too large to duplicate

Question

When adding a column to a MyISAM table, MySQL will Create>Copy>Drop>Rename. I have a table which is sufficiently large that it cannot be duplicated in the server's remaining free space. Attempting to add a column then results in the disk filling. Time is not an issue here, neither is table availability, just disk space.

Short of copying the data to another server, truncating, altering, and copying back, is there a way to add a column to a MyISAM table without it creating a temporary table and duplicating all the data?

Answer 1

You could create a new, empty table, manually move the data over in chunks (INSERT into new, DELETE from old a certain number of rows), drop the old table and rename the new table. Essentially what MySQL does, but moving the data over instead of copying it, which should allow you to use less space.

Answer 2

In general I try to keep enough free space to be able to rebuild my largest table. This allows me to run OPTIMIZE TABLE or ALTER TABLE when necessary.

Do you have another disk mounted on your server?

When space is too tight to rebuild a given table, my preferred workaround is to temporarily move some other table(s) to a separate disk volume. I do this by stopping MySQL, moving the relevant data files (MYD and MYI, or ibd), and then creating a symlink at the original location that points at the new location, and starting MySQL. Once the table rebuild is done I reverse that process to move the other table(s) back to their original location.