MySQL FabricにてSharding構成を組んだ環境において、MySQL Routerを利用する場合の設定確認。

Shardingされた環境においても、MySQL Fabric対応コネクターを利用するとアプリケーションからDBへの接続先はFabric管理サーバーで良い。
但し、MySQL Fabric対応コネクターを利用する為には既存アプリケーション書き換えなどが必要になり。導入が大変な為なかなか導入に踏み切れない。
MySQL Routerを利用するとアプリケーションの書き換えは殆ど必要無く、単純に接続先をMySQL Routerに向けるだけで済む為、導入が非常にシンプルになります。
データベースをMySQL FabricにてShardingしていない環境では、MySQL Routerの導入はアプリケーション側もインフラ側もハードルは高くない。
但し、MySQL FabricでShardingされている環境に対応させるには、MySQL Router設定ファイルにそれぞれのShardingグループ毎にMaster(Read-Write)とSlave(Read-Only)を追加する必要がある。
※ ここでは、MySQL Router2.0.2をベースに説明しています。

DB_Con_Shard

Fabricコネクターの場合(接続先はFabric管理ノード)
例)

import mysql.connector 
from mysql.connector import fabric
import time

省略 ...

       conn.set_property(tables=["test.employees"], key=str(emp_no), mode=fabric.MODE_READWRITE, scope=fabric.SCOPE_LOCAL)
省略 ...
       conn.set_property(tables=["test.employees"], key=str(emp_no), mode=fabric.MODE_READONLY, scope=fabric.SCOPE_LOCAL)
