I have an HAProxy server load balancing two mysql servers that are in master-master / active-passive mode. I can see that I've successfully scaled all READS to my two database nodes, but how would I easily switch masters for write operations if the current write master goes down?
Right now, I have a config file on each App server for a DB_HOST_W for writes and DB_HOST_R for reads. DB_HOST_R points to the HAProxy server. DB_HOST_W points to one of the master nodes.
HAProxy takes care of the failover automatically for READ operations, but it would be extremely time consuming to have to update the config file and change the DB_HOST_W value for 4+ App servers in case of failure.
Is there a better way? What am I missing here?
I want to point out that I have the following configuration:
server primary 10.152.142.184:3306 check server secondary 10.152.142.185:3306 check backup
But I don't like it because although it sends all WRITE operations to the primary, it also sends ALL READ operations to the primary as well, and removes scalability.