Tricks on MySQL Alter Table Online

 

 To add a column to a very large MyISAM table, here are the steps that is proofed to work fine:

  1. Create a new table of the same structure, including the new column, without keys
  2. Dump the data in the old table into a csv file:
    % mysqldump -u user -p -t -T /tmp --fields-enclosed-by=\" --fields-terminated-by=, database table
    (You may not need the option --fields-enclosed-by)
  3.  If you need to add data to the csv file, use a script to do so
  4. Load the csv file into the new table:
    mysql> LOAD DATA LOCAL INFILE '/tmp/table.txt' INTO TABLE newtable (col1,col2,..);
  5. Create another table with all keys defined
  6. Copy over .frm and .MYI files:
    # cp newtable2.frm newtable.frm
    # cp newtable2.MYI newtable.MYI
  7. Flush the table:
    mysql> FLUSH TABLE newtable;
    (Login as root or as a use that has the "reload" grant)
  8. Repair the table:
    mysql> REPAIR TABLE newtable QUICK;
  9. Drop the old table:
    mysql> DROP TABLE oldtable;
  10. Rename the new table back to the old table:
    mysql> RENAME TABLE newtable TO oldtable;

That's it.