省略 ...
       conn=mysql.connector.connect(
          fabric={"host" : "localhost", "port" : 32274, "username": "admin", "password": "admin"}, user="app", database="test", password="app", autocommit=True

MySQL Routerの場合

Router設定ファイルに関してのマニュアル
● Fabricを利用しない場合のRouter設定 (Routerのみの利用の場合)
必要に応じて、connect_timeoutやmax_connectionsを調整して下さい。
https://dev.mysql.com/doc/mysql-router/en/mysql-router-configuration-setup-connection-routing.html

● FabricとRouterを連携する場合のRouter設定
https://dev.mysql.com/doc/mysql-router/en/mysql-router-plugins-fabric-cache.html

MySQL Routerの接続定義ファイルに、以下のようにそれぞれのSharding Groupに対しての接続を作成する。
面倒ですが、アプリケーションからはCaseやIfなどによってSharding Keyの値によって接続先を適宜切り替える必要がある。

以下のような環境の場合の設定

-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         500        2     RANGE   shard2       global


sharding_def

上記のような環境では、MySQLFabricの定義ファイルは以下のように設定

[fabric_cache:ha1]
address = localhost:32275
user = admin

[routing:Read_Write_Global]
bind_port = 7001
destinations = fabric+cache://ha1/group/global
mode = read-write

[routing:Read_Only_Global]
bind_port = 7002 
destinations = fabric+cache://ha1/group/global
mode = read-only

[routing:Read_Write_shard1]
bind_port = 7003
destinations = fabric+cache://ha1/group/shard1
mode = read-write

[routing:Read_Only_shard1]
bind_port = 7004
destinations = fabric+cache://ha1/group/shard1
mode = read-only

[routing:Read_Write_shard2]
bind_port = 7005
destinations = fabric+cache://ha1/group/shard2
mode = read-write

[routing:Read_Only_shard2]
bind_port = 7006
destinations = fabric+cache://ha1/group/shard2
mode = read-only
[keepalive]
interval = 60

例えば、Shardingグループshard1に対して、書き込みする場合は以下のようにMySQL RouterのRead-Writeに対して接続し、読み込む場合は、shard1のRead-Onlyに対して接続します。書き込みは通常のRouterの動作として、マスター障害が発生するまでは、同じサーバーにアクセスします。読み込みは、以下のようにアクセスする度にラウンドロビンで異なるサーバーに接続されます。


-bash-4.2$ mysql -h 127.0.0.1 -P 7004 -u root --password=root -e "select count(*),@@port FROM test.employees"
+----------+--------+
| count(*) | @@port |
+----------+--------+
|       22 |  63305 |
+----------+--------+

-bash-4.2$ mysql -h 127.0.0.1 -P 7003 -u root --password=root -e "insert into test.employees(emp_no,first_name,last_name) values(23,'Shard1','Insert from P7003')"

-bash-4.2$ mysql -h 127.0.0.1 -P 7004 -u root --password=root -e "select count(*),@@port FROM test.employees"
+----------+--------+
| count(*) | @@port |
+----------+--------+
|       23 |  63304 |
+----------+--------+

-bash-4.2$ mysql -h 127.0.0.1 -P 7004 -u root --password=root -e "select count(*),@@port FROM test.employees"
+----------+--------+
| count(*) | @@port |
+----------+--------+
|       23 |  63305 |
+----------+--------+

MySQL Routerは2015年にリリースされたばかりですが、要件定義と事前検証をしっかり行い利用する事で、
サイト運用者の負荷を軽減する事が可能かと思います。また、システム開発する上での、システムデザインの幅も広げる事が可能です。

Routerを導入したり、その他サポートを受けて自社の工数を削減して生産性を上げたい場合はEnterprise Editionを検討すると良いかと思います。
https://www-jp.mysql.com/products/

その他、もっとシンプルにShardingを導入したい場合はMySQL Clusterを検討しても良いかと思います。
データを各データノードに分散するので、PKベースの処理が殆どの割合を占める場合には良い選択肢かと思います。
以下のページが参考になるかと思います。

奥野氏
MySQL Cluster 7.4で楽しむスケールアウト
山崎氏
MySQL Clusterの特徴とアーキテクチャ
@RDBMS
MySQL ClusterによるNoSQL処理(ClusterJ)

その他、関連メモ
MySQL RouterとMySQL Fabric連携による可用性の向上
MySQL Fabricにおけるsharding Table定義の変更
mysqlfabric shardingで分割したデータの読み込み


レプリケーションフレームワークのMySQL Fabricについては、昨年概要をブログに書いたので本日は、
MySQL FabricをMySQL Routerの連携による可用性や柔軟なスケールアウト方法について確認して見ます。

MySQL Fabricは便利なフレームワークですが、Fabric対応コネクターを利用しないと利用出来ず、
アプリケーションの書き換え労力が必要な為、あまりまだ広まっていませんでしたが、
MySQL Routerのリリースにより今後利用されるケースも増えてくるかと思います。

1. 障害発生時にマスターとスレーブ(新マスター)を自動切り替えし、アプリケーション接続変更は必要が無い。
2. アクセスが増えSlaveサーバーを追加した場合に、アプリケーションを変更せずに動的に新Slaveは参照用Round-Robinに追加される。
3. その他、サーバー入れ替え等のメンテナンスもFabricを利用する事で柔軟性が大幅に向上。

実際に障害発生した場合は、自動的に切り替えを行ってくれるので。
マスター障害発生時も機会損失を最小限にすると同時に緊急対応が不要にする事も可能なので運用負荷を軽減する事が可能です。

router_2

メモ
※ MySQL RouterはMySQL Fabricと連携しないでも利用する事は可能ですが、Fabricを利用した方がより柔軟な対応が可能です。
※ Fabricを利用しない場合は、設定ファイルのread-write、read-only項目にそれぞれアクセスするホストを書きます。マスター障害発生時は、記載されているホストを左から順に接続、スレーブはラウンドロビンです。

MySQL Fabricについて
http://variable.jp/2014/12/05/mysql-fabric%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6/

復習:MySQL Fabricの管理ノードではレプリケーションステータスを管理しています。(上記、過去ログ抜粋)
Fabric2

MySQL Router概要
https://www-jp.mysql.com/products/enterprise/router.html

MySQL Routerドキュメント
https://dev.mysql.com/doc/mysql-router/en/

MySQL Router FAQ
https://dev.mysql.com/doc/mysql-router/en/mysql-router-faq.html

MySQL Routerダウンロード
https://dev.mysql.com/downloads/router/
Oracle Linux, RedHat, CentOS6でも利用出来ますが、必要なソフトを事前にインストールしてコンパイルする必要があります。
ちなみに、検証で利用したところ7系のコンパイル済みTarをそのまま展開して6系のLinux上で動かしたところ動きました。

インストール必要条件
An operating system with a compiler that supports C++11.
Example systems that include this support are Ubuntu 14.04 and later, Oracle Linux 7, and OS X 10.10 and later.
Oracle Linux 6 works as well, but you have to install the Software Collection Library 1.2. For RedHat and CentOS, see Docs and Downloads.
MySQL Client Libraries development packages. For example, on Ubuntu this is the libmysqlclient-dev package.
CMake 2.8.9 or later.

MySQL Routerのインストールがおわったら、MySQL Router設定ファイルを作成して実行してください。
MySQL Routerの設定ファイルはシンプルです。
詳細はこちらのサンプルを参照下さい。

■ Static
https://dev.mysql.com/doc/mysql-router/en/mysql-router-configuration-file-example.html
■ Fabric利用
https://dev.mysql.com/doc/mysql-router/en/mysql-router-use-cases.html
■ Static & Fabric利用
http://mysqlhighavailability.com/mysql-router-on-labs-the-newest-member-of-the-mysql-family/

[MySQL Router起動例]
/home/mysql/mysql-router/bin/mysqlrouter –config=/etc/mysql/mysqlrouter.ini

設定ファイルを指定しないで立ち上げると以下のロケーションからファイルを読み込みます。
Generic Linux (standalone‐layout) : ./mysqlrouter.ini
Default, installing under /usr/local : /usr/local/etc/mysqlrouter.ini
RPM and Debian : /etc/mysqlrouter/mysqlrouter.ini

RouterからFabricに接続してマスター、スレーブの切り替わりなどを以下のようなSQLで確認してみる事が可能です。
ここでは、localhost:7001がマスター、localhost:7002がスレーブになっています。


[admin@Fabric01 mysql-router]$ cat mysql_router_balancing.sh 
#!/bin/sh

echo "[Current Slave Connection]"
/usr/local/mysql/bin/mysql -h 127.0.0.1 -P 7002 -u root --password=root -e "SELECT '現在のSlave',@@hostname,@@port,count(*) FROM test.employees"

echo "[Current Master Server]"
# read -p "Press [Enter] key to resume."

# Port 7001 is defined as read/write master server
/usr/local/mysql/bin/mysql -h 127.0.0.1 -P 7001 -u root --password=root -e "select '現在のMaster',@@hostname,@@port"

### Fail Over Demo ###
# /usr/local/mysql/bin/mysqladmin -h 127.0.0.1 -P63301 -u root -p shutdown

実際に実行してみると以下のようにスレーブへのアクセスが実行する度に、ラウンドロビンされている事が確認出来ます。
date

参照:
MySQL Router

補足:
サポートやコンサルテーティブサポートが必要な場合は、Enterprise Editionでサポートを受ける事も可能。
https://www-jp.mysql.com/products/


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


MySQL Fabricを利用していて、Node障害が発生した場合の対応手順確認。

■ FAULTYの状態からの回復手順
今回は、MySQLのProcessをkillコマンドで停止して疑似障害を起こしていたので、
再度起動しなおしてから以下の操作を行いました。

1) 現状確認


