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-table0

■テーブル定義の変更
上記で作成したテーブルに対して、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()

alter-table

こちらは、グローバルグループに複数のSharding定義を追加した場合(例)
グループにテーブル定義を追加してあげると、複数の定義を追加出来ます。

shardbefore

-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$ 

shardadd

参照: 8.8.4. MySQL Fabric Configuration for Running Samples


mysqlfabric group add実行時の新規追加サーバーのデータ同期について。

1)マスターDBに必要なバイナリーログがある場合は、新規Slaveがログを読み込んでデータ同期。
2) マスターDBに必要なバイナリーログが無い場合は、新規Slaveにデータをリストアしてからmysqlfabric group addコマンドを実行。

■ mysqlfabric group addコマンドについての前回のブログ
mysqlfabricにてSlaveを追加

【通常時Fabric管理ノードが受け取っている処理】

+---------------------+----------------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2015-02-26 10:35:43 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT group_id, description, master_uuid, master_defined, status FROM groups WHERE group_id = 'global'                                                                                                                                                                                                                                                                                   |
| 2015-02-26 10:35:43 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT server_uuid, server_address, mode, status, weight, group_id FROM servers WHERE group_id = 'global'                                                                                                                                                                                                                                                                                 |
| 2015-02-26 10:35:45 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT group_id, description, master_uuid, master_defined, status FROM groups WHERE group_id = 'global'                                                                                                                                                                                                                                                                                   |
| 2015-02-26 10:35:45 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT server_uuid, server_address, mode, status, weight, group_id FROM servers WHERE group_id = 'global'                                                                                                                                                                                                                                                                                 |
| 2015-02-26 10:35:47 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT group_id, description, master_uuid, master_defined, status FROM groups WHERE group_id = 'global'                                                                                                                                                                                                                                                                                   |
| 2015-02-26 10:35:47 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT server_uuid, server_address, mode, status, weight, group_id FROM servers WHERE group_id = 'global'                                                                                                                                                                                                                                                                                 |
| 2015-02-26 10:35:49 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT group_id, description, master_uuid, master_defined, status FROM groups WHERE group_id = 'global'                                                                                                                                                                                                                                                                                   |
| 2015-02-26 10:35:49 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT server_uuid, server_address, mode, status, weight, group_id FROM servers WHERE group_id = 'global'                                                                                                                                                                                                                                                                                 |
+---------------------+----------------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

グループにサーバーを追加した時のバックグラウンド処理の確認。

-bash-4.2$ mysqlfabric group add global 127.0.0.1:63307
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
b63f21d7-4b17-4602-a110-a6ad394c6438        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1424914567.27 Triggered by <mysql.fabric.events.Event object at 0x27465d0>.
    4       2 1424914567.33                               Executing action (_add_server).
    5       2 1424914567.46                                Executed action (_add_server).


-bash-4.2$

-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
------------------------------------ --------------- --------- ---------- ------
4790c170-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
483d8d5a-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
48d8c44f-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
4958fc6d-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0
49f47cf1-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
4a900296-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0
4b2ed657-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63307 SECONDARY  READ_ONLY    1.0


-bash-4.2$ 

【Slaveを追加した時に流れるQuery】 Fabric管理ノード(データストアDB)

+---------------------+----------------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        15 |         1 | Query        | SELECT username, protocol, password FROM users WHERE username = 'admin' AND protocol = 'xmlrpc'                                                                                                                                                                                                                                                                                           |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        15 |         1 | Query        | SELECT p.subsystem, p.component, p.function FROM users AS u LEFT JOIN user_roles AS ur USING (user_id) LEFT JOIN role_permissions AS rp USING (role_id) LEFT JOIN permissions AS p USING (permission_id) WHERE u.username = 'admin' AND protocol = 'xmlrpc'                                                                                                                               |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        15 |         1 | Query        | INSERT INTO log (subject, reported, reporter, message, category, type) VALUES('group.add', '2015-02-26 01:36:07', 'mysql.fabric.command', 'Started command (group, add).', 1, 0)                                                                                                                                                                                                          |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        15 |         1 | Query        | BEGIN                                                                                                                                                                                                                                                                                                                                                                                     |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        15 |         1 | Query        | INSERT INTO checkpoints(proc_uuid, lockable_objects, job_uuid, sequence, action_fqn, param_args, param_kwargs) VALUES ('b63f21d7-4b17-4602-a110-a6ad394c6438', 'c__builtin__\nset\np0\n((lp1\nS\'lock\'\np2\natp3\nRp4\n.', '12fe6274-f21b-4706-83c6-9ad2c763cbb9', 0, 'mysql.fabric.services.server._add_server', '(S\'global\'\np0\nS\'127.0.0.1:63307\'\np1\nNI00\ntp2\n.', '(dp0\n.') |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        15 |         1 | Query        | COMMIT                                                                                                                                                                                                                                                                                                                                                                                    |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | UPDATE checkpoints set started = 1424914567.333504 WHERE proc_uuid = 'b63f21d7-4b17-4602-a110-a6ad394c6438' and job_uuid = '12fe6274-f21b-4706-83c6-9ad2c763cbb9'                                                                                                                                                                                                                         |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | BEGIN                                                                                                                                                                                                                                                                                                                                                                                     |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | SELECT group_id, description, master_uuid, master_defined, status FROM groups WHERE group_id = 'global'                                                                                                                                                                                                                                                                                   |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | SELECT server_uuid, server_address, mode, status, weight, group_id FROM servers WHERE server_uuid = '4b2ed657-bd51-11e4-9ab3-08002766cefe'                                                                                                                                                                                                                                                |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | SELECT @@GLOBAL.SERVER_UUID                                                                                                                                                                                                                                                                                                                                                               |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | INSERT INTO servers(server_uuid, server_address, mode, status, weight, group_id) values('4b2ed657-bd51-11e4-9ab3-08002766cefe', '127.0.0.1:63307', 1, 2, 1.0, NULL)                                                                                                                                                                                                                       |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | UPDATE servers SET group_id = 'global' WHERE server_uuid = '4b2ed657-bd51-11e4-9ab3-08002766cefe'                                                                                                                                                                                                                                                                                         |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | SELECT server_uuid, server_address, mode, status, weight, group_id FROM servers WHERE server_uuid = '4790c170-bd51-11e4-9ab3-08002766cefe'                                                                                                                                                                                                                                                |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | UPDATE checkpoints set finished = 1424914567.455931 WHERE proc_uuid = 'b63f21d7-4b17-4602-a110-a6ad394c6438' and job_uuid = '12fe6274-f21b-4706-83c6-9ad2c763cbb9'                                                                                                                                                                                                                        |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | COMMIT                                                                                                                                                                                                                                                                                                                                                                                    |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | DELETE FROM checkpoints WHERE proc_uuid = 'b63f21d7-4b17-4602-a110-a6ad394c6438'                                                                                                                                                                                                                                                                                                          |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        15 |         1 | Query        | INSERT INTO log (subject, reported, reporter, message, category, type) VALUES('group.add', '2015-02-26 01:36:07', 'mysql.fabric.command', 'Finished command (group, add).', 1, 1)                                                                                                                                                                                                         |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT group_id, description, master_uuid, master_defined, status FROM groups WHERE group_id = 'global'                                                                                                                                                                                                                                                                                   |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT server_uuid, server_address, mode, status, weight, group_id FROM servers WHERE group_id = 'global'                                                                                                                                                                                                                                                                                 |
| 2015-02-26 10:36:08 | root[root] @ localhost [127.0.0.1]     |        17 |         1 | Query        | select * from general_log                                                                                                                                                                                                                                                                                                                                                                 |
+---------------------+----------------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

