MySQL8.0のヒント句が便利になっていたので基本動作確認。
バッチ処理前にSETコマンドで動的に設定していたセッション変数を実行するクエリーにヒントとして追加する事が出来るようです。

WL#681: Hint to temporarily set session variable for current statement
https://dev.mysql.com/worklog/task/?id=681

Syntax of the SET_VAR hint is:


 /*+ SET_VAR( = ) */

大量のデータをOrder by, Group by等でソート処理していて、Sort_merge_passesが多発している場合にソートバッファーの不足をヒント句で回避してディスクI/Oの発生を抑える事が出来る。
大量データのソートが多いバッチ処理などに組み込んでおくと、処理が早く終わらせる事が出来る。
(例)


mysql> SELECT /*+ SET_VAR(sort_buffer_size = 4M) */ name FROM city ORDER BY name;


実際にデータを準備してsort_buffer_sizeヒント句を検証した結果
Duration: 12.71 sec → 6.45 sec
sort_merge_passes: 216 → 0


mysql> select count(*) from ( select v.name, count(*) from t_group g join tbnode n on g.groupid = n.groupid join t_virual v on n.nodeid = v.nodeid group by v.name with rollup) t;
+----------+
| count(*) |
+----------+
|     1001 |
+----------+
1 row in set (12.71 sec)

