Mysql – With MySQL, how long does an “ALTER TABLE … DISABLE KEYS;” statement last

Your best option is to setup a slave database using mysql replication. Assuming your schema change is additive, then you can issue the update on that replica, which will be blocked for the duration of the update. Once that is done, and check your slave has caught up with all the updates then declare a small downtime to promote your slave database to the master.

You can find instructions on setting up mysql replication here.

I periodically need to make changes to tables in mysql 5.1, mostly adding columns.

Don't. No really. Just don't. It should be a very rare occasion when this is ever necessary.

Assuming your data really is normalized to start with, the right way to solve the problem is to add a new table with a 1:1 relationship to the base table (non-obligatory on the new table).

Having to add columns regularly is usually an indicator of a database which is not normalized - if your schema is not normalized then that's the problem you need to fix.

Finally, if your schema really, really is normalized and you really, really must keep adding columns then:

1. Ensure you've got a timestamp column on the database or that it is generating replication logs
2. Create a copy (B) of the table (A)
3. add the new columns to B (this will still block with myisam)
4. disable transactions
5. rename the original table (A) as something else (backup)
6. rename the new table (B) with the name of the original table (A)
7. replay the transactions from the start of the operation from the replication log or from the backup table
8. enable transactions.