【追加したSlave側のログ】

 
-----------------------------------------------------------------------------------------------------------------
-bash-4.2$ cat fabric_add_slave.sql | more
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Query   SELECT DATABASE()
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Init DB mysql
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Query   show databases
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Query   show tables
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      columns_priv 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      db 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      event 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      func 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      general_log 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      help_category 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      help_keyword 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      help_relation 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      help_topic 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      innodb_index_stats 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      innodb_table_stats 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      ndb_binlog_index 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      plugin 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      proc 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      procs_priv 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      proxies_priv 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      servers 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      slave_master_info 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      slave_relay_log_info 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      slave_worker_info 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      slow_log 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      tables_priv 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      time_zone 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      time_zone_leap_second 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      time_zone_name 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      time_zone_transition 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      time_zone_transition_type 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      user 
2015-02-26 10:48:58     root[root] @ localhost [127.0.0.1]      3       8       Query   show databases
2015-02-26 10:49:21     root[root] @ localhost [127.0.0.1]      3       8       Query   select @@hostname,@@port
2015-02-26 10:49:24     [root] @ localhost [127.0.0.1]  4       8       Connect root@localhost on 
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      4       8       Query   SET NAMES 'utf8' COLLATE 'utf8_general_ci'
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      4       8       Query   SET @@session.autocommit = ON
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      4       8       Query   SELECT @@GLOBAL.SERVER_UUID
2015-02-26 10:49:24     [root] @ localhost [127.0.0.1]  5       8       Connect root@localhost on 
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SET NAMES 'utf8' COLLATE 'utf8_general_ci'
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SET @@session.autocommit = ON
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SELECT @@GLOBAL.SERVER_UUID as SERVER_UUID
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SELECT @@GLOBAL.SERVER_ID as SERVER_ID
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SELECT @@GLOBAL.VERSION as VERSION
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SELECT @@GLOBAL.GTID_MODE as GTID_MODE
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SELECT @@GLOBAL.LOG_BIN as LOG_BIN
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      4       8       Quit
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SELECT @@GLOBAL.READ_ONLY as READ_ONLY
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SHOW GRANTS
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   STOP SLAVE
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SHOW SLAVE STATUS
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SHOW SLAVE STATUS
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   CHANGE MASTER TO MASTER_HOST = '127.0.0.1' MASTER_USER = 'root' MASTER_PASSWORD = <secret> MASTER_PORT = 63301
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SET @@GLOBAL.READ_ONLY = ON
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SELECT @@GLOBAL.READ_ONLY as READ_ONLY
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   START SLAVE
2015-02-26 10:49:24     [] @  []        6       8       Connect Out     root@127.0.0.1:63301
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SHOW SLAVE STATUS
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SHOW SLAVE STATUS
2015-02-26 10:49:24     [] @  []        7       8       Query   SET PASSWORD FOR 'root'@'localhost'='*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B'
2015-02-26 10:49:24     [] @  []        7       8       Query   GRANT SELECT, INSERT, DELETE, CREATE, DROP ON `test`.* TO 'app'@'localhost' IDENTIFIED BY PASSWORD '*5BCB3E6AC345B435C7C2E6B7949A04CE6F6563D3'
2015-02-26 10:49:24     [] @  []        7       8       Query   DROP TABLE IF EXISTS `employees` /* generated by server */
2015-02-26 10:49:24     [] @  []        7       8       Query   CREATE TABLE employees (   emp_no INT,    first_name CHAR(40),    last_name CHAR(40))
2015-02-26 10:49:24     [] @  []        7       8       Query   BEGIN
2015-02-26 10:49:24     [] @  []        7       8       Query   INSERT INTO employees VALUES (0, 'MySQL Fabric:0', 'Scale Out and HA Demo')
2015-02-26 10:49:24     [] @  []        7       8       Query   COMMIT /* implicit, from Xid_log_event */
2015-02-26 10:49:24     [] @  []        7       8       Query   BEGIN
2015-02-26 10:49:24     [] @  []        7       8       Query   INSERT INTO employees VALUES (1, 'MySQL Fabric:1', 'Scale Out and HA Demo')
2015-02-26 10:49:24     [] @  []        7       8       Query   COMMIT /* implicit, from Xid_log_event */
2015-02-26 10:49:24     [] @  []        7       8       Query   BEGIN
2015-02-26 10:49:24     [] @  []        7       8       Query   INSERT INTO employees VALUES (2, 'MySQL Fabric:2', 'Scale Out and HA Demo')
2015-02-26 10:49:24     [] @  []        7       8       Query   COMMIT /* implicit, from Xid_log_event */