mysql> select * from sys.statement_analysis limit 1\G
*************************** 1. row ***************************
            query: SELECT COUNT ( * ) FROM ( SELE ... v` . `name` WITH ROLLUP ) `t` 
               db: PERF
        full_scan: *
       exec_count: 1
        err_count: 0
       warn_count: 0
    total_latency: 12.71 s
      max_latency: 12.71 s
      avg_latency: 12.71 s
     lock_latency: 1.47 ms
        rows_sent: 1
    rows_sent_avg: 1
    rows_examined: 3102001
rows_examined_avg: 3102001
    rows_affected: 0
rows_affected_avg: 0
       tmp_tables: 2
  tmp_disk_tables: 0
      rows_sorted: 1000000
sort_merge_passes: 216
           digest: 1e941ce49e8f810963a8468995dc2eaf
       first_seen: 2017-09-28 20:29:46.768069
        last_seen: 2017-09-28 20:29:46.768069
1 row in set (0.01 sec)

mysql> 


mysql> CALL sys.ps_truncate_all_tables(FALSE);
+---------------------+
| summary             |
+---------------------+
| Truncated 49 tables |
+---------------------+
1 row in set (0.17 sec)

Query OK, 0 rows affected (0.18 sec)

mysql> select  /*+ SET_VAR(sort_buffer_size = 16M) */ count(*) from (select v.name, count(*) from t_group g join tbnode n on g.groupid = n.groupid join t_virual v on n.nodeid = v.nodeid  group by v.name with rollup) t;
+----------+
| count(*) |
+----------+
|     1001 |
+----------+
1 row in set (6.45 sec)

mysql> select * from sys.statement_analysis limit 1\G
*************************** 1. row ***************************
            query: SELECT /*+ SET_VAR ( `sort_buf ... v` . `name` WITH ROLLUP ) `t` 
               db: PERF
        full_scan: *
       exec_count: 1
        err_count: 0
       warn_count: 0
    total_latency: 9.94 s
      max_latency: 9.94 s
      avg_latency: 9.94 s
     lock_latency: 406.00 us
        rows_sent: 1
    rows_sent_avg: 1
    rows_examined: 3102001
rows_examined_avg: 3102001
    rows_affected: 0
rows_affected_avg: 0
       tmp_tables: 2
  tmp_disk_tables: 0
      rows_sorted: 1000000
sort_merge_passes: 0
           digest: bdf5d04f4574930fef467d6c359c49bf
       first_seen: 2017-09-28 20:37:42.923895
        last_seen: 2017-09-28 20:37:42.923895
1 row in set (0.01 sec)

使いどころが難しいが、以下のようにauto_increment_increment等の値もヒント句で変更出来るようです。


mysql> CREATE TABLE `T_SET_VAR` (
    ->   `id` int(10) NOT NULL AUTO_INCREMENT,
    ->   `memo` varchar(100) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.18 sec)

mysql> insert into T_SET_VAR(memo) values('auto_increment_increment test');
Query OK, 1 row affected (0.08 sec)

mysql> insert into T_SET_VAR(memo) values('auto_increment_increment test2');
Query OK, 1 row affected (0.03 sec)

mysql> select * from T_SET_VAR;                                                                                                  
+----+--------------------------------+
| id | memo                           |
+----+--------------------------------+
|  1 | auto_increment_increment test  |
|  2 | auto_increment_increment test2 |
+----+--------------------------------+
2 rows in set (0.00 sec)

mysql> select * from T_SET_VAR;                                             
+----+--------------------------------+
| id | memo                           |
+----+--------------------------------+
|  1 | auto_increment_increment test  |
|  2 | auto_increment_increment test2 |
+----+--------------------------------+
2 rows in set (0.00 sec)

mysql> insert /*+ SET_VAR(auto_increment_increment = 10) */ into T_SET_VAR(memo) values('auto_increment_increment test3?');
Query OK, 1 row affected (0.03 sec)

mysql> select * from T_SET_VAR;                                                                                            
+----+---------------------------------+
| id | memo                            |
+----+---------------------------------+
|  1 | auto_increment_increment test   |
|  2 | auto_increment_increment test2  |
| 11 | auto_increment_increment test3? |
+----+---------------------------------+
3 rows in set (0.00 sec)

その他、Worklogによると以下のように、色々なセッション変数をSETコマンドを利用しないでヒント句で指定出来るようです。

List of settable variables:
———————————————————————————-
auto_increment_increment
auto_increment_offset
big_tables
bulk_insert_buffer_size
default_tmp_storage_engine
div_precision_increment
end_markers_in_json
eq_range_index_dive_limit
foreign_key_checks
group_concat_max_len
insert_id
internal_tmp_mem_storage_engine
join_buffer_size
lock_wait_timeout
max_error_count
max_execution_time
max_heap_table_size
max_join_size
max_length_for_sort_data
max_points_in_geometry
max_seeks_for_key
max_sort_length
optimizer_prune_level
optimizer_search_depth variables
optimizer_switch
range_alloc_block_size
range_optimizer_max_mem_size
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
sql_auto_is_null
sql_big_selects
sql_buffer_result
sql_mode
sql_safe_updates
sql_select_limit
timestamp
tmp_table_size
updatable_views_with_limit
unique_checks
windowing_use_high_precision
———————————————————————————-

参照: The MySQL 8.0.3 Release Candidate is available


MySQL8.0 RCのDockerイメージがリリースされていたので、今後の検証やデモ用に設定しました。
手軽に検証出来るので、軽く検証するにはお勧めです。

Docker Image: https://github.com/mysql/mysql-docker


[root@DockerHost oracle]# docker pull mysql/mysql-server:8.0
8.0: Pulling from mysql/mysql-server
323fb8f65502: Pull complete 
b2a15600aac3: Pull complete 
a1116f4203e9: Pull complete 
8be6f234356c: Pull complete 
a09590e34bdc: Pull complete 
554cdb588e9e: Pull complete 
851fce189663: Pull complete 
ca60670c6cb3: Pull complete 
98a8195f4fc5: Pull complete 
ec8c0ade6c51: Pull complete 
73919c529833: Pull complete 
285b77036a3a: Pull complete 
270395aafb1e: Pull complete 
Digest: sha256:183772d6f5a1decd1eb0252e542d338a5ef8c02fe4cc2cc909b58788f8728c58
Status: Downloaded newer image for mysql/mysql-server:8.0
[root@DockerHost oracle]#

[root@DockerHost oracle]# docker run --name mysql83 -v /docker/docker83:/var/lib/mysql -v /docker/option83:/etc/mysql/conf.d -v /docker/init_script:/docker-entrypoint-initdb.d -e MYSQL_ROOT_PASSWORD=mysql -d mysql/mysql-server:8.0
ecd2156cdd36d735b5d01f6d7b89ea24cc7d499cbc59e1014bc42ba92c764365
[root@DockerHost oracle]# 

[root@DockerHost oracle]# docker exec -it mysql83 mysql --default-character-set=utf8mb4 -uroot -pmysql
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 7
Server version: 8.0.3-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 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;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@version;
+--------------+
| @@version    |
+--------------+
| 8.0.3-rc-log |
+--------------+
1 row in set (0.00 sec)

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.03 sec)

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


メモ:CTEの確認

mysql> WITH RECURSIVE  
    -> emp_ext (id, name, path) AS ( 
    ->    SELECT id, name, CAST(id AS CHAR(200)) 
    ->    FROM employees WHERE manager_id IS NULL 
    ->  UNION ALL 
    ->    SELECT s.id, s.name,CONCAT(m.path, ",", s.id) 
    ->    FROM emp_ext m JOIN  employees s ON m.id=s.manager_id )
    -> SELECT * FROM emp_ext ORDER BY path; 
+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+
7 rows in set (0.00 sec)

mysql> 

メモ:Windows Functionの確認


mysql> select employee,date,sale,SUM(sale)
    -> OVER (PARTITION BY employee) AS sum FROM sales;
+----------+------------+------+------+
| employee | date       | sale | sum  |
+----------+------------+------+------+
| A        | 2017-03-01 |  200 |  900 |
| A        | 2017-04-01 |  300 |  900 |
| A        | 2017-05-01 |  400 |  900 |
| B        | 2017-03-01 |  400 | 1200 |
| B        | 2017-04-01 |  300 | 1200 |
| B        | 2017-05-01 |  500 | 1200 |
| C        | 2017-03-01 |  100 | 1000 |
| C        | 2017-04-01 |  600 | 1000 |
| C        | 2017-05-01 |  300 | 1000 |
+----------+------------+------+------+
9 rows in set (0.00 sec)

mysql> 

MySQL8.0には管理者と開発者にとって使い易い機能や関数も増えているので、
色々な場面で活用する事が出来るかと思います。

詳細情報:
http://mysqlserverteam.com/

バグ報告:
https://bugs.mysql.com/

ブログにはRC1と書いてあったけど、RC2とかもリリース予定なのかな?
Please enjoy it.


MySQL8.0がリリース候補版になりました。

Changes in MySQL 8.0.3 (2017-09-21, Release Candidate)
リリースノート:https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-3.html

【主な変更点】
Histograms, Force Index, Hints, Invisible Indexes
Common Table Expressions, Windows Functions, Character Sets,
JSON, GIS, Resource Groups, Performance Schema, Security,
Protocol, Service Infrastructure, X Protocol / X Plugin,
Performance, Tablespaces, DDL, Replication, Group Replication,
Data Dictionary, MTR Tests, Library Upgrade, Changes to Defaults

詳細:http://mysqlserverteam.com/the-mysql-8-0-3-release-candidate-is-available/

全てをカバーしてませんが、先日のセミナー資料で概要を紹介しています。


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/


MySQL5.7で約20程のJSON関数が追加されていましたが、MySQL8.0においても更に追加でJSON関数が加えられているので、基本的な動作のみを確認しています。JSON_ARRAYAGG(), JSON_OBJECTAGG(),JSON_PRETTY()

MySQL5.7 JSON関数マニュアル
https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

MySQL5.7のJSONの概要に関しては、こちらにて資料がダウンロード可能です。https://www.mysql.com/jp/why-mysql/presentations/mysql-json-201701-ja/

まだ、MySQL8.0はDMRなので、これからまだ仕様が変わる部分がある事はご了承ください。

確認バージョン


mysql> select now(),@@version;     
+---------------------+-----------+
| now()               | @@version |
+---------------------+-----------+
| 2017-05-31 19:50:46 | 8.0.1-dmr |
+---------------------+-----------+
1 row in set (0.00 sec)

mysql> 

WL#7987 : JSON aggregation functions
https://dev.mysql.com/worklog/task/?id=7987

Add aggregation functions to generate JSON arrays and objects. This makes it possible to combine JSON documents in multiple rows into a JSON array or a JSON object.

mysql> select body from T_JSON_DOC where id in (1,2);              
+---------------------------------------------------------------------------------+
| body                                                                            |
+---------------------------------------------------------------------------------+
| {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} |
| {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}         |
+---------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> select JSON_ARRAYAGG(body) from T_JSON_DOC where id in(1,2);
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_ARRAYAGG(body)                                                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}] |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> select id,body from T_JSON_DOC where id in(1,2);            
+----+---------------------------------------------------------------------------------+
| id | body                                                                            |
+----+---------------------------------------------------------------------------------+
|  1 | {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} |
|  2 | {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}         |
+----+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC where id in(1,2);
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_OBJECTAGG(id,body)                                                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select id,body from T_JSON_DOC;                                                
+----+---------------------------------------------------------------------------------+
| id | body                                                                            |
+----+---------------------------------------------------------------------------------+
|  1 | {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} |
|  2 | {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}         |
|  3 | {"id": 3, "name": "", "price": 18198, "Conditions": ["NEW", 2015]}              |
|  4 | {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}             |
|  5 | {"id": 5, "name": "", "price": 25000, "Conditions": ["NEW", 2015, "January"]}   |
+----+---------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC group by body->"$.Conditions[0]"\G
*************************** 1. row ***************************
JSON_OBJECTAGG(id,body): {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "3": {"id": 3, "name": "", "price
": 18198, "Conditions": ["NEW", 2015]}, "4": {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}, "5": {"id": 5, "name": "", "pri
ce": 25000, "Conditions": ["NEW", 2015, "January"]}}
*************************** 2. row ***************************
JSON_OBJECTAGG(id,body): {"2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}}
2 rows in set (0.01 sec)

mysql> mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC group by body->"$.Conditions[1]"\G
*************************** 1. row ***************************
JSON_OBJECTAGG(id,body): {"2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}}
*************************** 2. row ***************************
JSON_OBJECTAGG(id,body): {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "3": {"id": 3, "name": "", "price": 18198, "Conditions": ["NEW", 2015]}, "4": {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}, "5": {"id": 5, "name": "", "price": 25000, "Conditions": ["NEW", 2015, "January"]}}
2 rows in set (0.00 sec)

mysql>
 

WL#9191: JSON_PRETTY function
https://dev.mysql.com/worklog/task/?id=9191

User Feedback from presenting JSON features has suggested that we are missing a function to format JSON in a human-readable way (with new lines and indentation).

This functionality is available in both PHP and PostgreSQL under the name “pretty”:


mysql> select body from T_JSON_DOC where id = 1\G             
*************************** 1. row ***************************
body: {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}
1 row in set (0.00 sec)

mysql> select json_pretty(body) from T_JSON_DOC where id = 1\G
*************************** 1. row ***************************
json_pretty(body): {
  "id": 1,
  "name": "",
  "price": 10000,
  "Conditions": [
    "NEW",
    2015,
    "Excellent"
  ]
}
1 row in set (0.00 sec)

mysql> 

その他のJSON関数(運用向け)

JSON_STORAGE_FREE(json_val)
For a JSON column value, this function shows how much storage space was freed in its binary representation after it was updated in place using JSON_SET() or JSON_REPLACE(). The argument can also be a valid JSON document or a string which can be parsed as one—either as a literal value or as the value of a user variable—in which case the function returns 0.

※Updating the column without using JSON_SET() (or JSON_REPLACE()) means that the optimizer cannot perform the update in place; in this case, JSON_STORAGE_FREE() returns 0.

JSON_STORAGE_SIZE(json_val)
This function returns the number of bytes used to store the binary representation of a JSON document.


mysql> SELECT 
    ->     jcol, 
    ->     JSON_STORAGE_SIZE(jcol) AS Size, 
    ->     JSON_STORAGE_FREE(jcol) AS Free 
    -> FROM jtable;

JSON_STORAGE_SIZE(): Return value (bytes)
JSON_STORAGE_FREE(): If no updates have yet been performed, this is 0, as expected.


SELECT
    ->     JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
    ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
    ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C, 
    ->     JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;


InnoDB Clusterの設定を行い、Group ReplicationはシングルマスターモードがDefaultなので、
Auto_Incrementの値も普段使いなれている値の1に設定し直して利用する事にしました。

Note: InnoDB Cluster = MySQL Group Replication + MySQL Router + MySQL Shell

もし、Group Replicationをシングルマスターモードで利用する予定の場合は、
実際にサーバーの初期設定時の段階で予め変更しておくと良いかと思います。
マルチマスターモードの場合は、ぶつからないように設定しておく必要があるので、
先ずは、Default設定の7で利用するのが良いでしょう。

念の為、構成がシングルマスターモードになっているか確認
Confirm is Group Replication configured as Single Master Mode.


mysql> show variables like 'group_replication_single_primary_mode';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | ON    |
+---------------------------------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'group_replication_enforce_update_everywhere_checks';
+----------------------------------------------------+-------+
| Variable_name                                      | Value |
+----------------------------------------------------+-------+
| group_replication_enforce_update_everywhere_checks | OFF   |
+----------------------------------------------------+-------+
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 | bc653b5a-3b8b-11e7-94cd-080027d65c57 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> 

現在のグループ構成
Current Group Configuration.


-bash-4.2$ ./2_gr_status.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| 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       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
-bash-4.2$ 

Enterprise Monitorのレプリケーショントポロジービューでの確認

DefaultでAuto_Incrementが7になっている事も確認出来る

Group Replication設定時のDefault値
マルチマスターモードであれば、このまま利用するが個人的には、
auto_incrementの値は1つずつ増えて欲しいので通常のMySQLの設定に変更。

mysql> show variables like '%increment%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| auto_increment_increment                   | 7     |
| auto_increment_offset                      | 1     |
| div_precision_increment                    | 4     |
| group_replication_auto_increment_increment | 7     |
| innodb_autoextend_increment                | 64    |
+--------------------------------------------+-------+
5 rows in set (0.00 sec)

値を変更:group_replication_auto_increment_increment=1
Change group_replication_auto_increment_increment for change auto increment vaule will be + 1.


mysql> show variables like '%increment%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| auto_increment_increment                   | 1     |
| auto_increment_offset                      | 1     |
| div_precision_increment                    | 4     |
| group_replication_auto_increment_increment | 1     |
| innodb_autoextend_increment                | 64    |
+--------------------------------------------+-------+
5 rows in set (0.01 sec)

設定変更後はMySQL Enterprise Monitorで確認しても、もちろんauto_incrementの値が1になっている

設定変更後のauto_incrementの動作確認
Confirm after change configuration.


mysql> CREATE TABLE `T_MEMO` (
    -> `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    -> `comment` varchar(100) NOT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.39 sec)

