I have four machines A,B,C,D.. A and B are "dual masters" while C and D are read only slaves
A <---> B | | C D I have a load balancer that connects all clients to A, and when it fails, connects them to be. In practice it is a little more complicated, because if a server becomes backlogged, it recuses itself from rotation (but there is logic to prevent a cascade where all servers recuse themselves). The problem is, that once a failover has occured, from A -> B, then B has to pretty much stay the master forever until it fails, and A takes over. My load balancer doesn't support this kind of logic and manual intervention would be required to implement it. I have thought about making the top few bits of the auto increment key reflect the server id (thanks for the idea Jeremy), but I don't really want partitioned auto-increment values. What I am thinking about doing, is modifying the autoincrement values so that server A always produces an EVEN value, while server B always produces an ODD value. That seems like it would nearly eliminate the possiblity of conflicts, and my writes could be load balanced without any problems. It won't fix problems with conflicts of other unique columns, but that seems like a good thing for the most part at least for my applications. My healthcheck logic will make sure that server A doesn't appear to be back up to the load balancer until it has caught up with server B, or vice versa. Does this sound like a good idea, or should I be thinking about some other way to do this? Justin Swanhart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
