Mysql – Using a AWS Network Load Balancer for MySQL Cluster

amazon-elbamazon-web-serviceshaproxyMySQL

I have asked this question on stackoverflow but I though it might be more suitable for this community.

I have a web server which connects to a MySQL cluster (it's Galera cluster)… the cluster works fine (if I update a record on the first instance of MySQL, it is automatically updated on the second instance). The problem is I need a load balancer between the web server and MySQL instances…

So What I have done is to setup an AWS Network Load Balancer (NLB works on Network Layer, TCP in my case)… I have added the 2 instances of MySQL to the NLB's Target group (their private IPs).

enter image description here

Now I go ahead an update the connection string in my Web Server to connect to the NLB:

<add name="MyDB" connectionString="Server=my-nlb-ap-southeast-2.amazonaws.com; Port=3306; Database=mydb; Uid=user1; Pwd=mypassword; Protocol=TCP" providerName="MySql.Data.MySqlClient"/>

But I get the following error:

[MySqlException (0x80004005): Unable to connect to any of the
specified MySQL hosts.] MySql.Data.MySqlClient.NativeDriver.Open()
+1734 MySql.Data.MySqlClient.Driver.Open() +60 MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder
settings) +309
MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection() +18
MySql.Data.MySqlClient.MySqlPool.GetPooledConnection() +290
MySql.Data.MySqlClient.MySqlPool.TryToGetDriver() +151
MySql.Data.MySqlClient.MySqlPool.GetConnection() +60
MySql.Data.MySqlClient.MySqlConnection.Open() +1588
MySql.Data.MySqlClient.MySqlProviderServices.GetDbProviderManifestToken(DbConnection
connection) +182
System.Data.Entity.Core.Common.DbProviderServices.GetProviderManifestToken(DbConnection
connection)

When I try to connect to MySQL from my web server, I get the following:

enter image description here

enter image description here

I believe that this means, the web server is able to reach the MySQL instance through the NLB, but I don't know why the connection string is not working.

I have opened port 3306 to all IPs, and I can connect to the NLB from my local computer which is not in the VPC:

enter image description here

But the web server is still getting the same error…

The reason I decided to use AWS NLB instead of HAProxy, is that NLB has high availability… I believe, if I want to use HAProxy, then I would need to add 2 more servers to my VPC as HAProxy (for high availability)… In case of NLB, I can just use 1 high availability load balancer, but I am not sure if is possible to use an AWS NLB for this purpose?

Best Answer

Interesting that the connection string doesn’t work. Can you run mysql client from the webserver and verify that it can connect and authenticate? 

C:\> mysql -hmy-nlb-ap-southeast-2.amazonaws.com -uuser1 -pmypassword

Also - are you sure you need NLB after all? How about using DNS for your load balancing? Create a record like mydb.example.com with two addresses, one for each of your db cluster nodes.

mydb.example.com. IN A 10.20.30.40  ; db node 1
                  IN A 10.20.40.50    ; db node 2

DNS records with multiple values are returned in round-robin order, which means that some clients will get node 1 IP first while others will get node 2 first. And if their primary node fails they still have the other IP to fail over to.

I would try this DNS approach first before spending too much time and money on NLB.

Hope that helps :)

Related Topic