mysql> insert into T_MEMO(comment) values('Change group_replication_auto_increment_increment from 7 to 1');
Query OK, 1 row affected (0.77 sec)

mysql> select * from T_MEMO;
+----+---------------------------------------------------------------+
| id | comment                                                       |
+----+---------------------------------------------------------------+
|  1 | Change group_replication_auto_increment_increment from 7 to 1 |
+----+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into T_MEMO(comment) values('Change id 1 possible only on Singale Master Mode');
Query OK, 1 row affected (0.11 sec)

mysql> select * from T_MEMO;
+----+---------------------------------------------------------------+
| id | comment                                                       |
+----+---------------------------------------------------------------+
|  1 | Change group_replication_auto_increment_increment from 7 to 1 |
|  2 | Change id 1 possible only on Singale Master Mode              |
+----+---------------------------------------------------------------+
2 rows in set (0.00 sec)

【メモ】シングルマスターモードなので、トランザクション分離レベルはREPEATABLE-READのままでOK。
Node: Since this is single master mode, user can keep transactiton isolation level as REPEATABLE-READ.


mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)


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/


MySQL8.0からのパーティションについての確認
MySQL5.7からは、InnoDB Native Partitioningがサポートされるようになり、多くパーティションを利用している環境で、メモリーの利用率を大幅に下げる事が出来るようになっている。
MySQL8.0からは、Partition Storage Engineがディフォルトでロードされなくなるので注意を促す為に、MySQL5.7.17から”–disable-partition-engine-check”を設定して下さいというWarningがエラーログに出るようになっています。

