バックアップをリモートの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


先日、パフォーマンス検証したMySQL Cluster(7.5.3 RC)ですが、MySQL Cluster7.5.4でついにGAになりました。高可用性、インメモリー処理、トランザクション対応したNoSQL/SQL処理、オンライン拡張性が求められるシステムを探されている場合は、是非試してみて下さい。

7.5では、パフォーマンス最適化オプションだけでなく、ndbinfoから様々な構成や稼働情報を簡単に確認する事が出来るようになっています。これまで、クラスターコンフィグファイル等で確認していた内容もクエリーで即座に複数ノードを跨った設定を確認出来るようになってますので、これまで以上に運用がし易くなるかと思います。

Changes in MySQL Cluster NDB 7.5.4 (5.7.16-ndb-7.5.4) (2016-10-18, General Availability)
http://dev.mysql.com/doc/relnotes/mysql-cluster/7.5/en/mysql-cluster-news-7-5-4.html

MySQL Cluster 7.5 パフォーマンス検証

確認:バージョン

mysql> select @@version;
+----------------------------------------------+
| @@version                                    |
+----------------------------------------------+
| 5.7.16-ndb-7.5.4-cluster-commercial-advanced |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> 

config_paramsとconfig_valuesを利用したMySQL Clusterパラメータの容易な確認

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.03 sec)

