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

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

mysqlfabric sharding create_definition RANGE global
# mysqlfabric sharding list_definitions

mysqlfabric group create shard1
mysqlfabric group add shard1 127.0.0.1:63304
mysqlfabric group add shard1 127.0.0.1:63305
mysqlfabric group add shard1 127.0.0.1:63306
mysqlfabric group promote shard1
mysqlfabric group activate shard1

Shard2

mysqlfabric group create shard2
mysqlfabric group add shard2 127.0.0.1:63307
mysqlfabric group add shard2 127.0.0.1:63308
mysqlfabric group add shard2 127.0.0.1:63309
mysqlfabric group promote shard2
mysqlfabric group activate shard2

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

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

shardingの状況確認

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

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

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


-bash-4.2$ 

fabricグループ確認

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

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


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

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


-bash-4.2$ 

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

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

schema_name table_name column_name lower_bound shard_id type_name group_id global_group
----------- ---------- ----------- ----------- -------- --------- -------- ------------
       test T_sharding          id           1        1     RANGE   shard1       global
       test T_sharding          id         100        2     RANGE   shard2       global


-bash-4.2$ 

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

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


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

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


-bash-4.2$ 

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

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

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

connector