yoku0825さんが速攻で確認して、ブログを書かれているので確認してみて下さい。

【InnoDB Native Partitioningに関して】
http://mysqlserverteam.com/innodb-native-partitioning-early-access/

【抜粋】

WL#6035: Add native partitioning support to InnoDB
https://dev.mysql.com/worklog/task/?id=6035

WL#8971: Deprecate and remove partitioning storage engine
https://dev.mysql.com/worklog/task/?id=8971

MySQL5.7からは、InnoDB Native Paritioningがサポートされている。
そして、MySQL5.7までは、partition Storage EngineもまだDefaultで含まれている。

MySQL5.7.17で確認すると以下のような感じ。


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

mysql> SELECT
    -> PLUGIN_NAME as Name,
    -> PLUGIN_VERSION as Version,
    -> PLUGIN_STATUS as Status
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_TYPE='STORAGE ENGINE';
+--------------------+---------+----------+
| Name               | Version | Status   |
+--------------------+---------+----------+
| binlog             | 1.0     | ACTIVE   |
| MyISAM             | 1.0     | ACTIVE   |
| PERFORMANCE_SCHEMA | 0.1     | ACTIVE   |
| InnoDB             | 5.7     | ACTIVE   |
| MRG_MYISAM         | 1.0     | ACTIVE   |
| MEMORY             | 1.0     | ACTIVE   |
| CSV                | 1.0     | ACTIVE   |
| BLACKHOLE          | 1.0     | DISABLED |
| partition          | 1.0     | ACTIVE   |
| FEDERATED          | 1.0     | DISABLED |
| ARCHIVE            | 3.0     | DISABLED |
+--------------------+---------+----------+
11 rows in set (0.00 sec)