mysql> 

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 like 'max%';
+-----------------------------------+------+----------+------------+----------+---------------+----------+------------+
| Name                              | Node | Type     | Default    | Minimum  | Maximum       | Required | Current    |
+-----------------------------------+------+----------+------------+----------+---------------+----------+------------+
| MaxNoOfSavedMessages              |    1 | unsigned | 25         | 0        | 4294967039    | N        | 25         |
| MaxNoOfTables                     |    1 | unsigned | 128        | 8        | 20320         | N        | 130        |
| MaxNoOfAttributes                 |    1 | unsigned | 1000       | 32       | 4294967039    | N        | 1009       |
| MaxNoOfTriggers                   |    1 | unsigned | 768        | 0        | 4294967039    | N        | 1784       |
| MaxNoOfConcurrentTransactions     |    1 | unsigned | 4096       | 32       | 4294967039    | N        | 4096       |
| MaxNoOfConcurrentOperations       |    1 | unsigned | 32768      | 32       | 4294967039    | N        | 10000      |
| MaxNoOfConcurrentScans            |    1 | unsigned | 256        | 2        | 500           | N        | 256        |
| MaxNoOfFiredTriggers              |    1 | unsigned | 4000       | 0        | 4294967039    | N        | 4000       |
| MaxNoOfConcurrentIndexOperations  |    1 | unsigned | 8192       | 0        | 4294967039    | N        | 8192       |
| MaxNoOfOrderedIndexes             |    1 | unsigned | 128        | 0        | 4294967039    | N        | 512        |
| MaxNoOfUniqueHashIndexes          |    1 | unsigned | 64         | 0        | 4294967039    | N        | 64         |
| MaxAllocate                       |    1 | unsigned | 33554432   | 1048576  | 1073741824    | N        | 33554432   |
| MaxNoOfSubscriptions              |    1 | unsigned | 0          | 0        | 4294967039    | N        | 0          |
| MaxNoOfSubscribers                |    1 | unsigned | 0          | 0        | 4294967039    | N        | 0          |
| MaxNoOfConcurrentSubOperations    |    1 | unsigned | 256        | 0        | 4294967039    | N        | 256        |
| MaxBufferedEpochs                 |    1 | unsigned | 100        | 1        | 100000        | N        | 100        |
| MaxNoOfExecutionThreads           |    1 | unsigned | 0          | 2        | 72            | N        | 2          |
| MaxBufferedEpochBytes             |    1 | unsigned | 26214400   | 26214400 | 4294967039    | N        | 26214400   |
| MaxLCPStartDelay                  |    1 | unsigned | 0          | 0        | 600           | N        | 0          |
| MaxStartFailRetries               |    1 | unsigned | 3          | 0        | 4294967039    | N        | 3          |
| MaxParallelScansPerFragment       |    1 | unsigned | 256        | 1        | 4294967039    | N        | 256        |
| MaxDMLOperationsPerTransaction    |    1 | unsigned | 4294967295 | 32       | 4294967295    | N        | 4294967295 |
| MaxDiskWriteSpeed                 |    1 | unsigned | 20971520   | 1048576  | 1099511627776 | N        | 20971520   |
| MaxDiskWriteSpeedOtherNodeRestart |    1 | unsigned | 52428800   | 1048576  | 1099511627776 | N        | 52428800   |
| MaxDiskWriteSpeedOwnRestart       |    1 | unsigned | 209715200  | 1048576  | 1099511627776 | N        | 209715200  |
| MaxParallelCopyInstances          |    1 | unsigned | 0          | 0        | 64            | N        | 0          |
| MaxSendDelay                      |    1 | unsigned | 0          | 0        | 11000         | N        | 0          |
| MaxNoOfSavedMessages              |    2 | unsigned | 25         | 0        | 4294967039    | N        | 25         |
| MaxNoOfTables                     |    2 | unsigned | 128        | 8        | 20320         | N        | 130        |
| MaxNoOfAttributes                 |    2 | unsigned | 1000       | 32       | 4294967039    | N        | 1009       |
| MaxNoOfTriggers                   |    2 | unsigned | 768        | 0        | 4294967039    | N        | 1784       |
| MaxNoOfConcurrentTransactions     |    2 | unsigned | 4096       | 32       | 4294967039    | N        | 4096       |
| MaxNoOfConcurrentOperations       |    2 | unsigned | 32768      | 32       | 4294967039    | N        | 10000      |
| MaxNoOfConcurrentScans            |    2 | unsigned | 256        | 2        | 500           | N        | 256        |
| MaxNoOfFiredTriggers              |    2 | unsigned | 4000       | 0        | 4294967039    | N        | 4000       |
| MaxNoOfConcurrentIndexOperations  |    2 | unsigned | 8192       | 0        | 4294967039    | N        | 8192       |
| MaxNoOfOrderedIndexes             |    2 | unsigned | 128        | 0        | 4294967039    | N        | 512        |
| MaxNoOfUniqueHashIndexes          |    2 | unsigned | 64         | 0        | 4294967039    | N        | 64         |
| MaxAllocate                       |    2 | unsigned | 33554432   | 1048576  | 1073741824    | N        | 33554432   |
| MaxNoOfSubscriptions              |    2 | unsigned | 0          | 0        | 4294967039    | N        | 0          |
| MaxNoOfSubscribers                |    2 | unsigned | 0          | 0        | 4294967039    | N        | 0          |
| MaxNoOfConcurrentSubOperations    |    2 | unsigned | 256        | 0        | 4294967039    | N        | 256        |
| MaxBufferedEpochs                 |    2 | unsigned | 100        | 1        | 100000        | N        | 100        |
| MaxNoOfExecutionThreads           |    2 | unsigned | 0          | 2        | 72            | N        | 2          |
| MaxBufferedEpochBytes             |    2 | unsigned | 26214400   | 26214400 | 4294967039    | N        | 26214400   |
| MaxLCPStartDelay                  |    2 | unsigned | 0          | 0        | 600           | N        | 0          |
| MaxStartFailRetries               |    2 | unsigned | 3          | 0        | 4294967039    | N        | 3          |
| MaxParallelScansPerFragment       |    2 | unsigned | 256        | 1        | 4294967039    | N        | 256        |
| MaxDMLOperationsPerTransaction    |    2 | unsigned | 4294967295 | 32       | 4294967295    | N        | 4294967295 |
| MaxDiskWriteSpeed                 |    2 | unsigned | 20971520   | 1048576  | 1099511627776 | N        | 20971520   |
| MaxDiskWriteSpeedOtherNodeRestart |    2 | unsigned | 52428800   | 1048576  | 1099511627776 | N        | 52428800   |
| MaxDiskWriteSpeedOwnRestart       |    2 | unsigned | 209715200  | 1048576  | 1099511627776 | N        | 209715200  |
| MaxParallelCopyInstances          |    2 | unsigned | 0          | 0        | 64            | N        | 0          |
| MaxSendDelay                      |    2 | unsigned | 0          | 0        | 11000         | N        | 0          |
+-----------------------------------+------+----------+------------+----------+---------------+----------+------------+
54 rows in set (0.01 sec)

