MySQL Group Replicationは、グループで一つのIDを持つ為、通常のシングルインスタンスと同じようにレプリケーションを組む事が出来ます。4月のInnoDB Clusterリリース以降、MySQLを利用されているお客様から、幾つか質問を受けていたので念の為に挙動を再確認。

環境
複数サーバーを準備出来なかったので,シングサーバーにポートを変更して、2グループ(6サーバー)で先ずはGROUP REPLICATIONを準備。

mysql> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.7.18-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

グループレプリケーション間のレプリケーションの設定・開始
マスター側のGroup Replicationにスレーブ接続用のアカウントを作成し、スレーブにデータをコピーしたので、レプリケーションをスレーブ側のグループレプリケーションで開始。


mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1',
    -> MASTER_PORT=3310,
    -> MASTER_USER='repl_user',
    -> MASTER_PASSWORD='password',
    -> MASTER_AUTO_POSITION=1 FOR CHANNEL 'remote-dc01';
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> START SLAVE FOR CHANNEL 'remote-dc01';
Query OK, 0 rows affected (3.11 sec)

MySQL Enterprise Monitorで確認

マスター側のGROUP REPLICATIONの状態

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 199e19cb-5326-11e7-947d-080027d65c57 | replications |        3330 | ONLINE       |
| group_replication_applier | d9e318f0-5325-11e7-8762-080027d65c57 | replications |        3310 | ONLINE       |
| group_replication_applier | fab196ae-5325-11e7-8edb-080027d65c57 | replications |        3320 | ONLINE       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
3 rows in set (0.00 sec)

mysql> SELECT LAST_CONFLICT_FREE_TRANSACTION  FROM performance_schema.replication_group_member_stats;
+-----------------------------------------+
| LAST_CONFLICT_FREE_TRANSACTION          |
+-----------------------------------------+
| 33cf36db-5326-11e7-8b16-080027d65c57:15 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> 

スレーブ側のGROUP REPLICATIONの状態


mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | bc653b5a-3b8b-11e7-94cd-080027d65c57 | replications |       63301 | ONLINE       |
| group_replication_applier | c68819f0-3b8b-11e7-958b-080027d65c57 | replications |       63302 | ONLINE       |
| group_replication_applier | d0a3d2c8-3b8b-11e7-97ef-080027d65c57 | replications |       63303 | ONLINE       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
3 rows in set (0.00 sec)

mysql> SELECT LAST_CONFLICT_FREE_TRANSACTION  FROM performance_schema.replication_group_member_stats;
+-----------------------------------------+
| LAST_CONFLICT_FREE_TRANSACTION          |
+-----------------------------------------+
| 33cf36db-5326-11e7-8b16-080027d65c57:15 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW SLAVE STATUS FOR CHANNEL 'remote-dc01'\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl_user
                  Master_Port: 3310
                Connect_Retry: 60
              Master_Log_File: replications-bin.000003
          Read_Master_Log_Pos: 15217
               Relay_Log_File: replications-relay-bin-remote@002ddc01.000003
                Relay_Log_Pos: 676
        Relay_Master_Log_File: replications-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 15217
              Relay_Log_Space: 16007
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1097457189
                  Master_UUID: d9e318f0-5325-11e7-8762-080027d65c57
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 33cf36db-5326-11e7-8b16-080027d65c57:1-15,
d9e318f0-5325-11e7-8762-080027d65c57:1-11
            Executed_Gtid_Set: 00000000-1111-2222-3333-123456789abc:1-6,
33cf36db-5326-11e7-8b16-080027d65c57:1-15,
d9e318f0-5325-11e7-8762-080027d65c57:1-11
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: remote-dc01
           Master_TLS_Version: 
1 row in set (0.01 sec)

mysql> 

マスター側でテーブルとデータを作成してレプリケーションの確認


