MySQL Fabricにて”Global” という名前でグループを作成し,その中にサーバを三台登録してあります。
本日は、Shardingの作成とShardingされたTableとグループ全体に対してDDLを実行しました。
【目的】 ShardingされたMySQL Fabric GroupへのCRATE,ALTERなどのDDLの実行と適用範囲の確認
■ Sharding作成前の状況
-bash-4.2$ mysqlfabric group lookup_servers global
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ --------------- --------- ---------- ------
fd8f10c5-c3c1-11e4-84b2-080027d65c57 127.0.0.1:63301 PRIMARY READ_WRITE 1.0
fe41edb4-c3c1-11e4-84b2-080027d65c57 127.0.0.1:63302 SECONDARY READ_ONLY 1.0
fed03648-c3c1-11e4-84b2-080027d65c57 127.0.0.1:63303 SECONDARY READ_ONLY 1.0
-bash-4.2$
■ Sharding Mapping定義作成
-bash-4.2$ mysqlfabric help sharding create_definition
sharding create_definition type_name group_id [–synchronous]
Define a shard mapping.
-bash-4.2$ mysqlfabric sharding create_definition RANGE global
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
1e42e197-7b22-45a5-ba65-7432572e47ee 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1425622316.87 Triggered by <mysql.fabric.events.Event object at 0x12fa310>.
4 2 1425622316.94 Executing action (_define_shard_mapping).
5 2 1425622316.95 Executed action (_define_shard_mapping).
-bash-4.2$
-bash-4.2$ mysqlfabric sharding list_definitions
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
mapping_id type_name global_group_id
---------- --------- ---------------
1 RANGE global
-bash-4.2$
■Sharding用にグループ作成とサーバ追加
-bash-4.2$ mysqlfabric group create shard1
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
fe58c7b1-d881-49f1-be60-c6c456d17926 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1425622571.12 Triggered by <mysql.fabric.events.Event object at 0x1b6ecd0>.
4 2 1425622571.13 Executing action (_create_group).
5 2 1425622571.14 Executed action (_create_group).
-bash-4.2$ mysqlfabric group add shard1 127.0.0.1:63304
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
0b058a45-a48b-4483-b405-0b8a645ba024 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1425622571.32 Triggered by <mysql.fabric.events.Event object at 0x1bc30d0>.
4 2 1425622571.32 Executing action (_add_server).
5 2 1425622571.33 Executed action (_add_server).
-bash-4.2$ mysqlfabric group add shard1 127.0.0.1:63305
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
02446f80-1c38-41fb-a7bc-88f3593ee10a 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1425622571.51 Triggered by <mysql.fabric.events.Event object at 0x1bc30d0>.
4 2 1425622571.51 Executing action (_add_server).
5 2 1425622571.52 Executed action (_add_server).
-bash-4.2$ mysqlfabric group promote shard1
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
34ef4ed7-db5d-4b8d-a4a9-c86784181cd0 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1425622571.66 Triggered by <mysql.fabric.events.Event object at 0x12fa410>.
4 2 1425622571.66 Executing action (_define_ha_operation).
5 2 1425622571.66 Executed action (_define_ha_operation).
3 2 1425622571.66 Triggered by <mysql.fabric.events.Event object at 0x12fa3d0>.
4 2 1425622571.66 Executing action (_find_candidate_fail).
5 2 1425622571.68 Executed action (_find_candidate_fail).
3 2 1425622571.68 Triggered by <mysql.fabric.events.Event object at 0x1b5a2d0>.
4 2 1425622571.68 Executing action (_check_candidate_fail).
5 2 1425622571.69 Executed action (_check_candidate_fail).
3 2 1425622571.68 Triggered by <mysql.fabric.events.Event object at 0x1b5a350>.
4 2 1425622571.69 Executing action (_wait_slave_fail).
5 2 1425622571.71 Executed action (_wait_slave_fail).
3 2 1425622571.71 Triggered by <mysql.fabric.events.Event object at 0x1b5a490>.
4 2 1425622571.71 Executing action (_change_to_candidate).
5 2 1425622571.79 Executed action (_change_to_candidate).
-bash-4.2$ mysqlfabric group activate shard1
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
77bc7aa8-8068-4d1d-a2da-4e2a5bd5da87 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1425622571.95 Triggered by <mysql.fabric.events.Event object at 0x1bc3290>.
4 2 1425622571.95 Executing action (_activate_group).
5 2 1425622571.96 Executed action (_activate_group).
-bash-4.2$
■以下のグループとサーバも上記と同じくShardingの為にGroupとして追加
mysqlfabric group create shard2
mysqlfabric group add shard2 127.0.0.1:63306
mysqlfabric group add shard2 127.0.0.1:63307
mysqlfabric group promote shard2
mysqlfabric group activate shard2
mysqlfabric group create shard3
mysqlfabric group add shard3 127.0.0.1:63308
mysqlfabric group add shard3 127.0.0.1:63309
mysqlfabric group promote shard3
mysqlfabric group activate shard3
■ Add sharding to employee
Mapping IDは以下のコマンドで確認すると1になっているので1でシャーディングを作成する。
-bash-4.2$ mysqlfabric sharding list_definitions
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
mapping_id type_name global_group_id
---------- --------- ---------------
1 RANGE global
-bash-4.2$ mysqlfabric sharding add_table 1 test.employees emp_no
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
115ea959-bb38-4a2b-ab04-a59dad52da0e 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1425623161.61 Triggered by <mysql.fabric.events.Event object at 0x12fa450>.
4 2 1425623161.61 Executing action (_add_shard_mapping).
5 2 1425623161.69 Executed action (_add_shard_mapping).
-bash-4.2$ mysqlfabric sharding add_shard 1 shard1/1,shard2/300,shard3/600 --state=enabled
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
794b2bb8-83a5-48c5-9a61-88c30b8d72a8 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1425623182.84 Triggered by <mysql.fabric.events.Event object at 0x1bc9410>.
4 2 1425623182.84 Executing action (_add_shard).
5 2 1425623183.45 Executed action (_add_shard).
-bash-4.2$
-bash-4.2$ mysqlfabric dump sharding_information
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
schema_name table_name column_name lower_bound shard_id type_name group_id global_group
----------- ---------- ----------- ----------- -------- --------- -------- ------------
test employees emp_no 1 1 RANGE shard1 global
test employees emp_no 300 2 RANGE shard2 global
test employees emp_no 600 3 RANGE shard3 global
-bash-4.2$
■テーブルが全てのサーバに作成されている。
グループ全体 ”Global” を指定して、CREATE TABLEを実行しています。
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63301 -e "show tables from test"
Warning: Using a password on the command line interface can be insecure.
-bash-4.2$ python ./alter_shared_create.py
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63301 -e "show tables from test"
Warning: Using a password on the command line interface can be insecure.
+----------------+
| Tables_in_test |
+----------------+
| employees |
+----------------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63301 -e "desc test.employees"
Warning: Using a password on the command line interface can be insecure.
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| first_name | char(40) | YES | | NULL | |
| last_name | char(40) | YES | | NULL | |
+------------+----------+------+-----+---------+-------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63304 -e "show tables from test"
Warning: Using a password on the command line interface can be insecure.
+----------------+
| Tables_in_test |
+----------------+
| employees |
+----------------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63304 -e "desc test.employees"
Warning: Using a password on the command line interface can be insecure.
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| first_name | char(40) | YES | | NULL | |
| last_name | char(40) | YES | | NULL | |
+------------+----------+------+-----+---------+-------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63306 -e "show tables from test"
Warning: Using a password on the command line interface can be insecure.
+----------------+
| Tables_in_test |
+----------------+
| employees |
+----------------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63306 -e "desc test.employees"
Warning: Using a password on the command line interface can be insecure.
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| first_name | char(40) | YES | | NULL | |
| last_name | char(40) | YES | | NULL | |
+------------+----------+------+-----+---------+-------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63308 -e "show tables from test"
Warning: Using a password on the command line interface can be insecure.
+----------------+
| Tables_in_test |
+----------------+
| employees |
+----------------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63308 -e "desc test.employees"
Warning: Using a password on the command line interface can be insecure.
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| first_name | char(40) | YES | | NULL | |
| last_name | char(40) | YES | | NULL | |
+------------+----------+------+-----+---------+-------+
-bash-4.2$
上記で実行したスクリプト
import mysql.connector
from mysql.connector import fabric
import time
def connect():
try:
conn=mysql.connector.connect(
fabric={"host" : "localhost", "port" : 32274, "username": "admin", "password": "admin"},
user="root", database="test", password="root",
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()
conn.set_property(group='global')
cur = conn.cursor()
cur.execute(
"CREATE TABLE employees ("
" emp_no INT PRIMARY KEY, "
" first_name CHAR(40), "
" last_name CHAR(40)"
")"
)
conn.commit()
conn.close()
conn = connect()
■テーブル定義の変更
上記で作成したテーブルに対して、Alter Tableで列を追加する。
-bash-4.2$ python ./alter_shared.py
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63301 -e "desc test.employees"
Warning: Using a password on the command line interface can be insecure.
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| first_name | char(40) | YES | | NULL | |
| last_name | char(40) | YES | | NULL | |
| nickname | varchar(64) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63304 -e "desc test.employees"
Warning: Using a password on the command line interface can be insecure.
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| first_name | char(40) | YES | | NULL | |
| last_name | char(40) | YES | | NULL | |
| nickname | varchar(64) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63306 -e "desc test.employees"
Warning: Using a password on the command line interface can be insecure.
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| first_name | char(40) | YES | | NULL | |
| last_name | char(40) | YES | | NULL | |
| nickname | varchar(64) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63308 -e "desc test.employees"
Warning: Using a password on the command line interface can be insecure.
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| first_name | char(40) | YES | | NULL | |
| last_name | char(40) | YES | | NULL | |
| nickname | varchar(64) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
-bash-4.2$
上記で実行したスクリプト
import mysql.connector
from mysql.connector import fabric
import time
def connect():
try:
conn=mysql.connector.connect(
fabric={"host" : "localhost", "port" : 32274, "username": "admin", "password": "admin"},
user="root", database="test", password="root",
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()
conn.set_property(group='global')
cur = conn.cursor()
cur.execute("ALTER TABLE test.employees ADD nickname VARCHAR(64)")
conn.commit()
conn.close()
conn = connect()
こちらは、グローバルグループに複数のSharding定義を追加した場合（例）
グループにテーブル定義を追加してあげると、複数の定義を追加出来ます。
-bash-4.2$ mysqlfabric sharding create_definition RANGE global
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
0d675538-8b4a-4c57-b4a8-a29b529f0c7c 1 1 2
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1425626498.5 Triggered by <mysql.fabric.events.Event object at 0x12fa310>.
4 2 1425626498.5 Executing action (_define_shard_mapping).
5 2 1425626498.59 Executed action (_define_shard_mapping).
-bash-4.2$ mysqlfabric sharding list_definitions
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
mapping_id type_name global_group_id
---------- --------- ---------------
1 RANGE global
2 RANGE global
-bash-4.2$ mysqlfabric sharding create_definition RANGE global
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
5a3500cf-df2f-4219-a96b-9c9af166f33c 1 1 3
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1425626647.47 Triggered by <mysql.fabric.events.Event object at 0x12fa310>.
4 2 1425626647.47 Executing action (_define_shard_mapping).
5 2 1425626647.47 Executed action (_define_shard_mapping).
-bash-4.2$ mysqlfabric sharding list_definitions
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
mapping_id type_name global_group_id
---------- --------- ---------------
1 RANGE global
2 RANGE global
3 RANGE global
-bash-4.2$
-bash-4.2$ mysqlfabric sharding add_table 2 test.employees2 emp_no
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
aadb02ef-c743-4839-b567-045723f7c3e8 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1425626950.87 Triggered by <mysql.fabric.events.Event object at 0x12fa450>.
4 2 1425626950.87 Executing action (_add_shard_mapping).
5 2 1425626950.88 Executed action (_add_shard_mapping).
-bash-4.2$ mysqlfabric sharding add_shard 2 shard1/1,shard2/300,shard3/600 --state=enabled
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
32dd4f37-725c-4717-b6eb-073a26b814b7 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1425626974.93 Triggered by <mysql.fabric.events.Event object at 0x1bc9410>.
4 2 1425626974.94 Executing action (_add_shard).
5 2 1425626975.45 Executed action (_add_shard).
-bash-4.2$ mysqlfabric sharding add_table 3 test.employees3 emp_no
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
ae7c4c6c-342c-4d8e-b1c4-8ce534a91ffd 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1425627239.67 Triggered by <mysql.fabric.events.Event object at 0x12fa450>.
4 2 1425627239.72 Executing action (_add_shard_mapping).
5 2 1425627239.73 Executed action (_add_shard_mapping).
-bash-4.2$ mysqlfabric sharding add_shard 3 shard1/1,shard2/300,shard3/600 --state=enabled
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid finished success result
------------------------------------ -------- ------- ------
0ccb5df2-216c-4076-9966-708cd189b95a 1 1 1
state success when description
----- ------- ------------- -------------------------------------------------------------
3 2 1425627253.49 Triggered by <mysql.fabric.events.Event object at 0x1bc9410>.
4 2 1425627253.49 Executing action (_add_shard).
5 2 1425627253.94 Executed action (_add_shard).
-bash-4.2$ mysqlfabric dump sharding_information
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
schema_name table_name column_name lower_bound shard_id type_name group_id global_group
----------- ---------- ----------- ----------- -------- --------- -------- ------------
test employees emp_no 1 1 RANGE shard1 global
test employees emp_no 300 2 RANGE shard2 global
test employees emp_no 600 3 RANGE shard3 global
test employees2 emp_no 1 4 RANGE shard1 global
test employees2 emp_no 300 5 RANGE shard2 global
test employees2 emp_no 600 6 RANGE shard3 global
test employees3 emp_no 1 7 RANGE shard1 global
test employees3 emp_no 300 8 RANGE shard2 global
test employees3 emp_no 600 9 RANGE shard3 global
-bash-4.2$
参照： 8.8.4. MySQL Fabric Configuration for Running Samples
![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
