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?