MySQL and MySQL Community information
- November 20, 2013
How to: Using Replication & Load balancing with Connector/NET
Overview
Connector/NET
provides replication & load balancing configuration that allows to connect
to master/slaves environments and at the same time balancing the request over
all the available server in this scenario.
This post
will show you how to configure and use these new features on a console
application.
Requirements:
- MySQL
Connector/NET 6.7 or later - Replication
environment using MySQL Server - Visual
Studio 2010 or 2012 - Sakila
sample database installed (or any other database sample)
Step 1: Creating a new
Console application
Create a
Console application In Visual Studio choosing File > New > Project > Templates > Visual C# > Windows
> Console Application called “ReplicationConsoleTest”:
Add the
reference to MySql.Data in the project:
Step 2: Configuring
replication & load balancing
Open
App.config and add the configuration for replication:
<configSections>
<section name="MySQL" type="MySql.Data.MySqlClient.MySqlConfiguration, MySql.Data, Version=6.7.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
</configSections>
<MySQL>
<Replication>
<ServerGroups>
<Group name="MasterSlave">
<Servers>
<Server name="master" IsMaster="true" connectionstring=" server=<server>;port=<port>;uid=<user>;password=<password>;"/>
<Server name="slave" IsMaster="false" connectionstring=" server=<server>;port=<port>;uid=<user>;password=<password>;"/>
</Servers>
</Group>
</ServerGroups>
</Replication>
</MySQL>
Here you
define the Group name and the
connection string for each server that is part of the replication environment.
You also define which server is the master using IsMaster="true" (by default this attribute is false
and can be omitted in the configuration for slaves).
Step 3: Using replication
& load balancing
Create a
new class MyData.cs in the project and a new static method GetData.
Inside
GetData method create a new MySqlConnection object using the Group name defined previously as the server attribute in a new connection
string and the database we want to use. Open the connection to validate the
configuration is correct (don’t forget to call GetData from Main method):
class MyData
{
public static void GetData()
{
using (MySqlConnection connection = new MySqlConnection("server=MasterSlave;database=sakila;"))
{
connection.Open();
}
}
}
Next step
will validate the connection to the master and the slave. This can be done executing
some queries to retrieve the hostname and the port for each connection to
verify connection to the master and slave.
Define a
new MySqlCommand object using the connection defined previously and the
following query:
show variables where variable_name in
('hostname', 'port')
Note: if
you are using the same host for master and slave is enough getting port without
hostname.
Create a
small loop showing this data into the console:
class MyData
{
public static void GetData()
{
using (MySqlConnection connection = new MySqlConnection("server=MasterSlave;database=sakila;"))
{
connection.Open();
MySqlCommand command = new MySqlCommand("show variables where variable_name in ('port')", connection);
for (int i = 0; i < 10; i++)
{
MySqlDataReader reader = command.ExecuteReader();
reader.Read();
Console.WriteLine("Port: {0}", reader.GetValue(1));
reader.Close();
}
}
}
}
As you can
see the port is different every time the query is executed and this is result
of the load balancing used in replication.
Important notes
- Read-only queries use a load
balancing behavior but the DML queries (insert, update, delete, etc.) are sent to
the master server. - Round Robin distribution is used in
Connector/NET for Load Balancing - Failover support: Connector/NET
monitors connections to the servers redirecting load to available servers on
each request. When a server is back online is marked available again. Group
section in App.config has a retryTime
attribute used to configure the period in seconds to retry the connection to a
failed server: