Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to add a last_modified column to a MyISAM table tbl_items. The crux of this problem is that tbl_items houses several gigabytes of data. Also of note, I am using a master-slave deployment with one slave.

Obviously, running the necessary ALTER TABLE command on the master is unacceptable since it locks the table for well over a half-hour. I cannot afford this kind of downtime at ANY time of day or night, most unfortunately.

A few sources have suggested some form of the following:

  1. Disable replication to the slave
  2. Run the ALTER TABLE command on the slave
  3. Switch replication back on and wait for slave to catch up
  4. Promote slave to master

Is this a viable solution? It would seem to me that all new tbl_items inserts would be lost between the beginning of step 2 and step 4; that is, is it possible for tbl_items data to replicate throughout the interval that it's missing the new column?

If that's the case, is there an alternate solution that eliminates the possibility of both downtime and data-loss?

share|improve this question

2 Answers 2

Try these possible recommendation and solution:

  • For MyISAM tables, you can speed up index re-creation (the slowest part of the alteration process) by setting the myisam_sort_buffer_size system variable to a high value.

  • If you use ALTER TABLE on a MyISAM table, all nonunique indexes are created in a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes. This feature can be activated explicitly for a MyISAM table. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating nonunique indexes. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.

While the nonunique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.

  • If you are just changing a column name on a MyISAM table and want to avoid duplicating the entire table, try the following (no warranty provided but worked for me):

For peace-of-mind -- try this with some dummy data first!

  1. Backup the .frm file from your master table (and the data if you can, but you're probably reading this because you can't).

  2. create table with the identical schema to the one you want to alter (type "show create table and just change the name to something). Lets say you called the table "rename_temp1"

  3. execute the "alter table change char(128) not null" [substituting your the old definition -- ensuring you keep column type the same]

  4. Ensuring you a have made a copy of your original .frm file -- copy the .frm file to .frm.

  5. voila -- all going well your column should be renamed without a full copy in/out (very useful for 140G tables...)

  6. probably best to run a myisamchck on the table before making live again

share|improve this answer
    
Thanks for the answer! I will upvote this once I gain the required rep level. I will be experimenting with myisam_sort_buffer_size. –  joadha Apr 13 '12 at 14:01

There is no fast way to ADD a column (until 5.6, rumor has it).

One way that might work is as follows. It requires that you already have "dual-master, single-writer" set up.

  1. Take the backup master offline
  2. ALTER there
  3. Bring it back online -- but watch out! Anything that touches that table may cause replication to hang because the schema is different. In particular, you probably have to make the new column NULLable so that INSERTs won't fail. An INSERT without a list of column names will fail.
  4. Fail over.

Another possibility... Facebook has a posting on how to do an online ALTER. It does have drawbacks... * FOREIGN KEYs may be a problem * It requires a TRIGGER; so you must not already have one. * There is a brief outage when it makes the final switchover. This is probably brief enough to be acceptable.

Percona has a similar animal. Start with theirs.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.