省略.....

2015-02-26 10:49:24     [] @  []        7       8       Query   INSERT INTO employees VALUES (298, 'MySQL Fabric:298', 'Scale Out and HA Demo')
2015-02-26 10:49:24     [] @  []        7       8       Query   COMMIT /* implicit, from Xid_log_event */
2015-02-26 10:49:24     [] @  []        7       8       Query   BEGIN
2015-02-26 10:49:24     [] @  []        7       8       Query   INSERT INTO employees VALUES (299, 'MySQL Fabric:299', 'Scale Out and HA Demo')
2015-02-26 10:49:24     [] @  []        7       8       Query   COMMIT /* implicit, from Xid_log_event */
2015-02-26 10:49:26     [root] @ localhost [127.0.0.1]  8       8       Connect root@localhost on 
2015-02-26 10:49:26     root[root] @ localhost [127.0.0.1]      8       8       Query   SET NAMES 'utf8' COLLATE 'utf8_general_ci'
2015-02-26 10:49:26     root[root] @ localhost [127.0.0.1]      8       8       Query   SET @@session.autocommit = ON
2015-02-26 10:49:26     root[root] @ localhost [127.0.0.1]      8       8       Quit
2015-02-26 10:49:28     [root] @ localhost [127.0.0.1]  9       8       Connect root@localhost on 
2015-02-26 10:49:28     root[root] @ localhost [127.0.0.1]      9       8       Query   SET NAMES 'utf8' COLLATE 'utf8_general_ci'
2015-02-26 10:49:28     root[root] @ localhost [127.0.0.1]      9       8       Query   SET @@session.autocommit = ON
2015-02-26 10:49:28     root[root] @ localhost [127.0.0.1]      9       8       Quit
2015-02-26 10:49:30     [root] @ localhost [127.0.0.1]  10      8       Connect root@localhost on 
2015-02-26 10:49:30     root[root] @ localhost [127.0.0.1]      10      8       Query   SET NAMES 'utf8' COLLATE 'utf8_general_ci'
2015-02-26 10:49:30     root[root] @ localhost [127.0.0.1]      10      8       Query   SET @@session.autocommit = ON
2015-02-26 10:49:30     root[root] @ localhost [127.0.0.1]      10      8       Quit
2015-02-26 10:49:32     [root] @ localhost [127.0.0.1]  11      8       Connect root@localhost on 
2015-02-26 10:49:32     root[root] @ localhost [127.0.0.1]      11      8       Query   SET NAMES 'utf8' COLLATE 'utf8_general_ci'
2015-02-26 10:49:32     root[root] @ localhost [127.0.0.1]      11      8       Query   SET @@session.autocommit = ON
2015-02-26 10:49:32     root[root] @ localhost [127.0.0.1]      11      8       Quit
2015-02-262015-02-26 10:54:48     root[root] @ localhost [127.0.0.1]      121     8       Query   select * into outfile 'fabric_add_slave.sql' from mysql.general_log
-bash-4.2$ 
-----------------------------------------------------------------------------------------------------------------

【マスターでバイバリーログを削除した場合に、サーバーを追加した場合の動作確認】

バイナリーログのPURGE


mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW MASTER LOGS;
+---------------------+-----------+
| Log_name            | File_size |
+---------------------+-----------+
| Fabric01-bin.000001 |     93831 |
| Fabric01-bin.000002 |       241 |
| Fabric01-bin.000003 |       191 |
+---------------------+-----------+
3 rows in set (0.00 sec)

mysql> PURGE MASTER LOGS TO 'Fabric01-bin.000002';
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW MASTER LOGS;
+---------------------+-----------+
| Log_name            | File_size |
+---------------------+-----------+
| Fabric01-bin.000002 |       241 |
| Fabric01-bin.000003 |       191 |
+---------------------+-----------+
2 rows in set (0.01 sec)

mysql> 

【マスターでバイバリーログを削除した場合はログが読み取れずスレーブ作成が失敗する】

-bash-4.2$ mysqlfabric group add global 127.0.0.1:63309
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

ServerError: Error trying to configure server (4c6e5d9d-bd51-11e4-9ab3-08002766cefe) as slave: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'.

-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
------------------------------------ --------------- --------- ---------- ------
4790c170-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
483d8d5a-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
48d8c44f-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
4958fc6d-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0
49f47cf1-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
4a900296-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0
4b2ed657-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63307 SECONDARY  READ_ONLY    1.0
4c2e3c4e-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63308 SECONDARY  READ_ONLY    1.0


-bash-4.2$ 

【マスターからダンプして,新規Slaveにリストア後にGROUPにADD】
既に、上記でGROUP ADDが失敗していたので、@@GLOBAL.GTID_EXECUTEDが存在していたので情報をリセットしてからリストア。