mysql> CREATE TABLE T_PARTITION (c1 int)
    -> PARTITION BY RANGE COLUMNS(c1) 
    -> (
    -> PARTITION p1 VALUES LESS THAN (5),
    -> PARTITION p2 VALUES LESS THAN (10),
    -> PARTITION p3 VALUES LESS THAN (MAXVALUE)
    -> );

Query OK, 0 rows affected (0.45 sec)

mysql> select TABLE_NAME,PARTITION_NAME,PARTITION_METHOD,PARTITION_DESCRIPTION from information_schema.partitions where table_name = 'T_PARTITION';
+-------------+----------------+------------------+-----------------------+
| TABLE_NAME  | PARTITION_NAME | PARTITION_METHOD | PARTITION_DESCRIPTION |
+-------------+----------------+------------------+-----------------------+
| T_PARTITION | p1             | RANGE COLUMNS    | 5                     |
| T_PARTITION | p2             | RANGE COLUMNS    | 10                    |
| T_PARTITION | p3             | RANGE COLUMNS    | MAXVALUE              |
+-------------+----------------+------------------+-----------------------+
3 rows in set (0.00 sec)

mysql> 

MySQL5.7までは、まだメタデータのFRMファイルも作成されている。

[oracle@shinya01-mysql-1 mydatabase]$ ls -l T_PART*
-rw-r----- 1 oracle oracle  8556 Apr 20 04:01 T_PARTITION.frm
-rw-r----- 1 oracle oracle 98304 Apr 20 04:01 T_PARTITION#P#p1.ibd
-rw-r----- 1 oracle oracle 98304 Apr 20 04:01 T_PARTITION#P#p2.ibd
-rw-r----- 1 oracle oracle 98304 Apr 20 04:01 T_PARTITION#P#p3.ibd
[oracle@shinya01-mysql-1 mydatabase]$

