Using a AWS Network Load Balancer for MySQL Cluster

1

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

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?

mysql
amazon-web-services
haproxy
amazon-elb
asked on Server Fault Dec 16, 2019 by Hooman Bahreini • edited Dec 17, 2019 by Hooman Bahreini

1 Answer

1

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

answered on Server Fault Dec 16, 2019 by MLu

User contributions licensed under CC BY-SA 3.0