-bash-4.2$ /usr/local/mysql/bin/mysqldump -uroot -proot -h127.0.0.1 -P63301 --all-databases --single-transaction --triggers --routines --events > GTID_63301.sql
Warning: Using a password on the command line interface can be insecure.
-bash-4.2$ ls
Fabric01-bin.000002  Fabric01-relay-bin.000001  Fabric01.pid    ib_logfile0  my.sock    mysql.out
Fabric01-bin.000003  Fabric01-relay-bin.index   GTID_63301.sql  ib_logfile1  mysql      performance_schema
Fabric01-bin.index   Fabric01.log               auto.cnf        ibdata1      mysql.err  test
-bash-4.2$ 


-bash-4.2$ /usr/local/mysql/bin/mysql -h127.0.0.1 -P63309 -u root -p < /home/mysql/fabric/mysql1/GTID_63301.sql
Enter password: 
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
-bash-4.2$ /usr/local/mysql/bin/mysql -h127.0.0.1 -P63309 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.6.22-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW GLOBAL VARIABLES LIKE '%GTID%';
+---------------------------------+------------------------------------------+
| Variable_name                   | Value                                    |
+---------------------------------+------------------------------------------+
| enforce_gtid_consistency        | ON                                       |
| gtid_executed                   | 4c6e5d9d-bd51-11e4-9ab3-08002766cefe:1-2 |
| gtid_mode                       | ON                                       |
| gtid_owned                      |                                          |
| gtid_purged                     |                                          |
| simplified_binlog_gtid_recovery | OFF                                      |
+---------------------------------+------------------------------------------+
6 rows in set (0.00 sec)

mysql> RESET MASTER;
Query OK, 0 rows affected (0.03 sec)

mysql> exit
Bye
-bash-4.2$ /usr/local/mysql/bin/mysql -h127.0.0.1 -P63309 -u root -p < /home/mysql/fabric/mysql1/GTID_63301.sql
Enter password: 
-bash-4.2$ 

&#91;/SHELL&#93;
※一度,group addに失敗している為、一度RESET MASTERを実行しています。


<strong>【ダンプからリストア後にSlaveをグループに追加】</strong> 
[SHELL]
-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
------------------------------------ --------------- --------- ---------- ------
4790c170-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
483d8d5a-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
48d8c44f-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
4958fc6d-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0
49f47cf1-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
4a900296-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0
4b2ed657-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63307 SECONDARY  READ_ONLY    1.0
4c2e3c4e-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63308 SECONDARY  READ_ONLY    1.0


-bash-4.2$ mysqlfabric group add global 127.0.0.1:63309
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
7e90ab70-f52f-4e18-b7e6-2a009cddd828        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1424917991.59 Triggered by <mysql.fabric.events.Event object at 0x27465d0>.
    4       2 1424917991.59                               Executing action (_add_server).
    5       2 1424917991.67                                Executed action (_add_server).


-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
------------------------------------ --------------- --------- ---------- ------
4790c170-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
483d8d5a-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
48d8c44f-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
4958fc6d-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0
49f47cf1-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
4a900296-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0
4b2ed657-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63307 SECONDARY  READ_ONLY    1.0
4c2e3c4e-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63308 SECONDARY  READ_ONLY    1.0
4c6e5d9d-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63309 SECONDARY  READ_ONLY    1.0


-bash-4.2$ 

-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63307 -se"select @@port,count(*) from test.employees"
Warning: Using a password on the command line interface can be insecure.
@@port  count(*)
63307   299
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63309 -se"select @@port,count(*) from test.employees"
Warning: Using a password on the command line interface can be insecure.
@@port  count(*)
63309   299
-bash-4.2$ 

【新規、ダンプ&リストアして追加したスレーブのログを確認してみる。】

mysql> select * from mysql.general_log;
+---------------------+------------------------------------+-----------+-----------+--------------+----------------------------------------------------------------------------------------------------------------+
| event_time          | user_host                          | thread_id | server_id | command_type | argument                                                                                                       |
+---------------------+------------------------------------+-----------+-----------+--------------+----------------------------------------------------------------------------------------------------------------+
| 2015-02-26 11:33:11 | [root] @ localhost [127.0.0.1]     |        24 |         9 | Connect      | root@localhost on                                                                                              |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |        24 |         9 | Query        | SET NAMES 'utf8' COLLATE 'utf8_general_ci'                                                                     |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |        24 |         9 | Query        | SET @@session.autocommit = ON                                                                                  |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |        24 |         9 | Query        | SELECT @@GLOBAL.SERVER_UUID                                                                                    |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |        24 |         9 | Quit         |                                                                                                                |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SELECT @@GLOBAL.SERVER_UUID as SERVER_UUID                                                                     |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SELECT @@GLOBAL.SERVER_ID as SERVER_ID                                                                         |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SELECT @@GLOBAL.VERSION as VERSION                                                                             |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SELECT @@GLOBAL.GTID_MODE as GTID_MODE                                                                         |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SELECT @@GLOBAL.LOG_BIN as LOG_BIN                                                                             |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SELECT @@GLOBAL.READ_ONLY as READ_ONLY                                                                         |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SHOW GRANTS                                                                                                    |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | STOP SLAVE                                                                                                     |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SHOW SLAVE STATUS                                                                                              |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SHOW SLAVE STATUS                                                                                              |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | CHANGE MASTER TO MASTER_HOST = '127.0.0.1' MASTER_USER = 'root' MASTER_PASSWORD = <secret> MASTER_PORT = 63301 |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SET @@GLOBAL.READ_ONLY = ON                                                                                    |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SELECT @@GLOBAL.READ_ONLY as READ_ONLY                                                                         |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | START SLAVE                                                                                                    |
| 2015-02-26 11:33:11 | [] @  []                           |        25 |         9 | Connect Out  | root@127.0.0.1:63301                                                                                           |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SHOW SLAVE STATUS                                                                                              |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SHOW SLAVE STATUS                                                                                              |
| 2015-02-26 11:33:13 | [root] @ localhost [127.0.0.1]     |        27 |         9 | Connect      | root@localhost on                                                                                              |
| 2015-02-26 11:33:13 | root[root] @ localhost [127.0.0.1] |        27 |         9 | Query        | SET NAMES 'utf8' COLLATE 'utf8_general_ci'                                                                     |
| 2015-02-26 11:33:13 | root[root] @ localhost [127.0.0.1] |        27 |         9 | Query        | SET @@session.autocommit = ON                                                                                  |
| 2015-02-26 11:33:13 | root[root] @ localhost [127.0.0.1] |        27 |         9 | Quit         |                                                                                                                |

