MySQL Router2.1βがリリースされていたので、念の為にMySQL Group Replicationとの連携を確認しました。まだ、βなので機能が全部追加されている訳ではないので、今後のGAに期待したいと思います。

1: Group Replication基本設定と動作
2: MySQL Router2.1βの基本動作確認
3: Single Primaryモードの場合のFailOverの挙動確認

検証で利用したMySQLのバージョン


[root@replications gr]# /usr/local/mysql/bin/mysql -uroot -proot -S/home/mysql/gr/mysql1/my.sock -e "select @@version"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.7.17-enterprise-commercial-advanced-log |
+-------------------------------------------+

グループレプリケーションメンバーのオプションファイル(my.cnf)の設定


[root@replications gr]# cat my1.cnf 
[mysqld]
socket     = /home/mysql/gr/mysql1/my.sock
port       = 63301
innodb_buffer_pool_size=32MB
datadir    = /home/mysql/gr/mysql1
user       = mysql
log_error   = /home/mysql/gr/mysql1/error.log
log-bin    = master-bin
server-id   =   1 
gtid-mode  = on
enforce-gtid-consistency = on
log-slave-updates = on
binlog-checksum = NONE
binlog-format = row
master-info-repository = TABLE
relay-log-info-repository = TABLE

slave_parallel_type=LOGICAL_CLOCK
slave_preserve_commit_order=ON
slave_parallel_workers=8

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="00000000-1111-2222-3333-123456789ABC"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="127.0.0.1:63201"
loose-group_replication_group_seeds="127.0.0.1:63201,127.0.0.1:63202,127.0.0.1:63203"
loose-group_replication_bootstrap_group=off

### Option for Multi Master Mode
#loose-group_replication_single_primary_mode=FALSE
#loose-group_replication_enforce_update_everywhere_checks= TRUE
#tx_isolation = READ-COMMITTED

[root@replications gr]# cat my2.cnf 
[mysqld]
socket     = /home/mysql/gr/mysql2/my.sock
port       = 63302
innodb_buffer_pool_size=32MB
datadir    =/home/mysql/gr/mysql2
user       = mysql
log_error   = /home/mysql/gr/mysql2/error.log
log-bin    = master-bin
server-id   =   2 
gtid-mode  = on
enforce-gtid-consistency
log-slave-updates
binlog-checksum = NONE
binlog-format = row
master-info-repository = TABLE
relay-log-info-repository = TABLE

slave_parallel_type=LOGICAL_CLOCK
slave_preserve_commit_order=ON
slave_parallel_workers=8

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="00000000-1111-2222-3333-123456789ABC"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="127.0.0.1:63202"
loose-group_replication_group_seeds="127.0.0.1:63201,127.0.0.1:63202,127.0.0.1:63203"
loose-group_replication_bootstrap_group=off

### Option for Multi Master Mode
#loose-group_replication_single_primary_mode=FALSE
#loose-group_replication_enforce_update_everywhere_checks= TRUE
#tx_isolation = READ-COMMITTED

[root@replications gr]# cat my3.cnf 
[mysqld]
socket     = /home/mysql/gr/mysql3/my.sock
port       = 63303
innodb_buffer_pool_size=32MB
datadir    = /home/mysql/gr/mysql3
user       = mysql 
log_error   = /home/mysql/gr/mysql3/error.log
log-bin    = master-bin
binlog-format = row
server-id   =   3 
gtid-mode  = on
enforce-gtid-consistency = on
log-slave-updates = on
binlog-checksum = NONE
master-info-repository = TABLE
relay-log-info-repository = TABLE

slave_parallel_type=LOGICAL_CLOCK
slave_preserve_commit_order=ON
slave_parallel_workers=8

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="00000000-1111-2222-3333-123456789ABC"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="127.0.0.1:63203"
loose-group_replication_group_seeds="127.0.0.1:63201,127.0.0.1:63202,127.0.0.1:63203"
loose-group_replication_bootstrap_group=off

### Option for Multi Master Mode
#loose-group_replication_single_primary_mode=FALSE
#loose-group_replication_enforce_update_everywhere_checks= TRUE
#tx_isolation = READ-COMMITTED
[root@replications gr]# 

MySQLインスタンスの起動

/usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/gr/my1.cnf &
/usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/gr/my2.cnf &
/usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/gr/my3.cnf &

MySQLインスタンスへのアクセス

/usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr/mysql1/my.sock
/usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr/mysql2/my.sock
/usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr/mysql3/my.sock

■ 全てのサーバーにプラグインインストールとアカウント作成

INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SET SQL_LOG_BIN=0;
CREATE USER gr_user@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO gr_user@'%';
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='gr_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';

■ マスターノードでグループ設定

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

■ セカンダリーノードをグループに参加させる

 
START GROUP_REPLICATION; 

Group Replicationの状態確認


mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 698f11c8-0397-11e7-aae1-080027d65c57 | replications |       63301 | ONLINE       |
| group_replication_applier | 713ad572-0397-11e7-aca3-080027d65c57 | replications |       63302 | ONLINE       |
| group_replication_applier | 78b1d98a-0397-11e7-aef2-080027d65c57 | replications |       63303 | ONLINE       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM performance_schema.replication_group_member_stats\G
*************************** 1. row ***************************
                      CHANNEL_NAME: group_replication_applier
                           VIEW_ID: 14889395677991618:3
                         MEMBER_ID: 698f11c8-0397-11e7-aae1-080027d65c57
       COUNT_TRANSACTIONS_IN_QUEUE: 0
        COUNT_TRANSACTIONS_CHECKED: 0
          COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
TRANSACTIONS_COMMITTED_ALL_MEMBERS: 00000000-1111-2222-3333-123456789abc:1-3
    LAST_CONFLICT_FREE_TRANSACTION: 
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%single_primary%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | ON    |
+---------------------------------------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME='group_replication_primary_member';
+----------------------------------+--------------------------------------+
| VARIABLE_NAME                    | VARIABLE_VALUE                       |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 698f11c8-0397-11e7-aae1-080027d65c57 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> 

メモ:シングルプライマリーモードのみで利用する場合は、auto_incrementの値がぶつかる事は無いので、以下の値は1に調整しても問題無い。


