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$ 

MySQL8.0から、Descending Indexがサポートされる。
これによりMySQL5.7と比較して、大きなテーブルでの降順(DESC)のデータ参照処理を、高速に実行する事が出来るようになる。小さいテーブルに関しては、既存のMySQL5.7でもインデックスが利用出来るので、それ程差は出ないかと思います。

MySQL8.0.1で、Sakila Sampleデータベースのrentalテーブルを参照した場合 (INDEXはASCで作成されている)


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

mysql> show create table rental\G                                     
*************************** 1. row ***************************
       Table: rental
Create Table: CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from rental order by rental_date asc limit 3; 
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date asc limit 3; 
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from rental order by rental_date desc limit 3;
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
|     11739 | 2006-02-14 15:16:03 |         4568 |         373 | NULL        |        2 | 2006-02-15 21:30:53 |
|     14616 | 2006-02-14 15:16:03 |         4537 |         532 | NULL        |        1 | 2006-02-15 21:30:53 |
|     11676 | 2006-02-14 15:16:03 |         4496 |         216 | NULL        |        2 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date desc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra               |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | Backward index scan |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 

MySQL8.0.1で、Sakila Sampleデータベースのrentalテーブルを参照した場合 (INDEXをDESCで作成し直した場合)
インデックスを降順に作成したので、先程とはEXTRAが反対になっている事が確認出来る。

mysql> alter table rental add unique key rental_date_desc (`rental_date` desc,`inventory_id` desc,`customer_id` desc);
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from rental order by rental_date asc limit 3; 
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date asc limit 3;                                                
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+---------------------+
| id | select_type | table  | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra               |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+---------------------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date_desc | 10      | NULL |    3 |   100.00 | Backward index scan |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+---------------------+
1 row in set, 1 warning (0.01 sec)

mysql> select * from rental order by rental_date desc limit 3;
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
|     11739 | 2006-02-14 15:16:03 |         4568 |         373 | NULL        |        2 | 2006-02-15 21:30:53 |
|     14616 | 2006-02-14 15:16:03 |         4537 |         532 | NULL        |        1 | 2006-02-15 21:30:53 |
|     11676 | 2006-02-14 15:16:03 |         4496 |         216 | NULL        |        2 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date desc limit 3;                                               
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date_desc | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> 

詳細は、以下のMySQL Server Teamのブログを確認下さい。
MySQL 8.0 Labs – Descending Indexes in MySQL
http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/

MySQL5.5 ~ MySQL5.7までは昇順(ASC)で格納されたインデックスデータを利用してASC, DESC共に参照処理を行う
MySQL 5.7までは、インデックスのデータはASCで格納されています。こちらは、DESCで格納したインデックスと比べて、降順のデータの参照パフォーマンスは遅いが、
クエリーにDESCオプションを付けて、直近のデータを参照するとASCで作成したINDEXを利用してデータを参照する為、インデックスを利用した後方参照処理になる為、
インデックスが無い場合と比べても高速なレスポンスで後方参照処理を行う事が出来る。以下のマニュアルは、若干分かり難いがASC、DESCを付けてインデックスを作成しても、
MySQL5.5以降のMySQLであれば昇順(ASC)で作成されたINDEXでASCもDESCも処理出来る為、高速に参照する事が可能です。MySQL8.0で処理がより高速になる。

5.7 英語マニュアル
https://dev.mysql.com/doc/refman/5.7/en/create-index.html
An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

5.6 日本語マニュアル
https://dev.mysql.com/doc/refman/5.6/ja/create-index.html
index_col_name の指定を ASC または DESC で終了させることができます。これらのキーワードは、インデックス値の昇順または降順での格納を指定する将来の拡張のために許可されています。現在、これらは解析されますが、無視されます。インデックス値は、常に昇順で格納されます。

MySQL5.7.18の実行プランは以下のような感じです。インデックスを利用して降順(DESC)参照が行われている事が確認出来る。
MySQL8.0RCがリリースされた頃に、大きなテーブルでレスポンスの差を確認して見たいと思います。


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

mysql> show create table rental\G
*************************** 1. row ***************************
       Table: rental
Create Table: CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

mysql> select * from rental limit 3;
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental limit 3;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16005 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.02 sec)

Note (Code 1003): /* select#1 */ select `sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from `sakila`.`rental` limit 3

mysql> select * from rental order by rental_date asc limit 3;
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date asc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)

Note (Code 1003): /* select#1 */ select `sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from `sakila`.`rental` order by `sakila`.`rental`.`rental_date` limit 3

mysql> select * from rental order by rental_date desc limit 3;
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
|     11739 | 2006-02-14 15:16:03 |         4568 |         373 | NULL        |        2 | 2006-02-15 21:30:53 |
|     14616 | 2006-02-14 15:16:03 |         4537 |         532 | NULL        |        1 | 2006-02-15 21:30:53 |
|     11676 | 2006-02-14 15:16:03 |         4496 |         216 | NULL        |        2 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date desc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