省略....
                                                                 |
| 2015-02-26 11:35:22 | [root] @ localhost [127.0.0.1]     |        91 |         9 | Connect      | root@localhost on                                                                                              |
| 2015-02-26 11:35:22 | root[root] @ localhost [127.0.0.1] |        91 |         9 | Query        | SET NAMES 'utf8' COLLATE 'utf8_general_ci'                                                                     |
| 2015-02-26 11:35:22 | root[root] @ localhost [127.0.0.1] |        91 |         9 | Query        | SET @@session.autocommit = ON                                                                                  |
| 2015-02-26 11:35:22 | root[root] @ localhost [127.0.0.1] |        91 |         9 | Quit         |                                                                                                                |
| 2015-02-26 11:35:24 | [root] @ localhost [127.0.0.1]     |        92 |         9 | Connect      | root@localhost on                                                                                              |
| 2015-02-26 11:35:24 | root[root] @ localhost [127.0.0.1] |        92 |         9 | Query        | SET NAMES 'utf8' COLLATE 'utf8_general_ci'                                                                     |
| 2015-02-26 11:35:24 | root[root] @ localhost [127.0.0.1] |        92 |         9 | Query        | SET @@session.autocommit = ON                                                                                  |
| 2015-02-26 11:35:24 | root[root] @ localhost [127.0.0.1] |        92 |         9 | Quit         |                                                                                                                |
| 2015-02-26 11:35:26 | [root] @ localhost [127.0.0.1]     |        93 |         9 | Connect      | root@localhost on                                                                                              |
| 2015-02-26 11:35:26 | root[root] @ localhost [127.0.0.1] |        93 |         9 | Query        | SET NAMES 'utf8' COLLATE 'utf8_general_ci'                                                                     |
| 2015-02-26 11:35:26 | root[root] @ localhost [127.0.0.1] |        93 |         9 | Query        | SET @@session.autocommit = ON                                                                                  |
| 2015-02-26 11:35:26 | root[root] @ localhost [127.0.0.1] |        93 |         9 | Quit         |                                                                                                                |
| 2015-02-26 11:35:27 | root[root] @ localhost [127.0.0.1] |        23 |         9 | Query        | select * from mysql.general_log                                                                                |
+---------------------+------------------------------------+-----------+-----------+--------------+----------------------------------------------------------------------------------------------------------------+
291 rows in set (0.01 sec)

mysql> 

17.1.3.2 Setting Up Replication Using GTIDs
http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-howto.html
MySQL Fabric – Part 1 – Installing
https://blog.marceloaltmann.com/mysql-fabric-installing-instalar/


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

select_shard

それぞれのグループに分散されたテーブルからデータを読み込み
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のスレーブから分散してデータを持ってきている。
select_shard_range


MySQLFabricはOpenStack Novaと連携可能なので、
選択肢の一つとしてProviderとしてOpenStackを登録してみた。

詳細はこちらの資料を参照下さい。
https://oracleus.activeevents.com/2014/connect/fileDownload/session/CF4F217F7E733EF6066ED14D5CB7371C/CON5636_Kindahl-MySQLFabricElasticityOOW2014.pdf

Provider登録用のコマンド

[admin@Fabric01 ~]$ mysqlfabric help provider register
provider register provider_id username password url  [--tenant=NONE] 
[--provider_type=OPENSTACK] [--default_image=NONE] [--default_flavor=NONE] 
[--extra=NONE] [--synchronous]  
Register a provider.

登録したProviderからサーバーをProvisioning

[admin@Fabric01 ~]$ mysqlfabric help server create
server create provider_id  [--image=NONE] [--flavor=NONE] [--number_machines=1] 
[--availability_zone=NONE] [--key_name=NONE] [--security_groups=NONE] 
[--private_network=NONE] [--public_network=NONE] [--userdata=NONE] [--swap=NONE] 
[--scheduler_hints=NONE] [--meta=NONE] [--datastore=NONE] 
[--datastore_version=NONE] [--size=NONE] [--databases=NONE] [--users=NONE] 
[--configuration=NONE] [--security=NONE] [--skip_store] [--wait_spawning] 
[--synchronous]  
Create a virtual machine instance:

コマンドを利用するにはOpenStackのNova Clientなどをインストールしておく必要があります。

[root@Fabric02 admin]# yum install gcc
読み込んだプラグイン:fastestmirror
Loading mirror speeds from cached hostfile
 * base: ftp.iij.ad.jp
 * extras: mirror.vastspace.net

[root@Fabric01 mysql]# yum install python-devel python-pip
読み込んだプラグイン:fastestmirror
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
base                                                                                                | 3.6 kB  00:00:00     extras                                                                                              | 3.4 kB  00:00:00     rpmforge                                                                                            | 1.9 kB  00:00:00     updates                                                                                             | 3.4 kB  00:00:00     (1/2): extras/7/x86_64/primary_db                                                                   |  43 kB  00:00:00     (2/2): updates/7/x86_64/primary_db                                                                  | 6.1 MB  00:00:04     Determining fastest mirrors
 * base: ftp.nara.wide.ad.jp
 * extras: ftp.nara.wide.ad.jp
 * rpmforge: ftp.kddilabs.jp
 * updates: ftp.nara.wide.ad.jp
