MySQL Fabric for .net
Connector/Net supports MySQL Fabric as a Replication/Load balancing plugin.
This feature was added in MySQL Connector/Net 6.9.4.
The following steps are required to use MySQL Fabric with Connector/Net:
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
First, add MySql.Data and MySql.Fabric.Plugin to the 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.
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:
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); } } } }
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:
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(); } } }