Note (Code 1003): /* select#1 */ select `sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from `sakila`.`rental` order by `sakila`.`rental`.`rental_date` desc limit 3
mysql>  


MySQL8.0におけるROLEによるユーザー権限管理についての確認。
MySQL5.7からProxy Userを利用する事で、ROLEと同じように権限をまとめて管理する事が出来るようになりましたが、MySQL8.0からは正式にROLEが追加されて、複数ROLEの切り替えやROLEのネストが出来るようになります。これにより、多くのユーザーを管理するような環境では、権限管理工数やミスが削減出来る事になるかと思います。

7.3.4 Using Roles
https://dev.mysql.com/doc/refman/8.0/en/roles.html

ROLEの作成とROLEへの権限付与

mysql> CREATE ROLE role80;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL on DEMO.* TO role80;
Query OK, 0 rows affected (0.05 sec)

mysql> create user user01 identified by 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> select host, user, authentication_string from mysql.user;
+-----------+-----------+-------------------------------------------+
| host      | user      | authentication_string                     |
+-----------+-----------+-------------------------------------------+
| %         | role80    |                                           |
| %         | root      | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| %         | user01    | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+-----------+-------------------------------------------+
4 rows in set (0.02 sec)

USER01にrole80の権限を付与
ROLE権限を付与する前は、何も権限を付与していない為、information_schemaのみ確認可能。

[root@DockerHost oracle]# docker exec -it mysql8 mysql -uuser01 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 8.0.0-dmr 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 |
+--------------------+
1 row in set (0.00 sec)

mysql> grant role80 to user01;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from mysql.role_edges;
+-----------+-----------+---------+---------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+---------+---------+-------------------+
| %         | role80    | %       | user01  | N                 |
+-----------+-----------+---------+---------+-------------------+
1 row in set (0.06 sec)

mysql> select * from mysql.default_roles;
Empty set (0.00 sec)

mysql> 

ユーザーのDEFAULT ROLEを設定しログイン時に反映させる


mysql> alter user user01 default role role80;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from mysql.default_roles;    
+------+--------+-------------------+-------------------+
| HOST | USER   | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+--------+-------------------+-------------------+
| %    | user01 | %                 | role80            |
+------+--------+-------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

権限が継承されているかログインして確認してみる


[root@DockerHost oracle]# docker exec -it mysql8 mysql -uuser01 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.0-dmr 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           |
+--------------------+
| DEMO               |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql> select user(),current_user(),current_role(); 
+------------------+----------------+----------------+
| user()           | current_user() | current_role() |
+------------------+----------------+----------------+
| user01@localhost | user01@%       | `role80`@`%`   |
+------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> show grants;
+--------------------------------------------------+
| Grants for user01@%                              |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `user01`@`%`               |
| GRANT ALL PRIVILEGES ON `DEMO`.* TO `user01`@`%` |
| GRANT `role80`@`%` TO `user01`@`%`               |
+--------------------------------------------------+
3 rows in set (0.00 sec)

mysql> show grants for user01; 
+------------------------------------+
| Grants for user01@%                |
+------------------------------------+
| GRANT USAGE ON *.* TO `user01`@`%` |
| GRANT `role80`@`%` TO `user01`@`%` |
+------------------------------------+
2 rows in set (0.01 sec)

mysql> show grants for user01 using role80;
+--------------------------------------------------+
| Grants for user01@%                              |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `user01`@`%`               |
| GRANT ALL PRIVILEGES ON `DEMO`.* TO `user01`@`%` |
| GRANT `role80`@`%` TO `user01`@`%`               |
+--------------------------------------------------+
3 rows in set (0.00 sec)

mysql>  

補足:Default Roleを設定しない場合
確認用アカウントuser02を作成


mysql> create user user02 identified by 'password';                                                              
Query OK, 0 rows affected (0.07 sec)

mysql> grant role80 to user02;
Query OK, 0 rows affected (0.01 sec)

mysql> 

Deault Roleが無い場合は、ログイン後にSETコマンドでROLEを選択する


[root@DockerHost oracle]# docker exec -it mysql8 mysql -uuser02 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.0-dmr 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 |
+--------------------+
1 row in set (0.01 sec)

mysql> select user(),current_user(),current_role();
+------------------+----------------+----------------+
| user()           | current_user() | current_role() |
+------------------+----------------+----------------+
| user02@localhost | user02@%       | NONE           |
+------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> set role role80;
Query OK, 0 rows affected (0.00 sec)

mysql> select user(),current_user(),current_role();
+------------------+----------------+----------------+
| user()           | current_user() | current_role() |
+------------------+----------------+----------------+
| user02@localhost | user02@%       | `role80`@`%`   |
+------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> show databases;                             
+--------------------+
| Database           |
+--------------------+
| DEMO               |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql> 

再起動するとSETコマンドで設定した値は消えるので、必要に応じて”set default role”コマンドでDefault Roleを設定する

mysql> set default role role80 to user02;
Query OK, 0 rows affected (0.01 sec)

mysql> select user(),current_user(),current_role();
+------------------+----------------+----------------+
| user()           | current_user() | current_role() |
+------------------+----------------+----------------+
| user02@localhost | user02@%       | NONE           |
+------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@DockerHost oracle]# docker exec -it mysql8 mysql -uuser02 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.0-dmr 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 user(),current_user(),current_role();
+------------------+----------------+----------------+
| user()           | current_user() | current_role() |
+------------------+----------------+----------------+
| user02@localhost | user02@%       | `role80`@`%`   |
+------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| DEMO               |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql> 

その他参考:roleはpassword_expireとaccount_lockedで設定されている
通常のアカウント同様に利用したい場合、必要であればexpiredとlockedを解除してあげる。

mysql> select host, user, authentication_string,password_expired,account_locked  from mysql.user;
+-----------+-----------+-------------------------------------------+------------------+----------------+
| host      | user      | authentication_string                     | password_expired | account_locked |
+-----------+-----------+-------------------------------------------+------------------+----------------+
| %         | role80    |                                           | Y                | Y              |
| %         | root      | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | N                | N              |
| %         | user01    | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | N                | N              |
| %         | user02    | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | N                | N              |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | Y              |
+-----------+-----------+-------------------------------------------+------------------+----------------+
5 rows in set (0.01 sec)

mysql> 

ユーザーに複数のROLEの権限を付与してみる
先ずは、ROLEを追加作成してsakilaデータベースに権限付与してみる


mysql> CREATE ROLE role81;    
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL on sakila.* TO role81;
Query OK, 0 rows affected (0.01 sec)

mysql> grant role81 to user01;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from mysql.role_edges;
+-----------+-----------+---------+---------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+---------+---------+-------------------+
| %         | role80    | %       | user01  | N                 |
| %         | role80    | %       | user02  | N                 |
| %         | role81    | %       | user01  | N                 |
+-----------+-----------+---------+---------+-------------------+
3 rows in set (0.00 sec)

mysql> 

Default Roleはrole80になっているので、DEMOデータベースのみにアクセス出来るが、SETコマンドでroleを切り替える事が出来る。

[root@DockerHost oracle]# docker exec -it mysql8 mysql -uuser01 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.0-dmr 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           |
+--------------------+
| DEMO               |
| information_schema |
+--------------------+
2 rows in set (0.01 sec)

mysql> select user(),current_user(),current_role();
+------------------+----------------+----------------+
| user()           | current_user() | current_role() |
+------------------+----------------+----------------+
| user01@localhost | user01@%       | `role80`@`%`   |
+------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> set role role81;          
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;                             
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sakila             |
+--------------------+
2 rows in set (0.00 sec)

mysql> select user(),current_user(),current_role();
+------------------+----------------+----------------+
| user()           | current_user() | current_role() |
+------------------+----------------+----------------+
| user01@localhost | user01@%       | `role81`@`%`   |
+------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> 

ROLEにROLE権限をネストして付与してみる


mysql> grant role81 to role80;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from mysql.role_edges;
+-----------+-----------+---------+---------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+---------+---------+-------------------+
| %         | role80    | %       | user01  | N                 |
| %         | role80    | %       | user02  | N                 |
| %         | role81    | %       | role80  | N                 |
| %         | role81    | %       | user01  | N                 |
+-----------+-----------+---------+---------+-------------------+
4 rows in set (0.01 sec)

mysql> show grants for role80@'%';
+--------------------------------------------------+
| Grants for role80@%                              |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `role80`@`%`               |
| GRANT ALL PRIVILEGES ON `DEMO`.* TO `role80`@`%` |
| GRANT `role81`@`%` TO `role80`@`%`               |
+--------------------------------------------------+
3 rows in set (0.00 sec)

mysql> show grants for role81@'%';
+----------------------------------------------------+
| Grants for role81@%                                |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `role81`@`%`                 |
| GRANT ALL PRIVILEGES ON `sakila`.* TO `role81`@`%` |
+----------------------------------------------------+
2 rows in set (0.00 sec)

role80のメンバーのuser01でログインして、付与された権限を確認してみる。
role80には、role81の権限が付与されているのでDEMOとsakilaデータベース共にアクセス可能になっている。


mysql> select user(),current_role(); 
+------------------+----------------+
| user()           | current_role() |
+------------------+----------------+
| user01@localhost | `role80`@`%`   |
+------------------+----------------+
1 row in set (0.00 sec)

mysql> show grants;
+----------------------------------------------------+
| Grants for user01@%                                |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `user01`@`%`                 |
| GRANT ALL PRIVILEGES ON `DEMO`.* TO `user01`@`%`   |
| GRANT ALL PRIVILEGES ON `sakila`.* TO `user01`@`%` |
| GRANT `role80`@`%`,`role81`@`%` TO `user01`@`%`    |
+----------------------------------------------------+
4 rows in set (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| DEMO               |
| information_schema |
| sakila             |
+--------------------+
3 rows in set (0.00 sec)

mysql>


Oracle Cloud MySQL Serviceでは、GUIベースのダッシュボードだけでは無く、APIやPaaS Service Manager (PSM)コマンドライン・インタフェース(CLI)を利用してインスタンスを管理する事が可能です。GUIでの管理も使いやすくて良いのですが、Provisioningの自動化による工数削減にはAPIかCLIの活用が必用になるかと思います。
今回は、PSMを利用したインスタンスの作成と削除方法を此方で紹介します。先ずは、MySQL Cloud Serviceを検証環境等で活用してみて頂ければと思います。

Japanese Manual
https://docs.oracle.com/cd/E60665_01/jcs_gs/PSCLI/GUID-A63D73BD-4F22-472D-9E04-D998CEE68A00.htm

https://docs.oracle.com/cd/E60665_01/jcs_gs/PSCLI/toc.htm

インストール方法の詳細に関しては、シンプルですので上記マニュアルを確認下さい。
基本的には、以下の2ステップでインストールします。

STEP1:PSM用ソフトウエアのダウンロード

$ export USER=email.address@oracle.com
$ export PASSWD=password
$ export IDENTITYDOMAIN=myidentitydomain

curl -v -X GET -u ${USER}:${PASSWD} -H X-ID-TENANT-NAME:${IDENTITYDOMAIN} https://psm.europe.oraclecloud.com/paas/core/api/v1.1/cli/${IDENTITYDOMAIN}/client -o psmcli.zip

STEP2:PSM用ソフトウエアのインストール

[root@misc01 opc]# pip install -U psmcli.zip 
Processing ./psmcli.zip
Requirement already up-to-date: requests<=2.8.1,>=2.7.0 in /usr/lib/python2.7/site-packages (from psmcli==1.1.8)
Requirement already up-to-date: keyring<=5.6,>=5.4 in /usr/lib/python2.7/site-packages (from psmcli==1.1.8)
Requirement already up-to-date: colorama==0.3.3 in /usr/lib/python2.7/site-packages (from psmcli==1.1.8)
Requirement already up-to-date: PyYAML==3.11 in /usr/lib64/python2.7/site-packages (from psmcli==1.1.8)
Installing collected packages: psmcli
  Found existing installation: psmcli 1.1.8
    Uninstalling psmcli-1.1.8:
      Successfully uninstalled psmcli-1.1.8
  Running setup.py install for psmcli ... done
Successfully installed psmcli-1.1.8
[root@misc01 opc]# 

PSMインストール後の動作確認


[root@misc01 admin]# psm help

DESCRIPTION
  A command line tool to interact with Oracle Cloud Platform Services (PaaS)

SYNOPSIS
  psm <service> <command> [parameters]

AVAILABLE SERVICES
  o BDCSCE
       Oracle Big Data Cloud Service - Compute Edition
  o CONTAINER
       Oracle Container Cloud Service
  o IDCS
       Oracle Identity Cloud Service
  o MySQLCS
       Oracle MySQL Cloud Service
  o OEHCS
       Oracle Event Hub Cloud Service
  o OEHPCS
       Oracle Event Hub Cloud Service - Platform
  o accs
       Oracle Application Container Cloud Service
  o caching
       Oracle Caching Service
  o dbcs
       Oracle Database Cloud Service
  o ggcs
       Oracle GoldenGate Cloud Service
  o jcs
       Oracle Java Cloud Service
  o stack
       Oracle Cloud Stack Manager
  o setup
       Configure psm client options
  o update
       Update psm client to latest version
  o log
       View or update psm client log level
  o help
       Show help

AVAILABLE PARAMETERS
  -v, --version  
       Show current version of psm client

[root@misc01 admin]# 

自分のアイデンティティドメインが利用出来るように初期設定します。
設定が終わると、利用可能なサービスが表示されます。ここでは、MySQLを管理するのでMySQLCSを選択してコマンドを実行します。


[root@misc01 admin]# psm setup
Username: email.address@oracle.com
Password: 
Retype Password: 
Identity domain: myidentitydomain
Region [us]: 
Output format [json]: 
Please enter password for encrypted keyring: 
----------------------------------------------------
'psm setup' was successful. Available services are:

  o ANALYTICS : Oracle Analytics Cloud
  o BDCSCE : Oracle Big Data Cloud Service - Compute Edition
  o CONTAINER : Oracle Container Cloud Service
  o IDCS : Oracle Identity Cloud Service
  o IOTAssetMon : Oracle IoT Asset Monitoring Cloud Service
  o IOTEnterpriseApps : Oracle Internet of Things Cloud - Enterprise
  o IOTFleetMon : Oracle IoT Fleet Monitoring Cloud Service
  o IOTProdMonitoring : Oracle IoT Production Monitoring Cloud Service
  o MySQLCS : Oracle MySQL Cloud Service
  o OEHCS : Oracle Event Hub Cloud Service
  o OEHPCS : Oracle Event Hub Cloud Service - Platform
  o accs : Oracle Application Container Cloud Service
  o caching : Oracle Application Cache
  o dbcs : Oracle Database Cloud Service
  o ggcs : Oracle GoldenGate Cloud Service
  o jcs : Oracle Java Cloud Service
  o stack : Oracle Cloud Stack Manager
----------------------------------------------------
[root@misc01 admin]# 

補足:PSMが古い場合はアップグレードするようにプロンプトが出てきますので、必要に応じてアップデートして下さい。

[root@misc01 admin]# psm update
Please enter password for encrypted keyring: 
...Downloading the latest psm client distribution - version 1.1.12
...Updating psm client from version 1.1.11 to 1.1.12
...If prompted for password, enter sudo password
You are using pip version 8.1.2, however version 9.0.1 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.
Processing /tmp/psmcli.zip
Requirement already up-to-date: requests<=2.8.1,>=2.7.0 in /usr/lib/python3.4/site-packages (from psmcli==1.1.12)
Requirement already up-to-date: keyring<=5.6,>=5.4 in /usr/lib/python3.4/site-packages (from psmcli==1.1.12)
Requirement already up-to-date: colorama==0.3.3 in /usr/lib/python3.4/site-packages (from psmcli==1.1.12)
Requirement already up-to-date: PyYAML==3.11 in /usr/lib64/python3.4/site-packages (from psmcli==1.1.12)
Installing collected packages: psmcli
  Found existing installation: psmcli 1.1.11
    Uninstalling psmcli-1.1.11:
      Successfully uninstalled psmcli-1.1.11
  Running setup.py install for psmcli: started
    Running setup.py install for psmcli: finished with status 'done'
Successfully installed psmcli-1.1.12
...Cleaning up
[root@misc01 admin]# 

MySQLCSオプションで利用可能なコマンドは以下の通りです。
サービスを拡張中なので、適宜追加されていきます。


[root@misc01 admin]# psm MySQLCS help

DESCRIPTION
  Oracle MySQL Cloud Service

SYNOPSIS
  psm MySQLCS <command> [parameters]

AVAILABLE COMMANDS
  o services
       List all Oracle MySQL Cloud Service instances
  o service
       List Oracle MySQL Cloud Service instance
  o create-service
       Create Oracle MySQL Cloud Service
  o delete-service
       Delete operation for MySQL.
  o stop
       Stop one or more VMs that are running Oracle MySQL Cloud Service instance
  o start
       Start one or more VMs that are running Oracle MySQL Cloud Service instance
  o restart
       Restart one or more VMs that are running Oracle MySQL Cloud Service instance
  o scale
       Scale-Up or scale-Down the Compute Shape used by service hosts
  o add-storage
       Add Capacity to storage volumes
  o view-backups
       List all backups of Oracle MySQL Cloud Service instance
  o view-backup
       List a backup of Oracle MySQL Cloud Service instance
  o backup
       Backup MySQL
  o delete-backup
       Delete an existing backup.
  o view-restores
       List all restore operations for Oracle MySQL Cloud Service instance
  o view-restore
       List a specified restore operation for Oracle MySQL Cloud Service instance
  o restore
       Restore MySQL from a backup.
  o view-backup-config
       List backup configuration of Oracle MySQL Cloud Service instance
  o update-backup-config
       Update the backup configuration for the MySQL service.
  o available-patches
       List all available patches for Oracle MySQL Cloud Service instance
  o applied-patches
       List all applied patches for Oracle MySQL Cloud Service instance
  o patch
       This operation will apply a patch to the service
  o precheck-patch
       This operation will run a precheck for a patch on the given service
  o rollback
       This operation will rollback a previously applied patch
  o operation-status
       View status of Oracle MySQL Cloud Service instance operation
  o activities
       View activities for Oracle MySQL Cloud Service instance
  o access-rules
       List access rules for Oracle MySQL Cloud Service instance
  o multisite-access-rules
       List access rules for multi-site service Oracle MySQL Cloud Service instance
  o create-access-rule
       Create an access rule for Oracle MySQL Cloud Service instance
  o create-multisite-access-rule
       Create an access rule for multisite Oracle MySQL Cloud Service instance
  o delete-access-rule
       Delete an access rule for Oracle MySQL Cloud Service instance
  o delete-multisite-access-rule
       Delete an access rule for multi-site Oracle MySQL Cloud Service instance
  o enable-access-rule
       Enable an access rule for Oracle MySQL Cloud Service instance
  o enable-multisite-access-rule
       Enable an access rule for multi-site Oracle MySQL Cloud Service instance
  o disable-access-rule
       Disable an access rule for Oracle MySQL Cloud Service instance
  o disable-multisite-access-rule
       Disable an access rule for multi-site Oracle MySQL Cloud Service instance
  o help
       Show help

[root@misc01 admin]# 

MySQLインスタンスの作成
既存のインスタンスが存在していないので、MySQLCSオプションでインスタンスを作成してみます。
先ずは、MySQLCS create-serviceのオプションを確認してみます。


[root@misc01 admin]# psm MySQLCS services
Please enter password for encrypted keyring: 
{
    "services":{}
}
[root@misc01 admin]# psm MySQLCS create-service help

DESCRIPTION
  Create Oracle MySQL Cloud Service

SYNOPSIS
  psm MySQLCS create-service [parameters]
       -c, --config-payload <value>
       [-of, --output-format <value>]

AVAILABLE PARAMETERS
  -c, --config-payload    (file)
       Path to JSON file containing payload for this command. A sample payload is
       included in EXAMPLES below.

  -of, --output-format    (string)
       Desired output format. Valid values are [json, html]

EXAMPLES
  psm MySQLCS create-service -c /home/templates/create-service-payload.json

SAMPLE PAYLOAD
Required properties are indicated as "required". Replace in the actual payload with real values.
{
    "serviceName":"required",
    "serviceDescription":"",
    "backupDestination":"",
    "cloudStorageContainer":"",
    "cloudStorageUser":"",
    "cloudStoragePassword":"",
    "cloudStorageContainerAutoGenerate":"",
    "vmPublicKeyText":"required",
    "serviceLevel":"required",
    "meteringFrequency":"",
    "serviceVersion":"required",
    "edition":"required",
    "vmUser":"required",
    "region":"",
    "availabilityDomain":"",
    "noRollback":"",
    "isManaged":"",
    "ipNetwork":"",
    "components":{
        "mysql":{
            "shape":"required",
            "mysqlUserName":"required",
            "mysqlUserPassword":"required",
            "mysqlPort":"required",
            "mysqlEMPort":"",
            "dbStorage":"required",
            "dbName":"required",
            "mysqlCharset":"",
            "mysqlCollation":"",
            "enterpriseMonitor":"",
            "enterpriseMonitorManagerUser":"",
            "enterpriseMonitorManagerPassword":"",
            "enterpriseMonitorAgentUser":"",
            "enterpriseMonitorAgentPassword":""
        }
    }
}

[root@misc01 admin]# 

上記のように、事前にサーバー構成を定義した、JSONフォーマットの定義ファイルの作成が必用ですので、以下のマニュアルを見て、定義ファイルを作成して下さい。

https://docs.oracle.com/cd/E60665_01/jcs_gs/PSCLI/GUID-C7254B5C-67E5-4321-A7ED-A6376FBC7956.htm

https://docs.oracle.com/en/cloud/paas/java-cloud/pscli/mysqlcs-create-service.html

JSONファイルの作成が終わったら、ファイルを指定してインスタンスを作成してみます。

[root@misc01 opc]# cat create-service-payload.json 
{
    "serviceName":"PSM01",
    "serviceDescription":"Create instance from PSM",
    "backupDestination":"NONE",
    "vmPublicKeyText":"ssh-rsa CBBB3NzaC2yc2EBBABBJQAAAQEAlL6Ud4xUYxHcKUUijX.......",
    "serviceLevel":"PAAS",
    "serviceVersion":"5.7",
    "vmUser":"opc",
    "components":{
        "mysql":{
            "shape":"oc3",
            "mysqlUserName":"root",
            "mysqlUserPassword":"MyPass-2017",
            "mysqlPort":"3306",
            "mysqlEMPort":"18443",
            "dbStorage":"25",
            "dbName":"PSM",
            "mysqlCharset":"utf8mb4",
            "enterpriseMonitor":"Yes",
            "enterpriseMonitorManagerUser":"mem_manager",
            "enterpriseMonitorManagerPassword":"password",
            "enterpriseMonitorAgentUser":"mem_user",
            "enterpriseMonitorAgentPassword":"password"
        }
    }
}
[root@misc01 opc]# psm MySQLCS create-service -c create-service-payload.json 
Please enter password for encrypted keyring: 
{
    "details":{
        "message":"Submitted job to create service [PSM01] in domain [myidentitydomain].",
        "jobId":"10382276"
    }
}
Job ID : 10382276
[root@misc01 opc]# 

インスタンスの作成が完了したら、SSHのみ初期設定ではアクセス可能ですので、
SSHでプライベート鍵を利用してアクセスしてみて下さい。
MySQLには、JSONで定義したrootパスワードが設定されているので、指定したパスワードを利用してアクセス確認して下さい。


[oracle@psm01-mysql-1 opc]$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 968
Server version: 5.7.17-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

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> 


mysql> select user,host,authentication_string from mysql.user where user = 'root';
+------+------+-------------------------------------------+
| user | host | authentication_string                     |
+------+------+-------------------------------------------+
| root | %    | *C6918734E7352333C2B35180C978E969B5CA1972 |
+------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> 

MySQLインスタンスの削除
PSMコマンドでインスタンスの状況を確認して、インスタンスが不要になったら以下の様にdelete-serviceオプションでインスタンスを削除して下さい。


[root@misc01 opc]# psm MySQLCS services | grep -A10 serviceId
Please enter password for encrypted keyring: 
            "serviceId":303252,
            "serviceName":"PSM01",
            "serviceType":"MySQLCS",
            "domainName":"myidentitydomain",
            "serviceVersion":"5.7",
            "releaseVersion":"5.7.17",
            "metaVersion":"17.1.5-170205",
            "serviceDescription":"Create instance from PSM",
            "serviceLevel":"PAAS",
            "subscription":"HOURLY",
            "meteringFrequency":"HOURLY",



[root@misc01 opc]# psm MySQLCS delete-service -s PSM01
Please enter password for encrypted keyring: 
{
    "details":{
        "message":"Submitted job to delete service [PSM01] in domain [myidentitydomain].",
        "jobId":"10390616"
    }
}
Job ID : 10390616
[root@misc01 opc]# 


[root@misc01 opc]# psm MySQLCS services
Please enter password for encrypted keyring: 
{
    "services":{}
}
[root@misc01 opc]# 


バックアップをリモートのPublic Cloud上のオブジェクトストレージに直接送り、DR, BCP対策を行う事が可能ですが、mysqlbackupにもオブジェクトストレージに直接バックアップを送るオプションがあるので、OracleのPublic Cloudを利用して動作確認しました。

※ 2017年3月現在、MySQL Serviceが日本のデータセンタでは提供して無い為、EUの検証環境に対して検証を行いました。
開発環境としては、Enterprise版のモジュール、サポート、コンサルティングサポート含めて利用する事が可能ですので、TCOの削減が可能ですが。レスポンスが要求される本番環境としては、日本のデータセンターでのサービスが待ち遠しい所です。

  

検証したmysqlbackupのバージョン
最新は、mysqlbackup4.1です。

[root@misc01 opc]# /usr/local/mysqlbackup/bin/mysqlbackup --version
MySQL Enterprise Backup version 4.0.3 Linux-2.6.18-194.el5-x86_64 [2016/08/04] 
Copyright (c) 2003, 2016, Oracle and/or its affiliates. All Rights Reserved.

クラウドストレージへのバックアップは此方のマニュアルを参照下さい。
https://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/meb-backup-cloud.html

バックアップの実行
ローカルのMySQLインスタンス上のサンプルデータベース(world)のみをオラクルのストレージクラウドサービスにバックアップ


[root@misc01 temp_dir]# /usr/local/mysqlbackup/bin/mysqlbackup --include=world.* -uroot -p --cloud-service=openstack \
> --cloud-container=objectstoragename --cloud-object=mysqlbackup_opc.img \
> --cloud-user-id=Storage-myidentitydomain:myaccount@oracle.com \
> --cloud-password=mypassword --cloud-ca-info=/etc/ssl/certs/ca-bundle.crt \
> --cloud-tempauth-url=https://myidentitydomain.storage.oraclecloud.com \
> --encrypt-password='encryptpass' --backup-dir=/home/admin/opc/temp_dir --backup-image=- backup-to-image
MySQL Enterprise Backup version 4.0.3 Linux-2.6.18-194.el5-x86_64 [2016/08/04] 
Copyright (c) 2003, 2016, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: INFO: Starting with following command line ...
 /usr/local/mysqlbackup/bin/mysqlbackup --include=world.* -uroot -p 
        --cloud-service=openstack --cloud-container=objectstoragename 
        --cloud-object=mysqlbackup_opc.img 
        --cloud-user-id=Storage-myidentitydomain:myaccount@oracle.com 
        --cloud-password=xxxxxxxxxx 
        --cloud-ca-info=/etc/ssl/certs/ca-bundle.crt 
        --cloud-tempauth-url=https://myidentitydomain.storage.oraclecloud.com 
        --encrypt-password=xxxxxxxxxxx --backup-dir=/home/admin/opc/temp_dir 
        --backup-image=- backup-to-image 

 mysqlbackup: INFO: 
Enter password: 
 mysqlbackup: INFO: MySQL server version is '5.7.17-enterprise-commercial-advanced-log'
 mysqlbackup: INFO: MySQL server compile os version is 'linux-glibc2.5'
 mysqlbackup: INFO: Got some server configuration information from running server.

 mysqlbackup: INFO: Validating cloud parameters.
IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'backup-to-image' run mysqlbackup
           prints "mysqlbackup completed OK!".

170313 21:15:25 mysqlbackup: INFO: MEB logfile created at /home/admin/opc/temp_dir/meta/MEB_2017-03-13.21-15-25_image_backup.log

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir = /usr/local/mysql/data/
  innodb_data_home_dir = 
  innodb_data_file_path = ibdata1:12M:autoextend
  innodb_log_group_home_dir = /usr/local/mysql/data/
  innodb_log_files_in_group = 2
  innodb_log_file_size = 268435456
  innodb_page_size = 16384
  innodb_checksum_algorithm = crc32
  innodb_buffer_pool_filename = ib_buffer_pool
  innodb_undo_directory = /usr/local/mysql/data/
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir = /home/admin/opc/temp_dir/datadir
  innodb_data_home_dir = /home/admin/opc/temp_dir/datadir
  innodb_data_file_path = ibdata1:12M:autoextend
  innodb_log_group_home_dir = /home/admin/opc/temp_dir/datadir
  innodb_log_files_in_group = 2
  innodb_log_file_size = 268435456
  innodb_page_size = 16384
  innodb_checksum_algorithm = crc32
  innodb_buffer_pool_filename = ib_buffer_pool
  innodb_undo_directory = /home/admin/opc/temp_dir/datadir
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

 mysqlbackup: INFO: Backup Image Path = 'stdout'
 mysqlbackup: INFO: Unique generated backup id for this is 14894073252216434

 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
 mysqlbackup: INFO: Checking if object already exists.
 mysqlbackup: INFO: This backup has encrypted tables, encrypt password is set.
New object(segment) name :/objectstoragename/mysqlbackup_opc.img_part_1.
170313 21:15:31 mysqlbackup: INFO: Full Image Backup operation starts with following threads
                1 read-threads    6 process-threads    1 write-threads
170313 21:15:31 mysqlbackup: INFO: System tablespace file format is Barracuda.
170313 21:15:31 mysqlbackup: INFO: Starting to copy all innodb files...
 mysqlbackup: INFO: Copying meta file /home/admin/opc/temp_dir/backup-my.cnf.
 mysqlbackup: INFO: Copying meta file /home/admin/opc/temp_dir/meta/backup_create.xml.
170313 21:15:31 mysqlbackup: INFO: Found checkpoint at lsn 4984955644.
170313 21:15:31 mysqlbackup: INFO: Starting log scan from lsn 4984955392.
170313 21:15:31 mysqlbackup: INFO: Copying log...
170313 21:15:31 mysqlbackup: INFO: Log copied, lsn 4984955653.
170313 21:15:31 mysqlbackup: INFO: Copying /usr/local/mysql/data/ibdata1 (Barracuda file format).
Uploading part %:10 20 30 40 50 60 70 80 90 

Uploading part %:10 20 30 40 50 60 70 80 90 

Uploading part %:10 20 30 40 50 60 70 80 90 

Uploading part %:10 20 30 40 50 60 70 80 90 

170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world/City.ibd (Barracuda file format).
170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world/City_Sample.ibd (Barracuda file format).
170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world/Country.ibd (Antelope file format).
170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world/CountryLanguage.ibd (Antelope file format).
170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world/T_STAT_SAMPLE.ibd (Barracuda file format).
170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world/T_TDE.ibd (Barracuda file format).
170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/City2.ibd (Barracuda file format).
170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/City3.ibd (Barracuda file format).
170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/City_Master.ibd (Barracuda file format).
170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/City_SnapShot.ibd (Barracuda file format).
Uploading part %:170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/City_Summary.ibd (Barracuda file format).
170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/T_GC_UK.ibd (Barracuda file format).
170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/T_JSON_FROM_TABLE_SCHEMA.ibd (Barracuda file format).
170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/T_Pump01.ibd (Barracuda file format).
170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/t1.ibd (Barracuda file format).
170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/t2.ibd (Barracuda file format).
170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world_x/City.ibd (Barracuda file format).
170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world_x/Country.ibd (Barracuda file format).
170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world_x/CountryInfo.ibd (Barracuda file format).
170313 21:26:15 mysqlbackup: INFO: Copying /usr/local/mysql/data/world_x/CountryLanguage.ibd (Barracuda file format).
170313 21:26:15 mysqlbackup: INFO: Completing the copy of innodb files.
170313 21:26:16 mysqlbackup: INFO: Starting to copy Binlog files...
170313 21:26:16 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql-bin.002623.
170313 21:26:16 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql-bin.002624.
10 20 30 40 50 60 70 80 90 

170313 21:28:37 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql-bin.002625.
170313 21:28:37 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql-bin.002626.
170313 21:28:37 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql-bin.002627.
170313 21:28:37 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
170313 21:28:37 mysqlbackup: INFO: Starting to lock all the tables...
170313 21:28:37 mysqlbackup: INFO: All tables are locked and flushed to disk
170313 21:28:37 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql-bin.002628.
170313 21:28:37 mysqlbackup: INFO: Completed the copy of binlog files...
170313 21:28:37 mysqlbackup: INFO: Opening backup source directory '/usr/local/mysql/data'
170313 21:28:37 mysqlbackup: INFO: Starting to backup all non-innodb files in 
        subdirectories of '/usr/local/mysql/data'
170313 21:28:37 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:37 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:37 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:37 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:37 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:37 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:37 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:37 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:38 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:38 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:38 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:38 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:38 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:38 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:38 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:38 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:38 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:38 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:38 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:38 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:38 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:38 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:38 mysqlbackup: INFO: Adding database directory: datadir
170313 21:28:38 mysqlbackup: INFO: Completing the copy of all non-innodb files.
170313 21:28:38 mysqlbackup: INFO: A copied database page was modified at 4984955604.
          (This is the highest lsn found on page)
          Scanned log up to lsn 4984955653.
          Was able to parse the log up to lsn 4984955653.
170313 21:28:38 mysqlbackup: INFO: All tables unlocked
170313 21:28:38 mysqlbackup: INFO: All MySQL tables were locked for 0.546 seconds.
170313 21:28:38 mysqlbackup: INFO: Reading all global variables from the server.
170313 21:28:38 mysqlbackup: INFO: Completed reading of all global variables from the server.
 mysqlbackup: INFO: Writing config file for server '5.7.17-enterprise-commercial-advanced-log'.
170313 21:28:38 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /home/admin/opc/temp_dir
 mysqlbackup: INFO: Copying meta file /home/admin/opc/temp_dir/meta/backup_variables.txt.
 mysqlbackup: INFO: Copying meta file /home/admin/opc/temp_dir/datadir/ibbackup_logfile.
 mysqlbackup: INFO: Copying meta file /home/admin/opc/temp_dir/server-all.cnf.
 mysqlbackup: INFO: Copying meta file /home/admin/opc/temp_dir/server-my.cnf.
 mysqlbackup: INFO: Copying meta file /home/admin/opc/temp_dir/meta/backup_gtid_executed.sql.
 mysqlbackup: INFO: Copying meta file /home/admin/opc/temp_dir/meta/backup_content.xml.
 mysqlbackup: INFO: Copying meta file /home/admin/opc/temp_dir/meta/image_files.xml.
 mysqlbackup: INFO: OS: Flushing buffers.
Uploading part %:10 20 30 40 50 60 70 80 90  mysqlbackup: INFO: Received response code 201. 

 mysqlbackup: INFO: Uploading the manifest file.
 mysqlbackup: INFO: Cloud operation complete.
170313 21:29:48 mysqlbackup: INFO: Full Image Backup operation completed successfully.
170313 21:29:48 mysqlbackup: INFO: Backup image created successfully.
 mysqlbackup: INFO: Image Path = 'stdout'
170313 21:29:48 mysqlbackup: INFO: MySQL binlog position: filename mysql-bin.002628, position 194

-------------------------------------------------------------
   Parameters Summary         
-------------------------------------------------------------
   Start LSN                  : 4984955392
   End LSN                    : 4984955653
-------------------------------------------------------------

mysqlbackup completed OK!
[root@misc01 temp_dir]# 

Storage CloudサービスのAPIを利用してバックアップが出来ているか確認
例) myidentitydomain = 自分のIdentity Domain, objectstoragename = オブジェクトストレージ名

 curl -X GET -H "X-Auth-Token: $XAuth" https://myidentitydomain.storage.oraclecloud.com/v1/Storage-myidentitydomain/objectstoragename/ 

マニュアル

https://docs.oracle.com/cd/E60665_01/storagecs_common/CSSTO/GUID-5778ADBB-A0E8-4451-B886-362A3B7237DB.htm
https://docs.oracle.com/cloud/latest/mysql-cloud/CSMCS/api-Backups.html

バックアップイメージとして指定した、mysqlbackup_opc.imgが出来ている事が確認出来ました。

[root@misc01 opc]# ./opc_backup_confirm.sh | grep mysqlbackup
Press [Enter] key to resume.
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   847  100   847    0     0    271      0  0:00:03  0:00:03 --:--:--   271
mysqlbackup_opc.img
mysqlbackup_opc.img_part_1
[root@misc01 opc]# 

補足:mysqldumpを利用する場合

■ mysqldumpでリモートサーバーにバックアップする場合

[admin@misc01 .ssh]$ mysqldump world -uroot -ppassword | gzip | ssh migration@xxx.xxx.xxx.xxx -i authorized_keys 'cat > /home/migration/mysqldump.sql.gz'
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed 
suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --al
l-databases --triggers --routines --events. 
Enter passphrase for key 'authorized_keys': 
[admin@misc01 .ssh]$ 

■ OPCのサーバーにてバックアップファイルの確認

bash-4.1$ hostname
shinyatest-mysql-1
bash-4.1$ pwd
/home/migration
-bash-4.1$ ls -l
total 152
-rw-r--r-- 1 migration migration 152518 Mar 14 02:08 mysqldump.sql.gz
-bash-4.1$ zcat mysqldump.sql.gz | grep CREATE
CREATE TABLE `City` (
CREATE TABLE `City_Sample` (
CREATE TABLE `Country` (
CREATE TABLE `CountryLanguage` (
CREATE TABLE `T_STAT_SAMPLE` (
CREATE TABLE `T_TDE` (
/*!50001 CREATE VIEW `V_Region` AS SELECT 
/*!50001 CREATE ALGORITHM=UNDEFINED */
-bash-4.1$ 

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/


MySQL Cluster7.5でNDBINFOに新たに便利なテーブルが追加されているので、此方にリストアップしておきました。詳細に関しては、以下のマニュアルを参照下さい。

MySQL Cluster7.5 pdfマニュアル
20.1.4 What is New in MySQL NDB Cluster 7.5

あと、オンラインでデータノードを追加したあとに、テーブルのデータを再分布する場合のコマンドがALTER TABLE ~ ONLINEではなくなりました。
MySQLと同じALTER TABLE ~ ALGORITHM = INPLACE ~に変わりました。

ALTER TABLE Changes:
NDB Cluster formerly supported an alternative syntax for online ALTER TABLE.
This is no longer supported in NDB Cluster 7.5, which makes exclusive use of
ALGORITHM = DEFAULT|COPY|INPLACE for table DDL, as in the standard MySQL Server.

New Tables in NDBINFO
メタデータの確認や、MySQL Cluster内部で何処でLOCKが発生しているか確認し易くなってます。
便利です。


/***** provides per-thread CPU statistics gathered each second, for each thread running in the NDB kernel. ******/

mysql> select * from cpustat;
+---------+--------+---------+-----------+---------+-------------+-----------------+-----------------+-------------+--------------------+--------------+
| node_id | thr_no | OS_user | OS_system | OS_idle | thread_exec | thread_sleeping | thread_spinning | thread_send | thread_buffer_full | elapsed_time |
+---------+--------+---------+-----------+---------+-------------+-----------------+-----------------+-------------+--------------------+--------------+
|       1 |      0 |       0 |         0 |      99 |           1 |              99 |               0 |           0 |                  0 |      1022319 |
|       1 |      1 |       0 |         1 |      99 |           0 |             100 |               0 |           0 |                  0 |      1043157 |
|       1 |      2 |       1 |         0 |      99 |           0 |             100 |               0 |           0 |                  0 |      1044002 |
|       1 |      3 |       6 |         1 |      93 |           2 |              98 |               0 |           0 |                  0 |      1004561 |
|       2 |      0 |       0 |         0 |      99 |           0 |             100 |               0 |           0 |                  0 |      1021821 |
|       2 |      1 |       0 |         0 |     100 |           0 |             100 |               0 |           0 |                  0 |      1041181 |
|       2 |      2 |       0 |         0 |      99 |           0 |             100 |               0 |           0 |                  0 |      1000446 |
|       2 |      3 |       4 |         4 |      92 |           2 |              98 |               0 |           0 |                  0 |      1006593 |
+---------+--------+---------+-----------+---------+-------------+-----------------+-----------------+-------------+--------------------+--------------+
8 rows in set (0.01 sec)

/***** provides raw, per-thread CPU data obtained each second for each thread running in the NDB kernel.  ******/

mysql> select * from cpustat_1sec limit 5;
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
| node_id | thr_no | OS_user_time | OS_system_time | OS_idle_time | exec_time | sleep_time | spin_time | send_time | buffer_full_time | elapsed_time |
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
|       1 |      0 |         4249 |           6543 |      1008090 |      6723 |    1012159 |         0 |         0 |                0 |      1018882 |
|       1 |      0 |         3042 |           7882 |      1012472 |      6636 |    1016760 |         0 |         0 |                0 |      1023396 |
|       1 |      0 |         3779 |           6373 |      1003011 |      6882 |    1006281 |         0 |         0 |                0 |      1013163 |
|       1 |      0 |         2011 |           6151 |      1030521 |      5951 |    1032732 |         0 |         0 |                0 |      1038683 |
|       1 |      0 |         4056 |           5567 |      1039330 |      6329 |    1042624 |         0 |         0 |                0 |      1048953 |
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
5 rows in set (0.00 sec)

/***** provides raw, per-thread CPU data obtained each 50 milliseconds for each thread running in the NDB kernel ******/

mysql> select * from cpustat_50ms limit 5;
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
| node_id | thr_no | OS_user_time | OS_system_time | OS_idle_time | exec_time | sleep_time | spin_time | send_time | buffer_full_time | elapsed_time |
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
|       1 |      0 |          138 |            180 |        56563 |       244 |      56637 |         0 |         0 |                0 |        56881 |
|       1 |      0 |          411 |            537 |        58241 |       626 |      58563 |         0 |         0 |                0 |        59189 |
|       1 |      0 |          124 |            630 |        52635 |       471 |      52918 |         0 |         0 |                0 |        53389 |
|       1 |      0 |            0 |            943 |        49634 |       398 |      50179 |         0 |         0 |                0 |        50577 |
|       1 |      0 |            0 |            377 |        52557 |       299 |      52635 |         0 |         0 |                0 |        52934 |
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
5 rows in set (0.00 sec)

mysql>

/***** provides raw, per-thread CPU data obtained each 20 seconds, for each thread running in the NDB kernel. ******/

mysql> mysql> select * from cpustat_20sec limit 5;
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
| node_id | thr_no | OS_user_time | OS_system_time | OS_idle_time | exec_time | sleep_time | spin_time | send_time | buffer_full_time | elapsed_time |
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
|       1 |      0 |        85502 |         105281 |     19833351 |    131831 |   19892303 |         0 |         0 |                0 |     20024134 |
|       1 |      0 |        85028 |          98842 |     19820167 |    121907 |   19882130 |         0 |         0 |                0 |     20004037 |
|       1 |      0 |        77030 |         105719 |     19842259 |    121151 |   19903857 |         0 |         0 |                0 |     20025008 |
|       1 |      0 |        74431 |         101572 |     19827545 |    122715 |   19880833 |         0 |         0 |                0 |     20003548 |
|       1 |      0 |        85342 |         101835 |     19813255 |    127802 |   19872630 |         0 |         0 |                0 |     20000432 |
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
5 rows in set (0.00 sec)

mysql> 


/***** provides information about threads running in the NDB kernel. ******/
 

mysql> select * from threads;
+---------+--------+-------------+------------------------------------------------------------------+
| node_id | thr_no | thread_name | thread_description                                               |
+---------+--------+-------------+------------------------------------------------------------------+
|       1 |      0 | main        | main thread, schema and distribution handling                    |
|       1 |      1 | rep         | rep thread, asynch replication and proxy block handling          |
|       1 |      2 | ldm         | ldm thread, handling a set of data partitions                    |
|       1 |      3 | recv        | receive thread, performing receieve and polling for new receives |
|       2 |      0 | main        | main thread, schema and distribution handling                    |
|       2 |      1 | rep         | rep thread, asynch replication and proxy block handling          |
|       2 |      2 | ldm         | ldm thread, handling a set of data partitions                    |
|       2 |      3 | recv        | receive thread, performing receieve and polling for new receives |
+---------+--------+-------------+------------------------------------------------------------------+
8 rows in set (0.01 sec)

mysql> 


/***** provides information about current lock requests holding and waiting for locks on NDB tables in an NDB Cluster , and is intended as a companion table to cluster_operations. ******/

mysql> desc cluster_locks;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| node_id         | int(10) unsigned    | YES  |     | NULL    |       |
| block_instance  | int(10) unsigned    | YES  |     | NULL    |       |
| tableid         | int(10) unsigned    | YES  |     | NULL    |       |
| fragmentid      | int(10) unsigned    | YES  |     | NULL    |       |
| rowid           | bigint(20) unsigned | YES  |     | NULL    |       |
| transid         | bigint(22) unsigned | YES  |     | NULL    |       |
| mode            | varchar(1)          | NO   |     |         |       |
| state           | varchar(1)          | NO   |     |         |       |
| detail          | varchar(1)          | NO   |     |         |       |
| op              | varchar(9)          | NO   |     |         |       |
| duration_millis | int(10) unsigned    | YES  |     | NULL    |       |
| lock_num        | int(10) unsigned    | YES  |     | NULL    |       |
| waiting_for     | bigint(10) unsigned | YES  |     | NULL    |       |
+-----------------+---------------------+------+-----+---------+-------+
13 rows in set (0.00 sec)

mysql> 



/***** provides information about counts of lock claim requests, and the outcomes of these requests on a per-fragment basis, serving as a companion table to operations_per_fragment and memory_per_fragment.  ******/

mysql> select * from locks_per_fragment limit 5;
+------------------------------+------------------------+-------------------+----------+---------+----------------+--------------+--------+-----------+------------+--------------+--------+-----------+------------+--------------+----------------+------------------+
| fq_name                      | parent_fq_name         | type              | table_id | node_id | block_instance | fragment_num | ex_req | ex_imm_ok | ex_wait_ok | ex_wait_fail | sh_req | sh_imm_ok | sh_wait_ok | sh_wait_fail | wait_ok_millis | wait_fail_millis |
+------------------------------+------------------------+-------------------+----------+---------+----------------+--------------+--------+-----------+------------+--------------+--------+-----------+------------+--------------+----------------+------------------+
| TEST_DB/def/NDB$BLOB_82_1    | TEST_DB/def/T_NDB_JSON | User table        |       83 |       1 |              1 |            0 |      0 |         0 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| TEST_DB/def/NDB$BLOB_82_1    | TEST_DB/def/T_NDB_JSON | User table        |       83 |       1 |              1 |            1 |      0 |         0 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| TEST_DB/def/NDB$BLOB_82_1    | TEST_DB/def/T_NDB_JSON | User table        |       83 |       2 |              1 |            0 |      0 |         0 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| TEST_DB/def/NDB$BLOB_82_1    | TEST_DB/def/T_NDB_JSON | User table        |       83 |       2 |              1 |            1 |      0 |         0 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| sys/def/24/idx_memo01$unique | TEST_DB/def/T_TABLE01  | Unique hash index |       79 |       1 |              1 |            0 |     10 |        10 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
+------------------------------+------------------------+-------------------+----------+---------+----------------+--------------+--------+-----------+------------+--------------+--------+-----------+------------+--------------+----------------+------------------+
5 rows in set (0.03 sec)

mysql> 



mysql> select * from locks_per_fragment where fq_name like '%sbtest1';
+----------------------+----------------+------------+----------+---------+----------------+--------------+--------+-----------+------------+--------------+--------+-----------+------------+--------------+----------------+------------------+
| fq_name              | parent_fq_name | type       | table_id | node_id | block_instance | fragment_num | ex_req | ex_imm_ok | ex_wait_ok | ex_wait_fail | sh_req | sh_imm_ok | sh_wait_ok | sh_wait_fail | wait_ok_millis | wait_fail_millis |
+----------------------+----------------+------------+----------+---------+----------------+--------------+--------+-----------+------------+--------------+--------+-----------+------------+--------------+----------------+------------------+
| sysbench/def/sbtest1 | NULL           | User table |       90 |       1 |              1 |            0 |   5080 |      5080 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| sysbench/def/sbtest1 | NULL           | User table |       90 |       1 |              1 |            1 |   4920 |      4920 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| sysbench/def/sbtest1 | NULL           | User table |       90 |       2 |              1 |            0 |   5080 |      5080 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| sysbench/def/sbtest1 | NULL           | User table |       90 |       2 |              1 |            1 |   4920 |      4920 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
+----------------------+----------------+------------+----------+---------+----------------+--------------+--------+-----------+------------+--------------+--------+-----------+------------+--------------+----------------+------------------+
4 rows in set (0.03 sec)

mysql> 


/***** similar in structure to the cluster_locks table, and provides a subset of the information found in the latter table, but which is specific to the SQL node ******/

mysql> desc server_locks;
+---------------------+---------------------+------+-----+---------+-------+
| Field               | Type                | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| mysql_connection_id | bigint(21) unsigned | NO   |     | 0       |       |
| node_id             | int(10) unsigned    | YES  |     | NULL    |       |
| block_instance      | int(10) unsigned    | YES  |     | NULL    |       |
| tableid             | int(10) unsigned    | YES  |     | NULL    |       |
| fragmentid          | int(10) unsigned    | YES  |     | NULL    |       |
| rowid               | bigint(20) unsigned | YES  |     | NULL    |       |
| transid             | bigint(22) unsigned | YES  |     | NULL    |       |
| mode                | varchar(1)          | NO   |     |         |       |
| state               | varchar(1)          | NO   |     |         |       |
| detail              | varchar(1)          | NO   |     |         |       |
| op                  | varchar(9)          | NO   |     |         |       |
| duration_millis     | int(10) unsigned    | YES  |     | NULL    |       |
| lock_num            | int(10) unsigned    | YES  |     | NULL    |       |
| waiting_for         | bigint(10) unsigned | YES  |     | NULL    |       |
+---------------------+---------------------+------+-----+---------+-------+
14 rows in set (0.00 sec)

mysql> 

/***** provides the names and types of database objects in NDB, as well as information about parent obejcts where applicable ******/

mysql> select * from dict_obj_info limit 5;
+------+------+----------+-------+-----------------+---------------+----------------------------+
| type | id   | version  | state | parent_obj_type | parent_obj_id | fq_name                    |
+------+------+----------+-------+-----------------+---------------+----------------------------+
|    6 |   89 |        1 |     4 |               2 |            88 | sys/def/88/PRIMARY         |
|   18 |   44 |        1 |     4 |               6 |            98 | NDB$INDEX_98_CUSTOM        |
|    2 |   31 | 16777218 |     4 |               0 |             0 | TEST_DB_InnoDB/def/Country |
|    6 |   59 |        1 |     4 |               2 |            33 | sys/def/33/PRIMARY         |
|   18 |    2 |        1 |     4 |               6 |            37 | NDB$INDEX_37_CUSTOM        |
+------+------+----------+-------+-----------------+---------------+----------------------------+
5 rows in set (0.00 sec)

mysql> 


/***** provides NDB table distribution status information  ******/

mysql> select * from table_distribution_status limit 5;
+---------+----------+-----------------+-------------------+----------------+------------+-------------+----------------+---------------+--------------------+-----------------+------------------+
| node_id | table_id | tab_copy_status | tab_update_status | tab_lcp_status | tab_status | tab_storage | tab_partitions | tab_fragments | current_scan_count | scan_count_wait | is_reorg_ongoing |
+---------+----------+-----------------+-------------------+----------------+------------+-------------+----------------+---------------+--------------------+-----------------+------------------+
|       1 |        2 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |        3 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |       11 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |       12 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |       13 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
+---------+----------+-----------------+-------------------+----------------+------------+-------------+----------------+---------------+--------------------+-----------------+------------------+
5 rows in set (0.00 sec)

mysql> 


/***** provides information about the distribution of NDB table fragments   ******/

mysql> select * from table_fragments limit 5;
+---------+----------+--------------+-------------+-----------------+-------------+----------------+-----------------+-------------------+----------------------+-----------------------+----------------------+--------------------+-------------------+------------------+
| node_id | table_id | partition_id | fragment_id | partition_order | log_part_id | no_of_replicas | current_primary | preferred_primary | current_first_backup | current_second_backup | current_third_backup | num_alive_replicas | num_dead_replicas | num_lcp_replicas |
+---------+----------+--------------+-------------+-----------------+-------------+----------------+-----------------+-------------------+----------------------+-----------------------+----------------------+--------------------+-------------------+------------------+
|       1 |        2 |   4294967295 |           0 |               0 |           0 |              2 |               1 |                 1 |                    2 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |        2 |   4294967295 |           1 |               0 |           0 |              2 |               2 |                 2 |                    1 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |        3 |   4294967295 |           0 |               0 |           1 |              2 |               1 |                 1 |                    2 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |        3 |   4294967295 |           1 |               0 |           1 |              2 |               2 |                 2 |                    1 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |       11 |   4294967295 |           0 |               0 |           3 |              2 |               1 |                 1 |                    2 |                     0 |                    0 |                  2 |                 0 |                0 |
+---------+----------+--------------+-------------+-----------------+-------------+----------------+-----------------+-------------------+----------------------+-----------------------+----------------------+--------------------+-------------------+------------------+
5 rows in set (0.00 sec)

mysql> 

/***** provides information about logging, checkpointing, storage, and other options in force for each NDB table    ******/

mysql> select * from table_info where table_id = 31;
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
| table_id | logged_table | row_contains_gci | row_contains_checksum | read_backup | fully_replicated | storage_type | hashmap_id | partition_balance | create_gci |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
|       31 |            1 |                1 |                     1 |           0 |                0 | MEMORY       |          1 | FOR_RP_BY_LDM     |          0 |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
1 row in set (0.01 sec)

mysql> 


mysql> select * from table_info where table_id = 90;
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
| table_id | logged_table | row_contains_gci | row_contains_checksum | read_backup | fully_replicated | storage_type | hashmap_id | partition_balance | create_gci |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
|       90 |            1 |                1 |                     1 |           1 |                0 | MEMORY       |          1 | SPECIFIC          |          0 |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
1 row in set (0.01 sec)

mysql> 

/***** provides information about fragment replicas   ******/

mysql> select * from table_replicas limit 5;
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
| node_id | table_id | fragment_id | initial_gci | replica_node_id | is_lcp_ongoing | num_crashed_replicas | last_max_gci_started | last_max_gci_completed | last_lcp_id | prev_lcp_id | prev_max_gci_started | prev_max_gci_completed | last_create_gci | last_replica_gci | is_replica_alive |
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
|       1 |        2 |           0 |           2 |               1 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |        2 |           0 |           2 |               2 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |        2 |           1 |           2 |               1 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |        2 |           1 |           2 |               2 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |        3 |           0 |           2 |               1 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
5 rows in set (0.00 sec)

mysql> 


mysql> select * from table_replicas where table_id = 90;
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
| node_id | table_id | fragment_id | initial_gci | replica_node_id | is_lcp_ongoing | num_crashed_replicas | last_max_gci_started | last_max_gci_completed | last_lcp_id | prev_lcp_id | prev_max_gci_started | prev_max_gci_completed | last_create_gci | last_replica_gci | is_replica_alive |
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
|       1 |       90 |           0 |       36225 |               1 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |       90 |           0 |       36225 |               2 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |       90 |           1 |       36225 |               1 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |       90 |           1 |       36225 |               2 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
4 rows in set (0.01 sec)

mysql> 

config_paramsとconfig_valuesをJOINした結果

パラメータ確認し易くて良い


mysql> SELECT  p.param_name AS Name,
    ->         v.node_id AS Node,
    ->         p.param_type AS Type,
    ->         p.param_default AS 'Default',
    ->         p.param_min AS Minimum,
    ->         p.param_max AS Maximum,
    ->         CASE p.param_mandatory WHEN 1 THEN 'Y' ELSE 'N' END AS 'Required',
    ->         v.config_value AS Current
    -> FROM    config_params p
    -> JOIN    config_values v
    -> ON      p.param_number = v.config_param
    -> WHERE   p. param_name IN ('NodeId', 'HostName','DataMemory', 'IndexMemory');
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
| Name        | Node | Type     | Default   | Minimum | Maximum       | Required | Current        |
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
| NodeId      |    1 | unsigned |           | 1       | 48            | Y        | 1              |
| HostName    |    1 | string   | localhost |         |               | N        | 192.168.56.114 |
| DataMemory  |    1 | unsigned | 83886080  | 1048576 | 1099511627776 | N        | 134217728      |
| IndexMemory |    1 | unsigned | 18874368  | 1048576 | 1099511627776 | N        | 68157440       |
| NodeId      |    2 | unsigned |           | 1       | 48            | Y        | 2              |
| HostName    |    2 | string   | localhost |         |               | N        | 192.168.56.115 |
| DataMemory  |    2 | unsigned | 83886080  | 1048576 | 1099511627776 | N        | 134217728      |
| IndexMemory |    2 | unsigned | 18874368  | 1048576 | 1099511627776 | N        | 68157440       |
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
8 rows in set (0.02 sec)

cluster_locksでロックを確認した場合


mysql> select * from ndbinfo.cluster_locks;
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
| node_id | block_instance | tableid | fragmentid | rowid | transid           | mode | state | detail | op   | duration_millis | lock_num | waiting_for |
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
|       2 |              1 |     107 |          1 |     0 | 13569072998383655 | X    | W     |        | READ |            2593 |        0 |           1 |
|       2 |              1 |     107 |          1 |     0 | 13567973486755886 | S    | H     | *      | READ |            7641 |        1 |        NULL |
|       2 |              1 |     113 |          1 |     0 | 13567973486755886 | S    | H     | *      | READ |            7641 |        3 |        NULL |
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
3 rows in set (0.04 sec)

MySQL7.5のDefault設定値
何も設定しなければ、こちらの値が適用されます。

+------------------------------------------+----------+------------+----------+----------------+----------+
| Name                                     | Type     | Default    | Minimum  | Maximum        | Required |
+------------------------------------------+----------+------------+----------+----------------+----------+
| MaxNoOfSubscriptions                     | unsigned | 0          | 0        | 4294967039     | N        |
| MaxNoOfSubscribers                       | unsigned | 0          | 0        | 4294967039     | N        |
| MaxNoOfConcurrentSubOperations           | unsigned | 256        | 0        | 4294967039     | N        |
| TcpBind_INADDR_ANY                       | bool     | 0          |          |                | N        |
| HostName                                 | string   | localhost  |          |                | N        |
| NodeId                                   | unsigned |            | 1        | 48             | Y        |
| ServerPort                               | unsigned |            | 1        | 65535          | N        |
| NoOfReplicas                             | unsigned | 2          | 1        | 4              | N        |
| MaxNoOfAttributes                        | unsigned | 1000       | 32       | 4294967039     | N        |
| MaxNoOfTables                            | unsigned | 128        | 8        | 20320          | N        |
| MaxNoOfOrderedIndexes                    | unsigned | 128        | 0        | 4294967039     | N        |
| MaxNoOfUniqueHashIndexes                 | unsigned | 64         | 0        | 4294967039     | N        |
| MaxNoOfConcurrentIndexOperations         | unsigned | 8192       | 0        | 4294967039     | N        |
| MaxNoOfTriggers                          | unsigned | 768        | 0        | 4294967039     | N        |
| MaxNoOfFiredTriggers                     | unsigned | 4000       | 0        | 4294967039     | N        |
| MaxNoOfSavedMessages                     | unsigned | 25         | 0        | 4294967039     | N        |
| LockExecuteThreadToCPU                   | bitmask  |            |          |                | N        |
| LockMaintThreadsToCPU                    | unsigned |            | 0        | 65535          | N        |
| RealtimeScheduler                        | bool     | 0          |          |                | N        |
| LockPagesInMainMemory                    | unsigned | 0          | 0        | 2              | N        |
| TimeBetweenWatchDogCheck                 | unsigned | 6000       | 70       | 4294967039     | N        |
| SchedulerExecutionTimer                  | unsigned | 50         | 0        | 11000          | N        |
| MaxSendDelay                             | unsigned | 0          | 0        | 11000          | N        |
| SchedulerSpinTimer                       | unsigned | 0          | 0        | 500            | N        |
| SchedulerResponsiveness                  | unsigned | 5          | 0        | 10             | N        |
| __sched_scan_priority                    | unsigned | 6          | 1        | 6              | N        |
| TimeBetweenWatchDogCheckInitial          | unsigned | 6000       | 70       | 4294967039     | N        |
| StopOnError                              | bool     | 1          |          |                | N        |
| MaxNoOfConcurrentOperations              | unsigned | 32768      | 32       | 4294967039     | N        |
| MaxDMLOperationsPerTransaction           | unsigned | 4294967295 | 32       | 4294967295     | N        |
| MaxNoOfLocalOperations                   | unsigned |            | 32       | 4294967039     | N        |
| MaxNoOfLocalScans                        | unsigned |            | 32       | 4294967039     | N        |
| BatchSizePerLocalScan                    | unsigned | 256        | 1        | 992            | N        |
| MaxNoOfConcurrentTransactions            | unsigned | 4096       | 32       | 4294967039     | N        |
| MaxNoOfConcurrentScans                   | unsigned | 256        | 2        | 500            | N        |
| TransactionBufferMemory                  | unsigned | 1048576    | 1024     | 4294967039     | N        |
| IndexMemory                              | unsigned | 18874368   | 1048576  | 1099511627776  | N        |
| DataMemory                               | unsigned | 83886080   | 1048576  | 1099511627776  | N        |
| UndoIndexBuffer                          | unsigned | 2097152    | 1048576  | 4294967039     | N        |
| UndoDataBuffer                           | unsigned | 16777216   | 1048576  | 4294967039     | N        |
| RedoBuffer                               | unsigned | 33554432   | 1048576  | 4294967039     | N        |
| LongMessageBuffer                        | unsigned | 67108864   | 524288   | 4294967039     | N        |
| DiskPageBufferMemory                     | unsigned | 67108864   | 4194304  | 1099511627776  | N        |
| SharedGlobalMemory                       | unsigned | 134217728  | 0        | 70368744177664 | N        |
| StartPartialTimeout                      | unsigned | 30000      | 0        | 4294967039     | N        |
| StartPartitionedTimeout                  | unsigned | 60000      | 0        | 4294967039     | N        |
| StartFailureTimeout                      | unsigned | 0          | 0        | 4294967039     | N        |
| StartNoNodegroupTimeout                  | unsigned | 15000      | 0        | 4294967039     | N        |
| HeartbeatIntervalDbDb                    | unsigned | 5000       | 10       | 4294967039     | N        |
| ConnectCheckIntervalDelay                | unsigned | 0          | 0        | 4294967039     | N        |
| HeartbeatIntervalDbApi                   | unsigned | 1500       | 100      | 4294967039     | N        |
| TimeBetweenLocalCheckpoints              | unsigned | 20         | 0        | 31             | N        |
| TimeBetweenGlobalCheckpoints             | unsigned | 2000       | 20       | 32000          | N        |
| TimeBetweenGlobalCheckpointsTimeout      | unsigned | 120000     | 10       | 4294967039     | N        |
| TimeBetweenEpochs                        | unsigned | 100        | 0        | 32000          | N        |
| TimeBetweenEpochsTimeout                 | unsigned | 0          | 0        | 256000         | N        |
| MaxBufferedEpochs                        | unsigned | 100        | 1        | 100000         | N        |
| MaxBufferedEpochBytes                    | unsigned | 26214400   | 26214400 | 4294967039     | N        |
| NoOfFragmentLogParts                     | unsigned | 4          | 4        | 32             | N        |
| NoOfFragmentLogFiles                     | unsigned | 16         | 3        | 4294967039     | N        |
| FragmentLogFileSize                      | unsigned | 16777216   | 4194304  | 1073741824     | N        |
| InitFragmentLogFiles                     | string   | sparse     |          |                | N        |
| DiskIOThreadPool                         | unsigned | 2          | 0        | 4294967039     | N        |
| MaxNoOfOpenFiles                         | unsigned |            | 20       | 4294967039     | N        |
| InitialNoOfOpenFiles                     | unsigned | 27         | 20       | 4294967039     | N        |
| TimeBetweenInactiveTransactionAbortCheck | unsigned | 1000       | 1000     | 4294967039     | N        |
| TransactionInactiveTimeout               | unsigned | 4294967039 | 0        | 4294967039     | N        |
| TransactionDeadlockDetectionTimeout      | unsigned | 1200       | 50       | 4294967039     | N        |
| Diskless                                 | bool     | 0          |          |                | N        |
| ArbitrationTimeout                       | unsigned | 7500       | 10       | 4294967039     | N        |
| Arbitration                              | enum     | Default    |          |                | N        |
| DataDir                                  | string   | .          |          |                | N        |
| FileSystemPath                           | string   |            |          |                | N        |
| LogLevelStartup                          | unsigned | 1          | 0        | 15             | N        |
| LogLevelShutdown                         | unsigned | 0          | 0        | 15             | N        |
| LogLevelStatistic                        | unsigned | 0          | 0        | 15             | N        |
| LogLevelCheckpoint                       | unsigned | 0          | 0        | 15             | N        |
| LogLevelNodeRestart                      | unsigned | 0          | 0        | 15             | N        |
| LogLevelConnection                       | unsigned | 0          | 0        | 15             | N        |
| LogLevelCongestion                       | unsigned | 0          | 0        | 15             | N        |
| LogLevelError                            | unsigned | 0          | 0        | 15             | N        |
| LogLevelInfo                             | unsigned | 0          | 0        | 15             | N        |
| BackupDataDir                            | string   |            |          |                | N        |
| DiskSyncSize                             | unsigned | 4194304    | 32768    | 4294967039     | N        |
| MinDiskWriteSpeed                        | unsigned | 10485760   | 1048576  | 1099511627776  | N        |
| MaxDiskWriteSpeed                        | unsigned | 20971520   | 1048576  | 1099511627776  | N        |
| MaxDiskWriteSpeedOtherNodeRestart        | unsigned | 52428800   | 1048576  | 1099511627776  | N        |
| MaxDiskWriteSpeedOwnRestart              | unsigned | 209715200  | 1048576  | 1099511627776  | N        |
| BackupDiskWriteSpeedPct                  | unsigned | 50         | 0        | 90             | N        |
| BackupDataBufferSize                     | unsigned | 16777216   | 524288   | 4294967039     | N        |
| BackupLogBufferSize                      | unsigned | 16777216   | 2097152  | 4294967039     | N        |
| BackupWriteSize                          | unsigned | 262144     | 32768    | 4294967039     | N        |
| BackupMaxWriteSize                       | unsigned | 1048576    | 262144   | 4294967039     | N        |
| StringMemory                             | unsigned | 25         | 0        | 4294967039     | N        |
| MaxAllocate                              | unsigned | 33554432   | 1048576  | 1073741824     | N        |
| MemReportFrequency                       | unsigned | 0          | 0        | 4294967039     | N        |
| BackupReportFrequency                    | unsigned | 0          | 0        | 4294967039     | N        |
| StartupStatusReportFrequency             | unsigned | 0          | 0        | 4294967039     | N        |
| ODirect                                  | bool     | 0          |          |                | N        |
| CompressedBackup                         | bool     | 0          |          |                | N        |
| CompressedLCP                            | bool     | 0          |          |                | N        |
| ExtraSendBufferMemory                    | unsigned | 0          | 0        | 34359738368    | N        |
| TotalSendBufferMemory                    | unsigned | 0          | 262144   | 4294967039     | N        |
| Nodegroup                                | unsigned |            | 0        | 65536          | N        |
| MaxNoOfExecutionThreads                  | unsigned | 0          | 2        | 72             | N        |
| __ndbmt_lqh_workers                      | unsigned |            | 1        | 4              | N        |
| __ndbmt_lqh_threads                      | unsigned |            | 1        | 4              | N        |
| __ndbmt_classic                          | bool     |            |          |                | N        |
| ThreadConfig                             | string   |            |          |                | N        |
| FileSystemPathDD                         | string   |            |          |                | N        |
| FileSystemPathDataFiles                  | string   |            |          |                | N        |
| FileSystemPathUndoFiles                  | string   |            |          |                | N        |
| InitialLogfileGroup                      | string   |            |          |                | N        |
| InitialTablespace                        | string   |            |          |                | N        |
| MaxLCPStartDelay                         | unsigned | 0          | 0        | 600            | N        |
| BuildIndexThreads                        | unsigned | 0          | 0        | 128            | N        |
| HeartbeatOrder                           | unsigned | 0          | 0        | 65535          | N        |
| DictTrace                                | unsigned |            | 0        | 100            | N        |
| MaxStartFailRetries                      | unsigned | 3          | 0        | 4294967039     | N        |
| StartFailRetryDelay                      | unsigned | 0          | 0        | 4294967039     | N        |
| EventLogBufferSize                       | unsigned | 8192       | 0        | 65536          | N        |
| Numa                                     | unsigned | 1          | 0        | 1              | N        |
| RedoOverCommitLimit                      | unsigned | 20         | 0        | 4294967039     | N        |
| RedoOverCommitCounter                    | unsigned | 3          | 0        | 4294967039     | N        |
| LateAlloc                                | unsigned | 1          | 0        | 1              | N        |
| MaxParallelCopyInstances                 | unsigned | 0          | 0        | 64             | N        |
| TwoPassInitialNodeRestartCopy            | bool     | 0          |          |                | N        |
| MaxParallelScansPerFragment              | unsigned | 256        | 1        | 4294967039     | N        |
| IndexStatAutoCreate                      | unsigned | 0          | 0        | 1              | N        |
| IndexStatAutoUpdate                      | unsigned | 0          | 0        | 1              | N        |
| IndexStatSaveSize                        | unsigned | 32768      | 0        | 4294967039     | N        |
| IndexStatSaveScale                       | unsigned | 100        | 0        | 4294967039     | N        |
| IndexStatTriggerPct                      | unsigned | 100        | 0        | 4294967039     | N        |
| IndexStatTriggerScale                    | unsigned | 100        | 0        | 4294967039     | N        |
| IndexStatUpdateDelay                     | unsigned | 60         | 0        | 4294967039     | N        |
| CrashOnCorruptedTuple                    | bool     | 1          |          |                | N        |
| MinFreePct                               | unsigned | 5          | 0        | 100            | N        |
| DefaultHashmapSize                       | unsigned | 0          | 0        | 3840           | N        |
| LcpScanProgressTimeout                   | unsigned | 60         | 0        | 4294967039     | N        |
| __at_restart_skip_indexes                | bool     | 0          |          |                | N        |
| __at_restart_skip_fks                    | bool     | 0          |          |                | N        |
| __debug_mixology_level                   | unsigned | 0          | 0        | 4294967039     | N        |
| RestartSubscriberConnectTimeout          | unsigned | 120000     | 0        | 4294967039     | N        |
| DiskPageBufferEntries                    | unsigned | 10         | 1        | 4294967295     | N        |
+------------------------------------------+----------+------------+----------+----------------+----------+

MySQL5.7で実装されたJSONデータ型とJSON関数のレビューになります。
ご利用になる場合は、参考にして頂ければと思います。

ネイティブJSONデータ型 (バイナリ形式)
Insert時のJSON構文バリデーション機能
組み込みJSON関数 (保存、検索、更新、操作)
ドキュメントにインデックス設定し高速アクセス
SQLとの統合を容易にする、新しいインライン構文
utf8mb4の文字セットとutf8mb4_binの照合 「🐬」
サイズはmax_allowed_packetの値で制限 (Default:4MB)

MySQL5.7からは、リレーショナル、スキーマレスを同じ技術スタックで利用可能になっています。

13.16.1 JSON Function Reference
https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

参考: Modifying JSON Values in MySQL 5.7
https://planet.mysql.com/entry/?id=5994648

JSONで表現する全てのデータ型をサポート

数値, 文字列, bool(true,false)
オブジェクト {“キー”: “値”}, 配列 [123456, “String”, …]
null
日付(date), 時刻, 日付(datetime), タイムスタンプ, その他


[CONFIRM]> show create table T_JSON_SUPPORT\G
*************************** 1. row ***************************
       Table: T_JSON_SUPPORT
Create Table: CREATE TABLE `T_JSON_SUPPORT` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`body` json DEFAULT NULL,
`type` varchar(20) GENERATED ALWAYS AS (json_type(`body`)) VIRTUAL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4


[CONFIRM]> select * from T_JSON_SUPPORT;
+----+---------------------------------------------+----------+
| id | body                                        | type     |
+----+---------------------------------------------+----------+
|  1 | 123456789                                   | INTEGER  |
|  2 | NULL                                        | NULL     |
|  3 | true                                        | BOOLEAN  |
|  4 | "abcde"                                     | STRING   |
|  5 | {"id": 5, "name": "オブジェクト"}            | OBJECT   |
|  6 | [-122.42200352825247, 37.80848009696725, 0] | ARRAY    |
|  7 | "2016-02-29"                                | DATE     |
|  8 | "2016-02-29 00:00:00.000000"                | DATETIME |
+----+---------------------------------------------+----------+

データ型と照合順
4byteなので絵文字を格納可能、またutf8mb4_binなので大文字と小文字を区別します。


 [NEW57]> SET @j = JSON_OBJECT('key', 'value');
1 row in set (0.00 sec)

 [NEW57]> SELECT @j;
+------------------+
| @j               |
+------------------+
| {"key": "value"} |
+------------------+
1 row in set (0.00 sec)

 [NEW57]> SELECT CHARSET(@j), COLLATION(@j);
+-------------+---------------+
| CHARSET(@j) | COLLATION(@j) |
+-------------+---------------+
| utf8mb4     | utf8mb4_bin   |
+-------------+---------------+
1 row in set (0.00 sec)

JSONドキュメントと生成列を利用して列を作成し、対象列にINDEXを付けて高速な検索を行う事が可能です
生成列はファンクションインデックスとして利用可能

こちらは、生成列を利用したテーブルの作成例になります。
string1とstring2にデータをINSERTする事で、string1_w_string、string2_w_string、compareのデータは自動生成されます。


[CONFIRM]> show create table T_Character_COLLATE_utf8mb4_bin\G
*************************** 1. row ***************************
       Table: T_Character_COLLATE_utf8mb4_bin
Create Table: CREATE TABLE `T_Character_COLLATE_utf8mb4_bin` (
  `pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `string1` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `string2` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `string1_w_string` char(8) GENERATED ALWAYS AS (hex(weight_string(`string1`))) VIRTUAL,
  `string2_w_string` char(8) 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

 [CONFIRM]> insert into T_Character_COLLATE_utf8mb4_bin(string1,string2) values('A','a');