[root@replications gr]# /usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr/mysql1/my.sock -e "show variables like 'auto_inc%'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 7     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
[root@replications gr]# /usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr/mysql2/my.sock -e "show variables like 'auto_inc%'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 7     |
| auto_increment_offset    | 2     |
+--------------------------+-------+
[root@replications gr]# /usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr/mysql3/my.sock -e "show variables like 'auto_inc%'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 7     |
| auto_increment_offset    | 3     |
+--------------------------+-------+
[root@replications gr]# 

[root@replications gr]# /usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr/mysql1/my.sock -e "show variables like 'group_replication_auto%';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| group_replication_auto_increment_increment | 7     |
+--------------------------------------------+-------+
[root@replications gr]# 

シングルプライマリーモードでは以下のように、PRIMARYノードのみで書き込みが可能になっている。(SECONDARYは、read_only & super_read_only)


[root@replications gr]# /usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr/mysql1/my.sock -e "SHOW VARIABLES LIKE '%read_only'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_read_only | OFF   |
| read_only        | OFF   |
| super_read_only  | OFF   |
| tx_read_only     | OFF   |
+------------------+-------+
[root@replications gr]# /usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr/mysql2/my.sock -e "SHOW VARIABLES LIKE '%read_only'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_read_only | OFF   |
| read_only        | ON    |
| super_read_only  | ON    |
| tx_read_only     | OFF   |
+------------------+-------+
[root@replications gr]# /usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr/mysql3/my.sock -e "SHOW VARIABLES LIKE '%read_only'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_read_only | OFF   |
| read_only        | ON    |
| super_read_only  | ON    |
| tx_read_only     | OFF   |
+------------------+-------+
[root@replications gr]# 

参考: http://mysqlhighavailability.com/gr/doc/getting_started.html

MySQL Router2.1(β)でInnoDB Clusterを利用しないで検証
MEMO: InnoDB Clusterを使わない場合は、GR内部にレプリケーション管理用のメタデータが出来ていない。


[root@replications bin]# ./mysqlrouter --version
MySQL Router v2.1.2 on Linux (64-bit) (GPL community edition)
[root@replications bin]# 

[root@replications mysql]# /home/mysql/mysqlrouter/bin/mysqlrouter --bootstrap 127.0.0.1:63301 --directory /home/mysql/mysqlrouter/tmp --conf-usePlease enter MySQL password for root: 
Error: The provided server does not seem to contain metadata for a MySQL InnoDB cluster
[root@replications mysql]# 

※ こちらは、別途確認します。

InnoDB Clusterに関しては、以前のブログ投稿を確認下さい。
http://variable.jp/2016/09/28/mysql-innodb-cluster%E6%A6%82%E8%A6%81/

以下、MySQL Routerの動作検証で利用した定義ファイル設定 
シングルプライマリーモードの場合の設定は、以下のルールになっているので63302をFailOver先に設定。

In the event the primary member is removed from the group, then an election is performed and a new primary is chosen from the remaining servers in the group. This election is performed by looking at the new view, ordering the server UUIDs in lexicographical order and by picking the first one.

https://dev.mysql.com/doc/refman/5.7/en/group-replication-single-primary-mode.html


[root@replications gr]# cat /etc/mysqlrouter/mysqlrouter.conf
[DEFAULT]
logging_folder=/home/mysql/mysqlrouter/tmp

[logger]
level = INFO

[routing:failover]
bind_port = 7001
mode = read-write
destinations = 127.0.0.1:63301,127.0.0.1:63302

[routing:balancing]
bind_port = 7002
mode = read-only
destinations = 127.0.0.1:63302,127.0.0.1:63303

#[metadata_cache]
#bootstrap_server_addresses=mysql://127.0.0.1:63301,mysql://127.0.0.1:63302,mysql://127.0.0.1:63303
#user=mysql_innodb_cluster_reader
#password=dtRSPqn
#metadata_cluster=test
#ttl=300
#metadata_replicaset=default

#[routing:default_rw]
#bind_port=6446
#destinations=metadata-cache:///default?role=PRIMARY
#mode=read-write

#[routing:default_ro]
#bind_port=6447
#destinations=metadata-cache:///default?role=SECONDARY
#mode=read-only
[root@replications gr]# 

MySQL Routerの起動

[root@replications bin]# ./mysqlrouter --config=/etc/mysqlrouter/mysqlrouter.conf &
[1] 2449
[root@replications bin]#

read-writeはFailOverモード、read-Onlyはラウンドロビン


[admin@replications ~]$ /usr/local/mysql/bin/mysql -uroot -proot -h 127.0.0.1 -P 7001 -e "select @@port"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
|  63301 |
+--------+
[admin@replications ~]$ /usr/local/mysql/bin/mysql -uroot -proot -h 127.0.0.1 -P 7001 -e "select @@port"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
|  63301 |
+--------+
[admin@replications ~]$ /usr/local/mysql/bin/mysql -uroot -proot -h 127.0.0.1 -P 7002 -e "select @@port"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
|  63302 |
+--------+
[admin@replications ~]$ /usr/local/mysql/bin/mysql -uroot -proot -h 127.0.0.1 -P 7002 -e "select @@port"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
|  63303 |
+--------+
[admin@replications ~]$ /usr/local/mysql/bin/mysql -uroot -proot -h 127.0.0.1 -P 7002 -e "select @@port"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
|  63302 |
+--------+
[admin@replications ~]$ 

PRIMARYをダウンさせた場合に、Single Primary Modeの仕様に従って、マスターが切り替わっている事が確認出来る。


[root@replications bin]# /usr/local/mysql/bin/mysql -uroot -proot -h 127.0.0.1 -P 7001 -e "select @@port"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
|  63301 |
+--------+
[root@replications bin]# /usr/local/mysql/bin/mysql -uroot -proot -h 127.0.0.1 -P 7001 -e "select @@port"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
|  63301 |
+--------+
[root@replications bin]#  /usr/local/mysql/bin/mysql -uroot -proot -S/home/mysql/gr/mysql1/my.sock -e "shutdown"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@replications bin]# /usr/local/mysql/bin/mysql -uroot -proot -h 127.0.0.1 -P 7001 -e "select @@port"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
|  63302 |
+--------+
[root@replications bin]# /usr/local/mysql/bin/mysql -uroot -proot -h 127.0.0.1 -P 7001 -e "select @@port"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
|  63302 |
+--------+

