MYSQL FABRIC集群分片测试
一、环境说明
3个mysql HA组，1个mysql fabric node，1个state store数据库
my_group是全局Group：
MYSQL HA GROUP my_group：192.168.10.228:3306 master
192.168.10.229:3306 slave1
192.168.10.230:3306 slave2
MYSQL HA GROUP shard1： 192.168.10.228:3308 master
192.168.10.229:3308 slave1
192.168.10.230:3308 slave2
MYSQL HA GROUP shard2： 192.168.10.228:3309 master
192.168.10.229:3309 slave1
192.168.10.230:3309 slave2
MYSQL FABRIC NODE : 192.168.10.232
MYSQL FABRIC STORE : 192.168.10.232 3306 (MYSQL 数据库)
二、创建分组
[root@yw32 mysql]# mysqlfabric group create my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
de19dcbf-7cb3-4783-84f7-2f636bef4e15 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x20c2050>.
4 2 1.4595e+09 Executing action (_create_group).
5 2 1.4595e+09 Executed action (_create_group).
[root@yw32 ~]# mysqlfabric group create shard1
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
9aa28247-e6f2-420c-86ae-f7a2a787fdb3 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122a050>.
4 2 1.46e+09 Executing action (_create_group).
5 2 1.46e+09 Executed action (_create_group).
[root@yw32 ~]# mysqlfabric group create shard2
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
071704b8-3cb7-41b9-986f-a7e6d5176ab5 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122a050>.
4 2 1.46e+09 Executing action (_create_group).
5 2 1.46e+09 Executed action (_create_group).
三、添加成员到组
添加全局组成员：
往HA组添加成员 192.168.10.228:3306
[root@yw32 mysql]# mysqlfabric group add my_group 192.168.10.228:3306
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
551981f6-de1c-4660-aef6-cbc9980a9489 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x20c2410>.
4 2 1.4595e+09 Executing action (_add_server).
5 2 1.4595e+09 Executed action (_add_server).
往HA组添加成员 192.168.10.229:3306
[root@yw32 mysql]# mysqlfabric group add my_group 192.168.10.229:3306
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
8e09e2d8-b7e7-46f7-be1a-266613dbbd8d 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x20c2410>.
4 2 1.4595e+09 Executing action (_add_server).
5 2 1.4595e+09 Executed action (_add_server).
往HA组添加成员 192.168.10.230:3306
[root@yw32 mysql]# mysqlfabric group add my_group 192.168.10.230:3306
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
0dff18a2-d8ee-4b33-88a9-74c12e19ce9d 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x20c2410>.
4 2 1.4595e+09 Executing action (_add_server).
5 2 1.4595e+09 Executed action (_add_server).
添加shard1组成员
[root@yw32 ~]# mysqlfabric group add shard1 192.168.10.228:3308
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
69806eae-f460-44f8-8cf3-84b33f4ce528 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122a410>.
4 2 1.46e+09 Executing action (_add_server).
5 2 1.46e+09 Executed action (_add_server).
[root@yw32 ~]# mysqlfabric group add shard1 192.168.10.229:3308
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
d7ae7217-2ce2-4448-aabe-748d9de8e535 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122a410>.
4 2 1.46e+09 Executing action (_add_server).
5 2 1.46e+09 Executed action (_add_server).
[root@yw32 ~]# mysqlfabric group add shard1 192.168.10.230:3308
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
808603fa-36bc-4c57-b70a-27732e2ae38e 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122a410>.
4 2 1.46e+09 Executing action (_add_server).
5 2 1.46e+09 Executed action (_add_server).
添加shard2组员：
[root@yw32 ~]# mysqlfabric group add shard2 192.168.10.228:3309
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
742166f8-4480-43de-9c3d-1dd981286cec 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122a410>.
4 2 1.46e+09 Executing action (_add_server).
5 2 1.46e+09 Executed action (_add_server).
[root@yw32 ~]# mysqlfabric group add shard2 192.168.10.229:3309
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
2284522a-2dad-46fd-aef9-0243eac2492b 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122a410>.
4 2 1.46e+09 Executing action (_add_server).
5 2 1.46e+09 Executed action (_add_server).
[root@yw32 ~]# mysqlfabric group add shard2 192.168.10.230:3309
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
e9b4e895-4b40-4ac1-ad0c-d8227b96ba10 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122a410>.
4 2 1.46e+09 Executing action (_add_server).
5 2 1.46e+09 Executed action (_add_server).
四、每个组选举一个PRIMARY节点
[root@yw32 mysql]# mysqlfabric group promote my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
f3e50f87-2109-44af-9144-ceae99bcc9f1 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x1ed83d0>.
4 2 1.4595e+09 Executing action (_define_ha_operation).
5 2 1.4595e+09 Executed action (_define_ha_operation).
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x202cc90>.
4 2 1.4595e+09 Executing action (_find_candidate_fail).
5 2 1.4595e+09 Executed action (_find_candidate_fail).
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x202ccd0>.
4 2 1.4595e+09 Executing action (_check_candidate_fail).
5 2 1.4595e+09 Executed action (_check_candidate_fail).
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x202ca50>.
4 2 1.4595e+09 Executing action (_wait_slave_fail).
5 2 1.4595e+09 Executed action (_wait_slave_fail).
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x2031a90>.
4 2 1.4595e+09 Executing action (_change_to_candidate).
5 2 1.4595e+09 Executed action (_change_to_candidate).
[root@yw32 ~]# mysqlfabric group promote shard1
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
99df0536-6776-4b4e-b782-0369ab0d236c 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x10403d0>.
4 2 1.46e+09 Executing action (_define_ha_operation).
5 2 1.46e+09 Executed action (_define_ha_operation).
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1194c90>.
4 2 1.46e+09 Executing action (_find_candidate_fail).
5 2 1.46e+09 Executed action (_find_candidate_fail).
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1194cd0>.
4 2 1.46e+09 Executing action (_check_candidate_fail).
5 2 1.46e+09 Executed action (_check_candidate_fail).
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1194a50>.
4 2 1.46e+09 Executing action (_wait_slave_fail).
5 2 1.46e+09 Executed action (_wait_slave_fail).
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1199a90>.
4 2 1.46e+09 Executing action (_change_to_candidate).
5 2 1.46e+09 Executed action (_change_to_candidate).
[root@yw32 ~]# mysqlfabric group promote shard2
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
4f37fd03-5731-4b41-ace7-310ca07087ba 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x10403d0>.
4 2 1.46e+09 Executing action (_define_ha_operation).
5 2 1.46e+09 Executed action (_define_ha_operation).
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1194c90>.
4 2 1.46e+09 Executing action (_find_candidate_fail).
5 2 1.46e+09 Executed action (_find_candidate_fail).
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1194cd0>.
4 2 1.46e+09 Executing action (_check_candidate_fail).
5 2 1.46e+09 Executed action (_check_candidate_fail).
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1194a50>.
4 2 1.46e+09 Executing action (_wait_slave_fail).
5 2 1.46e+09 Executed action (_wait_slave_fail).
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1199a90>.
4 2 1.46e+09 Executing action (_change_to_candidate).
5 2 1.46e+09 Executed action (_change_to_candidate).
五、查看各个组情况
[root@yw32 ~]# mysqlfabric group lookup_servers my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- ---------- ------
4c622c12-ebdd-11e5-8f8f-0050569c155b 192.168.10.229:3306 SECONDARY READ_ONLY 1.0
5348580a-f629-11e5-92b4-0050569c4a6a 192.168.10.228:3306 PRIMARY READ_WRITE 1.0
88c90835-f644-11e5-9365-0050569c0ea8 192.168.10.230:3306 SECONDARY READ_ONLY 1.0
[root@yw32 ~]# mysqlfabric group lookup_servers shard1
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- ---------- ------
4c622c13-ebdd-11e5-8f8f-0050569c155b 192.168.10.229:3308 SECONDARY READ_ONLY 1.0
53485802-f629-11e5-92b4-0050569c4a6a 192.168.10.228:3308 SECONDARY READ_ONLY 1.0
88c90836-f644-11e5-9365-0050569c0ea8 192.168.10.230:3308 PRIMARY READ_WRITE 1.0
[root@yw32 ~]# mysqlfabric group lookup_servers shard2
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- ---------- ------
4c622c14-ebdd-11e5-8f8f-0050569c155b 192.168.10.229:3309 SECONDARY READ_ONLY 1.0
53485803-f629-11e5-92b4-0050569c4a6a 192.168.10.228:3309 SECONDARY READ_ONLY 1.0
88c90837-f644-11e5-9365-0050569c0ea8 192.168.10.230:3309 PRIMARY READ_WRITE 1.0
六、定义分片策略
定义两个分片策略：
mysqlfabric sharding create_definition RANGE my_group
mysqlfabric sharding create_definition HASH my_group
[root@yw32 ~]# mysqlfabric sharding create_definition RANGE my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
e8aa6fd6-0fa4-479a-bcff-c446c11c31f1 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1187810>.
4 2 1.46e+09 Executing action (_define_shard_mapping).
5 2 1.46e+09 Executed action (_define_shard_mapping).
[root@yw32 ~]# mysqlfabric sharding create_definition HASH my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
346bdbcc-57a0-42bf-8759-672b369724da 1 1 2
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1187810>.
4 2 1.46e+09 Executing action (_define_shard_mapping).
5 2 1.46e+09 Executed action (_define_shard_mapping).
查看定义了哪些分片策略
[root@yw32 ~]# mysqlfabric sharding list_definitions
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
mapping_id type_name global_group_id
---------- --------- ---------------
1 RANGE my_group
2 HASH my_group
七、添加分片表和字段
定义两个分区表，一个用range策略，一个用hash策略：
mysqlfabric sharding add_table 1 mha.orders orderno
mysqlfabric sharding add_table 2 mha.users userno
[root@yw32 ~]# mysqlfabric sharding add_table 1 mha.orders orderno
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
3241e592-b71c-4bc9-8ddc-6504584509bd 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1187790>.
4 2 1.46e+09 Executing action (_add_shard_mapping).
5 2 1.46e+09 Executed action (_add_shard_mapping).
[root@yw32 ~]# mysqlfabric sharding add_table 2 mha.users userno
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
f9674616-1a10-4cea-b909-d4fb80f4272f 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1187790>.
4 2 1.46e+09 Executing action (_add_shard_mapping).
5 2 1.46e+09 Executed action (_add_shard_mapping).
查看表的分区情况
[root@yw32 ~]# mysqlfabric sharding lookup_table mha.orders;
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
mapping_id type_name table_name global_group column_name
---------- --------- ---------- ------------ -----------
1 RANGE mha.orders my_group orderno
八、设置表的具体分区办法
mysqlfabric sharding add_shard 1 "shard1/1, shard2/10" --state=ENABLED
这样orderno在1-10的放在shard1,10以上的放在shard2
mysqlfabric sharding add_shard 2 "shard1,shard2" --state=enabled
[root@yw32 ~]# mysqlfabric sharding add_shard 1 "shard1/1, shard2/10" --state=ENABLED
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
23eafea8-dece-4cd0-b3aa-a0bf21b283d4 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122f8d0>.
4 2 1.46e+09 Executing action (_add_shard).
5 2 1.46e+09 Executed action (_add_shard).
[root@yw32 ~]# mysqlfabric sharding add_shard 2 "shard1,shard2" --state=enabled
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
db7d7c24-7b07-4399-b7ae-a4f199f51936 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122f8d0>.
4 2 1.46e+09 Executing action (_add_shard).
5 2 1.46e+09 Executed action (_add_shard).
验证分区规则是否正确
[root@yw32 tmp]# mysqlfabric sharding lookup_servers mha.orders 30
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- ---------- ------
4c622c14-ebdd-11e5-8f8f-0050569c155b 192.168.10.229:3309 SECONDARY READ_ONLY 1.0
53485803-f629-11e5-92b4-0050569c4a6a 192.168.10.228:3309 SECONDARY READ_ONLY 1.0
88c90837-f644-11e5-9365-0050569c0ea8 192.168.10.230:3309 PRIMARY READ_WRITE 1.0
[root@yw32 tmp]# mysqlfabric sharding lookup_servers mha.orders 3
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- ---------- ------
4c622c13-ebdd-11e5-8f8f-0050569c155b 192.168.10.229:3308 SECONDARY READ_ONLY 1.0
53485802-f629-11e5-92b4-0050569c4a6a 192.168.10.228:3308 SECONDARY READ_ONLY 1.0
88c90836-f644-11e5-9365-0050569c0ea8 192.168.10.230:3308 PRIMARY READ_WRITE 1.0
九、在全局组创建测试表
mysql -u root -p123456 --socket=/var/lib/mysql/mysql.sock
use mha
create table orders(
id int primary key auto_increment,
orderno int not null,
product_name varchar(50) not null
);
create table users(
id int primary key auto_increment,
userno int not null,
user_name varchar(50) not null
);
十、插入测试数据
import random
import mysql.connector
from mysql.connector import fabric
def prepare_synchronization(cur):
# We need to keep track of what we have executed so far to guarantee
# that the employees.employees table exists at all shards.
gtid_executed = None
cur.execute("SELECT @@global.gtid_executed")
for row in cur:
gtid_executed = row[0]
return gtid_executed
def synchronize(cur, gtid_executed):
# Guarantee that a slave has created the employees.employees table
# before reading anything.
cur.execute(
"SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('%s', 0)" %
(gtid_executed, )
)
cur.fetchall()
def add_employee(conn, emp_no, first_name, last_name, gtid_executed):
conn.set_property(tables=["employees.employees"], key=emp_no,
mode=fabric.MODE_READWRITE)
cur = conn.cursor()
synchronize(cur, gtid_executed)
cur.execute("USE employees")
cur.execute(
"INSERT INTO employees VALUES (%s, %s, %s)",
(emp_no, first_name, last_name)
)
def find_employee(conn, emp_no, gtid_executed):
conn.set_property(tables=["employees.employees"], key=emp_no,
mode=fabric.MODE_READONLY)
cur = conn.cursor()
synchronize(cur, gtid_executed)
cur.execute("USE employees")
for row in cur:
print "Had to synchronize", row, "transactions."
cur.execute(
"SELECT first_name, last_name FROM employees "
"WHERE emp_no = %s", (emp_no, )
)
for row in cur:
print row
def pick_shard_key():
shard = random.randint(0, 2)
shard_range = shard * 100000
shard_range = shard_range if shard != 0 else shard_range + 1
shift_within_shard = random.randint(0, 99999)
return shard_range + shift_within_shard
# Address of the Fabric, not the host we are going to connect to.
conn = mysql.connector.connect(
fabric ={"host":"localhost","port":32274,"username":"admin","password":"admin"},
user="fabric",database="mha",password="123456",autocommit=True
)
conn.set_property(tables=["employees.employees"], scope=fabric.SCOPE_LOCAL,
mode=fabric.MODE_READWRITE)
cur = conn.cursor()
cur.execute("CREATE DATABASE IF NOT EXISTS employees")
cur.execute("USE employees")
cur.execute("DROP TABLE IF EXISTS employees")
cur.execute(
"CREATE TABLE employees ("
" emp_no INT, "
" first_name CHAR(40), "
" last_name CHAR(40)"
")"
)
gtid_executed = prepare_synchronization(cur)
conn.set_property(scope=fabric.SCOPE_LOCAL)
first_names = ["John", "Buffalo", "Michael", "Kate", "Deep", "Genesis"]
last_names = ["Doe", "Bill", "Jackson", "Bush", "Purple"]
list_emp_no = []
for count in range(10):
emp_no = pick_shard_key()
list_emp_no.append(emp_no)
add_employee(conn, emp_no,
first_names[emp_no % len(first_names)],
last_names[emp_no % len(last_names)],
gtid_executed
)
for emp_no in list_emp_no:
find_employee(conn, emp_no, gtid_executed)
conn.close()
我是MYSQL5.6搭建的，在下面的SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('%s', 0)"部分会一直等待
def synchronize(cur, gtid_executed):
# Guarantee that a slave has created the employees.employees table
# before reading anything.
cur.execute(
"SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('%s', 0)" %
(gtid_executed, )
)
cur.fetchall()
查看mysql show full processlist：
| 6967 | fabric | 192.168.10.232:60179 | mha | Query | 1032 | Waiting for the slave SQL thread to advance position | SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('5348580a-f629-11e5-92b4-0050569c4a6a:1-243,
88c90835-f644-11e5-9365-0050569c0ea8:1-5', 0) |
| 7007 | fabric | 192.168.10.232:60517 | mha | Query | 956 | Waiting for the slave SQL thread to advance position | SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('5348580a-f629-11e5-92b4-0050569c4a6a:1-247,
88c90835-f644-11e5-9365-0050569c0ea8:1-5', 0) |
| 7045 | fabric | 192.168.10.232:60849 | mha | Query | 885 | Waiting for the slave SQL thread to advance position | SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('5348580a-f629-11e5-92b4-0050569c4a6a:1-251,
88c90835-f644-11e5-9365-0050569c0ea8:1-5', 0) |
| 7106 | fabric | 192.168.10.232:33150 | mha | Query | 764 | Waiting for the slave SQL thread to advance position | SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('5348580a-f629-11e5-92b4-0050569c4a6a:1-255,
88c90835-f644-11e5-9365-0050569c0ea8:1-5', 0) |
| 7126 | fabric | 192.168.10.232:33329 | mha | Query | 728 | Waiting for the slave SQL thread to advance position | SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('5348580a-f629-11e5-92b4-0050569c4a6a:1-259,
88c90835-f644-11e5-9365-0050569c0ea8:1-5', 0) |
可以设置
def synchronize(cur, gtid_executed):
# Guarantee that a slave has created the employees.employees table
# before reading anything.
cur.execute(
"SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('%s', 5)" %
(gtid_executed, )
)
cur.fetchall()
十一、常用维护
删除表分区定义
[root@yw32 tmp]# mysqlfabric sharding remove_table employees.employees
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
a229fb3c-9632-4e24-9204-c823d2042885 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x11876d0>.
4 2 1.46e+09 Executing action (_remove_shard_mapping).
5 2 1.46e+09 Executed action (_remove_shard_mapping).
添加表分区定义
[root@yw32 tmp]# mysqlfabric sharding add_table 3 employees.employees emp_no
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
c34dabff-a2d9-4998-837e-1166a431bb18 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1187790>.
4 2 1.46e+09 Executing action (_add_shard_mapping).
5 2 1.46e+09 Executed action (_add_shard_mapping).
mysqlfabric sharding add_shard 3 "shard1/1, shard2/100000, shard2/200000" --state=ENABLED
一、环境说明
3个mysql HA组，1个mysql fabric node，1个state store数据库
my_group是全局Group：
MYSQL HA GROUP my_group：192.168.10.228:3306 master
192.168.10.229:3306 slave1
192.168.10.230:3306 slave2
MYSQL HA GROUP shard1： 192.168.10.228:3308 master
192.168.10.229:3308 slave1
192.168.10.230:3308 slave2
MYSQL HA GROUP shard2： 192.168.10.228:3309 master
192.168.10.229:3309 slave1
192.168.10.230:3309 slave2
MYSQL FABRIC NODE : 192.168.10.232
MYSQL FABRIC STORE : 192.168.10.232 3306 (MYSQL 数据库)
二、创建分组
[root@yw32 mysql]# mysqlfabric group create my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
de19dcbf-7cb3-4783-84f7-2f636bef4e15 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x20c2050>.
4 2 1.4595e+09 Executing action (_create_group).
5 2 1.4595e+09 Executed action (_create_group).
[root@yw32 ~]# mysqlfabric group create shard1
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
9aa28247-e6f2-420c-86ae-f7a2a787fdb3 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122a050>.
4 2 1.46e+09 Executing action (_create_group).
5 2 1.46e+09 Executed action (_create_group).
[root@yw32 ~]# mysqlfabric group create shard2
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
071704b8-3cb7-41b9-986f-a7e6d5176ab5 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122a050>.
4 2 1.46e+09 Executing action (_create_group).
5 2 1.46e+09 Executed action (_create_group).
三、添加成员到组
添加全局组成员：
往HA组添加成员 192.168.10.228:3306
[root@yw32 mysql]# mysqlfabric group add my_group 192.168.10.228:3306
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
551981f6-de1c-4660-aef6-cbc9980a9489 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x20c2410>.
4 2 1.4595e+09 Executing action (_add_server).
5 2 1.4595e+09 Executed action (_add_server).
往HA组添加成员 192.168.10.229:3306
[root@yw32 mysql]# mysqlfabric group add my_group 192.168.10.229:3306
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
8e09e2d8-b7e7-46f7-be1a-266613dbbd8d 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x20c2410>.
4 2 1.4595e+09 Executing action (_add_server).
5 2 1.4595e+09 Executed action (_add_server).
往HA组添加成员 192.168.10.230:3306
[root@yw32 mysql]# mysqlfabric group add my_group 192.168.10.230:3306
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
0dff18a2-d8ee-4b33-88a9-74c12e19ce9d 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x20c2410>.
4 2 1.4595e+09 Executing action (_add_server).
5 2 1.4595e+09 Executed action (_add_server).
添加shard1组成员
[root@yw32 ~]# mysqlfabric group add shard1 192.168.10.228:3308
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
69806eae-f460-44f8-8cf3-84b33f4ce528 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122a410>.
4 2 1.46e+09 Executing action (_add_server).
5 2 1.46e+09 Executed action (_add_server).
[root@yw32 ~]# mysqlfabric group add shard1 192.168.10.229:3308
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
d7ae7217-2ce2-4448-aabe-748d9de8e535 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122a410>.
4 2 1.46e+09 Executing action (_add_server).
5 2 1.46e+09 Executed action (_add_server).
[root@yw32 ~]# mysqlfabric group add shard1 192.168.10.230:3308
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
808603fa-36bc-4c57-b70a-27732e2ae38e 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122a410>.
4 2 1.46e+09 Executing action (_add_server).
5 2 1.46e+09 Executed action (_add_server).
添加shard2组员：
[root@yw32 ~]# mysqlfabric group add shard2 192.168.10.228:3309
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
742166f8-4480-43de-9c3d-1dd981286cec 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122a410>.
4 2 1.46e+09 Executing action (_add_server).
5 2 1.46e+09 Executed action (_add_server).
[root@yw32 ~]# mysqlfabric group add shard2 192.168.10.229:3309
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
2284522a-2dad-46fd-aef9-0243eac2492b 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122a410>.
4 2 1.46e+09 Executing action (_add_server).
5 2 1.46e+09 Executed action (_add_server).
[root@yw32 ~]# mysqlfabric group add shard2 192.168.10.230:3309
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
e9b4e895-4b40-4ac1-ad0c-d8227b96ba10 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122a410>.
4 2 1.46e+09 Executing action (_add_server).
5 2 1.46e+09 Executed action (_add_server).
四、每个组选举一个PRIMARY节点
[root@yw32 mysql]# mysqlfabric group promote my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
f3e50f87-2109-44af-9144-ceae99bcc9f1 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x1ed83d0>.
4 2 1.4595e+09 Executing action (_define_ha_operation).
5 2 1.4595e+09 Executed action (_define_ha_operation).
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x202cc90>.
4 2 1.4595e+09 Executing action (_find_candidate_fail).
5 2 1.4595e+09 Executed action (_find_candidate_fail).
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x202ccd0>.
4 2 1.4595e+09 Executing action (_check_candidate_fail).
5 2 1.4595e+09 Executed action (_check_candidate_fail).
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x202ca50>.
4 2 1.4595e+09 Executing action (_wait_slave_fail).
5 2 1.4595e+09 Executed action (_wait_slave_fail).
3 2 1.4595e+09 Triggered by <mysql.fabric.events.Event object at 0x2031a90>.
4 2 1.4595e+09 Executing action (_change_to_candidate).
5 2 1.4595e+09 Executed action (_change_to_candidate).
[root@yw32 ~]# mysqlfabric group promote shard1
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
99df0536-6776-4b4e-b782-0369ab0d236c 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x10403d0>.
4 2 1.46e+09 Executing action (_define_ha_operation).
5 2 1.46e+09 Executed action (_define_ha_operation).
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1194c90>.
4 2 1.46e+09 Executing action (_find_candidate_fail).
5 2 1.46e+09 Executed action (_find_candidate_fail).
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1194cd0>.
4 2 1.46e+09 Executing action (_check_candidate_fail).
5 2 1.46e+09 Executed action (_check_candidate_fail).
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1194a50>.
4 2 1.46e+09 Executing action (_wait_slave_fail).
5 2 1.46e+09 Executed action (_wait_slave_fail).
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1199a90>.
4 2 1.46e+09 Executing action (_change_to_candidate).
5 2 1.46e+09 Executed action (_change_to_candidate).
[root@yw32 ~]# mysqlfabric group promote shard2
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
4f37fd03-5731-4b41-ace7-310ca07087ba 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x10403d0>.
4 2 1.46e+09 Executing action (_define_ha_operation).
5 2 1.46e+09 Executed action (_define_ha_operation).
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1194c90>.
4 2 1.46e+09 Executing action (_find_candidate_fail).
5 2 1.46e+09 Executed action (_find_candidate_fail).
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1194cd0>.
4 2 1.46e+09 Executing action (_check_candidate_fail).
5 2 1.46e+09 Executed action (_check_candidate_fail).
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1194a50>.
4 2 1.46e+09 Executing action (_wait_slave_fail).
5 2 1.46e+09 Executed action (_wait_slave_fail).
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1199a90>.
4 2 1.46e+09 Executing action (_change_to_candidate).
5 2 1.46e+09 Executed action (_change_to_candidate).
五、查看各个组情况
[root@yw32 ~]# mysqlfabric group lookup_servers my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- ---------- ------
4c622c12-ebdd-11e5-8f8f-0050569c155b 192.168.10.229:3306 SECONDARY READ_ONLY 1.0
5348580a-f629-11e5-92b4-0050569c4a6a 192.168.10.228:3306 PRIMARY READ_WRITE 1.0
88c90835-f644-11e5-9365-0050569c0ea8 192.168.10.230:3306 SECONDARY READ_ONLY 1.0
[root@yw32 ~]# mysqlfabric group lookup_servers shard1
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- ---------- ------
4c622c13-ebdd-11e5-8f8f-0050569c155b 192.168.10.229:3308 SECONDARY READ_ONLY 1.0
53485802-f629-11e5-92b4-0050569c4a6a 192.168.10.228:3308 SECONDARY READ_ONLY 1.0
88c90836-f644-11e5-9365-0050569c0ea8 192.168.10.230:3308 PRIMARY READ_WRITE 1.0
[root@yw32 ~]# mysqlfabric group lookup_servers shard2
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- ---------- ------
4c622c14-ebdd-11e5-8f8f-0050569c155b 192.168.10.229:3309 SECONDARY READ_ONLY 1.0
53485803-f629-11e5-92b4-0050569c4a6a 192.168.10.228:3309 SECONDARY READ_ONLY 1.0
88c90837-f644-11e5-9365-0050569c0ea8 192.168.10.230:3309 PRIMARY READ_WRITE 1.0
六、定义分片策略
定义两个分片策略：
mysqlfabric sharding create_definition RANGE my_group
mysqlfabric sharding create_definition HASH my_group
[root@yw32 ~]# mysqlfabric sharding create_definition RANGE my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
e8aa6fd6-0fa4-479a-bcff-c446c11c31f1 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1187810>.
4 2 1.46e+09 Executing action (_define_shard_mapping).
5 2 1.46e+09 Executed action (_define_shard_mapping).
[root@yw32 ~]# mysqlfabric sharding create_definition HASH my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
346bdbcc-57a0-42bf-8759-672b369724da 1 1 2
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1187810>.
4 2 1.46e+09 Executing action (_define_shard_mapping).
5 2 1.46e+09 Executed action (_define_shard_mapping).
查看定义了哪些分片策略
[root@yw32 ~]# mysqlfabric sharding list_definitions
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
mapping_id type_name global_group_id
---------- --------- ---------------
1 RANGE my_group
2 HASH my_group
七、添加分片表和字段
定义两个分区表，一个用range策略，一个用hash策略：
mysqlfabric sharding add_table 1 mha.orders orderno
mysqlfabric sharding add_table 2 mha.users userno
[root@yw32 ~]# mysqlfabric sharding add_table 1 mha.orders orderno
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
3241e592-b71c-4bc9-8ddc-6504584509bd 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1187790>.
4 2 1.46e+09 Executing action (_add_shard_mapping).
5 2 1.46e+09 Executed action (_add_shard_mapping).
[root@yw32 ~]# mysqlfabric sharding add_table 2 mha.users userno
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
f9674616-1a10-4cea-b909-d4fb80f4272f 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1187790>.
4 2 1.46e+09 Executing action (_add_shard_mapping).
5 2 1.46e+09 Executed action (_add_shard_mapping).
查看表的分区情况
[root@yw32 ~]# mysqlfabric sharding lookup_table mha.orders;
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
mapping_id type_name table_name global_group column_name
---------- --------- ---------- ------------ -----------
1 RANGE mha.orders my_group orderno
八、设置表的具体分区办法
mysqlfabric sharding add_shard 1 "shard1/1, shard2/10" --state=ENABLED
这样orderno在1-10的放在shard1,10以上的放在shard2
mysqlfabric sharding add_shard 2 "shard1,shard2" --state=enabled
[root@yw32 ~]# mysqlfabric sharding add_shard 1 "shard1/1, shard2/10" --state=ENABLED
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
23eafea8-dece-4cd0-b3aa-a0bf21b283d4 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122f8d0>.
4 2 1.46e+09 Executing action (_add_shard).
5 2 1.46e+09 Executed action (_add_shard).
[root@yw32 ~]# mysqlfabric sharding add_shard 2 "shard1,shard2" --state=enabled
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
db7d7c24-7b07-4399-b7ae-a4f199f51936 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x122f8d0>.
4 2 1.46e+09 Executing action (_add_shard).
5 2 1.46e+09 Executed action (_add_shard).
验证分区规则是否正确
[root@yw32 tmp]# mysqlfabric sharding lookup_servers mha.orders 30
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- ---------- ------
4c622c14-ebdd-11e5-8f8f-0050569c155b 192.168.10.229:3309 SECONDARY READ_ONLY 1.0
53485803-f629-11e5-92b4-0050569c4a6a 192.168.10.228:3309 SECONDARY READ_ONLY 1.0
88c90837-f644-11e5-9365-0050569c0ea8 192.168.10.230:3309 PRIMARY READ_WRITE 1.0
[root@yw32 tmp]# mysqlfabric sharding lookup_servers mha.orders 3
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- ---------- ------
4c622c13-ebdd-11e5-8f8f-0050569c155b 192.168.10.229:3308 SECONDARY READ_ONLY 1.0
53485802-f629-11e5-92b4-0050569c4a6a 192.168.10.228:3308 SECONDARY READ_ONLY 1.0
88c90836-f644-11e5-9365-0050569c0ea8 192.168.10.230:3308 PRIMARY READ_WRITE 1.0
九、在全局组创建测试表
mysql -u root -p123456 --socket=/var/lib/mysql/mysql.sock
use mha
create table orders(
id int primary key auto_increment,
orderno int not null,
product_name varchar(50) not null
);
create table users(
id int primary key auto_increment,
userno int not null,
user_name varchar(50) not null
);
十、插入测试数据
import random
import mysql.connector
from mysql.connector import fabric
def prepare_synchronization(cur):
# We need to keep track of what we have executed so far to guarantee
# that the employees.employees table exists at all shards.
gtid_executed = None
cur.execute("SELECT @@global.gtid_executed")
for row in cur:
gtid_executed = row[0]
return gtid_executed
def synchronize(cur, gtid_executed):
# Guarantee that a slave has created the employees.employees table
# before reading anything.
cur.execute(
"SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('%s', 0)" %
(gtid_executed, )
)
cur.fetchall()
def add_employee(conn, emp_no, first_name, last_name, gtid_executed):
conn.set_property(tables=["employees.employees"], key=emp_no,
mode=fabric.MODE_READWRITE)
cur = conn.cursor()
synchronize(cur, gtid_executed)
cur.execute("USE employees")
cur.execute(
"INSERT INTO employees VALUES (%s, %s, %s)",
(emp_no, first_name, last_name)
)
def find_employee(conn, emp_no, gtid_executed):
conn.set_property(tables=["employees.employees"], key=emp_no,
mode=fabric.MODE_READONLY)
cur = conn.cursor()
synchronize(cur, gtid_executed)
cur.execute("USE employees")
for row in cur:
print "Had to synchronize", row, "transactions."
cur.execute(
"SELECT first_name, last_name FROM employees "
"WHERE emp_no = %s", (emp_no, )
)
for row in cur:
print row
def pick_shard_key():
shard = random.randint(0, 2)
shard_range = shard * 100000
shard_range = shard_range if shard != 0 else shard_range + 1
shift_within_shard = random.randint(0, 99999)
return shard_range + shift_within_shard
# Address of the Fabric, not the host we are going to connect to.
conn = mysql.connector.connect(
fabric ={"host":"localhost","port":32274,"username":"admin","password":"admin"},
user="fabric",database="mha",password="123456",autocommit=True
)
conn.set_property(tables=["employees.employees"], scope=fabric.SCOPE_LOCAL,
mode=fabric.MODE_READWRITE)
cur = conn.cursor()
cur.execute("CREATE DATABASE IF NOT EXISTS employees")
cur.execute("USE employees")
cur.execute("DROP TABLE IF EXISTS employees")
cur.execute(
"CREATE TABLE employees ("
" emp_no INT, "
" first_name CHAR(40), "
" last_name CHAR(40)"
")"
)
gtid_executed = prepare_synchronization(cur)
conn.set_property(scope=fabric.SCOPE_LOCAL)
first_names = ["John", "Buffalo", "Michael", "Kate", "Deep", "Genesis"]
last_names = ["Doe", "Bill", "Jackson", "Bush", "Purple"]
list_emp_no = []
for count in range(10):
emp_no = pick_shard_key()
list_emp_no.append(emp_no)
add_employee(conn, emp_no,
first_names[emp_no % len(first_names)],
last_names[emp_no % len(last_names)],
gtid_executed
)
for emp_no in list_emp_no:
find_employee(conn, emp_no, gtid_executed)
conn.close()
我是MYSQL5.6搭建的，在下面的SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('%s', 0)"部分会一直等待
def synchronize(cur, gtid_executed):
# Guarantee that a slave has created the employees.employees table
# before reading anything.
cur.execute(
"SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('%s', 0)" %
(gtid_executed, )
)
cur.fetchall()
查看mysql show full processlist：
| 6967 | fabric | 192.168.10.232:60179 | mha | Query | 1032 | Waiting for the slave SQL thread to advance position | SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('5348580a-f629-11e5-92b4-0050569c4a6a:1-243,
88c90835-f644-11e5-9365-0050569c0ea8:1-5', 0) |
| 7007 | fabric | 192.168.10.232:60517 | mha | Query | 956 | Waiting for the slave SQL thread to advance position | SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('5348580a-f629-11e5-92b4-0050569c4a6a:1-247,
88c90835-f644-11e5-9365-0050569c0ea8:1-5', 0) |
| 7045 | fabric | 192.168.10.232:60849 | mha | Query | 885 | Waiting for the slave SQL thread to advance position | SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('5348580a-f629-11e5-92b4-0050569c4a6a:1-251,
88c90835-f644-11e5-9365-0050569c0ea8:1-5', 0) |
| 7106 | fabric | 192.168.10.232:33150 | mha | Query | 764 | Waiting for the slave SQL thread to advance position | SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('5348580a-f629-11e5-92b4-0050569c4a6a:1-255,
88c90835-f644-11e5-9365-0050569c0ea8:1-5', 0) |
| 7126 | fabric | 192.168.10.232:33329 | mha | Query | 728 | Waiting for the slave SQL thread to advance position | SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('5348580a-f629-11e5-92b4-0050569c4a6a:1-259,
88c90835-f644-11e5-9365-0050569c0ea8:1-5', 0) |
可以设置
def synchronize(cur, gtid_executed):
# Guarantee that a slave has created the employees.employees table
# before reading anything.
cur.execute(
"SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('%s', 5)" %
(gtid_executed, )
)
cur.fetchall()
十一、常用维护
删除表分区定义
[root@yw32 tmp]# mysqlfabric sharding remove_table employees.employees
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
a229fb3c-9632-4e24-9204-c823d2042885 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x11876d0>.
4 2 1.46e+09 Executing action (_remove_shard_mapping).
5 2 1.46e+09 Executed action (_remove_shard_mapping).
添加表分区定义
[root@yw32 tmp]# mysqlfabric sharding add_table 3 employees.employees emp_no
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
c34dabff-a2d9-4998-837e-1166a431bb18 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1.46e+09 Triggered by <mysql.fabric.events.Event object at 0x1187790>.
4 2 1.46e+09 Executing action (_add_shard_mapping).
5 2 1.46e+09 Executed action (_add_shard_mapping).
mysqlfabric sharding add_shard 3 "shard1/1, shard2/100000, shard2/200000" --state=ENABLED
254
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
