Load balance and fail over for MySQL droplets

March 19, 2014 2k views
I'm looking for recommendations on how I could load balance two MySQL droplets. I have multiple droplets that will connect to these two droplets and there may be more than two eventually. Data replication is already setup but I'm unsure of how I could get my droplets to send the load between the two and have fail over. By the way, I'm using the Percona XtraDB Cluster. Thanks for any help.
2 Answers
I have not personally attempted something similar with mysql but. If I were going to try this, I would think about putting a load balancer server between mysql instances and the droplets that will connect to them. Perhaps something like HA proxy. We configure this proxy by telling it what its backends are and when to connect to each(perhaps a round robin) And then all your droplets would connect to the proxy server without caring how many sql you have.

When you want to add an additional mysql, it's a simple matter of settign up the replication, and adding its ip to the proxy list of backends so that it includes it as a possible reference...
We have an article on how to set up MySQL failover using HAProxy: https://www.digitalocean.com/community/articles/how-to-use-haproxy-to-set-up-mysql-load-balancing--3.
by Jesin A
HAProxy is an open source software which can load balance HTTP and TCP servers. In the previous article on HAProxy we configured load balancing for HTTP and in this one we'll do the same for MySQL. All your MySQL servers have to be configured to perform Master-Master replication as load balancing involves both reading and writing to all the backends.
Have another answer? Share your knowledge.