[root@replications bin]# /usr/local/mysql/bin/mysql -uroot -proot -h 127.0.0.1 -P 7001 -e "SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME='group_replication_primary_member'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------------------+--------------------------------------+
| VARIABLE_NAME                    | VARIABLE_VALUE                       |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 713ad572-0397-11e7-aca3-080027d65c57 |
+----------------------------------+--------------------------------------+

[root@replications bin]#  /usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr/mysql2/my.sock -e "SHOW VARIABLES LIKE '%read_only'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_read_only | OFF   |
| read_only        | OFF   |
| super_read_only  | OFF   |
| tx_read_only     | OFF   |
+------------------+-------+
[root@replications bin]# 


補足: 停止していた旧PRIMARYを起動すると、停止中に処理されたDDL、DMLも伝搬され復旧している事が確認出来る。


mysql> select @@port;
+--------+
| @@port |
+--------+
|  63301 |
+--------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.40 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| GR                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> 

MySQL Router2.1を利用する場合は、上記のようにGRのメタデータを参照しなくても利用出来ますが、
やはり状況によって運用でカバーしなければいけない事を考えると、MySQL Router2.1がInnoDB Clusterのメタデータ無しで利用出来るようになるのを待つか、
MySQL Router2.1がGAになったタイミングでMySQLShellも合わせてGAになるのを待って、InnoDB Clusterとして利用した方が良さそうです。

MySQL Routerマニュアル:
https://downloads.mysql.com/docs/mysql-router-2.1-en.a4.pdf

MySQL Router以外のHAアプリケーション (HAProxy, SQLProxy)
※ MySQL Router2.1がβが外れる前や、使い慣れている場合は以下のようなバランサーも良いかと思います。
http://lefred.be/content/ha-with-mysql-group-replication-and-proxysql/
http://lefred.be/content/mysql-group-replication-as-ha-solution/


InnoDB Clusterは、MySQLの高可用性構成をサポートするMySQLの可用性フレームワークです。
まだLab版ですが、既にRCになっているMySQL Group Replication、GAになっているMySQL Router (GAはバージョンは1.x),MySQL Shell (DMR)を組み合わせて高可用性を実現しています。
基本的な動作は、MySQL Fabricに似ていますが、管理リポジトリー自体がGroup Replication内にあるので、デフォルトで冗長化されています。また、MySQL Router,MySQL Shell,MySQL Group Replicationが蜜に連携していて1つのHAパッケージのようになっています。まだ、十分に検証出来ていませんが、基本的な動作を確認したので、此方にメモしておきます。
※まだLab版なので、仕様は変わると思います。

innodb_cluster

ダウンロード
MySQL Labs :: MySQL InnoDB Cluster 5.7.15 Preview
※ インストール方法は、RPMが提供されているので割愛しています。

■ Sandboxインスタンスのデプロイ
※ Do not run MySQL Shell as root.
以下のdeployLocalInstanceコマンドで新規インスタンスが構築されます。


[root@replications admin]# su - mysql
最終ログイン: 2016/09/27 (火) 22:32:27 JST日時 pts/2
-bash-4.2$ mysqlsh
Welcome to MySQL Shell 1.0.5-labs Development Preview

Copyright (c) 2016, 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', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> dba.deployLocalInstance(3310)
A new MySQL sandbox instance will be created on this host in 
/home/mysql/mysql-sandboxes/3310

Please enter a MySQL root password for the new instance: 
Deploying new MySQL instance...

Instance localhost:3310 successfully deployed and started.
Use '\connect root@localhost:3310' to connect to the instance.

mysql-js> dba.deployLocalInstance(3320) 
A new MySQL sandbox instance will be created on this host in 
/home/mysql/mysql-sandboxes/3320

Please enter a MySQL root password for the new instance: 
Deploying new MySQL instance...

Instance localhost:3320 successfully deployed and started.
Use '\connect root@localhost:3320' to connect to the instance.

mysql-js> dba.deployLocalInstance(3330)
A new MySQL sandbox instance will be created on this host in 
/home/mysql/mysql-sandboxes/3330

Please enter a MySQL root password for the new instance: 
Deploying new MySQL instance...

Instance localhost:3330 successfully deployed and started.
Use '\connect root@localhost:3330' to connect to the instance.

mysql-js> 


