mysql-proxy with AWS ELB

[Ross Carver]

Good afternoon,

In looking for a solution to making the most cost effective highly available mysql implementation in AWS, I've begun testing the following setup:

They have a multi-az option for failover which is nice (well, easy) for the master, however it gets costly to maintain a large instance with a hot-standby of equal size that is essentially useless 99% of the time.

With that said, I've opted for a smaller master setup with the option to play with either their read-replicas or to home role my own slaves using a regular ec2 (and perhaps even spots!) and standard asynch replication off the master.

Where mysql proxy comes in is on the read / write query splitting which allows the applicaton to be unaware and the managing of these different types of read-slaves etc. Furthermore, because mysql-proxy is lightweight, it gets loaded on each webserver instance running, all of which are plugged into Amazon's load balancer.


With this setup, the load balancer checks a URL response on each webserver and If you make the url response database connect dependent, the host will drop out of the load balancing if mysql-proxy should croak, so the AWS load balancer in this way is coerced into making mysql-proxy HA, which is nice (kill two birds, one stone).

The feature I see that is most of want in this setup is the ability to add backend (read slaves) into the proxy pool without restarting mysql-proxy.

Thoughts on any of this? I haven't done any real hard testing of durability, but the goal is accomplished of having a given proxy instance become as expendable as the webserver instance and to keep the setup simple.