パッケージ python-pip は利用できません。
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ python-devel.x86_64 0:2.7.5-16.el7 を インストール
--> 依存性解決を終了しました。

[root@Fabric01 mysql]# 

[root@Fabric01 mysql]# curl https://bitbucket.org/pypa/setuptools/raw/bootstrap/ez_setup.py -o - | python
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 10655  100 10655    0     0   4164      0  0:00:02  0:00:02 --:--:--  4163
Downloading https://pypi.python.org/packages/source/s/setuptools/setuptools-12.0.5.zip
Extracting in /tmp/tmp3TYzVX
Now working in /tmp/tmp3TYzVX/setuptools-12.0.5
Installing Setuptools
...
Finished processing dependencies for setuptools==12.0.5

[root@Fabric01 mysql]# 


[root@Fabric01 mysql]# curl -kL https://raw.github.com/pypa/pip/master/contrib/get-pip.py | python
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:--  0:00:01 --:--:--     0
100 1567k  100 1567k    0     0   539k      0  0:00:02  0:00:02 --:--:-- 2945k
Collecting pip
  Downloading pip-6.0.7-py2.py3-none-any.whl (1.3MB)
    100% |################################| 1.3MB 282kB/s 
Installing collected packages: pip

Successfully installed pip-6.0.7
[root@Fabric01 mysql]# 

[root@Fabric01 mysql]# pip install python-novaclient
Collecting python-novaclient
  Using cached python_novaclient-2.20.0-py2.py3-none-any.whl
Collecting oslo.utils>=1.0.0 (from python-novaclient)
...
Successfully installed Babel-1.3 PrettyTable-0.7.2 argparse-1.3.0 iso8601-0.1.10 netifaces-0.10.4 oslo.utils-1.2.1 pbr-0.10.7 python-keystoneclient-1.0.0 python-novaclient-2.20.0 requests-2.5.1 simplejson-3.6.5 six-1.9.0

[root@Fabric01 mysql]# 

[root@Fabric01 mysql]# pip install python-neutronclient
Collecting python-neutronclient
  Downloading python_neutronclient-2.3.10-py2.py3-none-any.whl (180kB)
    100% |################################| 184kB 1.7MB/s 
Requirement already satisfied (use --upgrade to upgrade): oslo.utils>=1.0.0 in /usr/lib/python2.7/site-packages (from python-neutronclient)
...
Successfully installed cliff-1.9.0 cmd2-0.6.8 pyparsing-2.0.3 python-neutronclient-2.3.10
[root@Fabric01 mysql]# 

インストールが完了したら、以下のコマンドでOpenStackをProviderとして登録。
※2015年2月現在で確認出来るProviderは,OpenStackのみが登録可能でした。

-bash-4.2$ mysqlfabric provider register my_stack my_user my_password http://8.21.28.222:5000/v2.0/--tenant=my_user_role --provider_type=OPENSTACK
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
7c9e3185-a664-42c9-94e8-e9179fc0bab8        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1422565944.03 Triggered by <mysql.fabric.events.Event object at 0x2e21a10>.
    4       2 1422565944.04                        Executing action (_register_provider).
    5       2  1422565944.1                         Executed action (_register_provider).

listコマンドで登録したProviderを確認

-bash-4.2$ mysqlfabric provider list
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

provider_id      type username                                                url tenant default_image default_flavor extra
----------- --------- -------- -------------------------------------------------- ------ ------------- -------------- -----
   my_stack OPENSTACK  my_user http://8.21.28.222:5000/v2.0/--tenant=my_user_role   None          None           None  None


-bash-4.2$ 

問題無く登録出来たので、他にも付加出来る情報を加えて再登録

-bash-4.2$ mysqlfabric provider register mysqlfabric01 my_user my_password http://8.21.28.222:5000/v2.0/ --tenant=my_user_role --provider_type=OPENSTACK --default_image=mysql_img --default_flavor=nova_mysql01
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
2b66b1a7-fc9c-4d5f-8178-bf865b2ca863        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1422566557.81 Triggered by <mysql.fabric.events.Event object at 0x2e21a10>.
    4       2 1422566557.82                        Executing action (_register_provider).
    5       2 1422566557.83                         Executed action (_register_provider).


-bash-4.2$ mysqlfabric provider list
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

  provider_id      type username                                                url       tenant default_image default_flavor extra
------------- --------- -------- -------------------------------------------------- ------------ ------------- -------------- -----
mysqlfabric01 OPENSTACK  my_user                      http://8.21.28.222:5000/v2.0/ my_user_role     mysql_img   nova_mysql01  None
     my_stack OPENSTACK  my_user http://8.21.28.222:5000/v2.0/--tenant=my_user_role         None          None           None  None


-bash-4.2$ 

古い方のProvider情報をunregisterコマンドで登録削除

-bash-4.2$ mysqlfabric provider unregister my_stack
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
bb497878-3a54-4bf4-8cc4-36e5606200fd        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1422566754.92 Triggered by <mysql.fabric.events.Event object at 0x2e21a90>.
    4       2 1422566754.92                      Executing action (_unregister_provider).
    5       2 1422566754.95                       Executed action (_unregister_provider).


-bash-4.2$ mysqlfabric provider list
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

  provider_id      type username                           url       tenant default_image default_flavor extra
