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">


Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Here's where you'll hear about all the new and fun stuff we are doing with MySQL on Windows.

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
5
6
7
8
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today