How to add column to big table in MySQL

Use MySQL Workbench. You can right-click a table and select "Send to SQL Editor" --> "Create Statement". This way no table "properties" will be forgotten to add (including CHARSET or COLLATE).
With this huge amount of data I'd recommend cleaning up either the table or the data structure you use (a good DBA comes handy). If not possible:

  • rename the table (ALTER) and create a new one with the CREATE script you get from Workbench. You can also extend that query with the new field you need
  • BULK LOAD the data from the old table to the new one:
    SET FOREIGN_KEY_CHECKS = 0;
    SET UNIQUE_CHECKS = 0;
    SET AUTOCOMMIT = 0;
    INSERT INTO new_table (fieldA, fieldB, fieldC, ..., fieldN)
       SELECT fieldA, fieldB, fieldC, ..., fieldN
       FROM old_table
    SET UNIQUE_CHECKS = 1;
    SET FOREIGN_KEY_CHECKS = 1;
    COMMIT;
    

    This way you avoid indexing/etc to run record by record. The "update" to the table still will be slow (as the amount of data is huge) but this is the fastest way I can think of.

    EDIT: Read this article to get details about the commands used in the above sample query ;)

alter table add column, algorithm=inplace, lock=none will alter a MySQL 5.6 table without copying the table and without locking impact.

Just tested this yesterday, mass inserted 70K rows into a 280K row 7 partition table, 10K rows into each partition, with 5 seconds sleep in between to allow other throughput.

Started the mass inserts, then in separate session started the online alter statement above in MySQL Workbench, the alter finished before the inserts, two new columns were added, and no rows resulted from the alter meaning MySQL did not copy any rows.


Your sed idea is a decent method, but without the errors or the command you ran, we can't help you.

However, a well known method for making online changes to large tables is pt-online-schema-change. The simplistic overlook of what this tool does is copied from the documentation:

pt-online-schema-change works by creating an empty copy of the table to alter, modifying it as desired, and then copying rows from the original table into the new table. When the copy is complete, it moves away the original table and replaces it with the new one. By default, it also drops the original table.

This method might also take a while to complete, but during the process the original table will be completely useable.

Tags:

Mysql

Innodb