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