-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
------------------------------------ --------------- --------- ---------- ------
4594fd2a-aa84-11e4-a01a-08002766cefe 127.0.0.1:63301    FAULTY READ_WRITE    1.0
46317949-aa84-11e4-a01a-08002766cefe 127.0.0.1:63302   PRIMARY READ_WRITE    1.0
470a3117-aa84-11e4-a01a-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
4783b13a-aa84-11e4-a01a-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0

2) 起動した状態ではFAULTのままになっているので、先ずはSET_STATUSコマンドでSPAREの状態にします。
以下のオプションから選択可能
Status (STANDBY). Possible values are (0, ‘FAULTY’), (1, ‘SPARE’), (2, ‘SECONDARY’), (3, ‘PRIMARY’)


-bash-4.2$ mysqlfabric server set_status 4594fd2a-aa84-11e4-a01a-08002766cefe SPARE
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
75fdf4e5-b87b-4f91-be74-25bf6e01a663        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1422845850.53 Triggered by <mysql.fabric.events.Event object at 0x345a990>.
    4       2 1422845850.54                        Executing action (_set_server_status).
    5       2  1422845851.0                         Executed action (_set_server_status).


-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
------------------------------------ --------------- --------- ---------- ------
4594fd2a-aa84-11e4-a01a-08002766cefe 127.0.0.1:63301     SPARE    OFFLINE    1.0
46317949-aa84-11e4-a01a-08002766cefe 127.0.0.1:63302   PRIMARY READ_WRITE    1.0
470a3117-aa84-11e4-a01a-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
4783b13a-aa84-11e4-a01a-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0


