Load balance and fail over for MySQL droplets

March 19, 2014 3.8k views
adam177966
By:
adam177966
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.
Log In to Comment
2 Answers
mr_j936 March 20, 2014
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...
Reply
kamaln7 MOD March 30, 2014
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.
How To Use HAProxy to Set Up MySQL Load Balancing
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.
Reply
Have another answer? Share your knowledge.