Using a AWS Network Load Balancer for MySQL Cluster

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).

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:

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:

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?

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 :)