-bash-4.2$ 

spare

■OFFLINEですが、この状態で既にデータが同期されているので、SECONDARYにしてユーザーのリクエストを処理開始します。
  – ユーザーに提供する為に、SPAREからSECONDARYにステータスを変更。

-bash-4.2$ mysqlfabric server set_status 4594fd2a-aa84-11e4-a01a-08002766cefe SECONDARY
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
dc804a2e-86b5-4694-bdd6-ddb3e3dd9461        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1422846492.45 Triggered by <mysql.fabric.events.Event object at 0x345a990>.
    4       2 1422846492.46                        Executing action (_set_server_status).
    5       2 1422846492.46                         Executed action (_set_server_status).


-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
------------------------------------ --------------- --------- ---------- ------
4594fd2a-aa84-11e4-a01a-08002766cefe 127.0.0.1:63301 SECONDARY  READ_ONLY    1.0
46317949-aa84-11e4-a01a-08002766cefe 127.0.0.1:63302   PRIMARY READ_WRITE    1.0
470a3117-aa84-11e4-a01a-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
4783b13a-aa84-11e4-a01a-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0


-bash-4.2$ 



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 Fabricについて理解仕切れてない部分もあり、
自信をもって話す事が出来なかった。

反省の意味も含めて、きちんとMySQL Fabricを理解する為に検証してみた。
丁度、スウェーデンから来た仲間から、色々とMySQL Fabricの詳細を教えて頂き、
短時間でかなり理解が深まり非常に助かりました。 Thank you very much!!

もう少し検証して、これからMySQL Fabricを使う皆さんに情報提供出来ればと思います。
Fabricはネット企業でMySQL Replicationを運用していた自分からすると、
かなり便利な機能だと思います。 GTIDも良い機能で運用を楽にしてくれましたが、
MySQL Fabricはそれにも増して良い機能です。
実際に開発、ステージング環境で利用して頂き、運用に慣れて頂いたら、
是非本番環境でも利用して頂き、マスターの冗長化、スケールアウトに使ってみて頂ければと思います。
コネクターは、今後更に増えて行く予定です。

connector

mysqlfabricコマンドリスト

-bash-4.2$ mysqlfabric --version
mysqlfabric 1.5.3
-bash-4.2$ mysqlfabric help commands
statistics node             Retrieve statistics on the Fabric node.
statistics group            Retrieve statistics on Procedures.
statistics procedure        Retrieve statistics on Procedures.
group activate              Activate failure detector for server(s) in a group.
group description           Update group's description.
group deactivate            Deactivate failure detector for server(s) in a group.
group create                Create a group.
group remove                Remove a server from a group.
group add                   Add a server into group.
group health                Check if any server within a group has failed and report health
                            information.
group lookup_servers        Return information on existing server(s) in a group.
group destroy               Remove a group.
group demote                Demote the current master if there is one.
group promote               Promote a server into master.
group lookup_groups         Return information on existing group(s).
dump fabric_nodes           Return a list of Fabric servers.
dump shard_index            Return information about the index for all mappings matching any of
                            the patterns provided.
dump sharding_information   Return all the sharding information about the tables passed as
                            patterns.
dump servers                Return information about servers.
dump shard_tables           Return information about all tables belonging to mappings matching any
                            of the provided patterns.
dump shard_maps             Return information about all shard mappings matching any of the
                            provided patterns.
manage teardown             Teardown Fabric Storage System.
manage stop                 Stop the Fabric server.
manage setup                Setup Fabric Storage System.
manage ping                 Check whether Fabric server is running or not.
manage start                Start the Fabric server.
manage logging_level        Set logging level.
server set_mode             Set a server's mode.
server clone                Clone the objects of a given server into a destination server.
server list                 Return information on existing machine(s) created by a provider.
server set_weight           Set a server's weight.
server lookup_uuid          Return server's uuid.
server set_status           Set a server's status.
server destroy              Destroy a virtual machine instance.
server create               Create a virtual machine instance:  mysqlfabric server create provider
                            --image name=image-mysql         --flavor
                            name=vm-template --meta db=mysql --meta
                            version=5.