mysql> 

dict_obj_info
The dict_obj_info table provides information about
NDB data dictionary (DICT) objects such as tables and indexes.
こちらのテーブルはNDBデータディクショナリーの情報を確認する事が可能です。


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> 

table_distribution_status
The table_distribution_status table provides information about the progress of table distribution for NDB tables.
こちらのテーブルでは、NDBテーブルのディストリビューションステータスを確認する事が可能です。

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> 

table_fragments
The table_fragments table provides information about the fragmentation, partitioning, distribution, and (internal) replication of NDB tables.
此方のテーブルでは、NDBテーブルのフラグメンテーション、パーティション、ディストリビューション、レプリケーションを確認する事が可能です。

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> 

table_info
The table_info table provides information about logging, checkpointing, distribution, and storage options in effect for individual NDB tables.
こちらのテーブルでは、各NDBテーブルのログ設定、チェックポイント、ディストリビューション、ストレージオプション等を確認する事が可能です。

mysql> select * from  table_info limit 5;
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
| 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 |
|       12 |            1 |                1 |                     1 |           0 |                0 | DISK         |          1 | FOR_RP_BY_LDM     |          0 |
|       82 |            1 |                1 |                     1 |           0 |                0 | MEMORY       |          1 | FOR_RP_BY_LDM     |          0 |
|       13 |            1 |                1 |                     1 |           0 |                0 | MEMORY       |          1 | FOR_RP_BY_LDM     |          0 |
|        3 |            1 |                1 |                     1 |           0 |                0 | MEMORY       |          1 | FOR_RP_BY_LDM     |          0 |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
5 rows in set (0.01 sec)

mysql> 

table_replicas
The table_replicas table provides information about the copying, distribution, and checkpointing of NDB table fragments and fragment replicas.
こちらのテーブルでは、NDBテーブルフラグメントとレプリカのコピー、ディストリビューション、チェックポイントの情報を確認する事が可能です。

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 |                60743 |                  60743 |          35 |       57834 |                57834 |                     34 |           57834 |       4294967295 |                1 |
|       1 |        2 |           0 |           2 |               2 |              0 |                    0 |                60743 |                  60743 |          35 |       57834 |                57834 |                     34 |           57834 |       4294967295 |                1 |
|       1 |        2 |           1 |           2 |               1 |              0 |                    0 |                60743 |                  60743 |          35 |       57834 |                57834 |                     34 |           57834 |       4294967295 |                1 |
|       1 |        2 |           1 |           2 |               2 |              0 |                    0 |                60743 |                  60743 |          35 |       57834 |                57834 |                     34 |           57834 |       4294967295 |                1 |
|       1 |        3 |           0 |           2 |               1 |              0 |                    0 |                60743 |                  60743 |          35 |       57834 |                57834 |                     34 |           57834 |       4294967295 |                1 |
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
5 rows in set (0.00 sec)

mysql> 

これらの追加機能やテーブルを活用して、更にMSQL Clusterを活用するケースが増えればと思います。

参考: https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-what-is-new.html

補足:アップグレードに再度パフォーマンス検証(MySQL Cluster 7.5.4 + Sysbench 0.5)
READ_BACKUP=0
185.78 per sec

