To add a column to a very large MyISAM table, here are the steps that is proofed to work fine:
- Create a new table of the same structure, including the new column, without keys
- 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)
- If you need to add data to the csv file, use a script to do so
- Load the csv file into the new table:
mysql> LOAD DATA LOCAL INFILE '/tmp/table.txt' INTO TABLE newtable (col1,col2,..);
- Create another table with all keys defined
- Copy over .frm and .MYI files:
# cp newtable2.frm newtable.frm
# cp newtable2.MYI newtable.MYI
- Flush the table:
mysql> FLUSH TABLE newtable;
(Login as root or as a use that has the "reload" grant)
- Repair the table:
mysql> REPAIR TABLE newtable QUICK;
- Drop the old table:
mysql> DROP TABLE oldtable;
- Rename the new table back to the old table:
mysql> RENAME TABLE newtable TO oldtable;
That's it.
Recent comments
2 weeks 3 days ago
27 weeks 4 days ago
28 weeks 3 days ago
39 weeks 3 days ago
40 weeks 5 days ago
49 weeks 3 days ago
49 weeks 5 days ago
50 weeks 4 days ago
51 weeks 3 days ago
51 weeks 4 days ago