role list                   List roles and associated permissions
user roles                  Change roles for a Fabric user  * protocol: Protocol of the user (for
                            example 'xmlrpc') * roles: Comma separated
                            list of roles, IDs or names (see `role
                            list`)
user usercommand            Base class for all user commands
user list                   List users and their roles
user add                    Add a new Fabric user.
user password               Change password of a Fabric user.
user delete                 Delete a Fabric user.
threat report_error         Report a server error.
threat report_failure       Report with certantity that a server has failed or is unreachable.
provider unregister         Unregister a provider.
provider register           Register a provider.
provider list               Return information on existing provider(s).
sharding list_definitions   Lists all the shard mapping definitions.
sharding remove_definition  Remove the shard mapping definition represented by the Shard Mapping
                            ID.
sharding move_shard         Move the shard represented by the shard_id to the destination group.
sharding disable_shard      Disable a shard.
sharding remove_table       Remove the shard mapping represented by the Shard Mapping object.
sharding split_shard        Split the shard represented by the shard_id into the destination
                            group.
sharding create_definition  Define a shard mapping.
sharding add_shard          Add a shard.
sharding add_table          Add a table to a shard mapping.
sharding lookup_table       Fetch the shard specification mapping for the given table
sharding enable_shard       Enable a shard.
sharding remove_shard       Remove a Shard.
sharding list_tables        Returns all the shard mappings of a particular sharding_type.
sharding prune_shard        Given the table name prune the tables according to the defined
                            sharding specification for the table.
sharding lookup_servers     Lookup a shard based on the give sharding key.
snapshot destroy            Destroy snapshot images associated to a machine.
snapshot create             Create a snapshot image from a machine.
event trigger               Trigger an event.
event wait_for_procedures   Wait until procedures, which are identified through their uuid in a
                            list and separated by comma, finish their
                            execution.
-bash-4.2$ 


Fabricで模擬障害を発生させた状態
MySQLFabric

ループさせたスクリプトが数秒で元に戻り、無事にマスターが切り替わっている事を確認出来た
Switch

スレーブの追加

[root@Fabric01 fabric]# mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
1711c4ac-7cd7-11e4-b640-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
17b7d8c5-7cd7-11e4-b640-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
183d8f8b-7cd7-11e4-b640-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0


[root@Fabric01 fabric]# mysqlfabric group add global 127.0.0.1:63304
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
5f8a1955-17de-48cf-af49-50a6fe82d08f        1       1      1

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


[root@Fabric01 fabric]# mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
1711c4ac-7cd7-11e4-b640-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
17b7d8c5-7cd7-11e4-b640-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
183d8f8b-7cd7-11e4-b640-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
18da4983-7cd7-11e4-b640-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0


[root@Fabric01 fabric]# 

スレーブ追加後は読み込みも分散されている事を確認出来た。
ノードの追加もスムーズに対応可能

(u'hostname:Fabric01', u'port:63302', u'John:800', u'Doe')
(u'hostname:Fabric01', u'port:63303', u'John:801', u'Doe')
(u'hostname:Fabric01', u'port:63304', u'John:802', u'Doe')

6ノード追加した状態

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

                                uuid finished success result
------------------------------------ -------- ------- ------
5c150bd9-2124-42db-9880-a48099cdb0e6        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1418042190.25 Triggered by <mysql.fabric.events.Event object at 0x2a4e410>.
    4       2 1418042190.26                               Executing action (_add_server).
    5       2 1418042190.65                                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
------------------------------------ --------------- --------- ---------- ------
6b22b751-7ed4-11e4-833a-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
6bb7b696-7ed4-11e4-833a-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
6c57c468-7ed4-11e4-833a-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
6cef3a69-7ed4-11e4-833a-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0
6d8a7a63-7ed4-11e4-833a-08002766cefe 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
6e2582b7-7ed4-11e4-833a-08002766cefe 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0

-bash-4.2$ 

fabric6

疑似障害でマスターを停止して、Fabricログテーブルを確認してみた

-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
------------------------------------ --------------- --------- ---------- ------
6b22b751-7ed4-11e4-833a-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
6bb7b696-7ed4-11e4-833a-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
6c57c468-7ed4-11e4-833a-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
6cef3a69-7ed4-11e4-833a-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0
6d8a7a63-7ed4-11e4-833a-08002766cefe 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
6e2582b7-7ed4-11e4-833a-08002766cefe 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0