mysql> CREATE TABLE `T_GR` (
    ->   `pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `string1` char(1) DEFAULT NULL,
    ->   `string2` char(1) DEFAULT NULL,
    ->   `string1_w_string` char(4) GENERATED ALWAYS AS (hex(weight_string(`string1`))) VIRTUAL,
    ->   `string2_w_string` char(4) GENERATED ALWAYS AS (hex(weight_string(`string2`))) VIRTUAL,
    ->   `compare` char(1) GENERATED ALWAYS AS ((`string1` = `string2`)) VIRTUAL,
    ->   PRIMARY KEY (`pid`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.25 sec)

mysql> insert into T_GR(string1,string2) values('A','a');
Query OK, 1 row affected (0.19 sec)

mysql> select * from T_GR;
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 0041             | 0041             | 1       |
+-----+---------+---------+------------------+------------------+---------+
1 row in set (0.00 sec)

mysql> 

スレーブ側でデータの確認

mysql> show tables;
Empty set (0.00 sec)

mysql> show tables;
+-----------------------+
| Tables_in_GR_GR_REPLI |
+-----------------------+
| T_GR                  |
+-----------------------+
1 row in set (0.01 sec)

mysql> select * from T_GR;
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 0041             | 0041             | 1       |
+-----+---------+---------+------------------+------------------+---------+
1 row in set (0.30 sec)

mysql> 


スレーブ側の全てのグループメンバーでデータがレプリケートされている事を確認

-bash-4.2$ /usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr2/mysql1/my.sock -e "select * from GR_GR_REPLI.T_GR"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 0041             | 0041             | 1       |
+-----+---------+---------+------------------+------------------+---------+
-bash-4.2$ /usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr2/mysql2/my.sock -e "select * from GR_GR_REPLI.T_GR"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 0041             | 0041             | 1       |
+-----+---------+---------+------------------+------------------+---------+
-bash-4.2$ /usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr2/mysql3/my.sock -e "select * from GR_GR_REPLI.T_GR"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 0041             | 0041             | 1       |
+-----+---------+---------+------------------+------------------+---------+
-bash-4.2$ 

グループレプリケーション稼働中はRESET MASTER出来なかったり、パラメータ周りやレプリケーションの設定でいくつか考慮が必要なので、慣れるまでは十分に手順の確認と検証して下さい。
若しくは、ミッションクリティカルな環境では、設計の段階でサポートを受けると安心かと思います。 
サポート: https://www.mysql.com/jp/support/

参考までに、Defaultでシングルマスターモードでもauto_incrementの値が7になっているため、group_replication_auto_increment_increment=1等で設定を変更すると良いかと思います。
変更しない場合は、以下のように値がIncrementされていきます。

補足:
双方向にレプリケーションも可能ですが、やはり事前に確認が必要ですので事前のPOCをお願いします。
以下の様に、逆方向にもレプリケーションを張る事は可能です。但し、グループレプリケーションとグループレプリケーションの間は非同期でレプリケーションを張っています。その為、両方で同じデータを同時に変更しないようにする必要があります。またInnoDB Clusterの場合は、グループレプリケーションだけで構成した以外にも考慮が必要です。


mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1',
    -> MASTER_PORT=63301,
    -> MASTER_USER='repl_user',
    -> MASTER_PASSWORD='password',
    -> MASTER_AUTO_POSITION=1 FOR CHANNEL 'remote-dc02';
Query OK, 0 rows affected, 2 warnings (0.27 sec)

mysql> START SLAVE FOR CHANNEL 'remote-dc02';
Query OK, 0 rows affected (0.19 sec)



InnoDB Clusterを構築すると、MySQL Routerの設定ファイルが自動的に作成されます。基本的にはTTLによって構成はキャッシュされますが、metadata cache componentが常にMySQLに接続していて、Group Replicationの状況をモニタリングしています。それにより、構成変更や障害発生時には自動的に検知してアプリケーションを適切なデータベースに振り分けてくれます。

【以下、マニュアル抜粋】
MySQL Router keeps a cached list of the online MySQL servers, or the topology and state of the configured InnoDB cluster. Initially, the list is loaded from Router’s configuration file when Router is started. This list was generated with InnoDB cluster servers when Router was bootstrapped using the –bootstrap option.

To keep the cache updated, the metadata cache component keeps an open connection to one of the InnoDB cluster servers that contains metadata. It does so by querying the metadata database and live state information from MySQL’s performance schema. The cluster metadata is changed whenever the InnoDB cluster is modified, such as adding or removing a MySQL server using the MySQL Shell, and the performance_schema tables are updated in real-time by the MySQL server’s Group Replication plugin whenever a cluster state change is detected. For example, if one of the MySQL servers unexpectedly exits.

When Router detects that a connected MySQL server crashes, for example because the metadata cache has lost its connection and can not connect again, it attempts to connect to a different MySQL server to fetch metadata and InnoDB cluster state from the new MySQL server.

Application connections to a MySQL server that crashes are automatically closed. They must then reconnect to Router, which redirects them to an online MySQL server.

参照: https://dev.mysql.com/doc/mysql-router/2.1/en/mysql-router-general-metadata.html

MySQL Router2.1から, MySQLへの常時接続の確認 (mysql_router5_212qj4063dmw)


mysql> show processlist;
+----+----------------------------+-----------------+-------+---------+------+--------------------------------------------------------+------------------+
| Id | User                       | Host            | db    | Command | Time | State                                                  | Info             |
+----+----------------------------+-----------------+-------+---------+------+--------------------------------------------------------+------------------+
| 11 | system user                |                 | NULL  | Connect | 4600 | executing                                              | NULL             |
| 14 | system user                |                 | NULL  | Connect | 4600 | Slave has read all relay log; waiting for more updates | NULL             |
| 42 | root                       | localhost       | mysql | Query   |    0 | System lock                                            | show processlist |
| 43 | mysql_router5_212qj4063dmw | localhost:48721 | NULL  | Sleep   |   66 |                                                        | NULL             |
+----+----------------------------+-----------------+-------+---------+------+--------------------------------------------------------+------------------+
4 rows in set (0.01 sec)

mysql> select * from general_log;
+----------------------------+------------------------------+-----------+-----------+--------------+----------------------------------------+
| event_time                 | user_host                    | thread_id | server_id | command_type | argument                               |
+----------------------------+------------------------------+-----------+-----------+--------------+----------------------------------------+
| 2017-06-09 14:34:32.995672 | root[root] @ localhost []    |        42 | 944686911 | Query        | select * from general_log              |
| 2017-06-09 14:34:37.233924 | root[root] @ localhost []    |        42 | 944686911 | Query        | show processlist                       |
| 2017-06-09 14:34:41.252563 | [root] @ localhost [::1]     |        46 | 944686911 | Connect      | root@localhost on  using SSL/TLS       |
| 2017-06-09 14:34:41.253181 | root[root] @ localhost [::1] |        46 | 944686911 | Query        | select @@version_comment limit 1       |
| 2017-06-09 14:34:41.253872 | root[root] @ localhost [::1] |        46 | 944686911 | Query        | select 'R-Port:6446',@@hostname,@@port |
| 2017-06-09 14:34:41.257997 | root[root] @ localhost [::1] |        46 | 944686911 | Quit         |                                        |
| 2017-06-09 14:34:44.344733 | root[root] @ localhost []    |        42 | 944686911 | Query        | select * from general_log              |
+----------------------------+------------------------------+-----------+-----------+--------------+----------------------------------------+
7 rows in set (0.00 sec)


PRIMARY(書き込み)は固定され、SECONDARYは参照専用でラウンドロビンされている

-bash-4.2$ ./mysql_status_router.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3320 |
+-------------+--------------+--------+
-bash-4.2$ ./mysql_status_router.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3330 |
+-------------+--------------+--------+
-bash-4.2$ 


SECONDARYの一台を停止してみる


mysql-js> shell.connect('root@localhost:3310');
Please provide the password for 'root@localhost:3310': 
Creating a Session to 'root@localhost:3310'
Classic Session successfully established. No default schema selected.
mysql-js> dba.killSandboxInstance(3320)
The MySQL sandbox instance on this host in 
/home/mysql/mysql-sandboxes/3320 will be killed


Killing MySQL instance...

Instance localhost:3320 successfully killed.

mysql-js> var cluster = dba.getCluster("testCluster")
mysql-js> cluster.status()
{
    "clusterName": "testCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3310", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}
mysql-js> 


MySQL Router2.1がトポロジーの変更を認識し、以下のコマンドをmysql_router5_212qj4063dmw@localhostから実行している事が確認出来る

mysql> select * from general_log;
+----------------------------+--------------------------------------------------------------------------------+-----------+-----------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| event_time                 | user_host                                                                      | thread_id | server_id | command_type | argument                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+----------------------------+--------------------------------------------------------------------------------+-----------+-----------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2017-06-09 14:34:57.700152 | root[root] @ localhost []                                                      |        42 | 944686911 | Query        | select * from general_log                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| 2017-06-09 14:35:39.294567 | _gr_user[_gr_user] @ localhost []                                              |        48 | 944686911 | Connect      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:35:39.294983 | _gr_user[_gr_user] @ localhost []                                              |        48 | 944686911 | Query        | SELECT @@GLOBAL.gtid_executed                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| 2017-06-09 14:35:39.295129 | _gr_user[_gr_user] @ localhost []                                              |        48 | 944686911 | Quit         |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:35:39.299868 | _gr_user[_gr_user] @ localhost []                                              |        49 | 944686911 | Connect      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:35:39.300055 | _gr_user[_gr_user] @ localhost []                                              |        49 | 944686911 | Quit         |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:35:41.755412 | root[root] @ localhost []                                                      |        42 | 944686911 | Query        | show processlist                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 2017-06-09 14:35:54.021036 | [root] @ localhost [::1]                                                       |        50 | 944686911 | Connect      | root@localhost on  using SSL/TLS                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 2017-06-09 14:35:54.021898 | root[root] @ localhost [::1]                                                   |        50 | 944686911 | Query        | select @@version_comment limit 1                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 2017-06-09 14:35:54.022239 | root[root] @ localhost [::1]                                                   |        50 | 944686911 | Query        | select 'R-Port:6446',@@hostname,@@port                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| 2017-06-09 14:35:54.025850 | root[root] @ localhost [::1]                                                   |        50 | 944686911 | Quit         |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:35:54.939557 | mysql_router5_212qj4063dmw[mysql_router5_212qj4063dmw] @ localhost [127.0.0.1] |        43 | 944686911 | Quit         |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:35:55.225193 | [mysql_router5_212qj4063dmw] @ localhost [127.0.0.1]                           |        51 | 944686911 | Connect      | mysql_router5_212qj4063dmw@localhost on  using SSL/TLS                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| 2017-06-09 14:35:55.227370 | mysql_router5_212qj4063dmw[mysql_router5_212qj4063dmw] @ localhost [127.0.0.1] |        51 | 944686911 | Query        | SELECT R.replicaset_name, I.mysql_server_uuid, I.role, I.weight, I.version_token, H.location, I.addresses->>'$.mysqlClassic', I.addresses->>'$.mysqlX' FROM mysql_innodb_cluster_metadata.clusters AS F JOIN mysql_innodb_cluster_metadata.replicasets AS R ON F.cluster_id = R.cluster_id JOIN mysql_innodb_cluster_metadata.instances AS I ON R.replicaset_id = I.replicaset_id JOIN mysql_innodb_cluster_metadata.hosts AS H ON I.host_id = H.host_id WHERE F.cluster_name = 'testCluster' |
| 2017-06-09 14:35:55.229489 | mysql_router5_212qj4063dmw[mysql_router5_212qj4063dmw] @ localhost [127.0.0.1] |        51 | 944686911 | Query        | show status like 'group_replication_primary_member'                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| 2017-06-09 14:35:55.232809 | mysql_router5_212qj4063dmw[mysql_router5_212qj4063dmw] @ localhost [127.0.0.1] |        51 | 944686911 | Query        | SELECT member_id, member_host, member_port, member_state, @@group_replication_single_primary_mode FROM performance_schema.replication_group_members WHERE channel_name = 'group_replication_applier'                                                                                                                                                                                                                                                                                          |
| 2017-06-09 14:35:55.764665 | [root] @ localhost [::1]                                                       |        52 | 944686911 | Connect      | root@localhost on  using SSL/TLS                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 2017-06-09 14:35:55.765502 | root[root] @ localhost [::1]                                                   |        52 | 944686911 | Query        | select @@version_comment limit 1                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 2017-06-09 14:35:55.767062 | root[root] @ localhost [::1]                                                   |        52 | 944686911 | Query        | select 'R-Port:6446',@@hostname,@@port                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| 2017-06-09 14:35:55.770798 | root[root] @ localhost [::1]                                                   |        52 | 944686911 | Quit         |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:36:00.277880 | root[root] @ localhost []                                                      |        42 | 944686911 | Query        | show processlist                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 2017-06-09 14:36:04.168023 | root[root] @ localhost []                                                      |        42 | 944686911 | Query        | select * from general_log                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+----------------------------+--------------------------------------------------------------------------------+-----------+-----------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
33 rows in set (0.00 sec)

MySQL Router 2.1が実行しているコマンド
このコマンドで、PRIMARYと現状のGroup Replicationの構成を認識している。

mysql> show status like 'group_replication_primary_member';      
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 1c008db5-4cca-11e7-be2d-080027d65c57 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT R.replicaset_name, I.mysql_server_uuid, I.role, I.weight, I.version_token, H.location, I.addresses->>'$.mysqlClassic', I.addresses->>'$.mysqlX' FROM mysql_innodb_cluster_metadata.clusters AS F JOIN mysql_innodb_cluster_metadata.replicasets AS R ON F.cluster_id = R.cluster_id JOIN mysql_innodb_cluster_metadata.instances AS I ON R.replicaset_id = I.replicaset_id JOIN mysql_innodb_cluster_metadata.hosts AS H ON I.host_id = H.host_id WHERE F.cluster_name = 'testCluster';
+-----------------+--------------------------------------+------+--------+---------------+----------+--------------------------------+--------------------------+
| replicaset_name | mysql_server_uuid                    | role | weight | version_token | location | I.addresses->>'$.mysqlClassic' | I.addresses->>'$.mysqlX' |
+-----------------+--------------------------------------+------+--------+---------------+----------+--------------------------------+--------------------------+
| default         | 1c008db5-4cca-11e7-be2d-080027d65c57 | HA   |   NULL |          NULL |          | localhost:3310                 | localhost:33100          |
| default         | 4acfab6c-4cca-11e7-848b-080027d65c57 | HA   |   NULL |          NULL |          | localhost:3320                 | localhost:33200          |
| default         | 7b6cb3c3-4cca-11e7-8af6-080027d65c57 | HA   |   NULL |          NULL |          | localhost:3330                 | localhost:33300          |
+-----------------+--------------------------------------+------+--------+---------------+----------+--------------------------------+--------------------------+
3 rows in set (0.01 sec)

mysql> SELECT member_id, member_host, member_port, member_state, @@group_replication_single_primary_mode FROM performance_schema.replication_group_members WHERE channel_name = 'group_replication_applier';
+--------------------------------------+--------------+-------------+--------------+-----------------------------------------+
| member_id                            | member_host  | member_port | member_state | @@group_replication_single_primary_mode |
+--------------------------------------+--------------+-------------+--------------+-----------------------------------------+
| 1c008db5-4cca-11e7-be2d-080027d65c57 | replications |        3310 | ONLINE       |                                       1 |
| 7b6cb3c3-4cca-11e7-8af6-080027d65c57 | replications |        3330 | ONLINE       |                                       1 |
+--------------------------------------+--------------+-------------+--------------+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> 

ノード3320は停止しているので、MySQL Router2.1が稼働しているノードに適切に割り振っている

mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3330 |
+-------------+--------------+--------+
-bash-4.2$ ./mysql_status_router.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3330 |
+-------------+--------------+--------+
-bash-4.2$ 

停止していたインスタンスを再稼働させる


-bash-4.2$ mysqlsh
Welcome to MySQL Shell 1.0.9

Copyright (c) 2016, 2017, 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> shell.connect('root@localhost:3310');
Please provide the password for 'root@localhost:3310': 
Creating a Session to 'root@localhost:3310'
Classic Session successfully established. No default schema selected.
mysql-js> dba.startSandboxInstance(3320);
The MySQL sandbox instance on this host in 
/home/mysql/mysql-sandboxes/3320 will be started


Starting MySQL instance...

Instance localhost:3320 successfully started.

mysql-js> var cluster = dba.getCluster("testCluster")
mysql-js> cluster.rejoinInstance('localhost:3320')
Rejoining the instance to 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.

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

The instance 'root@localhost:3320' was successfully rejoined on the cluster.

The instance 'localhost:3320' was successfully added to the MySQL Cluster.
mysql-js> cluster.status()
{
    "clusterName": "testCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3310", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}
mysql-js> 

MySQL Routerも認識して再度バランシングに追加されている事が確認出来る


mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3330 |
+-------------+--------------+--------+
-bash-4.2$ ./mysql_status_router.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3320 |
+-------------+--------------+--------+
-bash-4.2$ 

詳細は、此方を確認下さい
https://dev.mysql.com/doc/mysql-router/2.1/en/

サポート (HA & RouterはEEにてサポート)
https://www.mysql.com/jp/support/


MySQL Group Replicationの監視に関しては、Performance_schemaからレプリケーションの状態を確認して、モニタリングする事が可能ですが、MySQL Enterprise Monitor3.4ではGroup ReplicationのトポロジーViewやAdvisor等で、モニタリングを簡素化して、システムの安定稼働と運用負荷を軽減してくれるようになりました。自作でモニタリングツールを作る事も可能ですが、ツールのアップデート等に工数がかかるので、出来るだけ既存のツールを利用したい場合は有用かと思います。

Group Replicationステータスモニタリング用オブジェクト
(Group Replication Status Monitoring MySQL Objects)

performance_schema.replication_group_member_stats
performance_schema.replication_group_members
performance_schema.replication_connection_status
performance_schema.replication_applier_status

ステータスモニタリング例

グループ・レプリケーションのグループメンバーの状況を確認
(Can Confirm Group member status)

mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 698f11c8-0397-11e7-aae1-080027d65c57
 MEMBER_HOST: replications
 MEMBER_PORT: 63301
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 713ad572-0397-11e7-aca3-080027d65c57
 MEMBER_HOST: replications
 MEMBER_PORT: 63302
MEMBER_STATE: ONLINE

グループによってコミットされたトランザクション、キューの増加状況、競合の検出数、検査されたトランザクションの数等を確認。
(We can confirm Committed Transaction, Queue, Conflict and so on.)

mysql> SELECT * FROM performance_schema.replication_group_member_stats\G
*************************** 1. row ***************************
                      CHANNEL_NAME: group_replication_applier
                           VIEW_ID: 14896410386000092:7
                         MEMBER_ID: 78b1d98a-0397-11e7-aef2-080027d65c57
       COUNT_TRANSACTIONS_IN_QUEUE: 0
        COUNT_TRANSACTIONS_CHECKED: 2
          COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
TRANSACTIONS_COMMITTED_ALL_MEMBERS: 00000000-1111-2222-3333-123456789abc:1-29
    LAST_CONFLICT_FREE_TRANSACTION: 00000000-1111-2222-3333-123456789abc:29
1 row in set (0.00 sec)

CHANNEL名やグループから受信してアプライアキュー(リレーログ)に入れられたトランザクションを確認
(We can confirm Applier Queue and channel names.)

mysql> SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
             CHANNEL_NAME: group_replication_applier
               GROUP_NAME: 00000000-1111-2222-3333-123456789ABC
              SOURCE_UUID: 00000000-1111-2222-3333-123456789ABC
                THREAD_ID: NULL
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
 RECEIVED_TRANSACTION_SET: 00000000-1111-2222-3333-123456789abc:1-29
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************

Enterprise Monitorでの監視例
(How to monitor group replication by using MySQL Enterprise Monitor)
Group Replicationの状態を可視化して、詳細を管理ノードで一元管理する事が可能。
障害発生時は、メール若しくはSNMPでトラップ送信して知らせる事も可能。

グループトポロジー
(Replication Topology and status under normal condition.)

グループトポロジー(障害発生時)
(Replication Topology and status during system trouble.)

グループレプリケーションステータス
(Group Replication Status over view)

その他、詳細情報
(Group Replication Status and other replication related detail information.)

エラーが発生した場合のログ確認
(Group Replication Error Logs)

Nice to watch for catching up with Group Replication on YouTube.

詳細情報:
https://dev.mysql.com/doc/mysql-monitor/3.4/en/mem-replication.html

https://dev.mysql.com/doc/mysql-monitor/3.4/en/mem-replication-dashboard-ui-ref.html#fig-mem-group-replication-topology-single

MySQL Enterprise 試用版のダウンロード
https://www.mysql.com/jp/trials/


MySQLで高可用性構成を実現する為の、新しいフレームワーク “InnoDB Cluster”がGAになりました。
http://mysqlserverteam.com/mysql-innodb-cluster-ga/

複数サーバーを用意して検証する事も出来ますが、手取り早く手元のlocalhost環境で検証したい場合はSandboxモードを利用すると良いでしょう。

詳細: Deploying Sandbox Instances
https://dev.mysql.com/doc/refman/5.7/en/mysql-innodb-cluster-getting-started.html#idc-deploy-sandbox-instances

■ MySQLインスタンスの作成 (3台~9台:奇数構成を推奨しています)
mysqlshコマンドでSandbox用のインスタンスを作成しています。
※ mysqlユーザーで実行すると、ホームディレクトリに以下のようにPort毎にフォルダーが出来ます。
  データファイル、オプションファイル共に以下のフォルダーに配置されます。
  /home/mysql/mysql-sandboxes/ポート番号


-bash-4.2$ /usr/local/mysqlshell/bin/mysqlsh
Welcome to MySQL Shell 1.0.9

Copyright (c) 2016, 2017, 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.deploySandboxInstance(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 shell.connect('root@localhost:3310'); to connect to the instance.

mysql-js> dba.deploySandboxInstance(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 shell.connect('root@localhost:3320'); to connect to the instance.

mysql-js> dba.deploySandboxInstance(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 shell.connect('root@localhost:3330'); to connect to the instance.

mysql-js> 

■ 上記で3台のインスタンスが作成されたので、mysqlshを使って接続して、グループレプリケーションの設定とノードを参加させます。


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> var cluster = dba.createCluster('testCluster')
A new InnoDB cluster will be created on instance 'root@localhost:3310'.

Creating InnoDB cluster 'testCluster' 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.

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.


■ 上記コマンドでグループレプリケーション(シングルマスターモード)が作成されたので、ステータスを確認して見ます。

-bash-4.2$ /usr/local/mysqlshell/bin/mysqlsh
Welcome to MySQL Shell 1.0.9

Copyright (c) 2016, 2017, 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:3310
Creating a Session to 'root@localhost:3310'
Enter password: 
Classic Session successfully established. No default schema selected.
mysql-js> cluster = dba.getCluster()
<Cluster:testCluster>
mysql-js> cluster.status()
{
    "clusterName": "testCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3310", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}
mysql-js> cluster.describe()
{
    "clusterName": "testCluster", 
    "defaultReplicaSet": {
        "instances": [
            {
                "host": "localhost:3310", 
                "label": "localhost:3310", 
                "role": "HA"
            },
            {
                "host": "localhost:3320", 
                "label": "localhost:3320", 
                "role": "HA"
            },
            {
                "host": "localhost:3330", 
                "label": "localhost:3330", 
                "role": "HA"
            }
        ], 
        "name": "default"
    }
}
mysql-js> 

■ グループレプリケーションに接続する為に、bootstrapオプションを利用してmysqlrouterを立ち上げます。
これにより、mysqlにGroup ReplicationのステータスとRouterからの接続をハンドリングする為のメタデータが作成されます。


-bash-4.2$ /home/mysql/mysqlrouter/bin/mysqlrouter --bootstrap root@localhost:3310 
Please enter MySQL password for root: 

Bootstrapping system MySQL Router instance...
MySQL Router  has now been configured for the InnoDB cluster 'testCluster'.

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

Classic MySQL protocol connections to cluster 'testCluster':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447

X protocol connections to cluster 'testCluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470
-bash-4.2$ 

上記オプションで作成されたmysqlrouterのコンフィグレーションファイルは以下の様になっています。


-bash-4.2$ cat /home/mysql/mysql-router-commercial-2.1.3-linux-glibc2.12-x86-64bit/mysqlrouter.conf
# File automatically generated during MySQL Router bootstrap
[DEFAULT]
name=system
keyring_path=/home/mysql/mysql-router-commercial-2.1.3-linux-glibc2.12-x86-64bit/data/keyring
master_key_path=/home/mysql/mysql-router-commercial-2.1.3-linux-glibc2.12-x86-64bit/mysqlrouter.key

[logger]
level = INFO

[metadata_cache:testCluster]
router_id=5
bootstrap_server_addresses=mysql://localhost:3310,mysql://localhost:3320,mysql://localhost:3330
user=mysql_router5_o72w62ds45zk
metadata_cluster=testCluster
ttl=300

[routing:testCluster_default_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://testCluster/default?role=PRIMARY
mode=read-write
protocol=classic

[routing:testCluster_default_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://testCluster/default?role=SECONDARY
mode=read-only
protocol=classic

[routing:testCluster_default_x_rw]
bind_address=0.0.0.0
bind_port=64460
destinations=metadata-cache://testCluster/default?role=PRIMARY
mode=read-write
protocol=x

[routing:testCluster_default_x_ro]
bind_address=0.0.0.0
bind_port=64470
destinations=metadata-cache://testCluster/default?role=SECONDARY
mode=read-only
protocol=x

-bash-4.2$ 

mysql_innodb_cluster_metadata
mysql_innodb_cluster_metadataというInnoDB Clusterのリポジトリーが作成されていて、
MySQL Routerはこのメタデータを参照してサーバーを自動的に切り替えます。


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

mysql> 

■ MySQL Routerを起動します。

-bash-4.2$ /home/mysql/mysqlrouter/bin/mysqlrouter &
[1] 2623
-bash-4.2$ 


■ 接続を確認してみます。シングルマスターモードなので、PRIMARYは常に同じインスタンスに接続しに行きます。

SECONDARYはラウンドロビンで接続先を割り振られます。


-bash-4.2$ cat 5_mysql_status.sh 
#!/bin/sh

/usr/local/mysql/bin/mysql -u root -proot -h 127.0.0.1 -P 6446 -e "select 'R-Port:6446',@@hostname,@@port;"
/usr/local/mysql/bin/mysql -u root -proot -h 127.0.0.1 -P 6447 -e "select 'R-Port:6447',@@hostname,@@port;"
-bash-4.2$ 

-bash-4.2$ ./5_mysql_status.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3330 |
+-------------+--------------+--------+
-bash-4.2$ ./5_mysql_status.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3320 |
+-------------+--------------+--------+

-bash-4.2$ 

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/


MySQL5.7.14のGroup Replication DMR版が先月リリースされていたので、基本的な動作確認をしました。
まだ、DMR版なので前回の5.7.10とまた微妙に異なっていて、
group_replication_peer_addressesがgroup_replication_group_seedsに代わっていたり、
group_replication_recovery_userやgroup_replication_recovery_passwordが無くなっていたりします。

MySQL Group Replication: A Quick Start Guide
http://mysqlhighavailability.com/mysql-group-replication-a-quick-start-guide/

MySQL5.7.10版での検証: MYSQL GROUP REPLICATION
http://variable.jp/2016/07/18/mysql-group-replication/

検証環境のオプションファイル設定
基本的にはserver_idをノード間で変更しているのみ。

# Binary logging and Replication
server_id                      = 1
log_bin                        = mysql-bin
binlog_cache_size              = 1M
binlog_stmt_cache_size         = 1M                                  # Since 5.5
max_binlog_size                = 64M                                 # Make bigger for high traffic to reduce number of files
sync_binlog                    = 0                                   # Set to 1 or higher to reduce potential loss of binary-log data
expire_logs_days               = 30                                  # We will survive easter holidays
#binlog_format                 = MIXED                               # Use MIXED if you experience some troubles
binlog_format                  = ROW
binlog_row_image               = MINIMAL                             # Since 5.6
binlog_rows_query_log_events   = 1                                   # Log Statement in ROW Base Binlog
# auto_increment_increment       = 1                                 # For Master/Master set-ups use 1 and 2
# auto_increment_offset          = 2                                 # For Master/Master set-ups use 2 for both nodes
log_timestamps = SYSTEM


# Add for GTID Mode
gtid-mode                      = on
enforce-gtid-consistency       = on
log-slave-updates

master_info_repository        = TABLE
relay_log_info_repository     = TABLE

# Group replication
# plugin-load = group_replication.so
group_replication_start_on_boot = ON
group_replication_bootstrap_group = OFF
# transaction-write-set-extraction=MURMUR32
transaction-write-set-extraction=XXHASH64
binlog-checksum=NONE
group_replication = FORCE_PLUS_PERMANENT
transaction-write-set-extraction = XXHASH64
group_replication_group_name= "00000000-1111-2222-3333-123456789ABC"
#group_replication_recovery_user='rpl_user'                             #commented out at upgrading to 5.7.14
#group_replication_recovery_password='rpl_pass'                         #commented out at upgrading to 5.7.14
group_replication_recovery_retry_count= 2
group_replication_recovery_reconnect_interval= 120
group_replication_local_address="192.168.56.101:6606"
#group_replication_peer_addresses= "192.168.56.101:18620,192.168.56.102:18620" #commented out at upgrading to 5.7.14
group_replication_group_seeds =  "192.168.56.101:6606,192.168.56.102:6606"

# 5.7.14
relay-log-recovery = ON
slave-parallel-type = LOGICAL_CLOCK
slave-preserve-commit-order = ON
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
slave-type-conversions = ALL_NON_LOSSY
sync-master-info = 1000
sync-relay-log = 1000
slave-parallel-workers = 0

■Boot Strap Nodeの起動

– It will not try and participate in any group communication when starting but will instead configure the group as consisting only of itself.
– Any subsequent member that attempts to join the group will sync itself up with the state of this instance.

We need to pick one member and declare that it is the bootstrap node by setting group_replication_bootstrap_group=ON
Just remember to turn group_replication_bootstrap_group=OFF again after the first member is up.

root@localhost [GR_TEST]> select @@version,@@hostname;
+-----------------------+------------+
| @@version             | @@hostname |
+-----------------------+------------+
| 5.7.14-labs-gr080-log | misc01     |
+-----------------------+------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | OFFLINE      |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> show variables like 'group_replication_bootstrap_group';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| group_replication_bootstrap_group | OFF   |
+-----------------------------------+-------+
1 row in set (0.03 sec)

root@localhost [GR_TEST]> SET GLOBAL group_replication_bootstrap_group= 1;
Query OK, 0 rows affected (0.00 sec)

root@localhost [GR_TEST]> show variables like 'group_replication_bootstrap_group';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| group_replication_bootstrap_group | ON    |
+-----------------------------------+-------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> START GROUP_REPLICATION;
Query OK, 0 rows affected (1.07 sec)

root@localhost [GR_TEST]> SET GLOBAL group_replication_bootstrap_group= 0;
Query OK, 0 rows affected (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> select * from performance_schema.replication_group_member_stats\G
*************************** 1. row ***************************
                      CHANNEL_NAME: group_replication_applier
                           VIEW_ID: 14732520384730018:1
                         MEMBER_ID: 29ea17bc-3848-11e6-9900-0800279ca844
       COUNT_TRANSACTIONS_IN_QUEUE: 0
        COUNT_TRANSACTIONS_CHECKED: 0
          COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
TRANSACTIONS_COMMITTED_ALL_MEMBERS: 
    LAST_CONFLICT_FREE_TRANSACTION: 
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 

■2番目のノード追加
Specify valid MySQL credentials that this node will use when requesting GTIDs from existing members of the
group (a seed or donor) necessary to perform an automated recovery (such as when joining the group, which we’ll do next):
CHANGE MASTER TO MASTER_USER=’rpl_user’, MASTER_PASSWORD=’rpl_pass’ FOR CHANNEL ‘group_replication_recovery’;
Have the new node join to become a member with: STOP GROUP_REPLICATION; START GROUP_REPLICATION; (STOP is necessary because we have start_on_boot enabled).

root@localhost [GR_TEST]>  select @@version,@@hostname;
+-----------------------+------------+
| @@version             | @@hostname |
+-----------------------+------------+
| 5.7.14-labs-gr080-log | misc02     |
+-----------------------+------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 5b07d5d8-4057-11e6-a315-0800279cea3c | misc02      |        3306 | OFFLINE      |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

root@localhost [GR_TEST]> STOP GROUP_REPLICATION;START GROUP_REPLICATION;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (7.04 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | ONLINE       |
| group_replication_applier | 5b07d5d8-4057-11e6-a315-0800279cea3c | misc02      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> select * from performance_schema.replication_group_member_stats\G
*************************** 1. row ***************************
                      CHANNEL_NAME: group_replication_applier
                           VIEW_ID: 14732520384730018:2
                         MEMBER_ID: 5b07d5d8-4057-11e6-a315-0800279cea3c
       COUNT_TRANSACTIONS_IN_QUEUE: 0
        COUNT_TRANSACTIONS_CHECKED: 0
          COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
TRANSACTIONS_COMMITTED_ALL_MEMBERS: 
    LAST_CONFLICT_FREE_TRANSACTION: 
1 row in set (0.00 sec)

root@localhost [GR_TEST]> select user_name, user_password from mysql.slave_master_info where channel_name = 'group_replication_recovery';
+-----------+---------------+
| user_name | user_password |
+-----------+---------------+
| rpl_user  | rpl_pass      |
+-----------+---------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 

■ 追加の確認と設定(NODE2を追加後にBootstrapノードで実行)

You should also now execute step 3 (the CHANGE MASTER TO) on the node we used to bootstrap the group, if you haven’t previously done so.

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | ONLINE       |
| group_replication_applier | 5b07d5d8-4057-11e6-a315-0800279cea3c | misc02      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

root@localhost [GR_TEST]> select user_name, user_password from mysql.slave_master_info where channel_name = 'group_replication_recovery';
+-----------+---------------+
| user_name | user_password |
+-----------+---------------+
| rpl_user  | rpl_pass      |
+-----------+---------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 

以下、基本動作確認

■マルチマスターの基本動作確認 (NODE1とNODE2からINSERT)

NODE1)

root@localhost [GR_TEST]> desc T01;
+--------------+-------------+------+-----+-------------------+-------+
| Field        | Type        | Null | Key | Default           | Extra |
+--------------+-------------+------+-----+-------------------+-------+
| ID           | int(11)     | NO   | PRI | NULL              |       |
| MEMO         | varchar(30) | NO   |     |                   |       |
| created_time | datetime    | YES  |     | CURRENT_TIMESTAMP |       |
+--------------+-------------+------+-----+-------------------+-------+
3 rows in set (0.01 sec)

root@localhost [GR_TEST]> insert into T01(ID,MEMO) values(1,@@hostname);
Query OK, 1 row affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
+----+--------+---------------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE2)

root@localhost [GR_TEST]> select * from T01;
Empty set (0.00 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
+----+--------+---------------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> insert into T01(ID,MEMO) values(2,@@hostname);
Query OK, 1 row affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
+----+--------+---------------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]>

blog2

■トランザクションの確認
NODE1)

root@localhost [GR_TEST]> start transaction;insert into T01(ID,MEMO) values(3,@@hostname);
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

root@localhost [GR_TEST]> commit;
Query OK, 0 rows affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE2) 上記を実行中に同じテーブルに対してINSERTは問題ない。

root@localhost [GR_TEST]> start transaction;insert into T01(ID,MEMO) values(4,@@hostname);
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
3 rows in set (0.00 sec)

root@localhost [GR_TEST]> commit;
Query OK, 0 rows affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

■ 同じ行を更新してみる(where ID=4)
NODE1)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> start transaction;update T01 set MEMO = 'MISC02' where ID = 4;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | MISC02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> commit;
Query OK, 0 rows affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | MISC02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> start transaction;update T01 set MEMO = 'misc02' where ID = 4;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [GR_TEST]> commit;
Query OK, 0 rows affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE2) NODE1のトランザクション中に実行。
※トランザクション発行の順番では無く、先にcommitした方が優先される。

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> start transaction;update T01 set MEMO = 'MISC01' where ID = 4;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | MISC01 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> select * from T01;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
root@localhost [GR_TEST]> commit;
Query OK, 0 rows affected (0.00 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | MISC02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | MISC02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> start transaction;update T01 set MEMO = 'MISC01' where ID = 4;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [GR_TEST]> commit;
ERROR 1180 (HY000): Got error 149 during COMMIT
root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]>