MySQL Fabric for .net

MySQL Fabric for .net

Connector/Net supports MySQL Fabric as a Replication/Load balancing plugin.

Note

This feature was added in MySQL Connector/Net 6.9.4.

The following steps are required to use MySQL Fabric with Connector/Net:

System Requirements

Confirm that you have the required Connector/Net and Fabric versions installed:

• Connector/Net 6.9.4 or newer

• MySQL Fabric 1.5.0 or newer

Set up the MySQL Fabric plugin

First, add MySql.Data and MySql.Fabric.Plugin to the project references:

Figure 5.1 MySQL Fabric Project References

Second, add a configuration section with the Fabric connection to the App.config configuration file. For example:

 <configuration>
  <configSections>
   <section name="MySQL" type="MySql.Data.MySqlClient.MySqlConfiguration, MySql.Data,
     Version=6.9.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
  </configSections>
  <MySQL>
   <Replication>
    <ServerGroups>
     <Group name="Fabric" groupType="MySql.Fabric.FabricServerGroup, MySql.Fabric.Plugin">
      <Servers>
       <Server name="fabric" connectionstring="server=localhost;port=32275;uid=admin;password=adminpass;"/>
      </Servers>
     </Group>
    </ServerGroups>
   </Replication>
  </MySQL>
 </configuration>

Notice that the Fabric connection is set in the Server node:

<Server name="fabric" connectionstring="server=localhost;port=32275;uid=admin;password=adminpass;"/>

Connector/Net only supports the MySQL protocol for connecting to MySQL Fabric, so the correct port must be used.

Using MySQL Fabric Groups

The MySQL Fabric group is used with a MySqlConnection that contains the server name specified in the App.config file, and a username and password for connecting to the servers defined in the group.

A Fabric extension method is used to specify the group and mode:

MySqlConnection conn = new MySqlConnection(connectionString);

conn.SetFabricProperties(groupId: "my_group", mode: FabricServerModeEnum.Read_Write);

The following example shows how to store and retrieve information in a specific Fabric group:

Note

The initial MySQL Fabric configuration for this example is defined in the MySQL Fabric documentation at Example: Fabric and Replication.

using System;
using MySql.Data.MySqlClient;
using MySql.Fabric;

namespace FabricTest
{
  class Program
  {
    public const string connectionString = "server=fabric;uid=appuser;password=pass;";

    static void Main(string[] args)
    {
      RunFabricTest();
    }

    static string AddEmployee(MySqlConnection conn, int emp_no, string first_name, string last_name)
    {
      conn.SetFabricProperties(groupId: "my_group", mode: FabricServerModeEnum.Read_Write);

      MySqlCommand cmd = new MySqlCommand("USE employees", conn);
      cmd.ExecuteNonQuery();

      cmd.CommandText = "INSERT INTO employees VALUES (@emp_no, @first_name, @last_name)";
      cmd.Parameters.Add("emp_no", emp_no);
      cmd.Parameters.Add("first_name", first_name);
      cmd.Parameters.Add("last_name", last_name);
      cmd.ExecuteNonQuery();

      cmd.CommandText = "SELECT @@global.gtid_executed";
      cmd.Parameters.Clear();
      using (MySqlDataReader reader = cmd.ExecuteReader())
      {
        while (reader.Read())
        {
          Console.WriteLine("Transactions executed on the master " + reader.GetValue(0));
        }
        return reader.GetString(0);
      }
    }

    static void FindEmployee(MySqlConnection conn, int emp_no, string gtid_executed)
    {
      conn.SetFabricProperties(groupId: "my_group", mode: FabricServerModeEnum.Read_only);

      MySqlCommand cmd = new MySqlCommand("", conn);
      cmd.CommandText = "SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS(@gtid_executed, 0)";
      cmd.Parameters.Add("gtid_executed", gtid_executed);
      using (MySqlDataReader reader = cmd.ExecuteReader())
      {
        while (reader.Read())
        {
          Console.WriteLine("Had to synchronize " + reader.GetValue(0) + " transactions.");
        }
      }

      cmd.CommandText = "USE employees";
      cmd.Parameters.Clear();
      cmd.ExecuteNonQuery();

      cmd.CommandText = "SELECT first_name, last_name FROM employees ";
      cmd.CommandText += " WHERE emp_no = @emp_no";
      cmd.Parameters.Clear();
      cmd.Parameters.Add("emp_no", emp_no);
      using (MySqlDataReader reader = cmd.ExecuteReader())
      {
        while (reader.Read())
        {
          object[] values = new object[reader.FieldCount];
          reader.GetValues(values);
          Console.WriteLine("Retrieved {0}", string.Join(",", values));
        }
      }
    }