-bash-4.2$ kill 2430
-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
------------------------------------ --------------- --------- ---------- ------
6b22b751-7ed4-11e4-833a-08002766cefe 127.0.0.1:63301    FAULTY READ_WRITE    1.0
6bb7b696-7ed4-11e4-833a-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
6c57c468-7ed4-11e4-833a-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
6cef3a69-7ed4-11e4-833a-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0
6d8a7a63-7ed4-11e4-833a-08002766cefe 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
6e2582b7-7ed4-11e4-833a-08002766cefe 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0


-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
------------------------------------ --------------- --------- ---------- ------
6b22b751-7ed4-11e4-833a-08002766cefe 127.0.0.1:63301    FAULTY READ_WRITE    1.0
6bb7b696-7ed4-11e4-833a-08002766cefe 127.0.0.1:63302   PRIMARY READ_WRITE    1.0
6c57c468-7ed4-11e4-833a-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
6cef3a69-7ed4-11e4-833a-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0
6d8a7a63-7ed4-11e4-833a-08002766cefe 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
6e2582b7-7ed4-11e4-833a-08002766cefe 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0


-bash-4.2$ 
 mysql> select * from fabric.log order by reported desc limit 0,20;
+----------------------+----------------------------+----------------------+-------------------------------------------------------------------------------------------------------+----------+------+
| subject              | reported                   | reporter             | message                                                                                               | category | type |
+----------------------+----------------------------+----------------------+-------------------------------------------------------------------------------------------------------+----------+------+
| dump.servers         | 2014-12-08 12:53:36.000000 | mysql.fabric.command | Started command (dump, servers).                                                                      |        1 |    0 |
| dump.servers         | 2014-12-08 12:53:36.000000 | mysql.fabric.command | Finished command (dump, servers).                                                                     |        1 |    1 |
| group.lookup_servers | 2014-12-08 12:52:41.000000 | mysql.fabric.command | Started command (group, lookup_servers).                                                              |        1 |    0 |
| group.lookup_servers | 2014-12-08 12:52:41.000000 | mysql.fabric.command | Finished command (group, lookup_servers).                                                             |        1 |    1 |
| dump.servers         | 2014-12-08 12:52:36.000000 | mysql.fabric.command | Started command (dump, servers).                                                                      |        1 |    0 |
| dump.servers         | 2014-12-08 12:52:36.000000 | mysql.fabric.command | Finished command (dump, servers).                                                                     |        1 |    1 |
| dump.servers         | 2014-12-08 12:52:35.000000 | mysql.fabric.command | Started command (dump, servers).                                                                      |        1 |    0 |
| dump.servers         | 2014-12-08 12:52:35.000000 | mysql.fabric.command | Finished command (dump, servers).                                                                     |        1 |    1 |
| group.lookup_servers | 2014-12-08 12:52:35.000000 | mysql.fabric.command | Started command (group, lookup_servers).                                                              |        1 |    0 |
| group.lookup_servers | 2014-12-08 12:52:35.000000 | mysql.fabric.command | Finished command (group, lookup_servers).                                                             |        1 |    1 |
| dump.servers         | 2014-12-08 12:52:34.000000 | mysql.fabric.command | Started command (dump, servers).                                                                      |        1 |    0 |
| dump.servers         | 2014-12-08 12:52:34.000000 | mysql.fabric.command | Finished command (dump, servers).                                                                     |        1 |    1 |
| global               | 2014-12-08 12:52:33.000000 | mysql.fabric.server  | Master has changed from 6b22b751-7ed4-11e4-833a-08002766cefe to 6bb7b696-7ed4-11e4-833a-08002766cefe. |        3 |    3 |
| dump.servers         | 2014-12-08 12:52:33.000000 | mysql.fabric.command | Started command (dump, servers).                                                                      |        1 |    0 |
| dump.servers         | 2014-12-08 12:52:33.000000 | mysql.fabric.command | Finished command (dump, servers).                                                                     |        1 |    1 |
| dump.servers         | 2014-12-08 12:52:30.000000 | mysql.fabric.command | Started command (dump, servers).                                                                      |        1 |    0 |
| dump.servers         | 2014-12-08 12:52:30.000000 | mysql.fabric.command | Finished command (dump, servers).                                                                     |        1 |    1 |
| dump.servers         | 2014-12-08 12:52:28.000000 | mysql.fabric.command | Started command (dump, servers).                                                                      |        1 |    0 |
| dump.servers         | 2014-12-08 12:52:28.000000 | mysql.fabric.command | Finished command (dump, servers).                                                                     |        1 |    1 |
| dump.servers         | 2014-12-08 12:52:06.000000 | mysql.fabric.command | Started command (dump, servers).                                                                      |        1 |    0 |
+----------------------+----------------------------+----------------------+-------------------------------------------------------------------------------------------------------+----------+------+
20 rows in set (0.00 sec)

