MySQL Master / Master Replication : Load Balancing With EC 2’s Elastic Load Balancer

amazon ec2mysql-replication

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.

Best Answer

I once read a blog entry on this URL posted on July 13, 2009: http://www.mysqlperformanceblog.com/2009/07/13/what_problems_will_i_have_migrating_into_the_cloud/

Here is an excerpt from Point #1 : Most High Availability tools (like MMM or DRBD+Heartbeat) work on the principal of having a floating IP address which is used for the application to connect to the current master. With EC2, you can’t do this.

Now if this is true to some extent, you may have to implement some round-robin load balancing in your application for DB Writes to your MySQL servers in the MultiMaster setup. You many need to do this just to play it safe.

You should look deeper into how EC2 load balancing works.

Are you using EC2 load balancing on your read slaves? Check the servers loads on those slave to make sure load balancing is actually occurring.

Related Topic