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?


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  ; db node 1
                  IN A    ; 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 :)

| improve this answer | |
  • thanks a lot. I don't have MySQL installed on my WebServer... The above NLB is not Internet facing... I changed it to be internet facing, and then I could login to it from my Dev Machine (which is outside of the VPC)... so NLB is working, though I have not managed to get it running through the web server – Hooman Bahreini Dec 16 '19 at 9:50
  • So, are you suggesting to use weighted DNS records? The reason I wanted to use NLB was that I though the DNS records could be cached in the client, which means if a DB fails, that client would still try to connect to it because it has the ip in the cache... is this not the case? – Hooman Bahreini Dec 16 '19 at 9:52
  • NLB will reduce DNS lookup time, but with a decent DNS service that's likely not an issue. With DNS load balancing set your TTL to 5 minutes, then every 5 minutes the client will spend 10 - 100ms getting a new IP address. You will of course need some kind of solution to keep the MySQL instances synchronized, and if you change servers in the middle of a transaction bad things could happen. I would look at a supported solution for this rather than trying to put in something generic that isn't aware it's load balancing a database. – Tim Dec 17 '19 at 0:04
  • @Tim if you disconnect and connect to the other node in the middle of transaction the transaction will be aborted and you have to start again so that’s not an issue. – MLu Dec 17 '19 at 1:52
  • 1
    @HoomanBahreini yes private IPs, DNS doesn’t care. – MLu Dec 17 '19 at 2:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.