------------- --------- -------- ----------------------------- ------------ ------------- -------------- -----
mysqlfabric01 OPENSTACK  my_user http://8.21.28.222:5000/v2.0/ my_user_role     mysql_img   nova_mysql01  None


-bash-4.2$ 

Fab

OpenStack NovaのAPIの登録まではここで完了。
あとは、mysqlfabric server createコマンドでProvisioingしてMySQL Fabric Groupに追加してスケールアウト対応。

参照:http://docs.openstack.org/ja/user-guide/content/install_clients.html


mysqlfabric helpコマンドで見てもオプションがはっきりしなかったので
一応確認中ですが、set_modeにはREAD_WRITEとREAD_ONLY以外にもモードがあるようです。

Possible values are:
(0, ‘OFFLINE’), (1, ‘READ_ONLY’), (2, ‘WRITE_ONLY’), (3, ‘READ_WRITE’).

-bash-4.2$ mysqlfabric help server set_mode
server set_mode server_id mode  [--synchronous]  

Set a server's mode.

The mode determines whether a server can process read-only, read-write
or both transaction types.

WRITEにしてみる

-bash-4.2$ mysqlfabric server set_mode fbe1d550-a7f8-11e4-8f83-08002766cefe WRITE_ONLY
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
8a084578-b917-4864-a1db-68a430fb0b51        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1422603300.19 Triggered by <mysql.fabric.events.Event object at 0x2e36bd0>.
    4       2  1422603300.2                          Executing action (_set_server_mode).
    5       2  1422603300.2                           Executed action (_set_server_mode).


-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
------------------------------------ --------------- --------- ---------- ------
fbe1d550-a7f8-11e4-8f83-08002766cefe 127.0.0.1:63301   PRIMARY WRITE_ONLY    1.0
fc69c80d-a7f8-11e4-8f83-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
fd375683-a7f8-11e4-8f83-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
fdb1d34a-a7f8-11e4-8f83-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0

READ_WRITEに戻してみる

-bash-4.2$ mysqlfabric server set_mode fbe1d550-a7f8-11e4-8f83-08002766cefe READ_WRITE
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
3f778541-33da-4e95-95b3-c549e6b72a7f        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1422603379.35 Triggered by <mysql.fabric.events.Event object at 0x2e36bd0>.
    4       2 1422603379.35                          Executing action (_set_server_mode).
    5       2 1422603379.36                           Executed action (_set_server_mode).


-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
------------------------------------ --------------- --------- ---------- ------
fbe1d550-a7f8-11e4-8f83-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
fc69c80d-a7f8-11e4-8f83-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
fd375683-a7f8-11e4-8f83-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
fdb1d34a-a7f8-11e4-8f83-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0


-bash-4.2$

OFFLINEにしてみる
OFFLINEになったが、Connector Cacheに何処まで影響されるか次回確認してみる。

-bash-4.2$ mysqlfabric server set_mode fdb1d34a-a7f8-11e4-8f83-08002766cefe offline
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
f920e623-ad8d-43cf-aeb5-4fb22e1682be        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1422605003.74 Triggered by <mysql.fabric.events.Event object at 0x2e36bd0>.
    4       2 1422605003.74                          Executing action (_set_server_mode).
    5       2 1422605003.75                           Executed action (_set_server_mode).


-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
------------------------------------ --------------- --------- ---------- ------
fbe1d550-a7f8-11e4-8f83-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
fc69c80d-a7f8-11e4-8f83-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
fd375683-a7f8-11e4-8f83-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
fdb1d34a-a7f8-11e4-8f83-08002766cefe 127.0.0.1:63304 SECONDARY    OFFLINE    1.0


-bash-4.2$ 

MySQL Replicationは読み込みをスケールアウトする為の方法として多くのサイトで使われてきましたが、
マスターに関してもMySQL Fabricでスケールアウト出来るようになったので動作確認してみました。

特定のデータを分散する為に、Sharding Groupの作成
Shard 1

mysqlfabric sharding create_definition RANGE global
# mysqlfabric sharding list_definitions

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

Shard2

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

Shardingの定義を追加
test.T_shardingテーブルのid列で100番を境にマスターを分ける場合

mysqlfabric sharding add_table 1 test.T_sharding id
mysqlfabric sharding add_shard 1 shard1/1,shard2/100 --state=enabled

shardingの状況確認

-bash-4.2$ mysql -uroot -p -h127.0.0.1 -P63300 -e 'select * from fabric.shards'
+----------+----------+---------+
| shard_id | group_id | state   |
+----------+----------+---------+
|        1 | shard1   | ENABLED |
|        2 | shard2   | ENABLED |
+----------+----------+---------+
-bash-4.2$ 

-bash-4.2$ mysqlfabric sharding list_tables RANGE
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

mapping_id type_name      table_name global_group column_name
---------- --------- --------------- ------------ -----------
         1     RANGE test.T_sharding       global          id


-bash-4.2$ 

fabricグループ確認

-bash-4.2$ mysqlfabric group lookup_servers shard1
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
4aca246d-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63304   PRIMARY READ_WRITE    1.0
4b631544-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
4bff6f2a-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0


-bash-4.2$ mysqlfabric group lookup_servers shard2
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
4cb7ea8d-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63307   PRIMARY READ_WRITE    1.0
4d80f9e7-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63308 SECONDARY  READ_ONLY    1.0
57f4b83a-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63309 SECONDARY  READ_ONLY    1.0


-bash-4.2$ 

test.T_shardingをベースとしたsharding情報を確認

-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 T_sharding          id           1        1     RANGE   shard1       global
       test T_sharding          id         100        2     RANGE   shard2       global


-bash-4.2$ 

-bash-4.2$ mysqlfabric sharding lookup_servers test.T_sharding 99
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
4aca246d-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63304   PRIMARY READ_WRITE    1.0
4b631544-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
4bff6f2a-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0