[root@replications mysql-sandboxes]# pwd
/home/mysql/mysql-sandboxes
[root@replications mysql-sandboxes]# ls -l
合計 12
drwxrwxr-x. 4 mysql mysql 4096  9月 27 17:25 3310
drwxrwxr-x. 4 mysql mysql 4096  9月 27 17:26 3320
drwxrwxr-x. 4 mysql mysql 4096  9月 27 17:27 3330
[root@replications mysql-sandboxes]# ls -l 3310/
合計 28
-rw-r-----. 1 mysql mysql    5  9月 27 17:25 3310.pid
drwxr-x---. 5 mysql mysql 4096  9月 27 17:25 data
-rw-------. 1 mysql mysql  806  9月 27 17:25 my.cnf
drwxrwxr-x. 2 mysql mysql    6  9月 27 17:25 mysql-files
srwxrwxrwx. 1 mysql mysql    0  9月 27 17:25 mysqld.sock
-rw-------. 1 mysql mysql    5  9月 27 17:25 mysqld.sock.lock
srwxrwxrwx. 1 mysql mysql    0  9月 27 17:25 mysqlx.sock
-rw-------. 1 mysql mysql    6  9月 27 17:25 mysqlx.sock.lock
-rwx------. 1 mysql mysql  121  9月 27 17:25 start.sh
-rwx------. 1 mysql mysql  186  9月 27 17:25 stop.sh
[root@replications mysql-sandboxes]# ls -l 3310/data/
合計 122948
-rw-r-----. 1 mysql mysql      169  9月 27 17:25 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:25 ON.000002
-rw-r-----. 1 mysql mysql      150  9月 27 17:25 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:25 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:25 auto.cnf
-rw-r-----. 1 mysql mysql    14266  9月 27 17:25 error.log
-rw-r-----. 1 mysql mysql      362  9月 27 17:25 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:25 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:25 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:25 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:25 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:25 mysql
drwxr-x---. 2 mysql mysql     8192  9月 27 17:25 performance_schema
drwxr-x---. 2 mysql mysql     8192  9月 27 17:25 sys
[root@replications mysql-sandboxes]# ls -l 3320/data/
合計 122948
-rw-r-----. 1 mysql mysql      169  9月 27 17:26 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:26 ON.000002
-rw-r-----. 1 mysql mysql      150  9月 27 17:26 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:26 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:26 auto.cnf
-rw-r-----. 1 mysql mysql    14369  9月 27 17:26 error.log
-rw-r-----. 1 mysql mysql      362  9月 27 17:26 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:26 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:26 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:26 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:26 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:26 mysql
drwxr-x---. 2 mysql mysql     8192  9月 27 17:26 performance_schema
drwxr-x---. 2 mysql mysql     8192  9月 27 17:26 sys
[root@replications mysql-sandboxes]# ls -l 3330/data/
合計 122948
-rw-r-----. 1 mysql mysql      169  9月 27 17:27 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:27 ON.000002
-rw-r-----. 1 mysql mysql      150  9月 27 17:27 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:27 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:27 auto.cnf
-rw-r-----. 1 mysql mysql    13201  9月 27 17:27 error.log
-rw-r-----. 1 mysql mysql      359  9月 27 17:27 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:27 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:27 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:27 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:27 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:27 mysql
drwxr-x---. 2 mysql mysql     8192  9月 27 17:27 performance_schema
drwxr-x---. 2 mysql mysql     8192  9月 27 17:27 sys
[root@replications mysql-sandboxes]# ls -l 3330/data/
合計 122948
-rw-r-----. 1 mysql mysql      169  9月 27 17:27 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:27 ON.000002
-rw-r-----. 1 mysql mysql      150  9月 27 17:27 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:27 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:27 auto.cnf
-rw-r-----. 1 mysql mysql    13201  9月 27 17:27 error.log
-rw-r-----. 1 mysql mysql      359  9月 27 17:27 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:27 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:27 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:27 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:27 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:27 mysql
drwxr-x---. 2 mysql mysql     8192  9月 27 17:27 performance_schema
drwxr-x---. 2 mysql mysql     8192  9月 27 17:27 sys
[root@replications mysql-sandboxes]# 

■ InnoDB Clusterの初期化
※Do not lose the MASTER key because it is required for managing the InnoDB cluster.
構築したインスタンスでグループレプリケーションを構築します。


mysql-js> \connect root@localhost:3310
Creating a Session to 'root@localhost:3310'
Enter password: 
Classic Session successfully established. No default schema selected.
mysql-js> cluster = dba.createCluster('test')
A new InnoDB cluster will be created on instance 'root@localhost:3310'.

When setting up a new InnoDB cluster it is required to define an administrative
MASTER key for the cluster. This MASTER key needs to be re-entered when making
changes to the cluster later on, e.g.adding new MySQL instances or configuring
MySQL Routers. Losing this MASTER key will require the configuration of all
InnoDB cluster entities to be changed.

Please specify an administrative MASTER key for the cluster 'test': 
Creating InnoDB cluster 'test' on 'root@localhost:3310'...
Adding Seed Instance...

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

<Cluster:test>
mysql-js> 



[root@replications mysql-sandboxes]# ls -l 3310/data/
合計 122988
-rw-r-----. 1 mysql mysql      169  9月 27 17:25 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:25 ON.000002
-rw-r-----. 1 mysql mysql    10397  9月 27 17:31 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:25 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:25 auto.cnf
-rw-r-----. 1 mysql mysql    21676  9月 27 17:31 error.log
-rw-r-----. 1 mysql mysql      362  9月 27 17:25 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:31 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:25 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:31 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:31 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:25 mysql
drwxr-x---. 2 mysql mysql     4096  9月 27 17:31 mysql_innodb_cluster_metadata
drwxr-x---. 2 mysql mysql     8192  9月 27 17:25 performance_schema
-rw-r-----. 1 mysql mysql      232  9月 27 17:31 replications-relay-bin-group_replication_applier.000001
-rw-r-----. 1 mysql mysql      598  9月 27 17:31 replications-relay-bin-group_replication_applier.000002
-rw-r-----. 1 mysql mysql      116  9月 27 17:31 replications-relay-bin-group_replication_applier.index
-rw-r-----. 1 mysql mysql      150  9月 27 17:31 replications-relay-bin-group_replication_recovery.000001
-rw-r-----. 1 mysql mysql       59  9月 27 17:31 replications-relay-bin-group_replication_recovery.index
drwxr-x---. 2 mysql mysql     8192  9月 27 17:25 sys
[root@replications mysql-sandboxes]# ls -l 3320/data/
合計 122948
-rw-r-----. 1 mysql mysql      169  9月 27 17:26 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:26 ON.000002
-rw-r-----. 1 mysql mysql      150  9月 27 17:26 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:26 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:26 auto.cnf
-rw-r-----. 1 mysql mysql    14369  9月 27 17:26 error.log
-rw-r-----. 1 mysql mysql      362  9月 27 17:26 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:26 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:26 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:26 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:26 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:26 mysql
drwxr-x---. 2 mysql mysql     8192  9月 27 17:26 performance_schema
drwxr-x---. 2 mysql mysql     8192  9月 27 17:26 sys
[root@replications mysql-sandboxes]# ls -l 3330/data/
合計 122948
-rw-r-----. 1 mysql mysql      169  9月 27 17:27 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:27 ON.000002
-rw-r-----. 1 mysql mysql      150  9月 27 17:27 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:27 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:27 auto.cnf
-rw-r-----. 1 mysql mysql    13201  9月 27 17:27 error.log
-rw-r-----. 1 mysql mysql      359  9月 27 17:27 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:27 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:27 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:27 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:27 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:27 mysql
drwxr-x---. 2 mysql mysql     8192  9月 27 17:27 performance_schema
drwxr-x---. 2 mysql mysql     8192  9月 27 17:27 sys
[root@replications mysql-sandboxes]# 

■ InnoDB Clusterへのインスタンスの追加
グループレプリケーションへインスタンスを追加します。


mysql-js> cluster.addInstance('root@localhost:3320')
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Please provide the password for 'root@localhost:3320': 
Adding instance to the cluster ...

The instance 'root@localhost:3320' was successfully added to the cluster.

