How much disk space does innodb need to ALTER TABLE

I have a large InnoDB table that I want to alter by adding a small column to. I'm prepared to wait a while, but I'm wondering how much disk space will the ALTER TABLE command consume while it's running? Will I need enough room on my disk for two full copies of the table?

Solution 1:

Yes. You will need enough space for two copies of the table. MySQL performs alterations of tables by creating a new table, copying data from the old, and then deleting the old table.

Solution 2:

I know it's old, but I believe the accepted answer is incomplete.

MySQL will create a temporary copy of table in a lot of ALTER TABLE operations. But it will create that copy in a temporary folder:

Space for temporary sort files

Online DDL operations that rebuild the table write temporary sort files to the MySQL temporary directory ($TMPDIR on Unix, %TEMP% on Windows, or the directory specified by --tmpdir) during index creation. Temporary sort files are not created in the directory that contains the original table. Each temporary sort file is large enough to hold one column of data, and each sort file is removed when its data is merged into the final table or index. Operations involving temporary sort files may require temporary space equal to the amount of data in the table plus indexes. An error is reported if online DDL operation uses all of the available disk space on the file system where the data directory resides.

If the MySQL temporary directory is not large enough to hold the sort files, set tmpdir to a different directory. Alternatively, define a separate temporary directory for online DDL operations using innodb_tmpdir. This option was introduced to help avoid temporary directory overflows that could occur as a result of large temporary sort files.

Quote taken from the MySQL 8.0 Reference Manual, but I believe this behavior is quite ancient and will apply to all relevant MySQL versions to date (ATM this is 5.6, 5.7 and 8.0)