    static void RunFabricTest()
    {
      using (MySqlConnection conn = new MySqlConnection(connectionString))
      {
        string gtid_executed;
        conn.SetFabricProperties(groupId: "my_group", mode: FabricServerModeEnum.Read_Write);
        conn.Open();

        MySqlCommand cmd = new MySqlCommand("", conn);
        cmd.CommandText = "CREATE DATABASE IF NOT EXISTS employees;";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "USE employees;";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "DROP TABLE IF EXISTS employees;";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "CREATE TABLE employees(";
        cmd.CommandText += "   emp_no INT, ";
        cmd.CommandText += "   first_name CHAR(40), ";
        cmd.CommandText += "   last_name CHAR(40)";
        cmd.CommandText += ");";
        cmd.ExecuteNonQuery();

        gtid_executed = AddEmployee(conn, 12, "John", "Doe");
        FindEmployee(conn, 12, gtid_executed);
      }
    }
  }
}

Using Ranged Sharding

Sharding with Connector/Net requires you to specify the table, key, and scope for each executed query.

MySqlConnection con = new MySqlConnection(connectionString);

con.SetFabricProperties(table: "employees.employees", key: empId.ToString(),
  mode: FabricServerModeEnum.Read_Write, scope: FabricScopeEnum.Local);

MySqlCommand cmd = new MySqlCommand(
  string.Format("insert into employees(emp_no, first_name, last_name) values ({0}, '{1}', '{2}')",
  empId, firstName, lastName), con);
cmd.ExecuteScalar();

You can use the following MySQL Fabric configuration to execute the code example:

Note

For related MySQL Fabric documentation, see Sharding Scenario.

using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using MySql.Fabric;

namespace FabricTest
{
  class ShardTest
  {
    public const string connectionString = "server=fabric;uid=appuser;password=pass;";

    public static void test_shard_range()
    {
      using (MySqlConnection con = new MySqlConnection(connectionString))
      {
        con.SetFabricProperties(groupId: "group_id-global", mode: FabricServerModeEnum.Read_Write,
          scope: FabricScopeEnum.Global);
        con.Open();

        MySqlCommand cmd = new MySqlCommand("create database if not exists employees", con);
        cmd.ExecuteScalar();
      
        cmd.CommandText = "use employees";
        cmd.ExecuteScalar();

        cmd.CommandText = "drop table if exists employees";
        cmd.ExecuteScalar();

        cmd.CommandText =
@"create table employees (
    emp_no int,
    first_name char( 40 ),
    last_name char( 40 )
)";
        cmd.ExecuteScalar();
      
        string gtid = prepare_synchronization(con);

        string[] first_names = { "John", "Buffalo", "Michael", "Kate", "Deep", "Genesis" };
        string[] last_names = { "Doe", "Bill", "Jackson", "Bush", "Purple" };
        List<int> list_emp_no = new List<int>();

        con.SetFabricProperties(scope: FabricScopeEnum.Local);

        for (int i = 0; i < 10; i++)
        {
          int empId = pick_shard_key();
          list_emp_no.Add(empId);
          add_employee(con, empId, first_names[empId % first_names.Length], last_names[empId % last_names.Length], gtid);
        }

        for (int i = 0; i < list_emp_no.Count; i++)
        {
          int empId = list_emp_no[ i ];
          find_employee(con, empId, gtid);
        }
      }
    }

    public static int pick_shard_key()
    {
      Random r = new Random();
      int shard = r.Next(0, 2);
      int shard_range = shard * 10000;
      shard_range = (shard != 0) ? shard_range : shard_range + 1;
      int shift_within_shard = r.Next(0, 99999);
      return shard_range + shift_within_shard;
    }

    public static void add_employee(MySqlConnection con, int empId, string firstName, string lastName, string gtid)
    {
      con.SetFabricProperties( table: "employees.employees", key: empId.ToString(), mode: FabricServerModeEnum.Read_Write);
      synchronize(con, gtid);
      MySqlCommand cmd = new MySqlCommand(
        string.Format("insert into employees( emp_no, first_name, last_name ) values ( {0}, '{1}', '{2}' )",
        empId, firstName, lastName), con);
      cmd.ExecuteScalar();
    }

    public static void find_employee(MySqlConnection con, int empId, string gtid)
    {
      con.SetFabricProperties(table: "employees.employees", key: empId.ToString(),
        mode: FabricServerModeEnum.Read_only);
      synchronize(con, gtid);
      MySqlCommand cmd = new MySqlCommand(string.Format("
        select first_name, last_name from employees where emp_no = {0}", empId), con);
      using (MySqlDataReader r = cmd.ExecuteReader())
      {
        while (r.Read())
        {
          Console.WriteLine("( {0}, {1} )", r.GetString(0), r.GetString(1));
        }
      }
    }

    public static string prepare_synchronization(MySqlConnection con)
    {
      string gtid_executed = "";
      MySqlCommand cmd = new MySqlCommand("select @@global.gtid_executed", con);
      gtid_executed = ( string )cmd.ExecuteScalar();
      return gtid_executed;
    }

    public static void synchronize(MySqlConnection con, string gtid_executed)
    {
      MySqlCommand cmd = new MySqlCommand( string.Format( "SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('{0}', 0)",
        gtid_executed ), con);
      cmd.ExecuteScalar();
    }
  }
}