mysql-js> cluster.addInstance('root@localhost:3330')
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Please provide the password for 'root@localhost:3330': 
Adding instance to the cluster ...

The instance 'root@localhost:3330' was successfully added to the cluster.

mysql-js> 


[root@replications mysql-sandboxes]# ls -l 3310/data/
合計 122988
-rw-r-----. 1 mysql mysql      169  9月 27 17:25 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:25 ON.000002
-rw-r-----. 1 mysql mysql    12253  9月 27 17:36 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:25 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:25 auto.cnf
-rw-r-----. 1 mysql mysql    23760  9月 27 17:36 error.log
-rw-r-----. 1 mysql mysql      362  9月 27 17:25 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:36 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:25 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:36 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:36 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:25 mysql
drwxr-x---. 2 mysql mysql     4096  9月 27 17:31 mysql_innodb_cluster_metadata
drwxr-x---. 2 mysql mysql     8192  9月 27 17:25 performance_schema
-rw-r-----. 1 mysql mysql      232  9月 27 17:31 replications-relay-bin-group_replication_applier.000001
-rw-r-----. 1 mysql mysql     1336  9月 27 17:36 replications-relay-bin-group_replication_applier.000002
-rw-r-----. 1 mysql mysql      116  9月 27 17:31 replications-relay-bin-group_replication_applier.index
-rw-r-----. 1 mysql mysql      150  9月 27 17:31 replications-relay-bin-group_replication_recovery.000001
-rw-r-----. 1 mysql mysql       59  9月 27 17:31 replications-relay-bin-group_replication_recovery.index
drwxr-x---. 2 mysql mysql     8192  9月 27 17:25 sys
[root@replications mysql-sandboxes]# ls -l 3320/data/
合計 122996
-rw-r-----. 1 mysql mysql      169  9月 27 17:26 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:26 ON.000002
-rw-r-----. 1 mysql mysql    12129  9月 27 17:36 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:26 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:26 auto.cnf
-rw-r-----. 1 mysql mysql    24847  9月 27 17:37 error.log
-rw-r-----. 1 mysql mysql      362  9月 27 17:26 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:36 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:26 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:36 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:35 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:26 mysql
drwxr-x---. 2 mysql mysql     4096  9月 27 17:35 mysql_innodb_cluster_metadata
drwxr-x---. 2 mysql mysql     8192  9月 27 17:26 performance_schema
-rw-r-----. 1 mysql mysql      232  9月 27 17:35 replications-relay-bin-group_replication_applier.000001
-rw-r-----. 1 mysql mysql     2135  9月 27 17:36 replications-relay-bin-group_replication_applier.000002
-rw-r-----. 1 mysql mysql      116  9月 27 17:35 replications-relay-bin-group_replication_applier.index
-rw-r-----. 1 mysql mysql      233  9月 27 17:35 replications-relay-bin-group_replication_recovery.000001
-rw-r-----. 1 mysql mysql      269  9月 27 17:35 replications-relay-bin-group_replication_recovery.000002
-rw-r-----. 1 mysql mysql      118  9月 27 17:35 replications-relay-bin-group_replication_recovery.index
drwxr-x---. 2 mysql mysql     8192  9月 27 17:26 sys
[root@replications mysql-sandboxes]# ls -l 3330/data/
合計 122992
-rw-r-----. 1 mysql mysql      169  9月 27 17:27 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:27 ON.000002
-rw-r-----. 1 mysql mysql    12129  9月 27 17:36 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:27 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:27 auto.cnf
-rw-r-----. 1 mysql mysql    22841  9月 27 17:36 error.log
-rw-r-----. 1 mysql mysql      359  9月 27 17:27 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:36 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:27 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:36 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:36 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:27 mysql
drwxr-x---. 2 mysql mysql     4096  9月 27 17:36 mysql_innodb_cluster_metadata
drwxr-x---. 2 mysql mysql     8192  9月 27 17:27 performance_schema
-rw-r-----. 1 mysql mysql      232  9月 27 17:36 replications-relay-bin-group_replication_applier.000001
-rw-r-----. 1 mysql mysql     1202  9月 27 17:36 replications-relay-bin-group_replication_applier.000002
-rw-r-----. 1 mysql mysql      116  9月 27 17:36 replications-relay-bin-group_replication_applier.index
-rw-r-----. 1 mysql mysql      233  9月 27 17:36 replications-relay-bin-group_replication_recovery.000001
-rw-r-----. 1 mysql mysql      269  9月 27 17:36 replications-relay-bin-group_replication_recovery.000002
-rw-r-----. 1 mysql mysql      118  9月 27 17:36 replications-relay-bin-group_replication_recovery.index
drwxr-x---. 2 mysql mysql     8192  9月 27 17:27 sys
[root@replications mysql-sandboxes]# 


[root@replications mysql-sandboxes]# netstat -na | grep sandboxes
unix  2      [ ACC ]     STREAM     LISTENING     26245    /home/mysql/mysql-sandboxes/3310/mysqlx.sock
unix  2      [ ACC ]     STREAM     LISTENING     26250    /home/mysql/mysql-sandboxes/3310/mysqld.sock
unix  2      [ ACC ]     STREAM     LISTENING     22039    /home/mysql/mysql-sandboxes/3330/mysqlx.sock
unix  2      [ ACC ]     STREAM     LISTENING     22044    /home/mysql/mysql-sandboxes/3330/mysqld.sock
unix  2      [ ACC ]     STREAM     LISTENING     21930    /home/mysql/mysql-sandboxes/3320/mysqlx.sock
unix  2      [ ACC ]     STREAM     LISTENING     21935    /home/mysql/mysql-sandboxes/3320/mysqld.sock
[root@replications mysql-sandboxes]# 

■ InnoDB Clusterのステータス確認
全てのホストがONLINEでHAが構成されている事が確認出来る。


mysql-js> cluster.status()
{
    "clusterName": "test",
    "defaultReplicaSet": {
        "status": "Cluster tolerant to up to ONE failure.",
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310",
                "status": "ONLINE",
                "role": "HA",
                "mode": "R/W",
                "leaves": {
                    "localhost:3320": {
                        "address": "localhost:3320",
                        "status": "ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    },
                    "localhost:3330": {
                        "address": "localhost:3330",
                        "status": "ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    }
                }
            }
        }
    }
}
mysql-js> 

