we want to set up a complex mysql replication infrastructure. The idea is to have 3 clients, which handle requests from users and are configured as slaves in a master-slave replication in mysql. There are also two servers, which are configured as master-master replication. Now we want to use these master-master replication as a single master for the 3 slaves via load balancer (or proxy). Does anyone set up a similar configuration already? Is it possible, that the two master servers have a different binlog and could this break the replication? System is Debian Lenny with MySQL 5.1.48
I am not aware of a solution to do what you accomplish, as MySQL does not natively support slave servers replicating from multiple masters.
Circular replication is fragile and generally not recommended.
If your main master failed, which is acting master for all the slaves, you could potentially re-point them to the secondary master. This typically involves digging through the binlogs, which can be quite tedious and is easy to make a mistake. You could use Maatkit's mk-slave-move utility to make this a bit easier.
You could potentially run multiple instances of MySQL on each slave and then have heartbeat or failover logic, which would be able to switch in case of the primary master failing. This would have to have substantial logic to keep from being fragile.
You could run dual master, have a slave off of each master and then load balance both slaves. Have availability verification in your load balancing to remove the slave in case of a single master failure, which would probably be better than having multiple slaves on each server. This would not scale well.
Allegedly this set of scripts helps with this type of configuration but I have no experience with them.
If you want high availability for readonly queries, I would recommend having those more important queries run against the dual master highly available servers. For queries that do not need to be realtime, it makes sense to have them run against the multiple load balanced slaves, which could potentially not have current data in case of the master failing.
After you have the 2-server master-master setup, the hard part is done. Regardless of which of the two machines the writes are being sent to, if set up correctly, the the binary logs on either of the servers will be sufficient/complete. After that, just set up the slaves off of either one of of the two masters.
Aside, you can always set up the old master slave to the dual-masters, then the slaves will be 2-tiers down the replication hierarchy. It's suitability is dependent on tolerable replication lag to the end-application, but I'm a fan of two-tiered replication (to make maintenance easier).