mysql> 

動作確認中 [ Promote ] 2014/12/12現在

-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
------------------------------------ --------------- --------- ---------- ------
35c1ed6f-80d4-11e4-9043-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
360cc0ab-80d4-11e4-9043-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
373d787b-80d4-11e4-9044-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
375c9e12-80d4-11e4-9044-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0


-bash-4.2$ mysqlfabric group promote global --slave_id=375c9e12-80d4-11e4-9044-08002766cefe
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
267fa866-c5c9-4f5c-a5a4-6a9d90711d90        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2  1418262234.4 Triggered by <mysql.fabric.events.Event object at 0x17c8810>.
    4       2  1418262234.4                      Executing action (_define_ha_operation).
    5       2 1418262234.44                       Executed action (_define_ha_operation).
    3       2 1418262234.41 Triggered by <mysql.fabric.events.Event object at 0x1942bd0>.
    4       2 1418262234.44                   Executing action (_check_candidate_switch).
    5       2 1418262234.73                    Executed action (_check_candidate_switch).
    3       2 1418262234.52 Triggered by <mysql.fabric.events.Event object at 0x1942c10>.
    4       2 1418262234.73                       Executing action (_block_write_switch).
    5       2 1418262234.79                        Executed action (_block_write_switch).
    3       2 1418262234.77 Triggered by <mysql.fabric.events.Event object at 0x1942c50>.
    4       2 1418262234.79                       Executing action (_wait_slaves_switch).
    5       2 1418262234.86                        Executed action (_wait_slaves_switch).
    3       2 1418262234.86 Triggered by <mysql.fabric.events.Event object at 0x1942c90>.
    4       2 1418262234.86                      Executing action (_change_to_candidate).
    5       2 1418262236.49                       Executed action (_change_to_candidate).


-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
------------------------------------ --------------- --------- ---------- ------
35c1ed6f-80d4-11e4-9043-08002766cefe 127.0.0.1:63301 SECONDARY  READ_ONLY    1.0
360cc0ab-80d4-11e4-9043-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
373d787b-80d4-11e4-9044-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
375c9e12-80d4-11e4-9044-08002766cefe 127.0.0.1:63304   PRIMARY READ_WRITE    1.0


-bash-4.2$ 



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

                                uuid is_alive    status is_not_running is_not_configured io_not_running sql_not_running io_error sql_error
------------------------------------ -------- --------- -------------- ----------------- -------------- --------------- -------- ---------
35c1ed6f-80d4-11e4-9043-08002766cefe        1 SECONDARY              0                 0              0               0    False     False
360cc0ab-80d4-11e4-9043-08002766cefe        1 SECONDARY              0                 0              0               0    False     False
373d787b-80d4-11e4-9044-08002766cefe        1 SECONDARY              0                 0              0               0    False     False
375c9e12-80d4-11e4-9044-08002766cefe        1   PRIMARY              0                 0              0               0    False     False

issue
-----


-bash-4.2$ 

スレーブはロードバランサーで分散出来るし、障害も分散されていれば気にしないで良いけど、
MySQL Fabricはマスター障害でも自動でマスターを切り替えてくれるので便利です。
また、マスターサーバーの切り替えもコマンドで出来て、マスターサーバーの入れ替えが楽になりそうです。
これから、また色々と勉強と検証重ねていき、皆さんと共有して行きたいと思います。

Bug #75188 Promoteする場合のアプリケーションユーザー権限に関する留意点

参照: http://www-jp.mysql.com/products/enterprise/fabric.html