I have a RoR application that I have working on EC 2. I have two MySQL server instances in a master / master replicated environment, and a few read-slaves. Once I established that the masters were working in the way in which I expect, I set up an EC 2 Load Balancer across the two masters, which are in different availability zones, with this command:
elb-create-lb db_masters --zones us-east-1c,us-east-1b \ --listener "protocol=tcp,lb-port=3306, instance-port=3306"
When I manually add data via direct queries, it replicates fine, and I can use the Rails application for a few basic queries. However when I add data through the Rails app, the rails process fails with this error:
Status: 500 Internal Server Error Host 'ip-xxxxxxxxxxxx.internal' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
Adding data directly works as expected between the two servers, adding data on one is always replicated on the other faithfully. So the problem isn't with the replication set-up.
I have tried to add this IP address as a wildcard and specifically to the hosts for this user and still get this error. I have tried flush hosts also, and it works for a while before failing again.
My question is how does one set up MySQL master / master load balancing normally, and secondly, how would one do it on EC 2 with the elastic load balancer.