Query OK, 1 row affected (0.00 sec)

 [CONFIRM]> insert into T_Character_COLLATE_utf8mb4_bin(string1,string2) values('あ','ぁ');
Query OK, 1 row affected (0.00 sec)

 [CONFIRM]> insert into T_Character_COLLATE_utf8mb4_bin(string1,string2) values('A','A');
Query OK, 1 row affected (0.00 sec)

 [CONFIRM]> select * from T_Character_COLLATE_utf8mb4_bin;
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 000041           | 000061           | 0       |
|   2 | あ      | ぁ      | 003042           | 003041           | 0       |
|   3 | A       | A       | 000041           | 000041           | 1       |
+-----+---------+---------+------------------+------------------+---------+

JSONデータと生成列を利用すると、特定のJSON識別子からデータを抜き出して列を作成し、その列にインデックスを付ける事が可能
例えば、JSONデータをINSERTしてJSONドキュメントにUSER_IDやPRODUCT_IDがあれば、その項目だけを抜き出して列にしてINDEXを付与。
検索する場合は、それらの値を利用して検索するとJSONドキュメントも高速検索が可能になります。


[NEW57]> CREATE TABLE `T_JSON` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feature` json NOT NULL,
  `feature_type` varchar(30) GENERATED ALWAYS AS (json_unquote(feature->"$.type")) VIRTUAL,
  `feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,‘$.properties.STREET’)) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `idx_feature_street` (`feature_street`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;


[NEW57]> alter table features add index idx_feature_type(`feature_type`);
Query OK, 0 rows affected (6.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

[NEW57]> explain select distinct(feature_type) from features;
+----+-------------+----------+------------+-------+------------------+------------------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys    | key              | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+-------+------------------+------------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | features | NULL       | index | idx_feature_type | idx_feature_type | 123     | NULL | 199013 |   100.00 | Using index |
+----+-------------+----------+------------+-------+------------------+------------------+---------+------+--------+----------+-------------+

【JSON関数の使い方】

(1)JSONドキュメントの情報取得


[NEW57]> SELECT JSON_VALID(body) from T_JSON_DOC where id = 5;
+------------------+
| JSON_VALID(body) |
+------------------+
|                1 |
+------------------+

[NEW57]> SELECT JSON_TYPE(body) from T_JSON_DOC where id = 5;
+-----------------+
| JSON_TYPE(body) |
+-----------------+
| OBJECT          |
+-----------------+

[NEW57]> SELECT JSON_KEYS(body) from T_JSON_DOC where id = 5;
+---------------------------------------+
| JSON_KEYS(body)                       |
+---------------------------------------+
| ["id", "name", "price", "Conditions"] |
+---------------------------------------+


[NEW57]> SELECT JSON_SEARCH(feature,'one','MARKET') AS extract_path FROM features WHERE id = 121254;
+-----------------------+
| extract_path          |
+-----------------------+
| "$.properties.STREET" |
+-----------------------+

(2)通常のリレーショナルテーブルから,JSONドキュメントを作成する
Object{}とArray[]関数を利用


/*** リレーショナルテーブル ***/
[NEW57]> SELECT NAME,CountryCode from world.City where CountryCode ='JPN' limit 1;
+-------+-------------+
| NAME  | CountryCode |
+-------+-------------+
| Tokyo | JPN         |
+-------+-------------+

/*** リレーショナルテーブルからJSONデータを作成 ({})***/
[NEW57]> SELECT JSON_OBJECT('CITY',NAME,'Country',CountryCode) from world.City where CountryCode ='JPN' limit 1;
+------------------------------------------------+
| JSON_OBJECT('CITY',NAME,'Country',CountryCode) |
+------------------------------------------------+
| {"CITY": "Tokyo", "Country": "JPN"}            |
+------------------------------------------------+

/*** VIEWにしておくと呼び出しがより楽になります ***/
[NEW57]> CREATE VIEW v_City_json AS
    -> SELECT JSON_OBJECT('ID', ID, 'name', Name, 'CountryCode', CountryCode, 'District', District,'Population',Population) as doc FROM City where CountryCode = 'JPN';
Query OK, 0 rows affected (0.01 sec)

[NEW57]> select * from v_City_json limit 1\G
*************************** 1. row ***************************
doc: {"ID": 1532, "name": "Tokyo", "District": "Tokyo-to", "Population": 7980230, "CountryCode": "JPN"}
1 row in set (0.01 sec)


/*** リレーショナルテーブル ***/
[NEW57]>SELECT NAME,CountryCode from world.City where CountryCode ='JPN' limit 1;
+-------+-------------+
| NAME  | CountryCode |
+-------+-------------+
| Tokyo | JPN         |
+-------+-------------+


/*** リレーショナルテーブルからJSONデータを作成 ([])***/
[NEW57]> SELECT JSON_ARRAY(NAME,CountryCode)  from world.City where CountryCode ='JPN' limit 1;
+------------------------------+
| JSON_ARRAY(NAME,CountryCode) |
+------------------------------+
| ["Tokyo", "JPN"]             |
+------------------------------+


/*** リレーショナルテーブル***/
[NEW57]> select * from T_JSON_PLACE;
+----+-------------------------------------+----------+-----------+
| id | place                               | latitude | longitude |
+----+-------------------------------------+----------+-----------+
|  1 | 東京都港区北青山2-5-8                | 35.67125 | 139.71864 |
|  2 | 大阪府大阪市北区堂2-4-27            | 34.69584 | 135.49291 |
+----+-------------------------------------+----------+-----------+


/*** リレーショナルテーブルからJSONデータを作成 ({},[])***/
[NEW57]> select JSON_OBJECT('ID',id,'住所',place,'緯度経度', JSON_ARRAY(latitude,longitude)) from T_JSON_PLACE;
+---------------------------------------------------------------------------------------------------+
| JSON_OBJECT('ID',id,'住所',place,'緯度経度', JSON_ARRAY(latitude,longitude))                      |
+---------------------------------------------------------------------------------------------------+
| {"ID": 1, "住所": "東京都港区北青山2-5-8", "緯度経度": [35.67125, 139.71864]}                     |
| {"ID": 2, "住所": "大阪府大阪市北区堂2-4-27", "緯度経度": [34.69584, 135.49291]}                 |
+---------------------------------------------------------------------------------------------------+

メモ: ST_AsGeoJSONやST_GeomFromGeoJSON等のSpatial GeoJSON Functionsで空間を表すJSONも利用する事が可能

JSONドキュメントの更新処理


/**** JSON_REMOVE (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+---------------------------------------------------------------------------------------------------+------------+
| id | body                                                                                              | price_only |
+----+---------------------------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}             |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED", 2013, "故障"]}                 |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 17131, "Conditions": ["NEW", 2015]}                          |   17131.00 |
|  4 | {"id": 4, "name": "オートバイ", "price": 500000, "Conditions": ["NEW", 2015]}                     |  500000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Quantity": "[1,10]", "Conditions": ["NEW", 2015]}    |   25000.00 |
+----+---------------------------------------------------------------------------------------------------+------------+

[NEW57]> select JSON_REMOVE(body,"$.Quantity") from T_JSON_DOC where id = 5;
+-----------------------------------------------------------------------------+
| JSON_REMOVE(body,"$.Quantity")                                              |
+-----------------------------------------------------------------------------+
| {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}    |
+-----------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_REMOVE(body,"$.Quantity") where id = 5;



/**** JSON_SET (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> SELECT JSON_SET(body,'$.name',"オートバイ", '$.price',500000) from T_JSON_DOC where id = 4;
+------------------------------------------------------------------------------------+
| JSON_SET(body,'$.name',"オートバイ", '$.price',500000)                             |
+------------------------------------------------------------------------------------+
| {"id": 4, "name": "オートバイ", "price": 500000, "Conditions": ["NEW", 2015]}      |
+------------------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_SET(body,'$.name',"オートバイ", '$.price',500000) where id = 4;


/**** JSON_INSERT (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> select JSON_INSERT(body,'$.Quantity','[1,10]') from NEW57.T_JSON_DOC where id = 5;
+---------------------------------------------------------------------------------------------------+
| JSON_INSERT(body,'$.Quantity','[1,10]')                                                           |
+---------------------------------------------------------------------------------------------------+
| {"id": 5, "name": "自転車", "price": 25000, "Quantity": "[1,10]", "Conditions": ["NEW", 2015]}    |
+---------------------------------------------------------------------------------------------------+

[NEW57]> update NEW57.T_JSON_DOC set body = JSON_INSERT(body,'$.Quantity','[1,10]') where id = 5;


/**** JSON_REPLACE (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> select JSON_REPLACE(body,"$.price",FLOOR(10000 + (RAND() * 9000))) from T_JSON_DOC where id = 3;
+-----------------------------------------------------------------------------+
| JSON_REPLACE(body,"$.price",FLOOR(10000 + (RAND() * 9000)))                 |
+-----------------------------------------------------------------------------+
| {"id": 3, "name": "冷蔵庫", "price": 18359, "Conditions": ["NEW", 2015]}    |
+-----------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_REPLACE(body,"$.price",FLOOR(10000 + (RAND() * 9000))) where id = 3;


/**** JSON_ARRAY_INSERT (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]>SELECT JSON_ARRAY_INSERT(body,'$.Conditions[2]','January') from T_JSON_DOC where id = 5;
+----------------------------------------------------------------------------------------+
| JSON_ARRAY_INSERT(body,'$.Conditions[2]','January')                                    |
+----------------------------------------------------------------------------------------+
| {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015, "January"]}    |
+----------------------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_ARRAY_INSERT(body,'$.Conditions[2]','January') where id = 5;



/**** JSON_ARRAY_APPEND (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> select JSON_ARRAY_APPEND(body,'$.Conditions','故障') from NEW57.T_JSON_DOC where id = 2;
+----------------------------------------------------------------------------------------+
| JSON_ARRAY_APPEND(body,'$.Conditions','故障')                                          |
+----------------------------------------------------------------------------------------+
| {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED", 2013, "故障"]}      |
+----------------------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_ARRAY_APPEND(body,'$.Conditions','故障') where id = 2;


/**** JSON_MERGE (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> SELECT JSON_MERGE(body,'{"Conditions":"Excellent"}') from T_JSON_DOC where id = 1;
+------------------------------------------------------------------------------------------+
| JSON_MERGE(body,'{"Conditions":"Excellent"}')                                            |
+------------------------------------------------------------------------------------------+
| {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}    |
+------------------------------------------------------------------------------------------+


[NEW57]> update T_JSON_DOC set body = JSON_MERGE(body,'{"Conditions":"Excellent"}') where id = 1;

補足情報
json_extractと->は同等

`feature_type` varchar(30) GENERATED ALWAYS AS (json_unquote(feature->"$.type")) VIRTUAL
`feature_type` varchar(30) GENERATED ALWAYS AS (json_unquote(json_extract(`feature`,'$.type'))) VIRTUAL

メモ:
-> /* JSON_EXTRACT() */
->> /* JSON_UNQUOTE(JSON_EXTRACT()) */

必要に応じてjson_unquoteでQuoteを外して列を作成

`feature_type` varchar(30) GENERATED ALWAYS AS (json_unquote(feature->"$.type")) VIRTUAL
`feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.properties.STREET')) VIRTUAL

[NEW57]> select feature_type,feature_street from T_JSON limit 10,5;
+--------------+----------------+
| feature_type | feature_street |
+--------------+----------------+
| Feature      | "JEFFERSON"    |
| Feature      | "TAYLOR"       |
| Feature      | "BEACH"        |
| Feature      | "BEACH"        |
| Feature      | "JEFFERSON"    |
+--------------+----------------+

生成列のVirtualとStoredの違いはありますが、Virtualであれば作成時も参照、更新もオンライン処理が可能。
但し、参照時にCPUを利用するのでStoredを選択するかは状況により使い分け。
Storedは実データを含む為、高速だが追加でディスク容量が必用。また、作成時は参照のみが可能になります。

ALTER TABLE T_ONLINE ALGORITHM=INPLACE, ADD feature_type varchar(30) AS (feature->"$.type") VIRTUAL;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE T_ONLINE ALGORITHM=INPLACE, ADD feature_type varchar(30) AS (feature->"$.type") STORED;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.

ALTER TABLE T_ONLINE ADD feature_type varchar(30) AS (feature->"$.type") STORED;
Query OK, 206560 rows affected (6.17 sec)
Records: 206560  Duplicates: 0  Warnings: 0



MySQL5.7からslave_parallel_workersを調整してスレーブの遅延が対応出来る事は、
色々な資料やブログ等にも書かれているので詳細はそちらを確認してみて下さい。
Oracle MySQL Cloud Service(OC3 = 2vCPU)の環境でSQLSLAPで負荷をかけてみて、
マスターとスレーブで遅延がどれだけ解消できるか?若しくはどこまで調整すれば良いか確認してみました。
slave_parallel_workersを1,2,4,8,16,32と変更して確認した中では、slave_parallel_workers=8が安定していました。
但し、slave_parallel_workersが多いからと言ってCPUが少ないインスタンスより上がる訳では無く、全体的なシステムのバランスが重要なようです。
スレーブのCPUや実行されているQuery等を確認して、適宜最適な値を調整出来ると良いですね。

環境
Oracle Cloud
MySQL Service
Instance: OC3 (2vCPU)
Monitoring: MySQL Enterprise Monitor

slave_parallel_workers=1

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> SET GLOBAL SLAVE_PARALLEL_WORKERS=1;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.04 sec)

mysql> show variables like 'SLAVE_PARALLEL_WORKERS';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 1     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> 

[oracle@study01-mysql-1 ~]$ ./mysqlslap.sh 
Benchmark
        Running for engine InnoDB
        Average number of seconds to run all queries: 38.711 seconds
        Minimum number of seconds to run all queries: 38.711 seconds
        Maximum number of seconds to run all queries: 38.711 seconds
        Number of clients running queries: 10
        Average number of queries per client: 10000

[oracle@study01-mysql-1 ~]$ 

slave_parallel_workers=8

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL SLAVE_PARALLEL_WORKERS=8;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

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

mysql> 
[oracle@study01-mysql-1 ~]$ ./mysqlslap.sh 
Benchmark
        Running for engine InnoDB
        Average number of seconds to run all queries: 38.466 seconds
        Minimum number of seconds to run all queries: 38.466 seconds
        Maximum number of seconds to run all queries: 38.466 seconds
        Number of clients running queries: 10
        Average number of queries per client: 10000

[oracle@study01-mysql-1 ~]$ 

以下、MySQL Enterprise Monitorで確認したグラフです。スレーブの遅延の状況が分かり易いですね。

slave_parallel_workers=2とslave_parallel_workers=8の差です。
1) Master -> 2) slave_parallel_workers=8 -> slave_parallel_workers=2の順にデータベース内での処理が終わっています。

slave_parallel_workers=2とslave_parallel_workers=32の差です。
この検証では、マスターとスレーブがほぼ同時に終了しています。

数回CPUの負荷を取得してみましたが、今回の環境ではThreadが少ない方がCPU負荷が上がっていました。何処に差があるかはまた別途確認してみますが、Threadが多いからと言って必ずしも少ないインスタンスよりCPU負荷が上がる訳では無さそうです。

[oracle@study02-mysql-1 ~]$ 
01:00:01 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
01:10:01 PM     all      0.97      0.08      0.18      0.80      0.07     97.90
01:20:01 PM     all      4.08      0.32      0.31      4.32      0.08     90.89
01:30:01 PM     all      0.78      2.31      7.91      8.56      0.12     80.33
01:40:01 PM     all      0.57      2.46      7.94     10.96      0.13     77.93
01:50:01 PM     all      0.61      0.14      0.09      0.66      0.06     98.44
02:00:01 PM     all      0.74      0.14      0.09      0.63      0.07     98.33
Average:        all      0.10      0.10      0.22      0.69      0.06     98.83


[oracle@study03-mysql-1 ~]$ 
01:00:01 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
01:10:01 PM     all      0.79      0.15      0.09      0.55      0.06     98.35
01:20:01 PM     all      0.68      0.34      0.16      3.82      0.09     94.91
01:30:01 PM     all      0.56      1.75      0.70      6.41      0.10     90.48
01:40:01 PM     all      0.58      1.75      0.75      8.11      0.12     88.69
01:50:01 PM     all      0.52      0.13      0.09      0.43      0.08     98.75
02:00:01 PM     all      0.55      0.13      0.09      0.86      0.07     98.30
Average:        all      0.23      0.09      0.08      0.61      0.07     98.93

大目に負荷をかけて、もう少し長いスパンで影響を確認してみました

[oracle@study01-mysql-1 ~]$ ./mysqlslap.sh 
Benchmark
        Running for engine InnoDB
        Average number of seconds to run all queries: 185.845 seconds
        Minimum number of seconds to run all queries: 185.845 seconds
        Maximum number of seconds to run all queries: 185.845 seconds
        Number of clients running queries: 10
        Average number of queries per client: 50000

[oracle@study01-mysql-1 ~]$

以下の、グラフを確認してみるとslave_parallel_workersを適切に調節すれば、レプリケーションの遅延、余計なCPU負荷の削減なども出来る事が確認出来ます。

補足: SHOW SLAVE STATUSによるマスターからの遅延状況の確認

slave_parallel_workersが8と16では殆ど変りが無い事が分かります。
サーバースペック、ネットワーク、インスタンスの負荷等によっても変わってくるので適宜調整下さい。