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

Question

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.

Answer 1

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.

Answer 2

Pinging port 3306 from the EC2 LB will cause a host connection error in MySQL. The default for max_connect_errors is 10. You can raise this number arbitrarily high and periodically flush hosts or you can use a different health check methodology in the EC2 LB. For instance a simple PHP MySQL connection using HTTP port 80 that will throw a 200 if successful and a 404 if not.

<?php
$link = mysql_connect('localhost', 'user', 'pass');
if (!$link) {
header("HTTP/1.1 404 Not Found");
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?>

Set the EC2 LB to health check HTTP port 80 using the URL for this file and you should be good to go.