Tag Archives: Mysqlfabric Sharding

mysql fabricにて書き込みのRANGEシャーディングしたテーブルから、
データを読み込みの確認。

分割定義

# Create Sharding Grop1
mysqlfabric group create shard1
mysqlfabric group add shard1 127.0.0.1:63304
mysqlfabric group add shard1 127.0.0.1:63305
mysqlfabric group add shard1 127.0.0.1:63306
mysqlfabric group promote shard1
mysqlfabric group activate shard1

# Create Sharding Group2
mysqlfabric group create shard2
mysqlfabric group add shard2 127.0.0.1:63307
mysqlfabric group add shard2 127.0.0.1:63308
mysqlfabric group add shard2 127.0.0.1:63309
mysqlfabric group promote shard2
mysqlfabric group activate shard2

# Add sharding to employee
mysqlfabric sharding add_table 1 test.employees emp_no
mysqlfabric sharding add_shard 1 shard1/1,shard2/300 --state=enabled

それぞれのグループに分散されたテーブルからデータを読み込み
0 ~ 299は、shard1
300以上はshard2

# coding: utf-8

import mysql.connector 
from mysql.connector import fabric

import time

def find_employee(conn, emp_no):
    try:
       conn.set_property(tables=["test.employees"], key=str(emp_no), mode=fabric.MODE_READONLY, scope=fabric.SCOPE_LOCAL)
       cur = conn.cursor()
       cur.execute(
           "SELECT concat('Hostname:',@@hostname),concat(': ',@@port,' ; '), first_name, last_name "
           "FROM employees WHERE emp_no = %s", (emp_no,)
        )
    except mysql.connector.Error: 
       print "find_employee: Database connection error, trying to reconnect ..." 
       conn=connect() 
    for row in cur:
        print row[0],row[1],row[2],row[3]

# Address of the Fabric, not the host we are going to connect to.
def connect():
    try:
       conn=mysql.connector.connect(
          fabric={"host" : "localhost", "port" : 32274, "username": "admin", "password": "admin"},
          user="app", database="test", password="app",
          autocommit=True
       )
    except mysql.connector.Error as e: 
        print "Error trying to get a new database connection"
        print "Error code:", e.errno 
        print "SQLSTATE value:", e.sqlstate
        print "Error message:", e.msg
        quit()
    return conn

conn = connect()
for x in range(295, 310):
    find_employee(conn, x)

実行結果
メモ：それぞれのFabric Groupのスレーブから分散してデータを持ってきている。