Thursday, October 4. 2007
Multi-Master MySQL Replication
MySQL replication is pretty good. It’s easy to set up and fairly robust. There’s two things keeping it from being really awesome – you have a master and a slave. That means applications need to know which server to write to (the master) and if the master fails there is no automatic fail over to the slave for writes. Usually that means some manual work. Sure, you could get into clustering but what about a multi-master setup instead?
Ah, the problem there is how do you handle auto increments which can and will collide. There’s a trick to it described by an advanced replication article on onlamp.com: use auto_increment_increment and auto_increment_offset to force non-conflicting values on each server. Add a little circular replication, front end the server pool with your dns/load balancing trick of choice and profit!
Very interesting article, but it seems to be useful only if you are using auto_increment values for keys. If you are using anything else, some sort of hashes for example, this will not help. Plus I am not sure how scalable this workaround is. 
MySQL replication still sux hard without real multi-master option.
Hmm, not sure about hashes but it did seem you could add a bunch of servers in this scheme.
There’s also clustering but I haven’t dug into that too much:
http://mysql.org/doc/refman/5.1/en/mysql-cluster-overview.html
At OSCON I saw a few third party add ons to do what seemed like a true multi-master mysql setup. The demos were impressive but who knows how they do in practice.
I must admit I didn’t read the whole article so I may be wrong, but I believe it is possible to have 10 servers at the most with this scheme. Which may or may not be enough depending on your needs, but it is a big limitation for some larger growth.
But I didn’t tell this doesn’t look interesting.
Oops, I meant "I didn’t say this doesn’t look interesting." Sorry for the typo.
I think you could have as many as you’d like by just scaling the offset and increment accordingly.
By default, you’d have an auto increment like so:
1,2,3,4,5
With two:
Server A: 1,3,5,7
Server B: 2,4,6,8
With 20:
Server A: 21,41,61
Server B: 22,42,62
Server C: 23,43,63
etc…
Flickr Stream
Search This Blog
Planet Sysadmin Feed
Ubuntu Geek: ExpoSong 0.7 released!
mypapit gnu/linux blog: How to use GNU Privacy Guard (GPG) – Encrypt, Decrypt, Sign and Verifying identities
Sam Ruby: MPL Alpha 2
Simplehelp: How to save documents created in Pages as Microsoft Word (.doc) files
Disclaimer
"This blog is my own and does not represent the positions, strategies or opinions of my employer or anyone else." --Joe
This work is licensed under a Creative Commons Attribution Noncommercial Share Alike 3.0 License.
