Optimizing MySQL for ALTER TABLE of InnoDB

You might want to look at pt-online-schema-change from Percona toolkit. Essentially what it does is:

  • Copies original table structure, runs ALTER.
  • Copies rows from old table to newly created one.
  • Uses triggers to track and sync changes while copying.
  • When everything is finished it swaps tables by renaming both.

Works very well for single instance databases, but might be quite tricky if you use replication and you can’t afford stopping slaves and rebuilding them later.

There’s also a nice webinar about this here.

PS: I know it’s an old question, just answering in case someone hits this via search engine.

Leave a Comment