You could probably use the online schema change feature of ALTER TABLE in MySQL 5.6
Please note the MySQL 5.6 Documentation for
Specifying ALGORITHM=INPLACE makes the operation use the in-place technique for clauses and storage engines that support it, and fail with an error otherwise, thus avoiding a lengthy table copy if you try altering a table that uses a different storage engine than you expect. See Section 14.11, “InnoDB and Online DDL” for information about online DDL for InnoDB tables.
You would run the
ALTER TABLE like this:
ALTER TABLE mytable ALGORITHM-INPLACE ... ;
What this will do is create a shadow copy of the table being changed. All reads and writes can go on with the original table. Additional changes made to the table is placed in the shadow copy. Everything is merged and presented at the end.
The online schema change feature uses the folder mapped in tmpdir. Make sure the copy of the table can fit entirely in tmpdir. For example, if tmpdir is mapped to
/tmp and the table you are changing is 7GB, there has to be more than 7GB free in
/tmp is not large enough, please consider mounting
/tmp on a large volume.
Again, from the MySQL 5.6 Documentation for
As of MySQL 5.6.16, ALTER TABLE upgrades old temporal columns to 5.6 format for ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, and FORCE operations. This conversion cannot be done using the INPLACE algorithm because the table must be rebuilt, so specifying ALGORITHM=INPLACE in these cases results in an error. Specify ALGORITHM=COPY if necessary.
Changing Columns with older the DATETIME format will not work. You would have to use other means to make the new table.