CODEWITHSUNDEEP
c#mysqldatabase-connectionconnection-string
Odatia
Odatia

Reputation: 65

MySQL c# Connection String failover

I am aware that I can seperate hosts in the connection string with a comma and it will use different servers: https://www.connectionstrings.com/mysql-connector-net-mysqlconnection/multiple-servers/

for example: Server=serverAddress1, serverAddress2, serverAddress3;Database=myDataBase; Uid=myUsername;Pwd=myPassword;

But I need some information on how it specifically chooses servers. For example, is it round robin? Or does it go in order, until it finds a working one?

If the first one fails, and it moves to the second one, how long would it be before it attempted to use the second one?

I am open to other suggestions for failover connection strings

TIA - Joe

Upvotes: 2

Views: 1783

Answers (2)

Bradley Grainger
Bradley Grainger

Reputation: 28162

The MySQL documentation says that multiple hosts can be separated by commas:

Multiple hosts can be specified separated by commas. This can be useful where multiple MySQL servers are configured for replication and you are not concerned about the precise server you are connecting to.

Unfortunately, this behaviour was broken in Connector/NET 8.0.18 and earlier (it was fixed in 8.0.19).

Connector/NET 8.0.19 will try multiple hosts at random unless you specify a priority attribute for each host. For example:

// hosts will be tried at random
host=10.10.10.10:3306,192.101.10.2:3305,localhost:3306;uid=test;password=xxxx;

// hosts will be tried in descending priority order
server=(address=192.10.1.52:3305,priority=60),(address=localhost:3306,priority=100);

If you can't update to 8.0.19, there is an alternative OSS MySQL ADO.NET provider that does support multiple comma-delimited hosts: MySqlConnector on GitHub, NuGet. Additionally, it has a Load Balance connection string option that lets you specify the exact kind of load balancing you want: RoundRobin, FailOver, Random, LeastConnections.

Upvotes: 3

jason.kaisersmith
jason.kaisersmith

Reputation: 9610

The MySQL documentation is your friend here. It states

The host list in the connection URL comprises of two types of hosts, the primary and the secondary. When starting a new connection, the driver always tries to connect to the primary host first and, if required, fails over to the secondary hosts on the list sequentially when communication problems are experienced. Even if the initial connection to the primary host fails and the driver gets connected to a secondary host, the primary host never loses its special status

So in the connection string below, the first host is the primary and will be selected first for connection. Only if this is not available will a secondary host be selected. It will also try to fail back to the primary host ASAP, but how this works can be configured.

jdbc:mysql://[primary host][:port],[secondary host 1][:port][,[secondary host 2][:port]]...[/[database]]» [?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-config-failover.html

Upvotes: 1

Related Questions