MySQL8.0.1の段階で、既にpartition storage engineは含まれていない。

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.1-dmr |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT
    -> PLUGIN_NAME as Name,
    -> PLUGIN_VERSION as Version,
    -> PLUGIN_STATUS as Status
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_TYPE='STORAGE ENGINE';
+--------------------+---------+----------+
| Name               | Version | Status   |
+--------------------+---------+----------+
| binlog             | 1.0     | ACTIVE   |
| CSV                | 1.0     | ACTIVE   |
| MEMORY             | 1.0     | ACTIVE   |
| InnoDB             | 8.0     | ACTIVE   |
| MyISAM             | 1.0     | ACTIVE   |
| MRG_MYISAM         | 1.0     | ACTIVE   |
| PERFORMANCE_SCHEMA | 0.1     | ACTIVE   |
| ARCHIVE            | 3.0     | ACTIVE   |
| BLACKHOLE          | 1.0     | ACTIVE   |
| FEDERATED          | 1.0     | DISABLED |
+--------------------+---------+----------+
10 rows in set (0.00 sec)

mysql> CREATE TABLE T_PARTITION (c1 int)
    -> PARTITION BY RANGE COLUMNS(c1) 
    -> (
    -> PARTITION p1 VALUES LESS THAN (5),
    -> PARTITION p2 VALUES LESS THAN (10),
    -> PARTITION p3 VALUES LESS THAN (MAXVALUE)
    -> );
