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:
- Ensure you’ve got a timestamp column on the database or that it is generating replication logs
- Create a copy (B) of the table (A)
- add the new columns to B (this will still block with myisam)
- disable transactions
- rename the original table (A) as something else (backup)
- rename the new table (B) with the name of the original table (A)
- replay the transactions from the start of the operation from the replication log or from the backup table
- enable transactions.