-bash-4.2$ mysqlfabric sharding lookup_servers test.T_sharding 100
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
4cb7ea8d-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63307   PRIMARY READ_WRITE    1.0
4d80f9e7-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63308 SECONDARY  READ_ONLY    1.0
57f4b83a-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63309 SECONDARY  READ_ONLY    1.0


-bash-4.2$ 

実際に分散されたデータを確認してみる

-bash-4.2$ mysql -uroot -p -P63304 -h127.0.0.1 -se "select * from test.T_sharding" | tail -n 5
Enter password: 
95      John:95 Doe
96      John:96 Doe
97      John:97 Doe
98      John:98 Doe
99      John:99 Doe
-bash-4.2$ mysql -uroot -p -P63305 -h127.0.0.1 -se "select * from test.T_sharding" | tail -n 5
Enter password: 
95      John:95 Doe
96      John:96 Doe
97      John:97 Doe
98      John:98 Doe
99      John:99 Doe
-bash-4.2$ mysql -uroot -p -P63306 -h127.0.0.1 -se "select * from test.T_sharding" | tail -n 5
Enter password: 
95      John:95 Doe
96      John:96 Doe
97      John:97 Doe
98      John:98 Doe
99      John:99 Doe
-bash-4.2$ mysql -uroot -p -P63307 -h127.0.0.1 -se "select * from test.T_sharding" | tail -n 5
Enter password: 
300     John:300        Doe
301     John:301        Doe
302     John:302        Doe
303     John:303        Doe
304     John:304        Doe
-bash-4.2$ mysql -uroot -p -P63308 -h127.0.0.1 -se "select * from test.T_sharding" | tail -n 5
Enter password: 
300     John:300        Doe
301     John:301        Doe
302     John:302        Doe
303     John:303        Doe
304     John:304        Doe
-bash-4.2$ mysql -uroot -p -P63309 -h127.0.0.1 -se "select * from test.T_sharding" | tail -n 5
Enter password: 
300     John:300        Doe
301     John:301        Doe
302     John:302        Doe
303     John:303        Doe
304     John:304        Doe
-bash-4.2$ 

マスターデータの分散がされていて、書き込みも分散出来る事が確認出来た。
これまでのMySQLレプリケーションは読み込みが中心のスケールアウト手法でしたが、
書き込みもFabricでスケールアウトするという選択肢が増えた。

connector


mysqlfabricにてslaveを追加した場合に、新しいSlaveのバックグラウンド処理の確認。

Slave追加前

-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
------------------------------------ --------------- --------- ---------- ------
8cde5a0d-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
8cfb7b39-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
8da475e0-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0

スレーブ追加
mysqlfabric group add global 127.0.0.1:63304

スレーブ追加後

-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
------------------------------------ --------------- --------- ---------- ------
8cde5a0d-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
8cfb7b39-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
8da475e0-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
8e43506d-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0

新しいSlaveではリストア同期が行われ、アプリアカウント、テーブル、データのリストア同期が行われている。

-bash-4.2$ mysqlbinlog --no-defaults Fabric02-bin.000003 | egrep -v ^# | more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
BINLOG '
jledVA8EAAAAdAAAAHgAAAABAAQANS42LjIyLWVudGVycHJpc2UtY29tbWVyY2lhbC1hZHZhbmNl
ZC1sb2cAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQbR
bZs=
'/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:1'/*!*/;
SET TIMESTAMP=1419597327/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
SET PASSWORD FOR 'root'@'localhost'='*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B'
/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:2'/*!*/;
SET TIMESTAMP=1419597331/*!*/;
GRANT SELECT, INSERT, DELETE, CREATE, DROP ON `test`.* TO 'app'@'localhost' IDENTIFIED BY PASSWORD '*5BCB3E6AC345B435C7C2E6B7949A04CE6F6563D3'
/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:3'/*!*/;
use `test`/*!*/;
SET TIMESTAMP=1419597503/*!*/;
DROP TABLE IF EXISTS `employees` /* generated by server */
/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:4'/*!*/;
SET TIMESTAMP=1419597503/*!*/;
CREATE TABLE employees (   emp_no INT,    first_name CHAR(40),    last_name CHAR(40))
/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:5'/*!*/;
SET TIMESTAMP=1419597505/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=1419597505/*!*/;
INSERT INTO employees VALUES (0, 'John:0', 'Doe')
/*!*/;
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:6'/*!*/;
SET TIMESTAMP=1419597506/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=1419597506/*!*/;
INSERT INTO employees VALUES (1, 'John:1', 'Doe')
/*!*/;
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:7'/*!*/;
--続ける--
-bash-4.2$ mysqlbinlog --no-defaults Fabric02-bin.000003 |  egrep -i GTID | tail -n 10
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:417'/*!*/;
#141226 21:42:16 server id 1  end_log_pos 118585 CRC32 0x55ca8db9       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:418'/*!*/;
#141226 21:42:17 server id 1  end_log_pos 118870 CRC32 0x19ecc662       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:419'/*!*/;
#141226 21:42:18 server id 1  end_log_pos 119155 CRC32 0x63c53321       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:420'/*!*/;
#141226 21:42:18 server id 1  end_log_pos 119440 CRC32 0xee427af3       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:421'/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog *//*!*/;
-bash-4.2$ 

スレーブ追加時のデータ同期については、GTIDモードのレプリケーションに依存するので此方を参照下さい。
http://variable.jp/2015/02/26/mysqlfabric-group-add%E5%AE%9F%E8%A1%8C%E6%99%82%E3%81%AE%E3%83%87%E3%83%BC%E3%82%BF%E5%90%8C%E6%9C%9F/