Query OK, 0 rows affected (0.25 sec)

mysql> select TABLE_NAME,PARTITION_NAME,PARTITION_METHOD,PARTITION_DESCRIPTION from information_schema.partitions where table_name = 'T_PARTITION';
+-------------+----------------+------------------+-----------------------+
| TABLE_NAME  | PARTITION_NAME | PARTITION_METHOD | PARTITION_DESCRIPTION |
+-------------+----------------+------------------+-----------------------+
| T_PARTITION | p1             | RANGE COLUMNS    | 5                     |
| T_PARTITION | p2             | RANGE COLUMNS    | 10                    |
| T_PARTITION | p3             | RANGE COLUMNS    | MAXVALUE              |
+-------------+----------------+------------------+-----------------------+
3 rows in set (0.00 sec)

mysql> 

また、MySQL8.0からはfrmがテーブルに格納されるのでファイルは無い。

[root@DockerHost sakila]# ls -l T_PART*
-rw-r----- 1 27 27 131072 Apr 20 04:02 T_PARTITION#P#p1.ibd
-rw-r----- 1 27 27 131072 Apr 20 04:02 T_PARTITION#P#p2.ibd
-rw-r----- 1 27 27 131072 Apr 20 04:02 T_PARTITION#P#p3.ibd
[root@DockerHost sakila]# 

MySQL8.0でNDB,InnoDB以外でパーティションを作成しようとすると以下のようにエラーになる。

mysql> CREATE TABLE `T_PARTITION_MyISAM` (
    ->   `c1` int(11) DEFAULT NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
    -> /*!50500 PARTITION BY RANGE  COLUMNS(c1)
    -> (PARTITION p1 VALUES LESS THAN (5) ENGINE = MyISAM,
    ->  PARTITION p2 VALUES LESS THAN (10) ENGINE = MyISAM,
    ->  PARTITION p3 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */;
ERROR 1178 (42000): The storage engine for the table doesn't support native partitioning
mysql>

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$