ALTER TABLE sbtest1 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
ALTER TABLE sbtest2 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
ALTER TABLE sbtest3 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
ALTER TABLE sbtest4 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            156310
        write:                           0
        other:                           22330
        total:                           178640
    transactions:                        11165  (185.78 per sec.)
    read/write requests:                 156310 (2600.95 per sec.)
    other operations:                    22330  (371.56 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0973s
    total number of events:              11165
    total time taken by event execution: 1921.4885s
    response time:
         min:                                 38.13ms
         avg:                                172.10ms
         max:                                390.91ms
         approx.  95 percentile:             243.58ms

Threads fairness:
    events (avg/stddev):           348.9062/3.30
    execution time (avg/stddev):   60.0465/0.02

READ_BACKUP=1
240.78 per sec.

ALTER TABLE sbtest1 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
ALTER TABLE sbtest2 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
ALTER TABLE sbtest3 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
ALTER TABLE sbtest4 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            202482
        write:                           0
        other:                           28926
        total:                           231408
    transactions:                        14463  (240.78 per sec.)
    read/write requests:                 202482 (3370.96 per sec.)
    other operations:                    28926  (481.57 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0666s
    total number of events:              14463
    total time taken by event execution: 1919.6420s
    response time:
         min:                                 66.34ms
         avg:                                132.73ms
         max:                                500.21ms
         approx.  95 percentile:             183.90ms

Threads fairness:
    events (avg/stddev):           451.9688/3.86
    execution time (avg/stddev):   59.9888/0.04

[root@CL-SLAVE01 sys03]# 

MySQL Cluster7.5からPK参照以外の参照性能が向上している。
テーブル作成時にREAD_BACKUPオプションを利用するか、ndb_read_backupオプションを設定しておくとPRIMARYレプリカだけでなく、SECONDARYレプリカからもデータが参照する事が出来るようになる。
MySQL7.4までは常に、PRIMARYレプリカからデータを参照していたので、ローカルにPRIMARYデータが無い場合は、PRIMARYデータを持っているノードまで取得しにいく必要があった。

イメージ

cluster

参照:
14.1.18.7 Setting NDB_TABLE options in table comments

抜粋: 
READ_BACKUP: Setting this option to 1 has the same effect as though ndb_read_backup were enabled; enables reading from any replica.
Setting this option to 1 automatically sets FRAGMENT_COUNT_TYPE to ONE_PER_LDM_PER_NODE_GROUP (see next item).
Starting with MySQL Cluster NDB 7.5.3, you can set READ_BACKUP for an existing table online,
using an ALTER TABLE statement similar to one of those shown here:


ALTER TABLE ... ALGORITHM=INPLACE, COMMENT="NDB_TABLE=READ_BACKUP=1";
ALTER TABLE ... ALGORITHM=INPLACE, COMMENT="NDB_TABLE=READ_BACKUP=0";

【結論】
結果としては、READ_BACKUPをONにした方が、3割程パフォーマンスが向上していました。
非常に非力な、仮想環境での検証なので皆さんの環境でも是非検証してみて下さい。
大幅にパフォーマンスが改善する可能性があります。

READ_BACKUP=0 (OFF)
transactions: 5650 (93.91 per sec.)
transactions: 5718 (94.91 per sec.)
transactions: 5698 (94.71 per sec.)
transactions: 5490 (91.24 per sec.)

READ_BACKUP=1 (ON)
transactions: 7234 (120.17 per sec.)
transactions: 7403 (123.00 per sec.)
transactions: 7419 (123.32 per sec.)
transactions: 7264 (120.77 per sec.)

※ マシーン自体は非力なのでTPSは少ないですが、どちらも全く同じ状況で検証しています。

■ 簡単にSYSBENCH0.5のOLTP READで、参照パフォーマンスの確認を行ってみました。
メモリーが無いので、データは1万件しかいれてません。(1万 X 4テーブル)


mysql> select @@version;
+----------------------------------+
| @@version                        |
+----------------------------------+
| 5.7.13-ndb-7.5.3-cluster-gpl-log |
+----------------------------------+
1 row in set (0.00 sec)

mysql> show tables;
+--------------------+
| Tables_in_sysbench |
+--------------------+
| sbtest1            |
| sbtest2            |
| sbtest3            |
| sbtest4            |
+--------------------+
4 rows in set (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

mysql> 

SYSBENCHのスレッド数と実行しているQUERY
‐ 32スレッドで60秒程実行しています。 
‐ Queryの殆どは、BETWEEN等を利用しているのでPKで特定のデータをピンポイントで持ってくる処理ではありません。


/bin/sysbench \
  --test=../lua/db/oltp.lua \
  --rand-init=on \
  --db-driver=mysql \
  --oltp-table-size=${TSIZE} \
  --rand-type=uniform \
  --oltp-read-only=on \
  --oltp-tables-count=${NTBLE}\
  --mysql-db=sysbench \
  --mysql-host=${ENDPOINT} \
  --mysql-user=bench_user \
  --mysql-password=password \
  --max-time=60 \
  --max-requests=0 \
  --num-threads=32 \




[root@CL-SLAVE01 sys03]# cat ../lua/db/oltp.lua | egrep -i select 
   for i=1, oltp_point_selects do
      rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))
      rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
      rs = db_query("SELECT SUM(K) FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
      rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
      rs = db_query("SELECT DISTINCT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
[root@CL-SLAVE01 sys03]# 

上記のテーブルとデータでDEFAULTの状態(READ_BACKUPをOFF)で検証してみます。

[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            79100
        write:                           0
        other:                           11300
        total:                           90400
    transactions:                        5650   (93.91 per sec.)
    read/write requests:                 79100  (1314.76 per sec.)
    other operations:                    11300  (187.82 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1630s
    total number of events:              5650
    total time taken by event execution: 1919.4297s
    response time:
         min:                                 89.72ms
         avg:                                339.72ms
         max:                                617.21ms
         approx.  95 percentile:             442.32ms

Threads fairness:
    events (avg/stddev):           176.5625/2.45
    execution time (avg/stddev):   59.9822/0.11

[root@CL-SLAVE01 sys03]# 




[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            80052
        write:                           0
        other:                           11436
        total:                           91488
    transactions:                        5718   (94.91 per sec.)
    read/write requests:                 80052  (1328.77 per sec.)
    other operations:                    11436  (189.82 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.2452s
    total number of events:              5718
    total time taken by event execution: 1924.6034s
    response time:
         min:                                161.28ms
         avg:                                336.59ms
         max:                                646.96ms
         approx.  95 percentile:             441.26ms

Threads fairness:
    events (avg/stddev):           178.6875/2.28
    execution time (avg/stddev):   60.1439/0.07

[root@CL-SLAVE01 sys03]# 

READ_BACKUPをONにしています。ALTERテーブルでテーブル毎に設定変更可能です。


mysql> ALTER TABLE sbtest1 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest2 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest3 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest4 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

READ_BACKUPオプションをONにしたので、同じテストを実行してみます。


[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            101276
        write:                           0
        other:                           14468
        total:                           115744
    transactions:                        7234   (120.17 per sec.)
    read/write requests:                 101276 (1682.33 per sec.)
    other operations:                    14468  (240.33 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1997s
    total number of events:              7234
    total time taken by event execution: 1919.6510s
    response time:
         min:                                 61.99ms
         avg:                                265.37ms
         max:                                515.43ms
         approx.  95 percentile:             350.01ms

Threads fairness:
    events (avg/stddev):           226.0625/0.97
    execution time (avg/stddev):   59.9891/0.10

[root@CL-SLAVE01 sys03]# 



[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            103642
        write:                           0
        other:                           14806
        total:                           118448
    transactions:                        7403   (123.00 per sec.)
    read/write requests:                 103642 (1722.05 per sec.)
    other operations:                    14806  (246.01 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1852s
    total number of events:              7403
    total time taken by event execution: 1923.2176s
    response time:
         min:                                143.96ms
         avg:                                259.79ms
         max:                                421.40ms
         approx.  95 percentile:             328.68ms

Threads fairness:
    events (avg/stddev):           231.3438/0.59
    execution time (avg/stddev):   60.1006/0.05

[root@CL-SLAVE01 sys03]# 

■ 再テストでREAD_BACKUPをOFFにしてみます。


mysql> ALTER TABLE sbtest1 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest2 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest3 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest4 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

READ_BACKUPをOFFにした状態で再度検証してみると、TPSは落ちています。

[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            79772
        write:                           0
        other:                           11396
        total:                           91168
    transactions:                        5698   (94.71 per sec.)
    read/write requests:                 79772  (1325.97 per sec.)
    other operations:                    11396  (189.42 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1611s
    total number of events:              5698
    total time taken by event execution: 1920.7135s
    response time:
         min:                                118.55ms
         avg:                                337.09ms
         max:                                600.45ms
         approx.  95 percentile:             440.47ms

Threads fairness:
    events (avg/stddev):           178.0625/1.71
    execution time (avg/stddev):   60.0223/0.09

[root@CL-SLAVE01 sys03]# 


[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            76860
        write:                           0
        other:                           10980
        total:                           87840
    transactions:                        5490   (91.24 per sec.)
    read/write requests:                 76860  (1277.31 per sec.)
    other operations:                    10980  (182.47 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1734s
    total number of events:              5490
    total time taken by event execution: 1922.5091s
    response time:
         min:                                171.24ms
         avg:                                350.18ms
         max:                                801.92ms
         approx.  95 percentile:             478.98ms

Threads fairness:
    events (avg/stddev):           171.5625/1.32
    execution time (avg/stddev):   60.0784/0.06

[root@CL-SLAVE01 sys03]# 

READ_BACKUP設定を再度ONにしています。


mysql> ALTER TABLE sbtest1 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest2 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest3 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest4 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

もう一度、READ_BACKUPをONにしてパフォーマンスを確認してみました。
やはり、ONにした方が今回の環境ではパフォーマンスが良いようです。

[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            103866
        write:                           0
        other:                           14838
        total:                           118704
    transactions:                        7419   (123.32 per sec.)
    read/write requests:                 103866 (1726.47 per sec.)
    other operations:                    14838  (246.64 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1609s
    total number of events:              7419
    total time taken by event execution: 1920.0195s
    response time:
         min:                                 91.51ms
         avg:                                258.80ms
         max:                                463.14ms
         approx.  95 percentile:             351.79ms

Threads fairness:
    events (avg/stddev):           231.8438/0.83
    execution time (avg/stddev):   60.0006/0.13

[root@CL-SLAVE01 sys03]#


[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            101696
        write:                           0
        other:                           14528
        total:                           116224
    transactions:                        7264   (120.77 per sec.)
    read/write requests:                 101696 (1690.77 per sec.)
    other operations:                    14528  (241.54 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1476s
    total number of events:              7264
    total time taken by event execution: 1921.1800s
    response time:
         min:                                 77.60ms
         avg:                                264.48ms
         max:                                454.03ms
         approx.  95 percentile:             343.88ms

Threads fairness:
    events (avg/stddev):           227.0000/0.79
    execution time (avg/stddev):   60.0369/0.06

[root@CL-SLAVE01 sys03]# 

MySQL Cluster 7.5はMySQL5.7ベースのMySQLが利用可能なので、JSONが利用出来たり色々な改善が加わっています。
これまでより、多くのケースで活用出来る場面が増えてくるかと思います。詳細は以下のURLを参照下さい。
19.1.4 What is New in MySQL Cluster NDB 7.5

例) MySQL Cluster7.5のndbinfoから,こちらのようにテーブルの情報も確認出来るようになってます。


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.09 sec)

mysql> 

MySQL Clusterに関しても、セミナーで紹介していたりするので都合が付けば是非参加下さい。
https://www-jp.mysql.com/news-and-events/events/