So I was thinking about maybe doing MySQL fault tolerance and load balancing through hardware load balancers by setting up a virtual cluster for database reads and another for database writes. We could setup 2 master servers in a circular replication, making sure you only actually write to one at a time (define one as a hot spare in the load balancer’s “DB write cluster”, then the backup master takes over writes only if the primary master is down). That part is no problem… I don’ t have any questions there. 🙂
Now, let’s say we have three MySQL slave DB servers that read from the master through the load balancer’s “DB write” connection (again, they would only be reading from one at a time). But what I want to know is what would happen to the slave servers if you fail over the master -> slave connection to a different physical master server. Is this going to cause problems with the MASTER_LOG_POS position on the slaves if they fail over to a different master server?
Does anyone know much about the inner workings of MySQL’s master/slave setup and what happens in the event of a MASTER_LOG_POS conflict? I’m just thinking it would be nice to have a truly redundant database setup that was handled 100% through hardware (load balancers).
If no one knows, I guess that will be something I’ll be testing later this week. 🙂