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の削減が可能ですが。レスポンスが要求される本番環境としては、日本のデータセンターでのサービスが待ち遠しい所です。

Additional Note on 2017/04/30:
mysqlbackupを利用してobject storageへのbackupを行う場合は、圧縮オプション(compress)を付けて頂いた方が圧倒的に早いです。
sampleデータベースのworldの圧縮率は80%以上で、7倍近く早くbackupが終了しました。

  

検証した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]# 

補足:mysqlbackupのCompressionオプションを利用してバックアップを圧縮した場合。
ネットワーク経由のBackup処理に勧め:(compression 85.26%).
圧縮した方が、7倍近く早く終了しました。


[root@misc01 temp_dir]# /usr/local/mysqlbackup/bin/mysqlbackup --include=world.* -uroot -p --cloud-service=openstack \
> --compress --compress-level=9 \
> --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 --compress --compress-level=9 
        --cloud-container=objectstoragename --cloud-object=mysqlbackup_opc.img 
        --cloud-user-id=Storage-myidentitydomain:myaccount@oracle.com 
        --cloud-password=xxxxxxxxxxxxxx 
        --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.18-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!".

170430 23:03:49 mysqlbackup: INFO: MEB logfile created at /home/admin/opc/temp_dir/meta/MEB_2017-04-30.23-03-49_compress_img_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 14935610299668812

 mysqlbackup: INFO: Uses LZ4 r109 for data compression.
 mysqlbackup: INFO: Creating 18 buffers each of size 16794072.
 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.
170430 23:04:01 mysqlbackup: INFO: Compress Image Backup operation starts with following threads
                1 read-threads    6 process-threads    1 write-threads
