Zero downtime (or near zero) db schema changes

Question

I was wondering how/if people have worked around db schema changes that would otherwise cause a production system to be down. It seems with additive changes that are constrained in some way (e.g. unique constraint) would be difficult to do b/c the app and the db must change at the same time otherwise errors will occur either in the data or in the application.

I have thought about maybe switching to a slave db (using mysql replication) and running the schema changes on the master but then you would need to somehow capture the update queries applied to the slave that where not applied to the master and you would run the risk of not having a backup server.

What techniques have people used to work around these problems?

Thanks, manish

Answer 1

I'd say you're close on the idea; effectively, I'd have a master and slave, with the master being live, and the slave having the changes replicated to it; pause the replication on the slave, and then perform the schema changes on the slave, and once the schema changes are done, unpause the replication; once that whole process is complete, pause the master for a very short period of time to assure that the replicated changes are flushed on the slave, and then switch the master and slave. That should do what you need.

Note that this only works if the changes you're making to the schema aren't touched by the pending replication commands; this generally is best done at low-traffic times to assure that the collision is unlikely. Note that because this makes no changes to the master until the slave has completely updated the schema and and replicating changes, it's very safe on the master.