X

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:
<Group name="MasterSlave" retryTime="60">


Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.