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

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

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

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


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

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

MySQL Enterprise Monitorで確認

マスター側のGROUP REPLICATIONの状態

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

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

mysql> 

スレーブ側のGROUP REPLICATIONの状態


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

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

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

mysql> 

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


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

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

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

mysql> 

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

mysql> show tables;
Empty set (0.00 sec)

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

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

mysql> 


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

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

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

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

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


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

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



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

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

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

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

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

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

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


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

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


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

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


SECONDARYの一台を停止してみる


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


Killing MySQL instance...

Instance localhost:3320 successfully killed.

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


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

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

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

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

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

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

mysql> 

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

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

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


-bash-4.2$ mysqlsh
Welcome to MySQL Shell 1.0.9

Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> shell.connect('root@localhost:3310');
Please provide the password for 'root@localhost:3310': 
Creating a Session to 'root@localhost:3310'
Classic Session successfully established. No default schema selected.
mysql-js> dba.startSandboxInstance(3320);
The MySQL sandbox instance on this host in 
/home/mysql/mysql-sandboxes/3320 will be started


Starting MySQL instance...

Instance localhost:3320 successfully started.

mysql-js> var cluster = dba.getCluster("testCluster")
mysql-js> cluster.rejoinInstance('localhost:3320')
Rejoining the instance to the InnoDB cluster. Depending on the original
problem that made the instance unavailable, the rejoin operation might not be
successful and further manual steps will be needed to fix the underlying
problem.

Please monitor the output of the rejoin operation and take necessary action if
the instance cannot rejoin.

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

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

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

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


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

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

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


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

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

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

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

確認バージョン


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

mysql> 

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

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

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

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


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

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

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

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

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

mysql>
 

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

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

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


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

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

mysql> 

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

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

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

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


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

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


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


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

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

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

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


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

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


mysql> SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME='group_replication_primary_member';
+----------------------------------+--------------------------------------+
| VARIABLE_NAME                    | VARIABLE_VALUE                       |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | bc653b5a-3b8b-11e7-94cd-080027d65c57 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> 

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


-bash-4.2$ ./2_gr_status.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | bc653b5a-3b8b-11e7-94cd-080027d65c57 | replications |       63301 | ONLINE       |
| group_replication_applier | c68819f0-3b8b-11e7-958b-080027d65c57 | replications |       63302 | ONLINE       |
| group_replication_applier | d0a3d2c8-3b8b-11e7-97ef-080027d65c57 | replications |       63303 | ONLINE       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
-bash-4.2$ 

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

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

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

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

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


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

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

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


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

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

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

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

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

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


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


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

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

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

【抜粋】

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

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

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

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


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

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

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

Query OK, 0 rows affected (0.45 sec)

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

mysql> 

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

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

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

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

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

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

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

mysql> 

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

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

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

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

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

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

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

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


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

Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> dba.deploySandboxInstance(3310)
A new MySQL sandbox instance will be created on this host in 
/home/mysql/mysql-sandboxes/3310

Please enter a MySQL root password for the new instance: 
Deploying new MySQL instance...

Instance localhost:3310 successfully deployed and started.
Use shell.connect('root@localhost:3310'); to connect to the instance.

mysql-js> dba.deploySandboxInstance(3320)
A new MySQL sandbox instance will be created on this host in 
/home/mysql/mysql-sandboxes/3320

Please enter a MySQL root password for the new instance: 
Deploying new MySQL instance...


Instance localhost:3320 successfully deployed and started.
Use shell.connect('root@localhost:3320'); to connect to the instance.

mysql-js> dba.deploySandboxInstance(3330)
A new MySQL sandbox instance will be created on this host in 
/home/mysql/mysql-sandboxes/3330

Please enter a MySQL root password for the new instance: 
Deploying new MySQL instance...

Instance localhost:3330 successfully deployed and started.
Use shell.connect('root@localhost:3330'); to connect to the instance.

mysql-js> 

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


mysql-js> \connect root@localhost:3310
Creating a Session to 'root@localhost:3310'
Enter password: 
Classic Session successfully established. No default schema selected.
mysql-js> var cluster = dba.createCluster('testCluster')
A new InnoDB cluster will be created on instance 'root@localhost:3310'.

Creating InnoDB cluster 'testCluster' on 'root@localhost:3310'...
Adding Seed Instance...

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

mysql-js> cluster.addInstance('root@localhost:3320')
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

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

The instance 'root@localhost:3320' was successfully added to the cluster.

mysql-js> cluster.addInstance('root@localhost:3330')
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Please provide the password for 'root@localhost:3330': 
Adding instance to the cluster ...

The instance 'root@localhost:3330' was successfully added to the cluster.


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

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

Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> \connect root@localhost:3310
Creating a Session to 'root@localhost:3310'
Enter password: 
Classic Session successfully established. No default schema selected.
mysql-js> cluster = dba.getCluster()
<Cluster:testCluster>
mysql-js> cluster.status()
{
    "clusterName": "testCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3310", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}
mysql-js> cluster.describe()
{
    "clusterName": "testCluster", 
    "defaultReplicaSet": {
        "instances": [
            {
                "host": "localhost:3310", 
                "label": "localhost:3310", 
                "role": "HA"
            },
            {
                "host": "localhost:3320", 
                "label": "localhost:3320", 
                "role": "HA"
            },
            {
                "host": "localhost:3330", 
                "label": "localhost:3330", 
                "role": "HA"
            }
        ], 
        "name": "default"
    }
}
mysql-js> 

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


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

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

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

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

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

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


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

[logger]
level = INFO

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

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

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

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

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

-bash-4.2$ 

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


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

mysql> 

■ MySQL Routerを起動します。

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


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

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


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

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

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

-bash-4.2$ 

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

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

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

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

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

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

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

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

[root@DockerHost oracle]# docker exec -it mysql8 mysql -uuser01 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 8.0.0-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

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

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

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

mysql> 

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


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

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

mysql> 

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


[root@DockerHost oracle]# docker exec -it mysql8 mysql -uuser01 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.0-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

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

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

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

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

mysql>  

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


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

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

mysql> 

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


[root@DockerHost oracle]# docker exec -it mysql8 mysql -uuser02 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.0-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

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

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

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

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

mysql> 

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

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

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

mysql> exit
Bye
[root@DockerHost oracle]# docker exec -it mysql8 mysql -uuser02 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.0-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

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

mysql> 

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

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

mysql> 

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


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

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

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

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

mysql> 

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

[root@DockerHost oracle]# docker exec -it mysql8 mysql -uuser01 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.0-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

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

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

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

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

mysql> 

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


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

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

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

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

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


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

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

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

mysql>


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