Sandbox環境のGroup ReplicationはSingle Primary Modeで動いています。
It is a configuration mode for Group Replication that makes a single member act as a writeable master (PRIMARY) and the rest of the members act as hot-standbys (SECONDARY).
The group itself coordinates and configures itself automatically to determine which member will act as the PRIMARY, through a leader election mechanism.


-bash-4.2$ mysql -u root -pP@33word -h 127.0.0.1 -P 6446 -e "show variables like 'group_replication_single_primary_mode'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | ON    |
+---------------------------------------+-------+
-bash-4.2$ 

-bash-4.2$ mysql -u root -pP@33word -h 127.0.0.1 -P 6446 -e "show variables like 'group_replication_enforce_update_everywhere_checks'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------------------------------------+-------+
| Variable_name                                      | Value |
+----------------------------------------------------+-------+
| group_replication_enforce_update_everywhere_checks | OFF   |
+----------------------------------------------------+-------+
-bash-4.2$ 

-bash-4.2$ mysql -u root -pP@33word -h 127.0.0.1 -P 6447 -e "SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME='group_replication_primary_member'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------------------+--------------------------------------+
| VARIABLE_NAME                    | VARIABLE_VALUE                       |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 944915c6-853a-11e6-8e3d-080027d65c57 |
+----------------------------------+--------------------------------------+
-bash-4.2$ 

■ MySQL Routerのデプロイ
MySQL Routerを起動させて、グループレプリケーションへの接続をコントロールします。


-bash-4.2$ sudo mysqlrouter --bootstrap localhost:3310
[sudo] password for mysql: 
Please enter the administrative MASTER key for the MySQL InnoDB cluster: 
MySQL Router has now been configured for the InnoDB cluster 'test'.

The following connection information can be used to connect to the cluster.

Classic MySQL protocol connections to cluster 'test':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
-bash-4.2$ 


-bash-4.2$ mysqlrouter &
[1] 3615
-bash-4.2$ Logging to /var/log/mysqlrouter/mysqlrouter.log

-bash-4.2$ ps -ef | grep mysqlrouter
mysql     3615  3589  0 14:32 pts/1    00:00:00 mysqlrouter
mysql     3625  3589  0 14:32 pts/1    00:00:00 grep --color=auto mysqlrouter
-bash-4.2$ 

■ MySQLRouter経由でmysqlに接続し動作確認
mysqlshell経由


1) MySQL Shell経由

-bash-4.2$ mysqlsh --uri root@localhost:6446
Creating a Session to 'root@localhost:6446'
Enter password: 
Classic Session successfully established. No default schema selected.
Welcome to MySQL Shell 1.0.5-labs Development Preview

Copyright (c) 2016, 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', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> select @@hostname,@@port;
+--------------+--------+
| @@hostname   | @@port |
+--------------+--------+
| replications |   3310 |
+--------------+--------+
1 row in set (0.00 sec)
mysql-sql> 


-bash-4.2$ mysqlsh --uri root@localhost:6447
Creating a Session to 'root@localhost:6447'
Enter password: 
Classic Session successfully established. No default schema selected.
Welcome to MySQL Shell 1.0.5-labs Development Preview

Copyright (c) 2016, 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', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> select @@hostname,@@port;
+--------------+--------+
| @@hostname   | @@port |
+--------------+--------+
| replications |   3330 |
+--------------+--------+
1 row in set (0.00 sec)
mysql-sql>

2) MySQLコマンド経由での確認とステータスの確認


-bash-4.2$ mysql -u root -p -h 127.0.0.1 -P 6446
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.7.15-labs-gr090-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> select @@hostname,@@port;
+--------------+--------+
| @@hostname   | @@port |
+--------------+--------+
| replications |   3310 |
+--------------+--------+
1 row in set (0.00 sec)

mysql> 


-bash-4.2$ mysql -u root -p -h 127.0.0.1 -P 6447
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.7.15-labs-gr090-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> select @@hostname,@@port;
+--------------+--------+
| @@hostname   | @@port |
+--------------+--------+
| replications |   3320 |
+--------------+--------+
1 row in set (0.00 sec)

mysql> 



mysql-js> cluster.status()
{
    "clusterName": "test",
    "defaultReplicaSet": {
        "status": "Cluster tolerant to up to ONE failure.",
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310",
                "status": "ONLINE",
                "role": "HA",
                "mode": "R/W",
                "leaves": {
                    "localhost:3320": {
                        "address": "localhost:3320",
                        "status": "ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    },
                    "localhost:3330": {
                        "address": "localhost:3330",
                        "status": "ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    }
                }
            }
        }
    }
}
mysql-js> 

mysql-js> cluster.describe();
{
    "clusterName": "test",
    "adminType": "local",
    "defaultReplicaSet": {
        "name": "default",
        "instances": [
            {
                "name": "localhost:3310",
                "host": "localhost:3310",
                "role": "HA"
            },
            {
                "name": "localhost:3320",
                "host": "localhost:3320",
                "role": "HA"
            },
            {
                "name": "localhost:3330",
                "host": "localhost:3330",
                "role": "HA"
            }
        ]
    }
}
mysql-js> 

■ フェイルオーバーの確認 (HA)
既存のマスターインスタンスをdba.killLocalInstanceでダウンさせてみる。


mysql-js> dba.killLocalInstance(3310)
The MySQL sandbox instance on this host in 
/home/mysql/mysql-sandboxes/3310 will be killed

Killing MySQL instance...

Instance localhost:3310 successfully killed.

mysql-js> 

– 書き込みの接続が3310から3320に代わっている事が確認出来ます。


-bash-4.2$ mysqlsh --uri root@localhost:6446
Creating a Session to 'root@localhost:6446'
Enter password: 
ERROR: 2003 (HY000): Can't connect to remote MySQL server on '127.0.0.1:6446'
-bash-4.2$ mysqlsh --uri root@localhost:6446
Creating a Session to 'root@localhost:6446'
Enter password: 
Classic Session successfully established. No default schema selected.
Welcome to MySQL Shell 1.0.5-labs Development Preview

Copyright (c) 2016, 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', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> select @@hostname,@@port;
+--------------+--------+
| @@hostname   | @@port |
+--------------+--------+
| replications |   3320 |
+--------------+--------+
1 row in set (0.00 sec)
mysql-sql> 



