Altering a huge table in MySQL with no downtime

This is a pain right, the alter command takes ages to complete. Lucky for us, there is great tool from Percona that takes care of this (in most cases).

http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html

This tool creates a new table and then adds triggers to the old table to update the new table. Then once the data copy is complete, it will swap the tables. There are plenty of options for you to modify the behaviour. Here is a sample:

./pt-online-schema-change --alter "ADD INDEX ( project_id ,  user_id ), DROP INDEX project_name"  D=MyGreatDB,t=project --user my_user --ask-pass --no-drop-old-table --no-check-replication-filters --max-lag 10 --dry-run

Change --dry-run to --execute when you want to actually start the conversion