2

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.

1

Without speaking to the advisability of your solution the way to accomplish your goal is to define two frontends listening on two different ports, e.g., 3306 and 3307 and two backends one with your read only config and one with your write config. Then change your app so that DB_HOST_R and DB_HOST_W can include a port number.

Another solution is to assign another IP address to the server and have two frontends binding to specific IPs rather than bind *:3306 and two backends as above.

  • I've changed my config to have two frontend and two backends. The READ front/backend is working great - set to port 3306. But my WRITE front/backend is not working properly. Here is my config - jsfiddle.net/698YT - what am I missing? – Freddie Feb 24 '14 at 23:45
  • Nevermind - firewall issue. Thanks for your help! – Freddie Feb 24 '14 at 23:50
0

Ideally... this would be implemented on the application side.

For a server side implementation, we have employed MySQL Cluster . I have always found master-master MySQL setups to be more work than they are worth.

The major issue you will run into with this setup is keepalive behavior. If your application expects to talk to the same database and it fails (or just balances the load to another) you can see interesting errors depending on your implementation.

Short && Long

MySQL Cluster is designed for this kind of thing, but there are gotchas that you will need to review with your development/systems teams.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.