(Did I send this off to the list already? It doesn't seem to be updated! )
I've been thinking recently about supporting transparent schema update of production systems in order to deliver zero downtime.
What I'm thinking of is a scenario where you use a load balancer to take one of
the slaves, alter its schema, and then promote it to master.
It should be possible to perform runtime updates of schema with the following
steps:
* Starting with a cluster of MySQL boxes replicated to a master as slaves.
* Take one of the MySQL boxes which is curently setup to replicate from the master and run all ALTER TABLE commands on it.
* Then let it catch up to the master so that its only a few seconds behind.
* Then run the ALTER TABLE commands on the other MySQL slaves one at a time (or
concurrently) (each after they've been taken out of production).
* Promote the current slave to the current master by doing all the normal config of a master and then disabling the slave setup.
* Change the load balancer to have the NEW master be the ALTER'd slave
From: masterdb.server.com -> 10.0.0.2
To: masterdb.server.com -> 10.0.0.3* This will allow new MySQL clients to start using the new master (which is
now ready but has locks blocked).
* Then lock the tables on the current master.
* Kill ALL connections of the current master so that the slaves and clients reconnect.
There are a few problems with this approach (but it shows some serious potential)
* What if your clients are in the middle of a transaction? They'll have to failover correctly to the new master.
* This really isn't LIVE alter as it would take a few seconds for everything to
switch over.
* The slaves will need their binary log settings fixed (or can I just run RESET MASTER) to correct these?
* What other steps am I missing?
--
Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat.
Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod!
Kevin A. Burton, Location - San Francisco, CA
AIM/YIM - sfburtonator, Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