-bash-4.2$ mysql -u root -p -h 127.0.0.1 -P 6446
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.7.15-labs-gr090-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> select @@hostname,@@port;
+--------------+--------+
| @@hostname   | @@port |
+--------------+--------+
| replications |   3320 |
+--------------+--------+
1 row in set (0.00 sec)

mysql> 


-bash-4.2$ mysql -u root -p -h 127.0.0.1 -P 6447
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.7.15-labs-gr090-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> select @@hostname,@@port;
+--------------+--------+
| @@hostname   | @@port |
+--------------+--------+
| replications |   3330 |
+--------------+--------+
1 row in set (0.00 sec)

mysql> 


-bash-4.2$ mysqlsh
Welcome to MySQL Shell 1.0.5-labs Development Preview

Copyright (c) 2016, 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', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> \connect root@localhost:6446
Creating a Session to 'root@localhost:6446'
Enter password: 
Classic Session successfully established. No default schema selected.
mysql-js> cluster = dba.getCluster()
When the InnoDB cluster was setup, a MASTER key was defined in order to enable
performing administrative tasks on the cluster.

Please specify the administrative MASTER key for the default cluster: 
<Cluster:test>
mysql-js> cluster.status()
{
    "clusterName": "test",
    "defaultReplicaSet": {
        "status": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "localhost:3320": {
                "address": "localhost:3320",
                "status": "ONLINE",
                "role": "HA",
                "mode": "R/W",
                "leaves": {
                    "localhost:3330": {
                        "address": "localhost:3330",
                        "status": "ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    },
                    "localhost:3310": {
                        "address": "localhost:3310",
                        "status": "OFFLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    }
                }
            }
        }
    }
}
mysql-js> cluster.describe();
{
    "clusterName": "test",
    "adminType": "local",
    "defaultReplicaSet": {
        "name": "default",
        "instances": [
            {
                "name": "localhost:3310",
                "host": "localhost:3310",
                "role": "HA"
            },
            {
                "name": "localhost:3320",
                "host": "localhost:3320",
                "role": "HA"
            },
            {
                "name": "localhost:3330",
                "host": "localhost:3330",
                "role": "HA"
            }
        ]
    }
}
mysql-js> 

■ 停止したインスタンスの再開


mysql-js> dba.startLocalInstance(3310)
The MySQL sandbox instance on this host in 
/home/mysql/mysql-sandboxes/3310 will be started

Starting MySQL instance...

Instance localhost:3310 successfully started.

mysql-js> cluster.status()
{
    "clusterName": "test",
    "defaultReplicaSet": {
        "status": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "localhost:3320": {
                "address": "localhost:3320",
                "status": "ONLINE",
                "role": "HA",
                "mode": "R/W",
                "leaves": {
                    "localhost:3330": {
                        "address": "localhost:3330",
                        "status": "ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    },
                    "localhost:3310": {
                        "address": "localhost:3310",
                        "status": "OFFLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    }
                }
            }
        }
    }
}
mysql-js> 

■ インスタンスをInnoDB Clusterグループへ再参加


mysql-js> dba.validateInstance('root@localhost:3310')
Please provide a password for 'root@localhost:3310': 
Validating instance...


Running check command.
Checking Group Replication prerequisites.
* Comparing options compatibility with Group Replication... PASS
Server configuration is compliant with the requirements.
* Checking server version... PASS
Server is 5.7.15

* Checking that server_id is unique... PASS
The server_id is valid.


* Checking compliance of existing tables... PASS

The instance: localhost:3310 is valid for Cluster usage

mysql-js> cluster.rejoinInstance('root@localhost:3310')
Please provide the password for 'root@localhost:3310': 
The instance will try rejoining the InnoDB cluster. Depending on the original
problem that made the instance unavailable the rejoin, operation might not be
successful and further manual steps will be needed to fix the underlying
problem.

Please monitor the output of the rejoin operation and take necessary action if
the instance cannot rejoin.
mysql-js> cluster.status()
{
    "clusterName": "test",
    "defaultReplicaSet": {
        "status": "Cluster tolerant to up to ONE failure.",
        "topology": {
            "localhost:3320": {
                "address": "localhost:3320",
                "status": "ONLINE",
                "role": "HA",
                "mode": "R/W",
                "leaves": {
                    "localhost:3310": {
                        "address": "localhost:3310",
                        "status": "ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    },
                    "localhost:3330": {
                        "address": "localhost:3330",
                        "status": "ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    }
                }
            }
        }
    }
}
mysql-js> 

■ SQLクライアントを利用したデータ同期と参照用インスタンスへのラウンドロビンを確認。

1) データ作成


-bash-4.2$ mysql -u root -p -h 127.0.0.1 -P 6446
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.7.15-labs-gr090-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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 databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+
5 rows in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.05 sec)

mysql> use test
Database changed
mysql> CREATE TABLE `InnoDB_Cluster` (
    -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    -> `comment` varchar(100) NOT NULL,
    -> `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.05 sec)

mysql> show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
| test                          |
+-------------------------------+
6 rows in set (0.00 sec)

mysql> exit
Bye
-bash-4.2$ mysql -u root -p -h 127.0.0.1 -P 6447
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.7.15-labs-gr090-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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 databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
| test                          |
+-------------------------------+
6 rows in set (0.00 sec)

mysql> 

■ データを1万件入れてます。

※ もし、参照側データベースにデータをINSERTしようとすると以下のようにエラーになる。
1290 (HY000): The MySQL server is running with the –super-read-only option so it cannot execute this statement
MySQL5.7からのsuper-read-onlyオプションが設定されている。

-bash-4.2$ python innodb_cluster.py 
Finish Creating Data
-bash-4.2$ 

■ 書き込み接続の確認(MySQL Routerの設定により、特定のマスターへ接続)

-bash-4.2$ mysql -u root -pP@33word -h 127.0.0.1 -P 6446 -e 'select @@port,count(*) from test.InnoDB_Cluster'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+----------+
| @@port | count(*) |
+--------+----------+
|   3320 |    10000 |
+--------+----------+
-bash-4.2$ mysql -u root -pP@33word -h 127.0.0.1 -P 6446 -e 'select @@port,count(*) from test.InnoDB_Cluster'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+----------+
| @@port | count(*) |
+--------+----------+
|   3320 |    10000 |
+--------+----------+
-bash-4.2$ 

■ 読み込み接続の確認(MySQL Routerの設定により、複数スレーブへのラウンドロビン接続)

-bash-4.2$ mysql -u root -pP@33word -h 127.0.0.1 -P 6447 -e 'select @@port,count(*) from test.InnoDB_Cluster'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+----------+
| @@port | count(*) |
+--------+----------+
|   3330 |    10000 |
+--------+----------+
-bash-4.2$ mysql -u root -pP@33word -h 127.0.0.1 -P 6447 -e 'select @@port,count(*) from test.InnoDB_Cluster'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+----------+
| @@port | count(*) |
+--------+----------+
|   3310 |    10000 |
+--------+----------+
-bash-4.2$ mysql -u root -pP@33word -h 127.0.0.1 -P 6447 -e 'select @@port,count(*) from test.InnoDB_Cluster'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+----------+
| @@port | count(*) |
+--------+----------+
|   3330 |    10000 |
+--------+----------+
-bash-4.2$ 

■ その他参考:リポジトリー(各ノードで持っているので冗長化が取れている)

-bash-4.2$ mysql -u root -pP@33word -h 127.0.0.1 -P 6446
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 5.7.15-labs-gr090-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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 databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
| test                          |
+-------------------------------+
6 rows in set (0.01 sec)

mysql> use mysql_innodb_cluster_metadata
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------------------------------+
| Tables_in_mysql_innodb_cluster_metadata |
+-----------------------------------------+
| clusters                                |
| hosts                                   |
| instances                               |
| replicasets                             |
| schema_version                          |
+-----------------------------------------+
5 rows in set (0.00 sec)

mysql> desc clusters;
+---------------------+------------------+------+-----+---------+----------------+
| Field               | Type             | Null | Key | Default | Extra          |
+---------------------+------------------+------+-----+---------+----------------+
| cluster_id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| cluster_name        | varchar(40)      | NO   | UNI | NULL    |                |
| default_replicaset  | int(10) unsigned | YES  | MUL | NULL    |                |
| description         | text             | YES  |     | NULL    |                |
| mysql_user_accounts | blob             | YES  |     | NULL    |                |
| options             | json             | YES  |     | NULL    |                |
| attributes          | json             | YES  |     | NULL    |                |
+---------------------+------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> desc hosts;
+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| host_id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| host_name          | varchar(128)     | YES  |     | NULL    |                |
| ip_address         | varchar(45)      | YES  |     | NULL    |                |
| location           | varchar(256)     | NO   |     | NULL    |                |
| attributes         | json             | YES  |     | NULL    |                |
| admin_user_account | json             | YES  |     | NULL    |                |
+--------------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> desc instances;
+-------------------+---------------------------+------+-----+---------+----------------+
| Field             | Type                      | Null | Key | Default | Extra          |
+-------------------+---------------------------+------+-----+---------+----------------+
| instance_id       | int(10) unsigned          | NO   | PRI | NULL    | auto_increment |
| host_id           | int(10) unsigned          | NO   | MUL | NULL    |                |
| replicaset_id     | int(10) unsigned          | YES  | MUL | NULL    |                |
| mysql_server_uuid | varchar(40)               | NO   | UNI | NULL    |                |
| instance_name     | varchar(40)               | NO   | UNI | NULL    |                |
| role              | enum('HA','readScaleOut') | NO   |     | NULL    |                |
| weight            | float                     | YES  |     | NULL    |                |
| addresses         | json                      | NO   |     | NULL    |                |
| attributes        | json                      | YES  |     | NULL    |                |
| version_token     | int(10) unsigned          | YES  |     | NULL    |                |
| description       | text                      | YES  |     | NULL    |                |
+-------------------+---------------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

mysql> desc replicasets;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| replicaset_id   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| cluster_id      | int(10) unsigned | NO   | MUL | NULL    |                |
| replicaset_type | enum('gr')       | NO   |     | NULL    |                |
| topology_type   | enum('pm','mm')  | NO   |     | pm      |                |
| replicaset_name | varchar(40)      | NO   |     | NULL    |                |
| active          | tinyint(1)       | NO   |     | NULL    |                |
| attributes      | json             | YES  |     | NULL    |                |
| description     | text             | YES  |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

mysql> desc schema_version;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| major | int(1) | NO   |     | 0       |       |
| minor | int(1) | NO   |     | 0       |       |
+-------+--------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> 

■ その他参考:MySQL Router設定

[root@replications mysql-sandboxes]# cat /etc/mysqlrouter/mysqlrouter.conf 
[DEFAULT]
plugin_folder=/lib64/mysqlrouter
# logging_folder=/var/log/mysqlrouter

[logger]
level = INFO

[metadata_cache]
bootstrap_server_addresses=mysql://localhost:3310,mysql://localhost:3320,mysql://localhost:3330
user=mysql_innodb_cluster_reader
password=]yZQL(C7H7AU.x(2
metadata_cluster=test
ttl=300
metadata_replicaset=default

[routing:default_rw]
bind_port=6446
destinations=metadata-cache:///default?role=PRIMARY
mode=read-write

[routing:default_ro]
bind_port=6447
destinations=metadata-cache:///default?role=SECONDARY
mode=read-only
[root@replications mysql-sandboxes]# 


[root@replications mysql-sandboxes]# cat /etc/mysqlrouter/mysqlrouter.ini 
# Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# MySQL Router configuration file
#
# Documentation is available at
#    http://dev.mysql.com/doc/mysql-router/en/

[DEFAULT]
logging_folder = /var/log/mysqlrouter/
plugin_folder = /usr/lib64/mysqlrouter
runtime_folder = /var/run/mysqlrouter
config_folder = /etc/mysqlrouter

[logger]
level = info

# If no plugin is configured which starts a service, keepalive
# will make sure MySQL Router will not immediately exit. It is
# safe to remove once Router is configured.
[keepalive]
interval = 60
[root@replications mysql-sandboxes]# 

参考)
https://dev.mysql.com/doc/mysql-innodb-cluster/en/mysql-innodb-cluster-getting-started.html
http://wagnerbianchi.com/blog/?p=1229
http://wagnerbianchi.com/blog/?p=1259
https://www.pythian.com/blog/rejoining-node-mysqls-innodb-cluster/


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/