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でスケールアウトするという選択肢が増えた。