170430 23:04:01 mysqlbackup: INFO: System tablespace file format is Barracuda.
170430 23:04:01 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.
170430 23:04:01 mysqlbackup: INFO: Found checkpoint at lsn 4985717698.
170430 23:04:01 mysqlbackup: INFO: Starting log scan from lsn 4985717248.
170430 23:04:01 mysqlbackup: INFO: Copying log...
170430 23:04:01 mysqlbackup: INFO: Log copied, lsn 4985717707.
170430 23:04:01 mysqlbackup: INFO: Copying /usr/local/mysql/data/ibdata1 (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world/City.ibd (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world/City_Sample.ibd (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world/Country.ibd (Antelope file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world/CountryLanguage.ibd (Antelope file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world/T_STAT_SAMPLE.ibd (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world/T_TDE.ibd (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/City2.ibd (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/City3.ibd (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/City_Master.ibd (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/City_SnapShot.ibd (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/City_Summary.ibd (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/T_GC_UK.ibd (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/T_JSON_FROM_TABLE_SCHEMA.ibd (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/T_Pump01.ibd (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/t1.ibd (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world2/t2.ibd (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world_x/City.ibd (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world_x/Country.ibd (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world_x/CountryInfo.ibd (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Copying /usr/local/mysql/data/world_x/CountryLanguage.ibd (Barracuda file format).
170430 23:04:02 mysqlbackup: INFO: Completing the copy of innodb files.
170430 23:04:03 mysqlbackup: INFO: Starting to copy Binlog files...
170430 23:04:03 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql-bin.002652.
170430 23:04:03 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql-bin.002653.
170430 23:04:03 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql-bin.002654.
170430 23:04:03 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql-bin.002655.
170430 23:04:03 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql-bin.002656.
170430 23:04:03 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql-bin.002657.
170430 23:04:03 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
170430 23:04:03 mysqlbackup: INFO: Starting to lock all the tables...
170430 23:04:03 mysqlbackup: INFO: All tables are locked and flushed to disk
170430 23:04:03 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql-bin.002658.
170430 23:04:03 mysqlbackup: INFO: Completed the copy of binlog files...
170430 23:04:03 mysqlbackup: INFO: Opening backup source directory '/usr/local/mysql/data'
170430 23:04:03 mysqlbackup: INFO: Starting to backup all non-innodb files in 
        subdirectories of '/usr/local/mysql/data'
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Adding database directory: datadir
170430 23:04:03 mysqlbackup: INFO: Completing the copy of all non-innodb files.
170430 23:04:04 mysqlbackup: INFO: A copied database page was modified at 4985717580.
          (This is the highest lsn found on page)
          Scanned log up to lsn 4985717707.
          Was able to parse the log up to lsn 4985717707.
170430 23:04:04 mysqlbackup: INFO: All tables unlocked
170430 23:04:04 mysqlbackup: INFO: All MySQL tables were locked for 1.086 seconds.
170430 23:04:04 mysqlbackup: INFO: Reading all global variables from the server.
170430 23:04:04 mysqlbackup: INFO: Completed reading of all global variables from the server.
 mysqlbackup: INFO: Writing config file for server '5.7.18-enterprise-commercial-advanced-log'.
170430 23:04:04 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.
170430 23:05:41 mysqlbackup: INFO: 82 MB of data files compressed to 12376 kbytes (compression 85.26%).
170430 23:05:41 mysqlbackup: INFO: Compress Image Backup operation completed successfully.
 mysqlbackup: INFO: Image Path = 'stdout'
170430 23:05:41 mysqlbackup: INFO: MySQL binlog position: filename mysql-bin.002658, position 194

-------------------------------------------------------------
   Parameters Summary         
-------------------------------------------------------------
   Start LSN                  : 4985717248
   End LSN                    : 4985717707
-------------------------------------------------------------

mysqlbackup completed OK!
[root@misc01 temp_dir]# 

補足: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/


Oracle MySQL Cloud ServiceでMySQLインスタンスのPoint In Time Recoveryを実行
MySQLCS (MySQL Cloud Service)
ダッシュボードから時間指定でのリストアを実行した時に、バックグラウンドで動いているプロセスの確認になります。

STEP1) 既存データをフルバックアップ
STEP2) 差分データのINSERT
STEP3) 差分バックアップ
STEP4) データの削除
STEP5) STEP3の直前まで時間指定でリストア
STEP6) データの確認

■ FULL BACKUP実行時のデータ

mysql> show tables;
+--------------+
| Tables_in_SC |
+--------------+
| T_POT_TEST   |
+--------------+
1 row in set (0.00 sec)

mysql> select * from T_POT_TEST;
+----+-----------+---------+
| id | body      | type    |
+----+-----------+---------+
|  1 | 123456789 | INTEGER |
|  2 | "abcde"   | STRING  |
+----+-----------+---------+
2 rows in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-01-27 14:20:56 |
+---------------------+
1 row in set (0.00 sec)

■ FULLバックアップ

mysql> select * from backup_history order by backup_id desc limit 1\G
*************************** 1. row ***************************
                backup_id: 14855264590951390
                tool_name: /u01/bin/meb/bin/mysqlbackup --user=oracle --backup-dir=/u01/backup/MySQLCS/OPC-MYSQL01/scheduledFull/e27856f6-25d7-45d8-9214-a8a3eb899d0d --backup-image=backufull.mbi --compress backup-to-image 
               start_time: 2017-01-27 14:14:19
                 end_time: 2017-01-27 14:14:28
               binlog_pos: 154
              binlog_file: opc-mysql01-mysql-1.000001
        compression_level: 1
                  engines: CSV:InnoDB:MyISAM:PERFORMANCE_SCHEMA
    innodb_data_file_path: ibdata1:12M:autoextend
       innodb_file_format: Barracuda
                start_lsn: 6623068672
                  end_lsn: 6623068712
     incremental_base_lsn: 0
              backup_type: FULL
            backup_format: IMAGE
           mysql_data_dir: /u01/data/mysql/
     innodb_data_home_dir: 
innodb_log_group_home_dir: /u01/translog/inno-bin-logs
innodb_log_files_in_group: 6
     innodb_log_file_size: 1073741824
       backup_destination: /u01/backup/MySQLCS/OPC-MYSQL01/scheduledFull/e27856f6-25d7-45d8-9214-a8a3eb899d0d
                lock_time: 0.809
               exit_state: SUCCESS
               last_error: NO_ERROR
          last_error_code: 0
1 row in set (0.01 sec)

■ FULL BACKUP後にデータをINSERT

mysql> insert into T_POT_TEST(body) values('"PITR TEST"');
Query OK, 1 row affected (0.11 sec)

mysql> select * from T_POT_TEST;
+----+-------------+---------+
| id | body        | type    |
+----+-------------+---------+
|  1 | 123456789   | INTEGER |
|  2 | "abcde"     | STRING  |
|  3 | "PITR TEST" | STRING  |
+----+-------------+---------+
3 rows in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-01-27 14:21:46 |
+---------------------+
1 row in set (0.00 sec)

mysql> 

■ ここで差分を取得してみる。

mysql> select * from backup_history order by backup_id desc limit 1\G
*************************** 1. row ***************************
                backup_id: 14855271179669251
                tool_name: /u01/bin/meb/bin/mysqlbackup --user=oracle --backup-dir=/u01/backup/MySQLCS/OPC-MYSQL01/scheduledIncremental/63412628-3fcd-4b60-9e9d-ef1bacc15032 --backup-image=backup_incremental.mbi --incremental_base=dir:/u01/backup/MySQLCS/OPC-MYSQL01/scheduledFull/e27856f6-25d7-45d8-9214-a8a3eb899d0d --incremental backup-to-image 
               start_time: 2017-01-27 14:25:17
                 end_time: 2017-01-27 14:25:21
               binlog_pos: 19807766
              binlog_file: opc-mysql01-mysql-1.000002
        compression_level: 0
                  engines: CSV:InnoDB:MyISAM:PERFORMANCE_SCHEMA
    innodb_data_file_path: ibdata1:12M:autoextend
       innodb_file_format: Barracuda
                start_lsn: 6623068713
                  end_lsn: 6633728074
     incremental_base_lsn: 0
              backup_type: INCREMENTAL
            backup_format: IMAGE
           mysql_data_dir: /u01/data/mysql/
     innodb_data_home_dir: 
innodb_log_group_home_dir: /u01/translog/inno-bin-logs
innodb_log_files_in_group: 6
     innodb_log_file_size: 1073741824
       backup_destination: /u01/backup/MySQLCS/OPC-MYSQL01/scheduledIncremental/63412628-3fcd-4b60-9e9d-ef1bacc15032
                lock_time: 0.684
               exit_state: SUCCESS
               last_error: NO_ERROR
          last_error_code: 0
1 row in set (0.00 sec)

mysql> 

■テーブルからデータを削除します。

mysql> truncate table T_POT_TEST;select * from T_POT_TEST;select now();
Query OK, 0 rows affected (0.02 sec)

Empty set (0.00 sec)

+---------------------+
| now()               |
+---------------------+
| 2017-01-27 14:28:32 |
+---------------------+
1 row in set (0.00 sec)

mysql> 

■ 目標リストア時間を指定してリストア

2017-01-27 14:21:45

※こちらの画像はサンプルですので、時間は上記の時間を指定しました。

■ダッシュボードから時間指定でリストア開始
STEP1) フルリストアが実行されている


[oracle@opc-mysql01-mysql-1 ~]$ ps -ef | grep mysql
oracle   14423 14348 20 14:31 ?        00:00:05 /u01/bin/meb/bin/mysqlbackup --user=oracle --cloud-service=openstack --cloud-user-id=Storage-xxxxxxx:xxxxx.xxxxx@oracle.com --cloud-password=xxxxxxxxxx --cloud-tempauth-url=https://xxxxxxx.storage.oraclecloud.com --cloud-container=mysqlstorage --cloud-object=MySQLCS/OPC-MYSQL01/scheduledFull/e27856f6-25d7-45d8-9214-a8a3eb899d0d/backup_full.mbi --backup-dir=/u01/backup/MySQLCS/OPC-MYSQL01/scheduledFull/e27856f6-25d7-45d8-9214-a8a3eb899d0d --cloud-ca-info=/etc/ssl/certs/ca-bundle.crt --datadir=/u01/data/mysql-14348 --innodb_log_group_home_dir=/u01/translog/inno-bin-logs-14348 --backup-image=- --uncompress --force copy-back-and-apply-log
oracle   14459 10312  0 14:32 pts/0    00:00:00 grep mysql
[oracle@opc-mysql01-mysql-1 ~]$ 

STEP2) 差分リストアが実行されている

[oracle@opc-mysql01-mysql-1 ~]$ ps -ef | grep mysql
oracle   14509     1  0 14:33 ?        00:00:00 /bin/sh ./bin/mysqld_safe --skip-networking
oracle   15593 14509 25 14:33 ?        00:00:06 /u01/bin/mysql/bin/mysqld --basedir=/u01/bin/mysql --datadir=/u01/data/mysql --plugin-dir=/u01/bin/mysql/lib/plugin --skip-networking --log-error=/u01/data/mysql/opc-mysql01-mysql-1.err --open-files-limit=20000 --pid-file=/u01/data/mysql/opc-mysql01-mysql-1.pid
oracle   15721 14348  0 14:33 ?        00:00:00 /bin/sh -c /u01/bin/mysql/bin/mysqlbinlog --skip-gtids --start-position=154 --stop-datetime="2017-01-27 14:21:45"  /u01/backup/temp/datadir/opc-mysql01-mysql-1.000001 /u01/backup/temp/datadir/opc-mysql01-mysql-1.000002|/u01/bin/mysql/bin/mysql -uoracle 
oracle   15722 15721  1 14:33 ?        00:00:00 /u01/bin/mysql/bin/mysqlbinlog --skip-gtids --start-position=154 --stop-datetime=2017-01-27 14:21:45 /u01/backup/temp/datadir/opc-mysql01-mysql-1.000001 /u01/backup/temp/datadir/opc-mysql01-mysql-1.000002
oracle   15723 15721  3 14:33 ?        00:00:00 /u01/bin/mysql/bin/mysql -uoracle
oracle   15729 10312  0 14:34 pts/0    00:00:00 grep mysql
[oracle@opc-mysql01-mysql-1 ~]$ 

STEP3) MySQLの起動

[oracle@opc-mysql01-mysql-1 ~]$ ps -ef | grep mysql
oracle   15811     1  1 14:35 ?        00:00:00 /bin/sh ./bin/mysqld_safe
oracle   16875 15811 28 14:35 ?        00:00:00 /u01/bin/mysql/bin/mysqld --basedir=/u01/bin/mysql --datadir=/u01/data/mysql --plugin-dir=/u01/bin/mysql/lib/plugin --log-error=/u01/data/mysql/opc-mysql01-mysql-1.err --open-files-limit=20000 --pid-file=/u01/data/mysql/opc-mysql01-mysql-1.pid
oracle   16974 10312  0 14:35 pts/0    00:00:00 grep mysql
[oracle@opc-mysql01-mysql-1 ~]$ 

※ リストアが問題無く終わると自動的にFULLバックアップの取得が開始されます。

■リストア後のデータ確認


mysql> select * from T_POT_TEST;select now();
+----+-------------+---------+
| id | body        | type    |
+----+-------------+---------+
|  1 | 123456789   | INTEGER |
|  2 | "abcde"     | STRING  |
|  3 | "PITR TEST" | STRING  |
+----+-------------+---------+
3 rows in set (0.00 sec)

+---------------------+
| now()               |
+---------------------+
| 2017-01-27 14:36:26 |
+---------------------+
1 row in set (0.00 sec)

mysql> 


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では殆ど変りが無い事が分かります。
サーバースペック、ネットワーク、インスタンスの負荷等によっても変わってくるので適宜調整下さい。


Oracle MySQL Cloud Serviceのダッシュボードにパッチがアナウンスされていたのでパッチの適用を行いました。基本的な適用フローは以下の手順となります。

プリチェック → バックアップ(時間はDBサイズによる)→ パッチ適用 → パッチ適用完了
※バックアップはmysqldump(論理バックアップ)では無く、mysqlbackup(物理バックアップ)なので処理は高速です。

MySQL Cloud Serviceに関しては、こちらにてドキュメントとトライアルを確認する事が出来ます。
https://cloud.oracle.com/ja_JP/mysql
※ 2016年12月現在ではU.SとEMEAでのみ、MySQL Cloud Serviceを提供しているので検証環境、海外サービス展開、Enterprise版の機能検証に利用するのが宜しいかと思います。

■ パッチ適用前

mysql> select @@hostname,@@version,now();
+-----------------+-------------------------------------------+---------------------+
| @@hostname      | @@version                                 | now()               |
+-----------------+-------------------------------------------+---------------------+
| study01-mysql-1 | 5.7.15-enterprise-commercial-advanced-log | 2016-12-13 08:16:06 |
+-----------------+-------------------------------------------+---------------------+
1 row in set (0.00 sec)

mysql> 

1) パッチ適用前の状態

2) パッチ適用前の事前チェック

3) 事前チェック完了

4) パッチ適用開始による事前フルバックアップの自動実施

5) バックアップ完了と同時にパッチの適用開始

6) パッチ適用の完了

補足:バックアップの完了した時点でのダッシュボードでの履歴確認

■ パッチ適用中のバックグラウド処理

1) MySQL5.7.16のバイナリーを取得

[oracle@study01-mysql-1 ~]$ ps -ef | grep mysql
oracle   15317     1  0 08:16 ?        00:00:00 /bin/sh ./bin/mysqld_safe
oracle   16372 15317  1 08:16 ?        00:00:01 /u01/bin/mysql/bin/mysqld --basedir=/u01/bin/mysql --datadir=/u01/data/mysql --plugin-dir=/u01/bin/mysql/lib/plugin --log-error=/u01/data/mysql/study01-mysql-1.err --open-files-limit=20000 --pid-file=/u01/data/mysql/study01-mysql-1.pid
oracle   17135 17122  0 08:19 ?        00:00:00 /bin/sh -c source /u01/app/oracle/tools/mscs/mysql/vm-scripts/msaas-provisioning-utils.sh && fetchArtifact /tmp/patch20161213-081909/msaas-installer-patch.zip  https://em2.storage.oraclecloud.com/v1/backups-jimops/jcs MSCS/5.7.16/MySQL_Binaries-5.7.16.zip
oracle   17183 15032  0 08:19 pts/0    00:00:00 grep mysql

2) 展開

[oracle@study01-mysql-1 ~]$ ps -ef | grep mysql
oracle   17242 17122  0 08:19 ?        00:00:00 /bin/sh -c source /u01/app/oracle/tools/mscs/mysql/vm-scripts/msaas-provisioning-utils.sh && source /u01/app/oracle/tools/mscs/mysql/vm-scripts/mysql-installation-utils.sh && extractMysql /tmp/patch-msaas-installers20161213-081909/mysql/*.tar.gz 5.7.16 /u01/bin oracle
oracle   17244 17243 11 08:19 ?        00:00:01 tar zxvf /tmp/patch-msaas-installers20161213-081909/mysql/mysql-installer-5.7.16.tar.gz --directory=/u01/bin
oracle   17289 15032  0 08:19 pts/0    00:00:00 grep mysql
[oracle@study01-mysql-1 ~]$ 

3) MySQLへのネットワークアクセスをOFFにして起動して、パッチを適用 (ホストにはアクセス可能)

[oracle@study01-mysql-1 ~]$ ps -ef | grep mysql
oracle   17346     1  0 08:20 ?        00:00:00 /bin/sh ./bin/mysqld_safe --skip-networking
oracle   18416 17346 45 08:20 ?        00:00:02 /u01/bin/mysql/bin/mysqld --basedir=/u01/bin/mysql --datadir=/u01/data/mysql --plugin-dir=/u01/bin/mysql/lib/plugin --skip-networking --log-error=/u01/data/mysql/study01-mysql-1.err --open-files-limit=20000 --pid-file=/u01/data/mysql/study01-mysql-1.pid
oracle   18533 17122  0 08:20 ?        00:00:00 /bin/sh -c source /u01/app/oracle/tools/mscs/mysql/vm-scripts/mysql-installation-utils.sh && shutdownMysql oracle 1
oracle   18539 18533  0 08:20 ?        00:00:00 mysqladmin shutdown -uoracle
oracle   18582 15032  0 08:20 pts/0    00:00:00 grep mysql
[oracle@study01-mysql-1 ~]$ 

4) パッチ適用が完了してMySQLを再起動して完了

[oracle@study01-mysql-1 ~]$ ps -ef | grep mysql
oracle   18591     1  0 08:20 ?        00:00:00 /bin/sh ./bin/mysqld_safe
oracle   19646 18591  3 08:20 ?        00:00:00 /u01/bin/mysql/bin/mysqld --basedir=/u01/bin/mysql --datadir=/u01/data/mysql --plugin-dir=/u01/bin/mysql/lib/plugin --log-error=/u01/data/mysql/study01-mysql-1.err --open-files-limit=20000 --pid-file=/u01/data/mysql/study01-mysql-1.pid
oracle   19883 15032  0 08:21 pts/0    00:00:00 grep mysql
[oracle@study01-mysql-1 ~]$ 

■ パッチ適用後

mysql> select @@hostname,@@version,now();
+-----------------+-------------------------------------------+---------------------+
| @@hostname      | @@version                                 | now()               |
+-----------------+-------------------------------------------+---------------------+
| study01-mysql-1 | 5.7.16-enterprise-commercial-advanced-log | 2016-12-13 08:23:18 |
+-----------------+-------------------------------------------+---------------------+
1 row in set (0.00 sec)

mysql> 

REST APIを利用したインスタンスの詳細確認
Path: /paas/api/v1.1/instancemgmt/{identityDomainId}/services/MySQLCS/instances/{serviceName}/patches/applied

[admin@misc01 opc]$ cat temp.sh 
#!/bin/sh

curl -X GET -u "{user:password}" \
-H "X-ID-TENANT-NAME:{ServiceDomainID}" -H "Accept: application/json" \
https://psm.europe.oraclecloud.com/paas/api/v1.1/instancemgmt/{ServiceDomainID}/services/MySQLCS/instances/{InstanceName}/patches/applied
[admin@misc01 opc]$ 

以下、実行結果

[admin@misc01 opc]$ ./patchchk_opc_mysql.sh 
[{"backupStatus":"Available","additionalNote":"Apply Patch to Server.\nVersion Up to 5.7.16\nby admin","appliedBy":"opc_user","appliedDate":"2016-12-13T08:18:35.189+0000","totalTime":"2 min, 20 sec","patchId":"5.7.16-EE","patchDescription":"MySQL Server version 5.7.16","patchReleaseUrl":"https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-16.html","releaseDate":"2016-10-12T09:00:00.000+0000","resultMessage":"MySQL patching succeeded","lastStatus":"COMPLETED","lastStatusMessage":"MySQL patching succeeded","componentPatches":{"mysql":{"id":1201,"version":"5.7","releaseVersion":"5.7.16","displayName":"MySQL Server version 5.7.16","description":"MySQL Server version 5.7.16","zipBundles":{"MSAAS":{"id":1214,"md5sum":"ec534747c1e3d5d0f644ff3d9bd52115","provisioningObjectRef":"MSCS/5.7.16/MySQL_Binaries-5.7.16.zip","storageKey":"MSCS/5.7.16/MySQL_Binaries-5.7.16.zip","releaseDate":"10/12/2016","releaseUrl":"https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-16.html","displayName":"MySQL Server version 5.7.16"}},"preserveFiles":[]}},"patchComponents":[{"id":1201,"component":"mysql","version":"5.7","md5sum":"ec534747c1e3d5d0f644ff3d9bd52115","provisioningObjectRef":"MSCS/5.7.16/MySQL_Binaries-5.7.16.zip","patchingObjectRef":"MSCS/5.7.16/MySQL_Binaries-5.7.16.zip","preserveFiles":[]}],"patchType":"Quarterly","patchCategory":"Upgrade","patchSeverity":"Normal","jobId":"3260506","displayName":"5.7.16","toVersion":"5.7.16","backupId":"90b6eda2-969d-4753-9ae6-e8d318760530","inProgress":false,"operationType":"None","id":77602,"patchingResult":{"patchingId":60654,"backupId":"90b6eda2-969d-4753-9ae6-e8d318760530","versionBeforeThisPatch":"mysql 5.7.15","strategy":"Rolling","releaseVersionBeforeThisPatch":"5.7.15","customRollbackId":"3260506_1481617115189","startDate":"2016-12-13T08:18:35.189+0000","endDate":"2016-12-13T08:20:55.660+0000","patchingStatus":"COMPLETED","resultMessage":"MySQL patching succeeded","additionalNote":"Apply Patch to Server.\nVersion Up to 5.7.16\nby admin","appliedBy":"opc_user","jobId":"3260506","completeLog":"","progressMessages":["8:18:35.161 AM Phase initialize started","8:18:35.339 AM Phase initialize completed","8:18:35.696 AM Phase backup started","8:19:06.850 AM Phase backup completed","8:19:07.178 AM Phase patch started","8:19:07.616 AM Phase patch-rex started","8:20:54.463 AM Phase patch-rex completed","8:20:54.984 AM Phase patch completed","8:20:55.360 AM Phase finalize started","8:20:55.532 AM Completed"]},"rollbackId":"77602","rollbackVersion":"mysql 5.7.15","currentPatchLevel":"mysql 5.7.16","progressMessages":["8:18:35.161 AM Phase initialize started","8:18:35.339 AM Phase initialize completed","8:18:35.696 AM Phase backup started","8:19:06.850 AM Phase backup completed","8:19:07.178 AM Phase patch started","8:19:07.616 AM Phase patch-rex started","8:20:54.463 AM Phase patch-rex completed","8:20:54.984 AM Phase patch completed","8
[admin@misc01 opc]$ 

確認:スレーブ→マスターの順番でアップグレードし、MySQL Cloud Serviceで稼働しているレプリケーションも問題無いことを確認済み

REST APIを利用してパッチ適用する場合は、以下のURLを参照下さい。
【書式】
1) Pre-Check

/paas/api/v1.1/instancemgmt/{identityDomainId}/services/MySQLCS/instances/{serviceName}/patches/checks/{patchId}

http://docs.oracle.com/cloud/latest/mysql-cloud/CSMCS/op-paas-api-v1.1-instancemgmt-%7BidentityDomainId%7D-services-MySQLCS-instances-%7BserviceName%7D-patches-checks-%7BpatchId%7D-put.html

2) Apply Patch

/paas/api/v1.1/instancemgmt/{identityDomainId}/services/MySQLCS/instances/{serviceName}/patches/{patchId}

http://docs.oracle.com/cloud/latest/mysql-cloud/CSMCS/op-paas-api-v1.1-instancemgmt-%7BidentityDomainId%7D-services-MySQLCS-instances-%7BserviceName%7D-patches-%7BpatchId%7D-put.html

ALL) Patches REST Endpoints
http://docs.oracle.com/cloud/latest/mysql-cloud/CSMCS/api-Patches.html


MySQL5.6以降で標準準拠のGET DIAGNOSTICSが実装され、その中でMySQLのエラーコードを取得することが可能になります。
MySQL5.7(5.7.2)以降でSTACKEDが追加されています。エラーの詳細を記録するのに使えるかと思います。詳細については次のマニュアルをご参照ください。

GET DIAGNOSTICS Syntax
http://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html
http://dev.mysql.com/doc/refman/5.7/en/get-diagnostics.html
Worklog: http://dev.mysql.com/worklog/task/?id=2111

MySQL5.6
GET [CURRENT] DIAGNOSTICS

MySQL5.7
GET [CURRENT | STACKED] DIAGNOSTICS

SQL statements produce diagnostic information that populates the diagnostics area. Standard SQL has a diagnostics area stack, containing a diagnostics area for each nested execution context. Standard SQL also supports GET STACKED DIAGNOSTICS syntax for referring to the second diagnostics area during condition handler execution. MySQL supports the STACKED keyword as of MySQL 5.7. Before that, MySQL does not support STACKED; there is a single diagnostics area containing information from the most recent statement that wrote to it.
標準SQLは条件ハンドラの実行中に、第2の診断領域を参照するためのSTACKED診断構文をGETサポートしています。

■ GET CURRENTの挙動
The GET DIAGNOSTICS statement is typically used in a handler within a stored program.
It is a MySQL extension that GET [CURRENT] DIAGNOSTICS is permitted outside handler context to check the execution of any SQL statement.


root@localhost [CONFIRM]> DROP TABLE IF EXISTS t334;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [CONFIRM]> GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MYSQL_ERRNO, @p3 = MESSAGE_TEXT;
Query OK, 0 rows affected (0.00 sec)

root@localhost [CONFIRM]> SELECT @p1, @p2, @p3;
+-------+------+------------------------------+
| @p1   | @p2  | @p3                          |
+-------+------+------------------------------+
| 42S02 | 1051 | Unknown table 'CONFIRM.t334' |
+-------+------+------------------------------+
1 row in set (0.00 sec)

root@localhost [CONFIRM]> 

■ GET STACKEDの挙動
The GET DIAGNOSTICS statement is typically used in a handler within a stored program.
It is a MySQL extension that GET [CURRENT] DIAGNOSTICS is permitted outside handler context to check the execution of any SQL statement.
プロシジャー外部での利用の為、エラーになっています。

root@localhost [CONFIRM]> DROP TABLE IF EXISTS t334;
Query OK, 0 rows affected, 1 warning (0.01 sec)

root@localhost [CONFIRM]> GET STACKED DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MYSQL_ERRNO, @p3 = MESSAGE_TEXT;
ERROR 3004 (0Z002): GET STACKED DIAGNOSTICS when handler not active
root@localhost [CONFIRM]> 

■ CURRENTとSTACKEDの挙動

root@localhost [USER01]> delimiter //
root@localhost [USER01]> CREATE PROCEDURE p ()
    -> BEGIN
    ->   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
    ->     SIGNAL SQLSTATE '01000';
    ->     GET CURRENT DIAGNOSTICS CONDITION 1 @current = RETURNED_SQLSTATE;
    ->     GET STACKED DIAGNOSTICS CONDITION 1 @stacked = RETURNED_SQLSTATE;
    ->     END;
    ->   SIGNAL SQLSTATE '03000';
    ->   END//
Query OK, 0 rows affected (0.00 sec)

root@localhost [USER01]> CALL p()//
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [USER01]> SELECT @current, @stacked//
+----------+----------+
| @current | @stacked |
+----------+----------+
| 01000    | 03000    |
+----------+----------+
1 row in set (0.00 sec)

root@localhost [USER01]> 

■応用:エラーが発生した原因をテーブルに格納して後で確認

root@localhost [CONFIRM]> CREATE TABLE ids(id INT NOT NULL, UNIQUE(id));
Query OK, 0 rows affected (0.03 sec)

root@localhost [CONFIRM]> CREATE TABLE errors(id INT NOT NULL,RS VARCHAR(16),ME varchar(16),MT VARCHAR(1000));
Query OK, 0 rows affected (0.03 sec)

root@localhost [CONFIRM]> delimiter //
root@localhost [CONFIRM]> CREATE PROCEDURE insert_id(id INT)
    -> BEGIN
    -> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    -> BEGIN
    -> GET STACKED DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MYSQL_ERRNO, @p3 = MESSAGE_TEXT;
    -> INSERT INTO errors VALUES(id, @p1,@p2,@p3);
    -> IF @errno = 1213 THEN
    -> RESIGNAL;
    -> END IF;
    -> END;
    -> INSERT INTO ids VALUES(id);
    -> END;
    -> //
Query OK, 0 rows affected (0.01 sec)

root@localhost [CONFIRM]> delimiter ;
root@localhost [CONFIRM]> CALL insert_id(0);CALL insert_id(1);CALL insert_id(2);CALL insert_id(2);
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

root@localhost [CONFIRM]> select * from ids;
+----+
| id |
+----+
|  0 |
|  1 |
|  2 |
+----+
3 rows in set (0.00 sec)

root@localhost [CONFIRM]> select * from errors;
+----+-------+------+----------------------------------+
| id | RS    | ME   | MT                               |
+----+-------+------+----------------------------------+
|  2 | 23000 | 1062 | Duplicate entry '2' for key 'id' |
+----+-------+------+----------------------------------+
1 row in set (0.00 sec)

root@localhost [CONFIRM]> 

参考)
http://dev.mysql.com/doc/refman/5.6/ja/get-diagnostics.html
http://dev.mysql.com/doc/refman/5.6/ja/diagnostics-area.html
http://dev.mysql.com/doc/refman/5.7/en/get-diagnostics.html
http://dev.mysql.com/doc/refman/5.7/en/diagnostics-area.html