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

イメージ

cluster

参照:
14.1.18.7 Setting NDB_TABLE options in table comments

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


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

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

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

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

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

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


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

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

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

mysql> 

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


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




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

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

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

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

Random number generator seed is 0 and will be ignored


Threads started!

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

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

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

[root@CL-SLAVE01 sys03]# 




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

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

Random number generator seed is 0 and will be ignored


Threads started!

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

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

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

[root@CL-SLAVE01 sys03]# 

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


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

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

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

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

mysql> 

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


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

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

Random number generator seed is 0 and will be ignored


Threads started!

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

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

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

[root@CL-SLAVE01 sys03]# 



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

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

Random number generator seed is 0 and will be ignored


Threads started!

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

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

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

[root@CL-SLAVE01 sys03]# 

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


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

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

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

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

mysql> 

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

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

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

Random number generator seed is 0 and will be ignored


Threads started!

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

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

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

[root@CL-SLAVE01 sys03]# 


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

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

Random number generator seed is 0 and will be ignored


Threads started!

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

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

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

[root@CL-SLAVE01 sys03]# 

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


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

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

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

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

mysql> 

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

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

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

Random number generator seed is 0 and will be ignored


Threads started!

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

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

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

[root@CL-SLAVE01 sys03]#


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

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

Random number generator seed is 0 and will be ignored


Threads started!

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

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

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

[root@CL-SLAVE01 sys03]# 

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

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


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

mysql> 

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


InnoDB Clusterは、MySQLの高可用性構成をサポートするMySQLの可用性フレームワークです。
まだLab版ですが、既にRCになっているMySQL Group Replication、GAになっているMySQL Router (GAはバージョンは1.x),MySQL Shell (DMR)を組み合わせて高可用性を実現しています。
基本的な動作は、MySQL Fabricに似ていますが、管理リポジトリー自体がGroup Replication内にあるので、デフォルトで冗長化されています。また、MySQL Router,MySQL Shell,MySQL Group Replicationが蜜に連携していて1つのHAパッケージのようになっています。まだ、十分に検証出来ていませんが、基本的な動作を確認したので、此方にメモしておきます。
※まだLab版なので、仕様は変わると思います。

innodb_cluster

ダウンロード
MySQL Labs :: MySQL InnoDB Cluster 5.7.15 Preview
※ インストール方法は、RPMが提供されているので割愛しています。

■ Sandboxインスタンスのデプロイ
※ Do not run MySQL Shell as root.
以下のdeployLocalInstanceコマンドで新規インスタンスが構築されます。


[root@replications admin]# su - mysql
最終ログイン: 2016/09/27 (火) 22:32:27 JST日時 pts/2
-bash-4.2$ mysqlsh
Welcome to MySQL Shell 1.0.5-labs Development Preview

Copyright (c) 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', '\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.deployLocalInstance(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 '\connect root@localhost:3310' to connect to the instance.

mysql-js> dba.deployLocalInstance(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 '\connect root@localhost:3320' to connect to the instance.

mysql-js> dba.deployLocalInstance(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 '\connect root@localhost:3330' to connect to the instance.

mysql-js> 


[root@replications mysql-sandboxes]# pwd
/home/mysql/mysql-sandboxes
[root@replications mysql-sandboxes]# ls -l
合計 12
drwxrwxr-x. 4 mysql mysql 4096  9月 27 17:25 3310
drwxrwxr-x. 4 mysql mysql 4096  9月 27 17:26 3320
drwxrwxr-x. 4 mysql mysql 4096  9月 27 17:27 3330
[root@replications mysql-sandboxes]# ls -l 3310/
合計 28
-rw-r-----. 1 mysql mysql    5  9月 27 17:25 3310.pid
drwxr-x---. 5 mysql mysql 4096  9月 27 17:25 data
-rw-------. 1 mysql mysql  806  9月 27 17:25 my.cnf
drwxrwxr-x. 2 mysql mysql    6  9月 27 17:25 mysql-files
srwxrwxrwx. 1 mysql mysql    0  9月 27 17:25 mysqld.sock
-rw-------. 1 mysql mysql    5  9月 27 17:25 mysqld.sock.lock
srwxrwxrwx. 1 mysql mysql    0  9月 27 17:25 mysqlx.sock
-rw-------. 1 mysql mysql    6  9月 27 17:25 mysqlx.sock.lock
-rwx------. 1 mysql mysql  121  9月 27 17:25 start.sh
-rwx------. 1 mysql mysql  186  9月 27 17:25 stop.sh
[root@replications mysql-sandboxes]# ls -l 3310/data/
合計 122948
-rw-r-----. 1 mysql mysql      169  9月 27 17:25 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:25 ON.000002
-rw-r-----. 1 mysql mysql      150  9月 27 17:25 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:25 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:25 auto.cnf
-rw-r-----. 1 mysql mysql    14266  9月 27 17:25 error.log
-rw-r-----. 1 mysql mysql      362  9月 27 17:25 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:25 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:25 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:25 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:25 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:25 mysql
drwxr-x---. 2 mysql mysql     8192  9月 27 17:25 performance_schema
drwxr-x---. 2 mysql mysql     8192  9月 27 17:25 sys
[root@replications mysql-sandboxes]# ls -l 3320/data/
合計 122948
-rw-r-----. 1 mysql mysql      169  9月 27 17:26 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:26 ON.000002
-rw-r-----. 1 mysql mysql      150  9月 27 17:26 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:26 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:26 auto.cnf
-rw-r-----. 1 mysql mysql    14369  9月 27 17:26 error.log
-rw-r-----. 1 mysql mysql      362  9月 27 17:26 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:26 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:26 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:26 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:26 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:26 mysql
drwxr-x---. 2 mysql mysql     8192  9月 27 17:26 performance_schema
drwxr-x---. 2 mysql mysql     8192  9月 27 17:26 sys
[root@replications mysql-sandboxes]# ls -l 3330/data/
合計 122948
-rw-r-----. 1 mysql mysql      169  9月 27 17:27 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:27 ON.000002
-rw-r-----. 1 mysql mysql      150  9月 27 17:27 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:27 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:27 auto.cnf
-rw-r-----. 1 mysql mysql    13201  9月 27 17:27 error.log
-rw-r-----. 1 mysql mysql      359  9月 27 17:27 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:27 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:27 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:27 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:27 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:27 mysql
drwxr-x---. 2 mysql mysql     8192  9月 27 17:27 performance_schema
drwxr-x---. 2 mysql mysql     8192  9月 27 17:27 sys
[root@replications mysql-sandboxes]# ls -l 3330/data/
合計 122948
-rw-r-----. 1 mysql mysql      169  9月 27 17:27 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:27 ON.000002
-rw-r-----. 1 mysql mysql      150  9月 27 17:27 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:27 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:27 auto.cnf
-rw-r-----. 1 mysql mysql    13201  9月 27 17:27 error.log
-rw-r-----. 1 mysql mysql      359  9月 27 17:27 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:27 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:27 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:27 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:27 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:27 mysql
drwxr-x---. 2 mysql mysql     8192  9月 27 17:27 performance_schema
drwxr-x---. 2 mysql mysql     8192  9月 27 17:27 sys
[root@replications mysql-sandboxes]# 

■ InnoDB Clusterの初期化
※Do not lose the MASTER key because it is required for managing the InnoDB cluster.
構築したインスタンスでグループレプリケーションを構築します。


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.createCluster('test')
A new InnoDB cluster will be created on instance 'root@localhost:3310'.

When setting up a new InnoDB cluster it is required to define an administrative
MASTER key for the cluster. This MASTER key needs to be re-entered when making
changes to the cluster later on, e.g.adding new MySQL instances or configuring
MySQL Routers. Losing this MASTER key will require the configuration of all
InnoDB cluster entities to be changed.

Please specify an administrative MASTER key for the cluster 'test': 
Creating InnoDB cluster 'test' 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.

<Cluster:test>
mysql-js> 



[root@replications mysql-sandboxes]# ls -l 3310/data/
合計 122988
-rw-r-----. 1 mysql mysql      169  9月 27 17:25 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:25 ON.000002
-rw-r-----. 1 mysql mysql    10397  9月 27 17:31 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:25 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:25 auto.cnf
-rw-r-----. 1 mysql mysql    21676  9月 27 17:31 error.log
-rw-r-----. 1 mysql mysql      362  9月 27 17:25 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:31 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:25 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:31 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:31 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:25 mysql
drwxr-x---. 2 mysql mysql     4096  9月 27 17:31 mysql_innodb_cluster_metadata
drwxr-x---. 2 mysql mysql     8192  9月 27 17:25 performance_schema
-rw-r-----. 1 mysql mysql      232  9月 27 17:31 replications-relay-bin-group_replication_applier.000001
-rw-r-----. 1 mysql mysql      598  9月 27 17:31 replications-relay-bin-group_replication_applier.000002
-rw-r-----. 1 mysql mysql      116  9月 27 17:31 replications-relay-bin-group_replication_applier.index
-rw-r-----. 1 mysql mysql      150  9月 27 17:31 replications-relay-bin-group_replication_recovery.000001
-rw-r-----. 1 mysql mysql       59  9月 27 17:31 replications-relay-bin-group_replication_recovery.index
drwxr-x---. 2 mysql mysql     8192  9月 27 17:25 sys
[root@replications mysql-sandboxes]# ls -l 3320/data/
合計 122948
-rw-r-----. 1 mysql mysql      169  9月 27 17:26 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:26 ON.000002
-rw-r-----. 1 mysql mysql      150  9月 27 17:26 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:26 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:26 auto.cnf
-rw-r-----. 1 mysql mysql    14369  9月 27 17:26 error.log
-rw-r-----. 1 mysql mysql      362  9月 27 17:26 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:26 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:26 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:26 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:26 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:26 mysql
drwxr-x---. 2 mysql mysql     8192  9月 27 17:26 performance_schema
drwxr-x---. 2 mysql mysql     8192  9月 27 17:26 sys
[root@replications mysql-sandboxes]# ls -l 3330/data/
合計 122948
-rw-r-----. 1 mysql mysql      169  9月 27 17:27 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:27 ON.000002
-rw-r-----. 1 mysql mysql      150  9月 27 17:27 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:27 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:27 auto.cnf
-rw-r-----. 1 mysql mysql    13201  9月 27 17:27 error.log
-rw-r-----. 1 mysql mysql      359  9月 27 17:27 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:27 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:27 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:27 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:27 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:27 mysql
drwxr-x---. 2 mysql mysql     8192  9月 27 17:27 performance_schema
drwxr-x---. 2 mysql mysql     8192  9月 27 17:27 sys
[root@replications mysql-sandboxes]# 

■ InnoDB Clusterへのインスタンスの追加
グループレプリケーションへインスタンスを追加します。


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.

mysql-js> 


[root@replications mysql-sandboxes]# ls -l 3310/data/
合計 122988
-rw-r-----. 1 mysql mysql      169  9月 27 17:25 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:25 ON.000002
-rw-r-----. 1 mysql mysql    12253  9月 27 17:36 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:25 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:25 auto.cnf
-rw-r-----. 1 mysql mysql    23760  9月 27 17:36 error.log
-rw-r-----. 1 mysql mysql      362  9月 27 17:25 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:36 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:25 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:36 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:36 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:25 mysql
drwxr-x---. 2 mysql mysql     4096  9月 27 17:31 mysql_innodb_cluster_metadata
drwxr-x---. 2 mysql mysql     8192  9月 27 17:25 performance_schema
-rw-r-----. 1 mysql mysql      232  9月 27 17:31 replications-relay-bin-group_replication_applier.000001
-rw-r-----. 1 mysql mysql     1336  9月 27 17:36 replications-relay-bin-group_replication_applier.000002
-rw-r-----. 1 mysql mysql      116  9月 27 17:31 replications-relay-bin-group_replication_applier.index
-rw-r-----. 1 mysql mysql      150  9月 27 17:31 replications-relay-bin-group_replication_recovery.000001
-rw-r-----. 1 mysql mysql       59  9月 27 17:31 replications-relay-bin-group_replication_recovery.index
drwxr-x---. 2 mysql mysql     8192  9月 27 17:25 sys
[root@replications mysql-sandboxes]# ls -l 3320/data/
合計 122996
-rw-r-----. 1 mysql mysql      169  9月 27 17:26 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:26 ON.000002
-rw-r-----. 1 mysql mysql    12129  9月 27 17:36 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:26 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:26 auto.cnf
-rw-r-----. 1 mysql mysql    24847  9月 27 17:37 error.log
-rw-r-----. 1 mysql mysql      362  9月 27 17:26 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:36 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:26 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:36 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:35 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:26 mysql
drwxr-x---. 2 mysql mysql     4096  9月 27 17:35 mysql_innodb_cluster_metadata
drwxr-x---. 2 mysql mysql     8192  9月 27 17:26 performance_schema
-rw-r-----. 1 mysql mysql      232  9月 27 17:35 replications-relay-bin-group_replication_applier.000001
-rw-r-----. 1 mysql mysql     2135  9月 27 17:36 replications-relay-bin-group_replication_applier.000002
-rw-r-----. 1 mysql mysql      116  9月 27 17:35 replications-relay-bin-group_replication_applier.index
-rw-r-----. 1 mysql mysql      233  9月 27 17:35 replications-relay-bin-group_replication_recovery.000001
-rw-r-----. 1 mysql mysql      269  9月 27 17:35 replications-relay-bin-group_replication_recovery.000002
-rw-r-----. 1 mysql mysql      118  9月 27 17:35 replications-relay-bin-group_replication_recovery.index
drwxr-x---. 2 mysql mysql     8192  9月 27 17:26 sys
[root@replications mysql-sandboxes]# ls -l 3330/data/
合計 122992
-rw-r-----. 1 mysql mysql      169  9月 27 17:27 ON.000001
-rw-r-----. 1 mysql mysql      169  9月 27 17:27 ON.000002
-rw-r-----. 1 mysql mysql    12129  9月 27 17:36 ON.000003
-rw-r-----. 1 mysql mysql       36  9月 27 17:27 ON.index
-rw-r-----. 1 mysql mysql       56  9月 27 17:27 auto.cnf
-rw-r-----. 1 mysql mysql    22841  9月 27 17:36 error.log
-rw-r-----. 1 mysql mysql      359  9月 27 17:27 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:36 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648  9月 27 17:27 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:36 ibdata1
-rw-r-----. 1 mysql mysql 12582912  9月 27 17:36 ibtmp1
drwxr-x---. 2 mysql mysql     4096  9月 27 17:27 mysql
drwxr-x---. 2 mysql mysql     4096  9月 27 17:36 mysql_innodb_cluster_metadata
drwxr-x---. 2 mysql mysql     8192  9月 27 17:27 performance_schema
-rw-r-----. 1 mysql mysql      232  9月 27 17:36 replications-relay-bin-group_replication_applier.000001
-rw-r-----. 1 mysql mysql     1202  9月 27 17:36 replications-relay-bin-group_replication_applier.000002
-rw-r-----. 1 mysql mysql      116  9月 27 17:36 replications-relay-bin-group_replication_applier.index
-rw-r-----. 1 mysql mysql      233  9月 27 17:36 replications-relay-bin-group_replication_recovery.000001
-rw-r-----. 1 mysql mysql      269  9月 27 17:36 replications-relay-bin-group_replication_recovery.000002
-rw-r-----. 1 mysql mysql      118  9月 27 17:36 replications-relay-bin-group_replication_recovery.index
drwxr-x---. 2 mysql mysql     8192  9月 27 17:27 sys
[root@replications mysql-sandboxes]# 


[root@replications mysql-sandboxes]# netstat -na | grep sandboxes
unix  2      [ ACC ]     STREAM     LISTENING     26245    /home/mysql/mysql-sandboxes/3310/mysqlx.sock
unix  2      [ ACC ]     STREAM     LISTENING     26250    /home/mysql/mysql-sandboxes/3310/mysqld.sock
unix  2      [ ACC ]     STREAM     LISTENING     22039    /home/mysql/mysql-sandboxes/3330/mysqlx.sock
unix  2      [ ACC ]     STREAM     LISTENING     22044    /home/mysql/mysql-sandboxes/3330/mysqld.sock
unix  2      [ ACC ]     STREAM     LISTENING     21930    /home/mysql/mysql-sandboxes/3320/mysqlx.sock
unix  2      [ ACC ]     STREAM     LISTENING     21935    /home/mysql/mysql-sandboxes/3320/mysqld.sock
[root@replications mysql-sandboxes]# 

■ InnoDB Clusterのステータス確認
全てのホストがONLINEでHAが構成されている事が確認出来る。


mysql-js> cluster.status()
{
    "clusterName": "test",
    "defaultReplicaSet": {
        "status": "Cluster tolerant to up to ONE failure.",
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310",
                "status": "ONLINE",
                "role": "HA",
                "mode": "R/W",
                "leaves": {
                    "localhost:3320": {
                        "address": "localhost:3320",
                        "status": "ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    },
                    "localhost:3330": {
                        "address": "localhost:3330",
                        "status": "ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    }
                }
            }
        }
    }
}
mysql-js> 

Sandbox環境のGroup ReplicationはSingle Primary Modeで動いています。
It is a configuration mode for Group Replication that makes a single member act as a writeable master (PRIMARY) and the rest of the members act as hot-standbys (SECONDARY).
The group itself coordinates and configures itself automatically to determine which member will act as the PRIMARY, through a leader election mechanism.


-bash-4.2$ mysql -u root -pP@33word -h 127.0.0.1 -P 6446 -e "show variables like 'group_replication_single_primary_mode'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | ON    |
+---------------------------------------+-------+
-bash-4.2$ 

-bash-4.2$ mysql -u root -pP@33word -h 127.0.0.1 -P 6446 -e "show variables like 'group_replication_enforce_update_everywhere_checks'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------------------------------------+-------+
| Variable_name                                      | Value |
+----------------------------------------------------+-------+
| group_replication_enforce_update_everywhere_checks | OFF   |
+----------------------------------------------------+-------+
-bash-4.2$ 

-bash-4.2$ mysql -u root -pP@33word -h 127.0.0.1 -P 6447 -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 | 944915c6-853a-11e6-8e3d-080027d65c57 |
+----------------------------------+--------------------------------------+
-bash-4.2$ 

■ MySQL Routerのデプロイ
MySQL Routerを起動させて、グループレプリケーションへの接続をコントロールします。


-bash-4.2$ sudo mysqlrouter --bootstrap localhost:3310
[sudo] password for mysql: 
Please enter the administrative MASTER key for the MySQL InnoDB cluster: 
MySQL Router has now been configured for the InnoDB cluster 'test'.

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

Classic MySQL protocol connections to cluster 'test':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
-bash-4.2$ 


-bash-4.2$ mysqlrouter &
[1] 3615
-bash-4.2$ Logging to /var/log/mysqlrouter/mysqlrouter.log

-bash-4.2$ ps -ef | grep mysqlrouter
mysql     3615  3589  0 14:32 pts/1    00:00:00 mysqlrouter
mysql     3625  3589  0 14:32 pts/1    00:00:00 grep --color=auto mysqlrouter
-bash-4.2$ 

■ MySQLRouter経由でmysqlに接続し動作確認
mysqlshell経由


1) MySQL Shell経由

-bash-4.2$ mysqlsh --uri root@localhost:6446
Creating a Session to 'root@localhost:6446'
Enter password: 
Classic Session successfully established. No default schema selected.
Welcome to MySQL Shell 1.0.5-labs Development Preview

Copyright (c) 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', '\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> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> select @@hostname,@@port;
+--------------+--------+
| @@hostname   | @@port |
+--------------+--------+
| replications |   3310 |
+--------------+--------+
1 row in set (0.00 sec)
mysql-sql> 


-bash-4.2$ mysqlsh --uri root@localhost:6447
Creating a Session to 'root@localhost:6447'
Enter password: 
Classic Session successfully established. No default schema selected.
Welcome to MySQL Shell 1.0.5-labs Development Preview

Copyright (c) 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', '\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> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> select @@hostname,@@port;
+--------------+--------+
| @@hostname   | @@port |
+--------------+--------+
| replications |   3330 |
+--------------+--------+
1 row in set (0.00 sec)
mysql-sql>

2) MySQLコマンド経由での確認とステータスの確認


-bash-4.2$ mysql -u root -p -h 127.0.0.1 -P 6446
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.7.15-labs-gr090-log 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 @@hostname,@@port;
+--------------+--------+
| @@hostname   | @@port |
+--------------+--------+
| replications |   3310 |
+--------------+--------+
1 row in set (0.00 sec)

mysql> 


-bash-4.2$ mysql -u root -p -h 127.0.0.1 -P 6447
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.7.15-labs-gr090-log 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 @@hostname,@@port;
+--------------+--------+
| @@hostname   | @@port |
+--------------+--------+
| replications |   3320 |
+--------------+--------+
1 row in set (0.00 sec)

mysql> 



mysql-js> cluster.status()
{
    "clusterName": "test",
    "defaultReplicaSet": {
        "status": "Cluster tolerant to up to ONE failure.",
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310",
                "status": "ONLINE",
                "role": "HA",
                "mode": "R/W",
                "leaves": {
                    "localhost:3320": {
                        "address": "localhost:3320",
                        "status": "ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    },
                    "localhost:3330": {
                        "address": "localhost:3330",
                        "status": "ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    }
                }
            }
        }
    }
}
mysql-js> 

mysql-js> cluster.describe();
{
    "clusterName": "test",
    "adminType": "local",
    "defaultReplicaSet": {
        "name": "default",
        "instances": [
            {
                "name": "localhost:3310",
                "host": "localhost:3310",
                "role": "HA"
            },
            {
                "name": "localhost:3320",
                "host": "localhost:3320",
                "role": "HA"
            },
            {
                "name": "localhost:3330",
                "host": "localhost:3330",
                "role": "HA"
            }
        ]
    }
}
mysql-js> 

■ フェイルオーバーの確認 (HA)
既存のマスターインスタンスをdba.killLocalInstanceでダウンさせてみる。


mysql-js> dba.killLocalInstance(3310)
The MySQL sandbox instance on this host in 
/home/mysql/mysql-sandboxes/3310 will be killed

Killing MySQL instance...

Instance localhost:3310 successfully killed.

mysql-js> 

– 書き込みの接続が3310から3320に代わっている事が確認出来ます。


-bash-4.2$ mysqlsh --uri root@localhost:6446
Creating a Session to 'root@localhost:6446'
Enter password: 
ERROR: 2003 (HY000): Can't connect to remote MySQL server on '127.0.0.1:6446'
-bash-4.2$ mysqlsh --uri root@localhost:6446
Creating a Session to 'root@localhost:6446'
Enter password: 
Classic Session successfully established. No default schema selected.
Welcome to MySQL Shell 1.0.5-labs Development Preview

Copyright (c) 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', '\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> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> select @@hostname,@@port;
+--------------+--------+
| @@hostname   | @@port |
+--------------+--------+
| replications |   3320 |
+--------------+--------+
1 row in set (0.00 sec)
mysql-sql> 



-bash-4.2$ mysql -u root -p -h 127.0.0.1 -P 6446
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.7.15-labs-gr090-log 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 @@hostname,@@port;
+--------------+--------+
| @@hostname   | @@port |
+--------------+--------+
| replications |   3320 |
+--------------+--------+
1 row in set (0.00 sec)

mysql> 


-bash-4.2$ mysql -u root -p -h 127.0.0.1 -P 6447
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.7.15-labs-gr090-log 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 @@hostname,@@port;
+--------------+--------+
| @@hostname   | @@port |
+--------------+--------+
| replications |   3330 |
+--------------+--------+
1 row in set (0.00 sec)

mysql> 


-bash-4.2$ mysqlsh
Welcome to MySQL Shell 1.0.5-labs Development Preview

Copyright (c) 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', '\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:6446
Creating a Session to 'root@localhost:6446'
Enter password: 
Classic Session successfully established. No default schema selected.
mysql-js> cluster = dba.getCluster()
When the InnoDB cluster was setup, a MASTER key was defined in order to enable
performing administrative tasks on the cluster.

Please specify the administrative MASTER key for the default cluster: 
<Cluster:test>
mysql-js> cluster.status()
{
    "clusterName": "test",
    "defaultReplicaSet": {
        "status": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "localhost:3320": {
                "address": "localhost:3320",
                "status": "ONLINE",
                "role": "HA",
                "mode": "R/W",
                "leaves": {
                    "localhost:3330": {
                        "address": "localhost:3330",
                        "status": "ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    },
                    "localhost:3310": {
                        "address": "localhost:3310",
                        "status": "OFFLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    }
                }
            }
        }
    }
}
mysql-js> cluster.describe();
{
    "clusterName": "test",
    "adminType": "local",
    "defaultReplicaSet": {
        "name": "default",
        "instances": [
            {
                "name": "localhost:3310",
                "host": "localhost:3310",
                "role": "HA"
            },
            {
                "name": "localhost:3320",
                "host": "localhost:3320",
                "role": "HA"
            },
            {
                "name": "localhost:3330",
                "host": "localhost:3330",
                "role": "HA"
            }
        ]
    }
}
mysql-js> 

■ 停止したインスタンスの再開


mysql-js> dba.startLocalInstance(3310)
The MySQL sandbox instance on this host in 
/home/mysql/mysql-sandboxes/3310 will be started

Starting MySQL instance...

Instance localhost:3310 successfully started.

mysql-js> cluster.status()
{
    "clusterName": "test",
    "defaultReplicaSet": {
        "status": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "localhost:3320": {
                "address": "localhost:3320",
                "status": "ONLINE",
                "role": "HA",
                "mode": "R/W",
                "leaves": {
                    "localhost:3330": {
                        "address": "localhost:3330",
                        "status": "ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    },
                    "localhost:3310": {
                        "address": "localhost:3310",
                        "status": "OFFLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    }
                }
            }
        }
    }
}
mysql-js> 

■ インスタンスをInnoDB Clusterグループへ再参加


mysql-js> dba.validateInstance('root@localhost:3310')
Please provide a password for 'root@localhost:3310': 
Validating instance...


Running check command.
Checking Group Replication prerequisites.
* Comparing options compatibility with Group Replication... PASS
Server configuration is compliant with the requirements.
* Checking server version... PASS
Server is 5.7.15

* Checking that server_id is unique... PASS
The server_id is valid.


* Checking compliance of existing tables... PASS

The instance: localhost:3310 is valid for Cluster usage

mysql-js> cluster.rejoinInstance('root@localhost:3310')
Please provide the password for 'root@localhost:3310': 
The instance will try rejoining 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.
mysql-js> cluster.status()
{
    "clusterName": "test",
    "defaultReplicaSet": {
        "status": "Cluster tolerant to up to ONE failure.",
        "topology": {
            "localhost:3320": {
                "address": "localhost:3320",
                "status": "ONLINE",
                "role": "HA",
                "mode": "R/W",
                "leaves": {
                    "localhost:3310": {
                        "address": "localhost:3310",
                        "status": "ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    },
                    "localhost:3330": {
                        "address": "localhost:3330",
                        "status": "ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    }
                }
            }
        }
    }
}
mysql-js> 

■ SQLクライアントを利用したデータ同期と参照用インスタンスへのラウンドロビンを確認。

1) データ作成


-bash-4.2$ mysql -u root -p -h 127.0.0.1 -P 6446
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.7.15-labs-gr090-log 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            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+
5 rows in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.05 sec)

mysql> use test
Database changed
mysql> CREATE TABLE `InnoDB_Cluster` (
    -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    -> `comment` varchar(100) NOT NULL,
    -> `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.05 sec)

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

mysql> exit
Bye
-bash-4.2$ mysql -u root -p -h 127.0.0.1 -P 6447
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.7.15-labs-gr090-log 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            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
| test                          |
+-------------------------------+
6 rows in set (0.00 sec)

mysql> 

■ データを1万件入れてます。

※ もし、参照側データベースにデータをINSERTしようとすると以下のようにエラーになる。
1290 (HY000): The MySQL server is running with the –super-read-only option so it cannot execute this statement
MySQL5.7からのsuper-read-onlyオプションが設定されている。

-bash-4.2$ python innodb_cluster.py 
Finish Creating Data
-bash-4.2$ 

■ 書き込み接続の確認(MySQL Routerの設定により、特定のマスターへ接続)

-bash-4.2$ mysql -u root -pP@33word -h 127.0.0.1 -P 6446 -e 'select @@port,count(*) from test.InnoDB_Cluster'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+----------+
| @@port | count(*) |
+--------+----------+
|   3320 |    10000 |
+--------+----------+
-bash-4.2$ mysql -u root -pP@33word -h 127.0.0.1 -P 6446 -e 'select @@port,count(*) from test.InnoDB_Cluster'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+----------+
| @@port | count(*) |
+--------+----------+
|   3320 |    10000 |
+--------+----------+
-bash-4.2$ 

■ 読み込み接続の確認(MySQL Routerの設定により、複数スレーブへのラウンドロビン接続)

-bash-4.2$ mysql -u root -pP@33word -h 127.0.0.1 -P 6447 -e 'select @@port,count(*) from test.InnoDB_Cluster'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+----------+
| @@port | count(*) |
+--------+----------+
|   3330 |    10000 |
+--------+----------+
-bash-4.2$ mysql -u root -pP@33word -h 127.0.0.1 -P 6447 -e 'select @@port,count(*) from test.InnoDB_Cluster'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+----------+
| @@port | count(*) |
+--------+----------+
|   3310 |    10000 |
+--------+----------+
-bash-4.2$ mysql -u root -pP@33word -h 127.0.0.1 -P 6447 -e 'select @@port,count(*) from test.InnoDB_Cluster'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+----------+
| @@port | count(*) |
+--------+----------+
|   3330 |    10000 |
+--------+----------+
-bash-4.2$ 

■ その他参考:リポジトリー(各ノードで持っているので冗長化が取れている)

-bash-4.2$ mysql -u root -pP@33word -h 127.0.0.1 -P 6446
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 5.7.15-labs-gr090-log 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            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
| test                          |
+-------------------------------+
6 rows in set (0.01 sec)

mysql> use mysql_innodb_cluster_metadata
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------------------------------+
| Tables_in_mysql_innodb_cluster_metadata |
+-----------------------------------------+
| clusters                                |
| hosts                                   |
| instances                               |
| replicasets                             |
| schema_version                          |
+-----------------------------------------+
5 rows in set (0.00 sec)

mysql> desc clusters;
+---------------------+------------------+------+-----+---------+----------------+
| Field               | Type             | Null | Key | Default | Extra          |
+---------------------+------------------+------+-----+---------+----------------+
| cluster_id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| cluster_name        | varchar(40)      | NO   | UNI | NULL    |                |
| default_replicaset  | int(10) unsigned | YES  | MUL | NULL    |                |
| description         | text             | YES  |     | NULL    |                |
| mysql_user_accounts | blob             | YES  |     | NULL    |                |
| options             | json             | YES  |     | NULL    |                |
| attributes          | json             | YES  |     | NULL    |                |
+---------------------+------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> desc hosts;
+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| host_id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| host_name          | varchar(128)     | YES  |     | NULL    |                |
| ip_address         | varchar(45)      | YES  |     | NULL    |                |
| location           | varchar(256)     | NO   |     | NULL    |                |
| attributes         | json             | YES  |     | NULL    |                |
| admin_user_account | json             | YES  |     | NULL    |                |
+--------------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> desc instances;
+-------------------+---------------------------+------+-----+---------+----------------+
| Field             | Type                      | Null | Key | Default | Extra          |
+-------------------+---------------------------+------+-----+---------+----------------+
| instance_id       | int(10) unsigned          | NO   | PRI | NULL    | auto_increment |
| host_id           | int(10) unsigned          | NO   | MUL | NULL    |                |
| replicaset_id     | int(10) unsigned          | YES  | MUL | NULL    |                |
| mysql_server_uuid | varchar(40)               | NO   | UNI | NULL    |                |
| instance_name     | varchar(40)               | NO   | UNI | NULL    |                |
| role              | enum('HA','readScaleOut') | NO   |     | NULL    |                |
| weight            | float                     | YES  |     | NULL    |                |
| addresses         | json                      | NO   |     | NULL    |                |
| attributes        | json                      | YES  |     | NULL    |                |
| version_token     | int(10) unsigned          | YES  |     | NULL    |                |
| description       | text                      | YES  |     | NULL    |                |
+-------------------+---------------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

mysql> desc replicasets;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| replicaset_id   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| cluster_id      | int(10) unsigned | NO   | MUL | NULL    |                |
| replicaset_type | enum('gr')       | NO   |     | NULL    |                |
| topology_type   | enum('pm','mm')  | NO   |     | pm      |                |
| replicaset_name | varchar(40)      | NO   |     | NULL    |                |
| active          | tinyint(1)       | NO   |     | NULL    |                |
| attributes      | json             | YES  |     | NULL    |                |
| description     | text             | YES  |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

mysql> desc schema_version;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| major | int(1) | NO   |     | 0       |       |
| minor | int(1) | NO   |     | 0       |       |
+-------+--------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> 

■ その他参考:MySQL Router設定

[root@replications mysql-sandboxes]# cat /etc/mysqlrouter/mysqlrouter.conf 
[DEFAULT]
plugin_folder=/lib64/mysqlrouter
# logging_folder=/var/log/mysqlrouter

[logger]
level = INFO

[metadata_cache]
bootstrap_server_addresses=mysql://localhost:3310,mysql://localhost:3320,mysql://localhost:3330
user=mysql_innodb_cluster_reader
password=]yZQL(C7H7AU.x(2
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 mysql-sandboxes]# 


[root@replications mysql-sandboxes]# cat /etc/mysqlrouter/mysqlrouter.ini 
# Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# MySQL Router configuration file
#
# Documentation is available at
#    http://dev.mysql.com/doc/mysql-router/en/

[DEFAULT]
logging_folder = /var/log/mysqlrouter/
plugin_folder = /usr/lib64/mysqlrouter
runtime_folder = /var/run/mysqlrouter
config_folder = /etc/mysqlrouter

[logger]
level = info

# If no plugin is configured which starts a service, keepalive
# will make sure MySQL Router will not immediately exit. It is
# safe to remove once Router is configured.
[keepalive]
interval = 60
[root@replications mysql-sandboxes]# 

参考)
https://dev.mysql.com/doc/mysql-innodb-cluster/en/mysql-innodb-cluster-getting-started.html
http://wagnerbianchi.com/blog/?p=1229
http://wagnerbianchi.com/blog/?p=1259
https://www.pythian.com/blog/rejoining-node-mysqls-innodb-cluster/


MySQL8.0がDockerリポジトリーで提供されているので、Dockerに8.0のイメージをダウンロードしてインストールしてみました。
同時にMySQL8.0の新機能確認として、動的にGlobal Variablesを変更して永続化出来るか?また、データディクショナリの状況も確認してみました。

■ DockerレポジトリーとイメージTag
https://hub.docker.com/r/mysql/mysql-server/
https://hub.docker.com/r/mysql/mysql-server/tags/

MySQL8.0 Dockerイメージダウンロード~起動まで


[root@DockerHost oracle]# docker pull mysql/mysql-server:8.0
8.0: Pulling from mysql/mysql-server
7f369f1cac0b: Pull complete 
897fddccf3d8: Pull complete 
865c22dab1e4: Pull complete 
3e61c960af44: Pull complete 
fcd95ea99f45: Pull complete 
e9cae96efb21: Pull complete 
671450fab9a5: Pull complete 
1b8291aef5a7: Pull complete 
99ef814fb233: Pull complete 
c1add8e582f0: Pull complete 
Digest: sha256:c65b1da17c01749a28fc0b0865f94bf02053f290f23a28bf4fe9d8447dccadd6
Status: Downloaded newer image for mysql/mysql-server:8.0
[root@DockerHost oracle]# docker images
REPOSITORY           TAG                 IMAGE ID            CREATED             VIRTUAL SIZE
mysql/mysql-server   8.0                 c1add8e582f0        5 days ago          393.4 MB
mysql/mysql-server   5.7.12              4e1d42e32c43        5 months ago        296.7 MB
mysql                5.7.10              ea0aca21950d        9 months ago        360.3 MB
mysql/mysql-server   5.7.10              e472f1765697        9 months ago        294.6 MB
[root@DockerHost oracle]# 

[root@DockerHost oracle]# docker run --name mysql8 -v /docker/docker8:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=mysql -d mysql/mysql-server:8.0
f0d7a1a715633b76aaadbba8b8fa6a63b293a9a88d193d28cd44cebd33d08955
[root@DockerHost oracle]# 

[root@DockerHost oracle]# docker ps -a
CONTAINER ID        IMAGE                       COMMAND                  CREATED              STATUS                    PORTS                 NAMES
f0d7a1a71563        mysql/mysql-server:8.0      "/entrypoint.sh mysql"   About a minute ago   Up About a minute         3306/tcp, 33060/tcp   mysql8
88da7fe02e00        mysql:5.7.10                "/entrypoint.sh mysql"   3 months ago         Exited (0) 3 months ago                         my_docker03
4fde03dc4cb5        mysql/mysql-server:5.7.10   "/entrypoint.sh mysql"   8 months ago         Exited (0) 3 months ago                         my_docker02
5152a22b2aa0        mysql:5.7.10                "/entrypoint.sh mysql"   8 months ago         Exited (0) 8 months ago                         my_docker01
[root@DockerHost oracle]# 

[root@DockerHost oracle]# docker inspect -f "{{.Config.Hostname}}, {{.NetworkSettings.IPAddress}}" $(docker ps | grep -v "^CONTAINER" | awk '{print $1}')
f0d7a1a71563, 172.17.0.2
[root@DockerHost oracle]# 

ログインしてバージョン確認

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

mysql> 

■ SET PERSIST Statementの確認
http://mysqlserverteam.com/mysql-8-0-persisting-configuration-variables/


mysql> SELECT * FROM performance_schema.variables_info WHERE variable_source != 'COMPILED';
+--------------------+-----------------+---------------+-----------+-----------+
| VARIABLE_NAME      | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE |
+--------------------+-----------------+---------------+-----------+-----------+
| datadir            | GLOBAL          | /etc/my.cnf   | 0         | 0         |
| foreign_key_checks | DYNAMIC         |               | 0         | 0         |
| log_error          | GLOBAL          | /etc/my.cnf   | 0         | 0         |
| pid_file           | GLOBAL          | /etc/my.cnf   | 0         | 0         |
| secure_file_priv   | GLOBAL          | /etc/my.cnf   | 0         | 0         |
| skip_name_resolve  | GLOBAL          | /etc/my.cnf   | 0         | 0         |
| socket             | GLOBAL          | /etc/my.cnf   | 0         | 0         |
+--------------------+-----------------+---------------+-----------+-----------+
7 rows in set (0.00 sec)

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

mysql> SET PERSIST log_timestamps='SYSTEM';
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT * FROM performance_schema.variables_info WHERE variable_source != 'COMPILED';
+--------------------+-----------------+---------------+-----------+-----------+
| VARIABLE_NAME      | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE |
+--------------------+-----------------+---------------+-----------+-----------+
| datadir            | GLOBAL          | /etc/my.cnf   | 0         | 0         |
| foreign_key_checks | DYNAMIC         |               | 0         | 0         |
| log_error          | GLOBAL          | /etc/my.cnf   | 0         | 0         |
| log_timestamps     | DYNAMIC         |               | 0         | 0         |
| pid_file           | GLOBAL          | /etc/my.cnf   | 0         | 0         |
| secure_file_priv   | GLOBAL          | /etc/my.cnf   | 0         | 0         |
| skip_name_resolve  | GLOBAL          | /etc/my.cnf   | 0         | 0         |
| socket             | GLOBAL          | /etc/my.cnf   | 0         | 0         |
+--------------------+-----------------+---------------+-----------+-----------+
8 rows in set (0.00 sec)

mysql> 

再起動後も値が反映されている事を確認


[root@DockerHost oracle]# docker stop f0d7a1a71563
f0d7a1a71563
[root@DockerHost oracle]# docker ps -a
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                     PORTS               NAMES
f0d7a1a71563        mysql/mysql-server:8.0      "/entrypoint.sh mysql"   11 minutes ago      Exited (0) 2 seconds ago                       mysql8
88da7fe02e00        mysql:5.7.10                "/entrypoint.sh mysql"   3 months ago        Exited (0) 3 months ago                        my_docker03
4fde03dc4cb5        mysql/mysql-server:5.7.10   "/entrypoint.sh mysql"   8 months ago        Exited (0) 3 months ago                        my_docker02
5152a22b2aa0        mysql:5.7.10                "/entrypoint.sh mysql"   8 months ago        Exited (0) 8 months ago                        my_docker01
[root@DockerHost oracle]# docker start f0d7a1a71563
f0d7a1a71563
[root@DockerHost oracle]# docker ps -a
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                    PORTS                 NAMES
f0d7a1a71563        mysql/mysql-server:8.0      "/entrypoint.sh mysql"   11 minutes ago      Up 2 seconds              3306/tcp, 33060/tcp   mysql8
88da7fe02e00        mysql:5.7.10                "/entrypoint.sh mysql"   3 months ago        Exited (0) 3 months ago                         my_docker03
4fde03dc4cb5        mysql/mysql-server:5.7.10   "/entrypoint.sh mysql"   8 months ago        Exited (0) 3 months ago                         my_docker02
5152a22b2aa0        mysql:5.7.10                "/entrypoint.sh mysql"   8 months ago        Exited (0) 8 months ago                         my_docker01
[root@DockerHost oracle]# 

[root@DockerHost oracle]# docker exec -it mysql8 mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
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 variables like 'log_timestamps';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| log_timestamps | SYSTEM |
+----------------+--------+
1 row in set (0.02 sec)

mysql> 

(補足) SET PERSISTで設定変更した、GLOBAL変数は以下のようにmysqld-auto.cnfから読み込まれています。

Under the hood the settings will be persisted to a file named mysqld-auto.cnf which will be created in the data directory.
This file will be read during server startup just like any other configuration file, and all variables present in this file will be applied as the highest priority.
That means the file mysqld-auto.cnf will be the last file to be applied on server startup (even after command-line options) and takes precedence if a specific setting has been specified in more than one location.


mysql>  select VARIABLE_NAME,VARIABLE_SOURCE,VARIABLE_PATH from performance_schema.variables_info where VARIABLE_PATH <> '';
+-------------------+-----------------+--------------------------------+
| VARIABLE_NAME     | VARIABLE_SOURCE | VARIABLE_PATH                  |
+-------------------+-----------------+--------------------------------+
| datadir           | GLOBAL          | /etc/my.cnf                    |
| log_error         | GLOBAL          | /etc/my.cnf                    |
| log_timestamps    | PERSISTED       | /var/lib/mysql/mysqld-auto.cnf |
| pid_file          | GLOBAL          | /etc/my.cnf                    |
| secure_file_priv  | GLOBAL          | /etc/my.cnf                    |
| skip_name_resolve | GLOBAL          | /etc/my.cnf                    |
| socket            | GLOBAL          | /etc/my.cnf                    |
+-------------------+-----------------+--------------------------------+
7 rows in set (0.00 sec)

mysql> 

■MySQL8.0データディクショナリの確認


[root@DockerHost var]# docker exec -it mysql8 mysql -uroot -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> source /var/lib/mysql/init-docker-sakila.sql

Query OK, 200 rows affected (0.01 sec)
Records: 200  Duplicates: 0  Warnings: 0

Query OK, 603 rows affected (0.04 sec)
Records: 603  Duplicates: 0  Warnings: 0

Query OK, 600 rows affected (0.01 sec)
Records: 600  Duplicates: 0  Warnings: 0

<SNIP>

Query OK, 109 rows affected (0.01 sec)
Records: 109  Duplicates: 0  Warnings: 0

Query OK, 599 rows affected (0.04 sec)
Records: 599  Duplicates: 0  Warnings: 0

Query OK, 1000 rows affected (0.17 sec)
Records: 1000  Duplicates: 0  Warnings: 0

Query OK, 5462 rows affected (0.18 sec)
Records: 5462  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

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

mysql> 

■ MySQL8.0で作成したSakilaサンプルデータベースのデータディクショナリー確認

[root@DockerHost sakila]# ls -l /docker/docker8/sakila
total 25104
-rw-r----- 1 27 27   147456 Sep 20 13:55 actor.ibd
-rw-r----- 1 27 27   278528 Sep 20 13:55 address.ibd
-rw-r----- 1 27 27   131072 Sep 20 13:55 category.ibd
-rw-r----- 1 27 27   147456 Sep 20 13:55 city.ibd
-rw-r----- 1 27 27   131072 Sep 20 13:55 country.ibd
-rw-r----- 1 27 27   229376 Sep 20 13:55 customer.ibd
-rw-r----- 1 27 27   376832 Sep 20 13:55 film_actor.ibd
-rw-r----- 1 27 27   180224 Sep 20 13:55 film_category.ibd
-rw-r----- 1 27 27   376832 Sep 20 13:55 film.ibd
-rw-r----- 1 27 27   294912 Sep 20 13:55 film_text.ibd
-rw-r----- 1 27 27   131072 Sep 20 13:55 FTS_000000000000005b_00000000000000ae_INDEX_1.ibd
-rw-r----- 1 27 27   131072 Sep 20 13:55 FTS_000000000000005b_00000000000000ae_INDEX_2.ibd
-rw-r----- 1 27 27   131072 Sep 20 13:55 FTS_000000000000005b_00000000000000ae_INDEX_3.ibd
-rw-r----- 1 27 27   131072 Sep 20 13:55 FTS_000000000000005b_00000000000000ae_INDEX_4.ibd
-rw-r----- 1 27 27   131072 Sep 20 13:55 FTS_000000000000005b_00000000000000ae_INDEX_5.ibd
-rw-r----- 1 27 27   131072 Sep 20 13:55 FTS_000000000000005b_00000000000000ae_INDEX_6.ibd
-rw-r----- 1 27 27   131072 Sep 20 13:55 FTS_000000000000005b_BEING_DELETED_CACHE.ibd
-rw-r----- 1 27 27   131072 Sep 20 13:55 FTS_000000000000005b_BEING_DELETED.ibd
-rw-r----- 1 27 27   131072 Sep 20 13:55 FTS_000000000000005b_CONFIG.ibd
-rw-r----- 1 27 27   131072 Sep 20 13:55 FTS_000000000000005b_DELETED_CACHE.ibd
-rw-r----- 1 27 27   131072 Sep 20 13:55 FTS_000000000000005b_DELETED.ibd
-rw-r----- 1 27 27   475136 Sep 20 13:56 inventory.ibd
-rw-r----- 1 27 27   131072 Sep 20 13:55 language.ibd
-rw-r----- 1 27 27 10485760 Sep 20 13:56 payment.ibd
-rw-r----- 1 27 27 10485760 Sep 20 13:56 rental.ibd
-rw-r----- 1 27 27   180224 Sep 20 13:55 staff.ibd
-rw-r----- 1 27 27   163840 Sep 20 13:55 store.ibd
[root@DockerHost sakila]# 

■ MySQL5.7で作成したSakilaサンプルデータベースのデータディクショナリー確認
frmやTRGファイルが存在しています。


[root@DockerHost sakila]# ls -l /docker/docker03/sakila/
total 24620
-rw-r----- 1 999 999     8694 Jun  7 07:17 actor.frm
-rw-r----- 1 999 999   114688 Jun  7 07:17 actor.ibd
-rw-r----- 1 999 999     2863 Jun  7 07:17 actor_info.frm
-rw-r----- 1 999 999     8878 Jun  7 07:17 address.frm
-rw-r----- 1 999 999   245760 Jun  7 07:17 address.ibd
-rw-r----- 1 999 999     8648 Jun  7 07:17 category.frm
-rw-r----- 1 999 999    98304 Jun  7 07:17 category.ibd
-rw-r----- 1 999 999     8682 Jun  7 07:17 city.frm
-rw-r----- 1 999 999   114688 Jun  7 07:17 city.ibd
-rw-r----- 1 999 999     8652 Jun  7 07:17 country.frm
-rw-r----- 1 999 999    98304 Jun  7 07:17 country.ibd
-rw-r----- 1 999 999       40 Jun  7 07:17 customer_create_date.TRN
-rw-r----- 1 999 999     8890 Jun  7 07:17 customer.frm
-rw-r----- 1 999 999   196608 Jun  7 07:17 customer.ibd
-rw-r----- 1 999 999     1892 Jun  7 07:17 customer_list.frm
-rw-r----- 1 999 999      300 Jun  7 07:17 customer.TRG
-rw-r----- 1 999 999       61 Jun  7 07:17 db.opt
-rw-r----- 1 999 999       36 Jun  7 07:17 del_film.TRN
-rw-r----- 1 999 999     8648 Jun  7 07:17 film_actor.frm
-rw-r----- 1 999 999   344064 Jun  7 07:17 film_actor.ibd
-rw-r----- 1 999 999     8654 Jun  7 07:17 film_category.frm
-rw-r----- 1 999 999   147456 Jun  7 07:17 film_category.ibd
-rw-r----- 1 999 999     9188 Jun  7 07:17 film.frm
-rw-r----- 1 999 999   344064 Jun  7 07:17 film.ibd
-rw-r----- 1 999 999     2616 Jun  7 07:17 film_list.frm
-rw-r----- 1 999 999     8642 Jun  7 07:17 film_text.frm
-rw-r----- 1 999 999   262144 Jun  7 07:17 film_text.ibd
-rw-r----- 1 999 999     1093 Jun  7 07:17 film.TRG
-rw-r----- 1 999 999    98304 Jun  7 07:17 FTS_0000000000000035_0000000000000045_INDEX_1.ibd
-rw-r----- 1 999 999   131072 Jun  7 07:17 FTS_0000000000000035_0000000000000045_INDEX_2.ibd
-rw-r----- 1 999 999    98304 Jun  7 07:17 FTS_0000000000000035_0000000000000045_INDEX_3.ibd
-rw-r----- 1 999 999    98304 Jun  7 07:17 FTS_0000000000000035_0000000000000045_INDEX_4.ibd
-rw-r----- 1 999 999   131072 Jun  7 07:17 FTS_0000000000000035_0000000000000045_INDEX_5.ibd
-rw-r----- 1 999 999    98304 Jun  7 07:17 FTS_0000000000000035_0000000000000045_INDEX_6.ibd
-rw-r----- 1 999 999    98304 Jun  7 07:17 FTS_0000000000000035_BEING_DELETED_CACHE.ibd
-rw-r----- 1 999 999    98304 Jun  7 07:17 FTS_0000000000000035_BEING_DELETED.ibd
-rw-r----- 1 999 999    98304 Jun  7 07:17 FTS_0000000000000035_CONFIG.ibd
-rw-r----- 1 999 999    98304 Jun  7 07:17 FTS_0000000000000035_DELETED_CACHE.ibd
-rw-r----- 1 999 999    98304 Jun  7 07:17 FTS_0000000000000035_DELETED.ibd
-rw-r----- 1 999 999       36 Jun  7 07:17 ins_film.TRN
-rw-r----- 1 999 999     8694 Jun  7 07:17 inventory.frm
-rw-r----- 1 999 999   442368 Jun  7 07:17 inventory.ibd
-rw-r----- 1 999 999     8648 Jun  7 07:17 language.frm
-rw-r----- 1 999 999    98304 Jun  7 07:17 language.ibd
-rw-r----- 1 999 999     3234 Jun  7 07:17 nicer_but_slower_film_list.frm
-rw-r----- 1 999 999       39 Jun  7 07:17 payment_date.TRN
-rw-r----- 1 999 999     8818 Jun  7 07:17 payment.frm
-rw-r----- 1 999 999 10485760 Jun  7 07:17 payment.ibd
-rw-r----- 1 999 999      292 Jun  7 07:17 payment.TRG
-rw-r----- 1 999 999       38 Jun  7 07:17 rental_date.TRN
-rw-r----- 1 999 999     8830 Jun  7 07:17 rental.frm
-rw-r----- 1 999 999 10485760 Jun  7 07:17 rental.ibd
-rw-r----- 1 999 999      289 Jun  7 07:17 rental.TRG
-rw-r----- 1 999 999     1669 Jun  7 07:17 sales_by_film_category.frm
-rw-r----- 1 999 999     2344 Jun  7 07:17 sales_by_store.frm
-rw-r----- 1 999 999     8952 Jun  7 07:17 staff.frm
-rw-r----- 1 999 999   147456 Jun  7 07:17 staff.ibd
-rw-r----- 1 999 999     1705 Jun  7 07:17 staff_list.frm
-rw-r----- 1 999 999     8708 Jun  7 07:17 store.frm
-rw-r----- 1 999 999   131072 Jun  7 07:17 store.ibd
-rw-r----- 1 999 999       36 Jun  7 07:17 upd_film.TRN

メモ:MySQL8.0 DATA DICTIONARY

Data dictionary tables are invisible. They cannot be read with SELECT, do not appear in the output of SHOW TABLES,
are not listed in the INFORMATION_SCHEMA.TABLES table, and so forth. However, in most cases there are corresponding INFORMATION_SCHEMA tables
that can be queried. Conceptually, the INFORMATION_SCHEMA provides a view through which MySQL exposes data dictionary metadata.
For example, you cannot select from the mysql.schemata table directly:
http://dev.mysql.com/doc/refman/8.0/en/system-database.html#system-database-data-dictionary-tables


mysql> SELECT * FROM mysql.schemata;                                                                         
ERROR 3554 (HY000): Access to system table 'mysql.schemata' is rejected.
mysql> 

mysql> SELECT * FROM INFORMATION_SCHEMA.SCHEMATA limit 10; 
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | mysql              | latin1                     | latin1_swedish_ci      |     NULL |
| def          | information_schema | utf8                       | utf8_general_ci        |     NULL |
| def          | performance_schema | utf8                       | utf8_general_ci        |     NULL |
| def          | sys                | utf8                       | utf8_general_ci        |     NULL |
| def          | sakila             | latin1                     | latin1_swedish_ci      |     NULL |
+--------------+--------------------+----------------------------+------------------------+----------+
5 rows in set (0.00 sec)

mysql> select * from INNODB_SYS_TABLES where NAME LIKE 'sakila%';
+----------+------------------------------------------------------+------+--------+-------+------------+---------------+------------+
| TABLE_ID | NAME                                                 | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+------------------------------------------------------+------+--------+-------+------------+---------------+------------+
|       97 | sakila/FTS_000000000000005b_00000000000000ae_INDEX_1 |   33 |      8 |    85 | Dynamic    |             0 | Single     |
|       98 | sakila/FTS_000000000000005b_00000000000000ae_INDEX_2 |   33 |      8 |    86 | Dynamic    |             0 | Single     |
|       99 | sakila/FTS_000000000000005b_00000000000000ae_INDEX_3 |   33 |      8 |    87 | Dynamic    |             0 | Single     |
|      100 | sakila/FTS_000000000000005b_00000000000000ae_INDEX_4 |   33 |      8 |    88 | Dynamic    |             0 | Single     |
|      101 | sakila/FTS_000000000000005b_00000000000000ae_INDEX_5 |   33 |      8 |    89 | Dynamic    |             0 | Single     |
|      102 | sakila/FTS_000000000000005b_00000000000000ae_INDEX_6 |   33 |      8 |    90 | Dynamic    |             0 | Single     |
|       92 | sakila/FTS_000000000000005b_BEING_DELETED            |   33 |      4 |    80 | Dynamic    |             0 | Single     |
|       93 | sakila/FTS_000000000000005b_BEING_DELETED_CACHE      |   33 |      4 |    81 | Dynamic    |             0 | Single     |
|       94 | sakila/FTS_000000000000005b_CONFIG                   |   33 |      5 |    82 | Dynamic    |             0 | Single     |
|       95 | sakila/FTS_000000000000005b_DELETED                  |   33 |      4 |    83 | Dynamic    |             0 | Single     |
|       96 | sakila/FTS_000000000000005b_DELETED_CACHE            |   33 |      4 |    84 | Dynamic    |             0 | Single     |
|      109 | sakila/OPC                                           |   33 |      4 |   100 | Dynamic    |             0 | Single     |
|       82 | sakila/actor                                         |   33 |      7 |    70 | Dynamic    |             0 | Single     |
|       83 | sakila/address                                       |   33 |     12 |    71 | Dynamic    |             0 | Single     |
|       84 | sakila/category                                      |   33 |      6 |    72 | Dynamic    |             0 | Single     |
|       85 | sakila/city                                          |   33 |      7 |    73 | Dynamic    |             0 | Single     |
|       86 | sakila/country                                       |   33 |      6 |    74 | Dynamic    |             0 | Single     |
|       87 | sakila/customer                                      |   33 |     12 |    75 | Dynamic    |             0 | Single     |
|       88 | sakila/film                                          |   33 |     16 |    76 | Dynamic    |             0 | Single     |
|       89 | sakila/film_actor                                    |   33 |      6 |    77 | Dynamic    |             0 | Single     |
|       90 | sakila/film_category                                 |   33 |      6 |    78 | Dynamic    |             0 | Single     |
|       91 | sakila/film_text                                     |   33 |      7 |    79 | Dynamic    |             0 | Single     |
|      103 | sakila/inventory                                     |   33 |      7 |    91 | Dynamic    |             0 | Single     |
|      104 | sakila/language                                      |   33 |      6 |    92 | Dynamic    |             0 | Single     |
|      105 | sakila/payment                                       |   33 |     10 |    93 | Dynamic    |             0 | Single     |
|      106 | sakila/rental                                        |   33 |     10 |    94 | Dynamic    |             0 | Single     |
|      107 | sakila/staff                                         |   33 |     14 |    95 | Dynamic    |             0 | Single     |
|      108 | sakila/store                                         |   33 |      7 |    96 | Dynamic    |             0 | Single     |
+----------+------------------------------------------------------+------+--------+-------+------------+---------------+------------+
28 rows in set (0.00 sec)

mysql> select * from INNODB_SYS_DATAFILES limit 5;
+-------+------------------------------+
| SPACE | PATH                         |
+-------+------------------------------+
|     2 | ./mysql/version.ibd          |
|     3 | ./mysql/character_sets.ibd   |
|     4 | ./mysql/collations.ibd       |
|     5 | ./mysql/tablespaces.ibd      |
|     6 | ./mysql/tablespace_files.ibd |
+-------+------------------------------+
5 rows in set (0.01 sec)

mysql> 

■ 参照
15.6 Data Dictionary Usage Differences
The MySQL 8.0.0 Milestone Release is available


現状、まだLab版ですが、MySQL5.7の追加のプラグインとして、マルチマスタまたはアクティブ/アクティブレプリケーションをサポートする
同期レプリケーション型のグループレプリケーションが準備されています。まだ、Lab版という事もあり、機能追加やバグ対応などがまだまだ必要な段階ですが、LAB版→DR版→RC版→GA版と段々と安定してくると思いますので、次のLab版がリリースされたら是非検証環境で試してみて頂ければと思います。
マスターサーバーのHA対応やスレーブが多い環境で、マスターサーバーのレプリケーション負荷分散等に活用出来そうです。

Group Replication関連参考ブログを見て頂けると、基本的なインストール方法が書かれていますので試される場合は、此方を参考にして下さい。

http://mysqlhighavailability.com/getting-started-with-mysql-group-replication/

NODE1にてグループレプリケーション開始
※オプションファイルに書いておくことで、SETコマンドの実行は不要です。
※XCOMで通信する為のポートは、通常のMySQL PORT3306とは別にしてください。


root@localhost [mysql]> SET GLOBAL group_replication_group_name= "00000000-1111-2222-3333-123456789ABC";
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SET GLOBAL group_replication_bootstrap_group= 1;
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SET GLOBAL group_replication_local_address="192.168.56.101:13001";
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SET GLOBAL group_replication_peer_addresses= "192.168.56.101:13001,192.168.56.102:13001";
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SET GLOBAL group_replication_recovery_user='rpl_user';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SET GLOBAL group_replication_recovery_password='rpl_pass';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.59 sec)

root@localhost [mysql]> SET GLOBAL group_replication_bootstrap_group= 0;
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
             CHANNEL_NAME: group_replication_applier
               GROUP_NAME: 00000000-1111-2222-3333-123456789ABC
              SOURCE_UUID: 00000000-1111-2222-3333-123456789ABC
                THREAD_ID: NULL
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
 RECEIVED_TRANSACTION_SET: 00000000-1111-2222-3333-123456789abc:1-4
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

root@localhost [mysql]> 


NODE2をGRに参加してみます。


root@localhost [mysql]> SET GLOBAL group_replication_group_name= "00000000-1111-2222-3333-123456789ABC";
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SET GLOBAL group_replication_local_address="192.168.56.102:13001";
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SET GLOBAL group_replication_peer_addresses= "192.168.56.101:13001,192.168.56.102:13001";
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SET GLOBAL group_replication_recovery_user='rpl_user';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SET GLOBAL group_replication_recovery_password='rpl_pass';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.04 sec)

root@localhost [mysql]> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 29ea17bc-3848-11e6-9900-0800279ca844
 MEMBER_HOST: misc01
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 5b07d5d8-4057-11e6-a315-0800279cea3c
 MEMBER_HOST: misc02
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
2 rows in set (0.01 sec)

root@localhost [mysql]> SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
             CHANNEL_NAME: group_replication_applier
               GROUP_NAME: 00000000-1111-2222-3333-123456789ABC
              SOURCE_UUID: 00000000-1111-2222-3333-123456789ABC
                THREAD_ID: NULL
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
 RECEIVED_TRANSACTION_SET: 00000000-1111-2222-3333-123456789abc:4
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

root@localhost [mysql]> 



group_members

MEMBER_STATEが共にONLINEになっているので、DDL、DMLを処理して同期されているか確認して見ます
先ずは、NODE1でデータベース、テーブルを作成してからデータを1件入れてみます。
NODE1で作成したオブジェクトやデータはNODE2でも確認出来ます。
また、同様にNODE2で入れたデータは、NODE1で確認する事が出来ます。


root@localhost [mysql]> CREATE DATABASE GR_TEST;
Query OK, 1 row affected (0.03 sec)

root@localhost [mysql]> use GR_TEST;
Database changed
root@localhost [GR_TEST]> CREATE TABLE GR_TEST.T01 (
    -> ID INT NOT NULL PRIMARY KEY,
    -> MEMO varchar(30) COLLATE utf8_bin NOT NULL DEFAULT ''
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.07 sec)

root@localhost [GR_TEST]> INSERT INTO GR_TEST.T01(ID,MEMO) VALUES (1,@@hostname);
Query OK, 1 row affected (0.07 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+
| ID | MEMO   |
+----+--------+
|  1 | misc01 |
+----+--------+
1 row in set (0.01 sec)

root@localhost [GR_TEST]> 

NODE2でデータを確認してみます。


root@localhost [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| GR_TEST            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

root@localhost [mysql]> use GR_TEST
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@localhost [GR_TEST]> select * from T01;
+----+--------+
| ID | MEMO   |
+----+--------+
|  1 | misc01 |
+----+--------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> INSERT INTO GR_TEST.T01(ID,MEMO) VALUES (2,@@hostname);
Query OK, 1 row affected (0.04 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+
| ID | MEMO   |
+----+--------+
|  1 | misc01 |
|  2 | misc02 |
+----+--------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | ONLINE       |
| group_replication_applier | 5b07d5d8-4057-11e6-a315-0800279cea3c | misc02      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
             CHANNEL_NAME: group_replication_applier
               GROUP_NAME: 00000000-1111-2222-3333-123456789ABC
              SOURCE_UUID: 00000000-1111-2222-3333-123456789ABC
                THREAD_ID: NULL
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
 RECEIVED_TRANSACTION_SET: 00000000-1111-2222-3333-123456789abc:4-7
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE2で入れたデータはNODE1でも確認出来ました。
これで、双方向にレプリケーションが張られている事が確認出来ました。


root@localhost [GR_TEST]> select * from T01;
+----+--------+
| ID | MEMO   |
+----+--------+
|  1 | misc01 |
|  2 | misc02 |
+----+--------+
2 rows in set (0.00 sec)


root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | ONLINE       |
| group_replication_applier | 5b07d5d8-4057-11e6-a315-0800279cea3c | misc02      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
             CHANNEL_NAME: group_replication_applier
               GROUP_NAME: 00000000-1111-2222-3333-123456789ABC
              SOURCE_UUID: 00000000-1111-2222-3333-123456789ABC
                THREAD_ID: NULL
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
 RECEIVED_TRANSACTION_SET: 00000000-1111-2222-3333-123456789abc:4-7
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 

GTIDの状態を確認
自分で更新したデータに関しては、RECEIVED_TRANSACTION_SETには反映されないので、@@GLOBAL.GTID_EXECUTEDで何処まで適用されているか確認。


root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
             CHANNEL_NAME: group_replication_applier
               GROUP_NAME: 00000000-1111-2222-3333-123456789ABC
              SOURCE_UUID: 00000000-1111-2222-3333-123456789ABC
                THREAD_ID: NULL
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
 RECEIVED_TRANSACTION_SET: 00000000-1111-2222-3333-123456789abc:4-7:9
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

root@localhost [GR_TEST]> SELECT @@GLOBAL.GTID_EXECUTED;
+-------------------------------------------+
| @@GLOBAL.GTID_EXECUTED                    |
+-------------------------------------------+
| 00000000-1111-2222-3333-123456789abc:1-10 |
+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE1とNODE2の間で、トランザクションの競合が発生した場合
(同時に同じデータを更新しようとした場合)

NODE1で先ずは、トランザクションを張って処理を実行してみます。そして、Commitを行う前に、NODE2で同じデータを更新処理してみます。
最初に処理を開始した、NODE1は問題無く処理出来てますが、NODE2のCommit処理はエラーで終了しています。

root@localhost [GR_TEST]> select * from T01;
+----+--------+
| ID | MEMO   |
+----+--------+
|  1 | misc01 |
|  2 | misc02 |
|  3 | misc01 |
|  4 | misc02 |
+----+--------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> start transaction;update T01 set MEMO = @@hostname where ID = 4;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [GR_TEST]> commit;
Query OK, 0 rows affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+
| ID | MEMO   |
+----+--------+
|  1 | misc01 |
|  2 | misc02 |
|  3 | misc01 |
|  4 | misc01 |
+----+--------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE2は、ERROR 1180 (HY000): Got error 149 during COMMITでエラーになっています。

root@localhost [GR_TEST]> select * from T01;
+----+--------+
| ID | MEMO   |
+----+--------+
|  1 | misc01 |
|  2 | misc02 |
|  3 | misc01 |
|  4 | misc02 |
+----+--------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> start transaction;update T01 set MEMO = 'MISC02' where ID = 4;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [GR_TEST]> commit;
ERROR 1180 (HY000): Got error 149 during COMMIT
root@localhost [GR_TEST]> select * from T01;
+----+--------+
| ID | MEMO   |
+----+--------+
|  1 | misc01 |
|  2 | misc02 |
|  3 | misc01 |
|  4 | misc01 |
+----+--------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

group_replication_tran_conf

ONLINE DDLでALTERを実行した場合
結果としては、問題無くDDLも実行可能で伝搬されます。但し、現状のGRの仕様としては、オンラインスキーマ変更は推奨されていないので、
1)BINLOGをOFFにしてDDL実行 2) 他のノードも同様にBINLOGをOFFにしてDDL実行 3) 最後にアプリケーションを変更し変更を反映させるのが良さそうです。

NODE1にてDDLを実行して列を追加してみます。

root@localhost [GR_TEST]> ALTER TABLE T01 add column created_time datetime DEFAULT CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [GR_TEST]> desc T01;
+--------------+-------------+------+-----+-------------------+-------+
| Field        | Type        | Null | Key | Default           | Extra |
+--------------+-------------+------+-----+-------------------+-------+
| ID           | int(11)     | NO   | PRI | NULL              |       |
| MEMO         | varchar(30) | NO   |     |                   |       |
| created_time | datetime    | YES  |     | CURRENT_TIMESTAMP |       |
+--------------+-------------+------+-----+-------------------+-------+
3 rows in set (0.03 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-07-17 15:40:29 |
|  2 | misc02 | 2016-07-17 15:40:29 |
|  3 | misc01 | 2016-07-17 15:40:29 |
|  4 | misc02 | 2016-07-17 15:40:29 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> INSERT INTO GR_TEST.T01(ID,MEMO) VALUES (5,@@hostname);
Query OK, 1 row affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-07-17 15:40:29 |
|  2 | misc02 | 2016-07-17 15:40:29 |
|  3 | misc01 | 2016-07-17 15:40:29 |
|  4 | misc02 | 2016-07-17 15:40:29 |
|  5 | misc01 | 2016-07-17 15:42:33 |
+----+--------+---------------------+
5 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE2でNODE1で実行されたDDLの結果を確認して、NODE2からデータを追加してみます。

root@localhost [GR_TEST]> desc T01;
+--------------+-------------+------+-----+-------------------+-------+
| Field        | Type        | Null | Key | Default           | Extra |
+--------------+-------------+------+-----+-------------------+-------+
| ID           | int(11)     | NO   | PRI | NULL              |       |
| MEMO         | varchar(30) | NO   |     |                   |       |
| created_time | datetime    | YES  |     | CURRENT_TIMESTAMP |       |
+--------------+-------------+------+-----+-------------------+-------+
3 rows in set (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-07-17 15:40:29 |
|  2 | misc02 | 2016-07-17 15:40:29 |
|  3 | misc01 | 2016-07-17 15:40:29 |
|  4 | misc02 | 2016-07-17 15:40:29 |
|  5 | misc01 | 2016-07-17 15:42:33 |
+----+--------+---------------------+
5 rows in set (0.00 sec)

root@localhost [GR_TEST]> INSERT INTO GR_TEST.T01(ID,MEMO) VALUES (6,@@hostname);
Query OK, 1 row affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-07-17 15:40:29 |
|  2 | misc02 | 2016-07-17 15:40:29 |
|  3 | misc01 | 2016-07-17 15:40:29 |
|  4 | misc02 | 2016-07-17 15:40:29 |
|  5 | misc01 | 2016-07-17 15:42:33 |
|  6 | misc02 | 2016-07-17 15:44:03 |
+----+--------+---------------------+
6 rows in set (0.00 sec)


root@localhost [GR_TEST]> SELECT @@GLOBAL.GTID_EXECUTED;
+-------------------------------------------+
| @@GLOBAL.GTID_EXECUTED                    |
+-------------------------------------------+
| 00000000-1111-2222-3333-123456789abc:1-15 |
+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
             CHANNEL_NAME: group_replication_applier
               GROUP_NAME: 00000000-1111-2222-3333-123456789ABC
              SOURCE_UUID: 00000000-1111-2222-3333-123456789ABC
                THREAD_ID: NULL
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
 RECEIVED_TRANSACTION_SET: 00000000-1111-2222-3333-123456789abc:4-7:9:11:13-14
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | ONLINE       |
| group_replication_applier | 5b07d5d8-4057-11e6-a315-0800279cea3c | misc02      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE2でデータを入れたあとにNODE1の状況を確認してみます。
特に問題無く、データが反映されている事が確認出来ます。
レプリケーションもRAWベースなので特に時間関連の関数なども気にしなくて良いです。


root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-07-17 15:40:29 |
|  2 | misc02 | 2016-07-17 15:40:29 |
|  3 | misc01 | 2016-07-17 15:40:29 |
|  4 | misc02 | 2016-07-17 15:40:29 |
|  5 | misc01 | 2016-07-17 15:42:33 |
|  6 | misc02 | 2016-07-17 15:44:03 |
+----+--------+---------------------+
6 rows in set (0.01 sec)

root@localhost [GR_TEST]> SELECT @@GLOBAL.GTID_EXECUTED;
+-------------------------------------------+
| @@GLOBAL.GTID_EXECUTED                    |
+-------------------------------------------+
| 00000000-1111-2222-3333-123456789abc:1-15 |
+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
             CHANNEL_NAME: group_replication_applier
               GROUP_NAME: 00000000-1111-2222-3333-123456789ABC
              SOURCE_UUID: 00000000-1111-2222-3333-123456789ABC
                THREAD_ID: NULL
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
 RECEIVED_TRANSACTION_SET: 00000000-1111-2222-3333-123456789abc:1-4:8:10:12:15
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | ONLINE       |
| group_replication_applier | 5b07d5d8-4057-11e6-a315-0800279cea3c | misc02      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

余談: Group Replicationは全てマスターなので、Show Slave Statusは不要ですね。


/*** NODE1 ***/
root@localhost [GR_TEST]> show slave status\G
Empty set (0.00 sec)

root@localhost [GR_TEST]> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000001 |     4391 |              |                  | 00000000-1111-2222-3333-123456789abc:1-15 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 


/*** NODE2 ***/
root@localhost [GR_TEST]> show slave status\G
Empty set (0.02 sec)

root@localhost [GR_TEST]> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000001 |     4391 |              |                  | 00000000-1111-2222-3333-123456789abc:1-15 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 

参考パラメータ


root@localhost [GR_TEST]> show global variables like '%group_repli%';
+---------------------------------------------------+-------------------------------------------+
| Variable_name                                     | Value                                     |
+---------------------------------------------------+-------------------------------------------+
| group_replication_allow_local_lower_version_join  | OFF                                       |
| group_replication_auto_increment_increment        | 7                                         |
| group_replication_bootstrap_group                 | OFF                                       |
| group_replication_components_stop_timeout         | 31536000                                  |
| group_replication_gcs_engine                      | xcom                                      |
| group_replication_group_name                      | 00000000-1111-2222-3333-123456789ABC      |
| group_replication_local_address                   | 192.168.56.101:13001                      |
| group_replication_peer_addresses                  | 192.168.56.101:13001,192.168.56.102:13001 |
| group_replication_pipeline_type_var               | STANDARD                                  |
| group_replication_recovery_complete_at            | TRANSACTIONS_CERTIFIED                    |
| group_replication_recovery_password               |                                           |
| group_replication_recovery_reconnect_interval     | 120                                       |
| group_replication_recovery_retry_count            | 2                                         |
| group_replication_recovery_ssl_ca                 |                                           |
| group_replication_recovery_ssl_capath             |                                           |
| group_replication_recovery_ssl_cert               |                                           |
| group_replication_recovery_ssl_cipher             |                                           |
| group_replication_recovery_ssl_crl                |                                           |
| group_replication_recovery_ssl_crlpath            |                                           |
| group_replication_recovery_ssl_key                |                                           |
| group_replication_recovery_ssl_verify_server_cert | OFF                                       |
| group_replication_recovery_use_ssl                | OFF                                       |
| group_replication_recovery_user                   | rpl_user                                  |
| group_replication_start_on_boot                   | OFF                                       |
+---------------------------------------------------+-------------------------------------------+
24 rows in set (0.01 sec)

root@localhost [GR_TEST]> 

【参考】

LABサイト
http://labs.mysql.com/

Group Replication関連参考ブログ
http://mysqlhighavailability.com/getting-started-with-mysql-group-replication/

MySQLセミナー資料
http://downloads.mysql.com/presentations/20160510_06_MySQL_57_ReplicationEnhancements.pdf

Auto Incrementの値
http://mysqlhighavailability.com/mysql-group-replication-auto-increment-configuration-handling/


MySQL5.7.13以降における、Enterprise Audit機能の改善

MySQL5.7.13以降で、全てのユーザー若しくは、特定ユーザーが特定のテーブルに対して行った、read,insert,update,delete処理のみを監査出来るようになりました。
フィルター作成はJSONフォーマットで定義するようです。この機能は、以前から待ち望んでいたので、嬉しい機能の一つです。

【検証バージョン】5.7.13-enterprise-commercial-advanced-log

フィルタリング詳細:
https://dev.mysql.com/doc/refman/5.7/en/audit-log-filtering.html

例) こちらは、confidentialテーブルに対してselectしたSQLだけ監査するようにフィルターしてある場合の監査ログです。
【フィルター】

root@localhost [mysql]> select * from audit_log_filter;
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| NAME             | FILTER                                                                                                                                            |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| log_all          | {"filter": {"log": true}}                                                                                                                         |
| log_confidential | {"filter": {"class": {"name": "table_access", "event": {"log": {"field": {"name": "table_name.str", "value": "confidential"}}, "name": "read"}}}} |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+

【監査ログ】
audit

1) 以下のように書かれていたので、既にインストール済みバージョンをアンインストールしました。
If the audit_log plugin is already installed from a version of MySQL before 5.7.13,
uninstall it using this statement and then restart the server before installing the current version:

■ オプションファイルもコメントアウト


[mysqld]
#plugin-load=audit_log.so
#audit-log=FORCE_PLUS_PERMANENT

■ Uninstall作業

root@localhost [(none)]> UNINSTALL PLUGIN audit_log;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [(none)]> show warnings;
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 1620 | Plugin is busy and will be uninstalled on shutdown |
+---------+------+----------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [(none)]> exit
Bye


[root@misc02 admin]# /etc/init.d/mysql.server restart

■ ロードされていない事を確認してインストール準備完了

root@localhost [information_Schema]> select PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE,LOAD_OPTION from FROM INFORMATION_SCHEMA.PLUGINS where PLUGIN_NAME like 'audit%';
Empty set (0.00 sec)

root@localhost [information_Schema]> 

■ログ
参考) 5.7.13のログを確認したら、UNINSTALL前は以下のようなログが出ていました。

[root@misc01 admin]# cat /usr/local/mysql/data/error.log | grep audit
2016-06-15T21:26:01.568228+09:00 0 [Warning] Plugin audit_log reported: 'Audit Log plugin supports a filtering, which has not been installed yet. Audit Log plugin will run in the legacy mode, which will be disabled in the next release.'
2016-06-15T21:34:11.395386+09:00 4 [Note] Shutting down plugin 'audit_log'
[root@misc01 admin]# 

2)スクリプトを利用してインストールします。(これまで、Audit Pluginを利用した事が無ければここから設定開始)

スクリプトの内容を確認すると2つのテーブルと5つのファンクション,そして、AUDIT PLUGINのインストールを行っています。テーブルはInnoDBに進もうとしているのに、なぜかMyISAMを利用していますが。。。あとユーザーのサイズ制限がなぜかVARCHAAR(16)
こちらは、後で問い合わせしてみたいと思います。
———————————————————————————-
audit_log_filter_linux_install.sqlの内容
———————————————————————————-


USE mysql;

CREATE TABLE IF NOT EXISTS audit_log_filter(NAME VARCHAR(64) BINARY NOT NULL PRIMARY KEY, FILTER JSON NOT NULL) engine= MyISAM;
CREATE TABLE IF NOT EXISTS audit_log_user(USER VARCHAR(16) BINARY NOT NULL, HOST VARCHAR(60) BINARY NOT NULL, FILTERNAME VARCHAR(64) BINARY NOT NULL, PRIMARY KEY (USER, HOST), FOREIGN KEY (FILTERNAME) REFERENCES mysql.audit_log_filter(NAME)) engine= MyISAM;

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

CREATE FUNCTION audit_log_filter_set_filter RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_filter_remove_filter RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_filter_set_user RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_filter_remove_user RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_filter_flush RETURNS STRING SONAME 'audit_log.so';

SELECT audit_log_filter_flush() AS 'Result';

Installの実行
インストールスクリプトはSHAREフォルダーにあります。(WindowsとLinuxで別々なので注意して下さい)


[root@misc01 admin]# mysql -u root -p < /usr/local/mysql/share/audit_log_filter_linux_install.sql 
Enter password: 
Result
OK
[root@misc01 admin]# 


[root@misc01 admin]# mysql -u root -p -e "select PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE,LOAD_OPTION FROM INFORMATION_SCHEMA.PLUGINS where PLUGIN_NAME like 'audit%'"
Enter password: 
+-------------+---------------+-------------+-------------+
| PLUGIN_NAME | PLUGIN_STATUS | PLUGIN_TYPE | LOAD_OPTION |
+-------------+---------------+-------------+-------------+
| audit_log   | ACTIVE        | AUDIT       | ON          |
+-------------+---------------+-------------+-------------+
[root@misc01 admin]# 




root@localhost [mysql]> show tables from mysql like 'audit%';
+--------------------------+
| Tables_in_mysql (audit%) |
+--------------------------+
| audit_log_filter         |
| audit_log_user           |
+--------------------------+
2 rows in set (0.00 sec)

root@localhost [mysql]> 

ここで、監査の初期設定は完了です。
これから、フィルター設定を幾つか入れて確認してみます。

3) 監査用のユーザー作成、ルール作成、ルール適用して検証してみます


root@localhost [mysql]> CREATE USER 'audit_target';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> GRANT ALL ON *.* TO 'audit_target';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SELECT user, host FROM mysql.user WHERE user = 'audit_target';
+----------------+------+
| user           | host |
+----------------+------+
| audit_target   | %    |
+----------------+------+
1 row in set (0.00 sec)

root@localhost [mysql]> select * from audit_log_user;
Empty set (0.00 sec)

root@localhost [mysql]> select * from audit_log_filter;
Empty set (0.00 sec)

root@localhost [mysql]> SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }') AS 'Result';
+--------+
| Result |
+--------+
| OK     |
+--------+
1 row in set (0.00 sec)

root@localhost [mysql]> select * from audit_log_filter;
+-------------+---------------------------+
| NAME        | FILTER                    |
+-------------+---------------------------+
| log_all     | {"filter": {"log": true}} |
+-------------+---------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> SELECT audit_log_filter_set_user('audit_target@%', 'log_all') AS 'Result';
+--------+
| Result |
+--------+
| OK     |
+--------+
1 row in set (0.00 sec)

root@localhost [mysql]> select * from audit_log_user;
+----------------+------+-------------+
| USER           | HOST | FILTERNAME  |
+----------------+------+-------------+
| audit_target   | %    | log_all     |
+----------------+------+-------------+
1 row in set (0.00 sec)

root@localhost [mysql]> 

4) 別ホストから対象アカウントを利用してアクセスしてみます。


[root@misc02 admin]# mysql -h 192.168.56.113 -u audit_target
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.13-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

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

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

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

audit_target@192.168.56.113 [(none)]> CREATE DATABASE audit_log_test_db;
Query OK, 1 row affected (0.05 sec)

audit_target@192.168.56.113 [(none)]> USE audit_log_test_db;
Database changed

audit_target@192.168.56.113 [audit_log_test_db]> CREATE TABLE confidential (memo varchar(100));
Query OK, 0 rows affected (0.02 sec)

audit_target@192.168.56.113 [audit_log_test_db]> INSERT INTO audit_log_test_table VALUES(1);
Query OK, 1 row affected (0.02 sec)

audit_target@192.168.56.113 [audit_log_test_db]> exit
Bye
[root@misc02 admin]# 

■上記のログがAuditログに記録されませんでした。
検証したところ、USER()を見ているようでした。

注意

current_user()を認識していないのは、Bugとの回答を頂きました。
既に修正済みで、次のメンテナンスリリースでFIXされるとの事でした。
なので、MySQL5.7.14を待ちたいと思います。

FIX後は以下の様に’%’でホストを指定している場合、user()毎に作成する必要は有りません。

また、JSONで定義を作成し有効にした場合に、mysqlクライアントからの接続出来ますが、
workbenchから接続出来なくなる不具合があるようです。Bug Reportを上げたので、5.7.13で利用される場合は制限がある事をご理解下さい。
http://bugs.mysql.com/bug.php?id=81897

上記修正されました。
Audit log filtering against the user was performing comparisons against USER(), not CURRENT_USER(). (Bug #23344762)
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-14.html


audit_target@192.168.56.113 [(none)]> select user(),current_user();
+-----------------------+------------------+
| user()                | current_user()   |
+-----------------------+------------------+
| audit_target@misc02   | audit_target@%   |
+-----------------------+------------------+
1 row in set (0.00 sec)

以下のように対象HOSTを追加しました。
追加したところ、audit_targetのAuditログが記録されている事が確認出来ました。

(例)
SELECT audit_log_filter_set_user(‘audit_target@misc02’, ‘log_all’) AS ‘Result’;


root@localhost [mysql]> select * from audit_log_user;select * from audit_log_filter;
+--------------+----------------+------------+
| USER         | HOST           | FILTERNAME |
+--------------+----------------+------------+
| audit_target | localhost      | log_all    |
| audit_target | %              | log_all    |
| audit_target | 192.168.56.109 | log_all    |
| audit_target | misc02         | log_all    |
+--------------+----------------+------------+
4 rows in set (0.00 sec)

root@localhost [mysql]> 

5) 基本的な機能は確認出来たので、ユーザー、テーブル、DMLの種類によるフィルタリングを確認してみます。
※ 全ての処理はもちろん取得し記録出来るので、特定のオブジェクトに対しての特定の処理のみを確認しています。

ここではユーザーを作成して、confidentialテーブルへのREAD(select)のみをログに記録する設定をしています。
READのみなので、INSERTはここでは取得していません。


root@localhost [mysql]> select * from audit_log_user;select * from audit_log_filter;
+--------------+----------------+------------+
| USER         | HOST           | FILTERNAME |
+--------------+----------------+------------+
| audit_target | localhost      | log_all    |
| audit_target | %              | log_all    |
| audit_target | 192.168.56.109 | log_all    |
| audit_target | misc02         | log_all    |
+--------------+----------------+------------+
4 rows in set (0.00 sec)

root@localhost [mysql]> SELECT audit_log_filter_set_filter('log_confidential', '{ "filter": { "class": { "name": "table_access","event": { "name": "read","log": { "field": { "name": "table_name.str","value": "confidential" } } } } } }');
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| audit_log_filter_set_filter('log_confidential', '{ "filter": { "class": { "name": "table_access","event": { "name": "read","log": { "field": { "name": "table_name.str","value": "confidential" } } } } } }') |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| OK                                                                                                                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

audit_target@localhost [mysql]> 


root@localhost [mysql]> CREATE USER confidential@'%';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> alter user confidential@'%' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> GRANT ALL ON *.* TO 'confidential'@'%';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SELECT audit_log_filter_set_user('confidential@%','log_confidential');
+----------------------------------------------------------------+
| audit_log_filter_set_user('confidential@%','log_confidential') |
+----------------------------------------------------------------+
| OK                                                             |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> SELECT audit_log_filter_set_user('confidential@localhost','log_confidential');
+------------------------------------------------------------------------+
| audit_log_filter_set_user('confidential@localhost','log_confidential') |
+------------------------------------------------------------------------+
| OK                                                                     |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]>  SELECT audit_log_filter_set_user('confidential@192.168.56.109','log_confidential');
+-----------------------------------------------------------------------------+
| audit_log_filter_set_user('confidential@192.168.56.109','log_confidential') |
+-----------------------------------------------------------------------------+
| OK                                                                          |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> SELECT audit_log_filter_set_user('confidential@misc02','log_confidential');
+---------------------------------------------------------------------+
| audit_log_filter_set_user('confidential@misc02','log_confidential') |
+---------------------------------------------------------------------+
| OK                                                                  |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> select * from audit_log_filter;
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| NAME             | FILTER                                                                                                                                            |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| log_all          | {"filter": {"log": true}}                                                                                                                         |
| log_confidential | {"filter": {"class": {"name": "table_access", "event": {"log": {"field": {"name": "table_name.str", "value": "confidential"}}, "name": "read"}}}} |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

root@localhost [mysql]> select * from audit_log_user;
+--------------+----------------+------------------+
| USER         | HOST           | FILTERNAME       |
+--------------+----------------+------------------+
| audit_target | localhost      | log_all          |
| audit_target | %              | log_all          |
| audit_target | 192.168.56.109 | log_all          |
| audit_target | misc02         | log_all          |
| confidential | %              | log_confidential |
| confidential | localhost      | log_confidential |
| confidential | 192.168.56.109 | log_confidential |
| confidential | misc02         | log_confidential |
+--------------+----------------+------------------+
8 rows in set (0.00 sec)

root@localhost [mysql]> 

6) 上記コマンドを実行して、別ホストからアクセスしてログを確認してみました。
Select -> Insert -> Selectの順番でログを確認しています。


confidential@192.168.56.113 [Audit]> select * from confidential;
Empty set (0.00 sec)

confidential@192.168.56.113 [Audit]> insert into confidential(memo) values('秘密の情報');
Query OK, 1 row affected (0.01 sec)

confidential@192.168.56.113 [Audit]> select * from confidential;
+-----------------+
| memo            |
+-----------------+
| 秘密の情報      |
+-----------------+
1 row in set (0.01 sec)

confidential@192.168.56.113 [Audit]> 

audit.logの中身を確認してみると。
きちんと設定したテーブルに対しての参照処理のみ記録されている事が確認出来ました。


 <AUDIT_RECORD>>
  <TIMESTAMP>2016-06-16T03:09:53 UTC</TIMESTAMP>
  <RECORD_ID>87435_2016-06-16T02:35:04</RECORD_ID>
  <NAME>TableRead</NAME>
  <CONNECTION_ID>6</CONNECTION_ID>
  <USER>confidential[confidential] @ misc02 [192.168.56.109]</USER>
  <OS_LOGIN/>
  <HOST>misc02</HOST>
  <IP>192.168.56.109</IP>
  <COMMAND_CLASS>select</COMMAND_CLASS>
  <SQLTEXT>select * from confidential</SQLTEXT>
  <DB>Audit</DB>
  <TABLE>confidential</TABLE>
 </AUDIT_RECORD>
 <AUDIT_RECORD>
  <TIMESTAMP>2016-06-16T03:10:12 UTC</TIMESTAMP>
  <RECORD_ID>87436_2016-06-16T02:35:04</RECORD_ID>
  <NAME>TableRead</NAME>
  <CONNECTION_ID>6</CONNECTION_ID>
  <USER>confidential[confidential] @ misc02 [192.168.56.109]</USER>
  <OS_LOGIN/>
  <HOST>misc02</HOST>
  <IP>192.168.56.109</IP>
  <COMMAND_CLASS>select</COMMAND_CLASS>
  <SQLTEXT>select * from confidential</SQLTEXT>
  <DB>Audit</DB>
  <TABLE>confidential</TABLE>
 </AUDIT_RECORD>

fixed

これまでより、断然監査がし易くなっているので是非検証してみて下さい。
トライアル(試用版)ダウンロード: https://www-jp.mysql.com/trials/

Enterprise Monitorも合わせて検証するとAudit Logの状況がリアルタイムで可視化出来ます。(閾値を超えたら、メールかSNMPで管理者に連絡)
index

indexlog

参考)
https://dev.mysql.com/doc/refman/5.7/en/audit-log-installation.html
http://mysqlserverteam.com/mysql-5-7-new-audit-log-filtering-feature-part-1/


先日、リリースされたMySQL5.7.12に含まれるmysqlxプラグインと、
同時にリリースされた、MySQL Shellの初期設定と基本動作確認を確認してみました。
基本的にこれらは、MySQL5.7で追加されたJSONデータ型を含むスキーマレスなドキュメントデータを、
mysqlxプラグインを利用する事でより柔軟に処理出来るようにする為に追加され、
MySQLをドキュメントデータベースとしてより利用し易くする為に追加された機能となっています。

MySQL Shellは、MySQLサーバの開発と管理をサポートする、
JavaScript、Python、SQLの対話型のインタフェースをサポートするMySQLの新しいコンポーネント。
データのクエリと更新処理だけでなく、様々な管理操作を実行する為にMySQLのシェルを使用することができます。
※MySQL Shellを利用する為には、事前にMySQL5.7.12以降に含まれているXプラグインをインストールしておく必要があります。

Xプラグイン
Xプロトコルを使用して通信を可能にするMySQLサーバプラグイン。
X DevAPIを実装するクライアントをサポートし、ドキュメントストアとしてMySQLを使用することができます。

Xプロトコル
Xプロトコルは、Xプラグインを実行しているMySQLサーバーと通信します。
Xプロトコルは、SSL経由でCRUDとSQL操作、認証の両方をサポートしていて、
コマンドのストリーミング処理を可能にし、プロトコルとメッセージレイヤ上に拡張可能。

root@localhost [information_schema]> select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_DESCRIPTION from plugins
    -> where PLUGIN_NAME = 'mysqlx';
+-------------+----------------+--------------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_DESCRIPTION |
+-------------+----------------+--------------------+
| mysqlx      | 1.0            | X Plugin for MySQL |
+-------------+----------------+--------------------+
1 row in set (0.00 sec)

root@localhost [information_schema]> 

【X Pluginインストール】
参照:http://dev.mysql.com/doc/refman/5.7/en/x-plugin-installation.html

通常通り、INSTALL PLUGINコマンドでインストール

root@localhost [mysql]> INSTALL PLUGIN mysqlx SONAME 'mysqlx.so';
Query OK, 0 rows affected (0.25 sec)

root@localhost [mysql]> show plugins;
+----------------------------+----------+--------------------+-------------------+-------------+
| Name                       | Status   | Type               | Library           | License     |
+----------------------------+----------+--------------------+-------------------+-------------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL              | PROPRIETARY |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL              | PROPRIETARY |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL              | PROPRIETARY |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL              | PROPRIETARY |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL              | PROPRIETARY |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL              | PROPRIETARY |
<SNIP>
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL              | PROPRIETARY |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| ngram                      | ACTIVE   | FTPARSER           | NULL              | PROPRIETARY |
| mecab                      | ACTIVE   | FTPARSER           | libpluginmecab.so | PROPRIETARY |
| MYSQL_FIREWALL             | ACTIVE   | AUDIT              | firewall.so       | PROPRIETARY |
| MYSQL_FIREWALL_USERS       | ACTIVE   | INFORMATION SCHEMA | firewall.so       | PROPRIETARY |
| MYSQL_FIREWALL_WHITELIST   | ACTIVE   | INFORMATION SCHEMA | firewall.so       | PROPRIETARY |
| mysqlx                     | ACTIVE   | DAEMON             | mysqlx.so         | PROPRIETARY |
+----------------------------+----------+--------------------+-------------------+-------------+
49 rows in set (0.00 sec)

root@localhost [mysql]> 

【X Plugin オプションと変数について】
設定パラメータに関しては、此方を参照下さい。
http://dev.mysql.com/doc/refman/5.7/en/x-plugin-option-variable-reference.html

【接続方法】
既に、mysqlshをインストール済みなので –sqlオプションを仕様してSQLを流し込んでみる。
mysqlxのPort33060(Default:mysqlx_port)から、ローカルのMySQLにアクセスしてSQLを実行しています。

【サンプルデータベース作成】
参照:
http://downloads.mysql.com/docs/world_x-db.zip
http://dev.mysql.com/doc/refman/5.7/en/mysql-shell-tutorial-javascript-download.html

mysqlsh経由でサンプルデータベースの作成して、mysqlクライアントで接続してスキーマとテーブルの確認

[root@misc01 MID2016]# mysqlsh -u admin -p --sql --recreate-schema world_x < world_x.sql
Enter password: 
Recreating schema world_x...

[root@misc01 MID2016]# mysql -u root -p -e "show databases like 'world%'"
Enter password: 
+-------------------+
| Database (world%) |
+-------------------+
| world             |
| world2            |
| world_x           |
+-------------------+
[root@misc01 MID2016]# mysql -u root -p -e "show tables from world_x"
Enter password: 
+-------------------+
| Tables_in_world_x |
+-------------------+
| City              |
| Country           |
| CountryInfo       |
| CountryLanguage   |
+-------------------+

【接続モード】
参考) 接続方法としては、現状では、以下のモードを選択できるようです。

# mysqlsh --help
MySQL Shell 1.0.3 Development Preview

<SNIP>

  --sql                    Start in SQL mode using a node session.
  --sqlc                   Start in SQL mode using a classic session.
  --js                     Start in JavaScript mode.
  --py                     Start in Python mode.

<SNIP>

こちらでは、mysqlsh経由でjava scriptモードの状態でJSONデータを処理してみます。


[root@misc01 MID2016]# mysqlsh -u admin -p 
Creating an X Session to admin@localhost:33060
Enter password: 
No default schema selected.

Welcome to MySQL Shell 1.0.3 Development Preview

Copyright (c) 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', '\h' or '\?' for help.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> news_collection = session.getSchema('NEW57').createCollection("X_JSON");
<Collection:X_JSON>
mysql-js> news_collection.add({"id": 1, "name": "Document Data", "price": 60000, "Conditions": ["NEW", 2016]});
Query OK, 1 item affected (0.01 sec)

mysql-js> news_collection.find('name like :n').bind('n','Document%');
[
    {
        "Conditions": [
            "NEW",
            2016
        ],
        "_id": "26c8af795703e611630c0800279cea3c",
        "id": 1,
        "name": "Document Data",
        "price": 60000
    }
]
1 document in set (0.03 sec)

mysql-js>  

MySQLに接続してデータを確認した状態


root@localhost [NEW57]> desc X_JSON;
+-------+-------------+------+-----+---------+------------------+
| Field | Type        | Null | Key | Default | Extra            |
+-------+-------------+------+-----+---------+------------------+
| doc   | json        | YES  |     | NULL    |                  |
| _id   | varchar(32) | NO   | PRI | NULL    | STORED GENERATED |
+-------+-------------+------+-----+---------+------------------+
2 rows in set (0.00 sec)

root@localhost [NEW57]> select * from X_JSON\G
*************************** 1. row ***************************
doc: {"id": 1, "_id": "26c8af795703e611630c0800279cea3c", "name": "Document Data", "price": 60000, "Conditions": ["NEW", 2016]}
_id: 26c8af795703e611630c0800279cea3c
1 row in set (0.00 sec)

root@localhost [NEW57]> 

【Beta Draft】
X DevAPI User Guide / Overview
http://dev.mysql.com/doc/x-devapi-userguide/en/devapi-users-introduction.html
その他、X DevAPI経由でコーディング出来そう。但し、現時点ではベータリリースなのでもう少ししたら確認。

【その他:Status変数】
こちらで、mysqlx経由の処理を確認する事が可能です。


[root@misc01 MID2016]# mysql -u root -p -e "show status like 'mysqlx%'"
Enter password: 
+-------------------------------------+--------------------------+
| Variable_name                       | Value                    |
+-------------------------------------+--------------------------+
| Mysqlx_bytes_received               | 389773                   |
| Mysqlx_bytes_sent                   | 3466                     |
| Mysqlx_connection_accept_errors     | 0                        |
| Mysqlx_connection_errors            | 0                        |
| Mysqlx_connections_accepted         | 2                        |
| Mysqlx_connections_closed           | 2                        |
| Mysqlx_connections_rejected         | 0                        |
| Mysqlx_crud_delete                  | 0                        |
| Mysqlx_crud_find                    | 0                        |
| Mysqlx_crud_insert                  | 0                        |
| Mysqlx_crud_update                  | 0                        |
| Mysqlx_errors_sent                  | 1                        |
| Mysqlx_expect_close                 | 0                        |
| Mysqlx_expect_open                  | 0                        |
| Mysqlx_init_error                   | 1                        |
| Mysqlx_notice_other_sent            | 76                       |
| Mysqlx_notice_warning_sent          | 2                        |
| Mysqlx_rows_sent                    | 18                       |
| Mysqlx_sessions                     | 0                        |
| Mysqlx_sessions_accepted            | 1                        |
| Mysqlx_sessions_closed              | 1                        |
| Mysqlx_sessions_fatal_error         | 0                        |
| Mysqlx_sessions_killed              | 0                        |
| Mysqlx_sessions_rejected            | 1                        |
| Mysqlx_ssl_accepts                  | 0                        |
| Mysqlx_ssl_active                   |                          |
| Mysqlx_ssl_cipher                   |                          |
| Mysqlx_ssl_cipher_list              |                          |
| Mysqlx_ssl_ctx_verify_depth         | 18446744073709551615     |
| Mysqlx_ssl_ctx_verify_mode          | 5                        |
| Mysqlx_ssl_finished_accepts         | 0                        |
| Mysqlx_ssl_server_not_after         | Oct 19 05:28:15 2025 GMT |
| Mysqlx_ssl_server_not_before        | Oct 22 05:28:15 2015 GMT |
| Mysqlx_ssl_verify_depth             |                          |
| Mysqlx_ssl_verify_mode              |                          |
| Mysqlx_ssl_version                  |                          |
| Mysqlx_stmt_create_collection       | 0                        |
| Mysqlx_stmt_create_collection_index | 0                        |
| Mysqlx_stmt_disable_notices         | 0                        |
| Mysqlx_stmt_drop_collection         | 0                        |
| Mysqlx_stmt_drop_collection_index   | 0                        |
| Mysqlx_stmt_enable_notices          | 0                        |
| Mysqlx_stmt_execute_sql             | 69                       |
| Mysqlx_stmt_execute_xplugin         | 2                        |
| Mysqlx_stmt_kill_client             | 0                        |
| Mysqlx_stmt_list_clients            | 0                        |
| Mysqlx_stmt_list_notices            | 0                        |
| Mysqlx_stmt_list_objects            | 2                        |
| Mysqlx_stmt_ping                    | 0                        |
| Mysqlx_worker_threads               | 2                        |
| Mysqlx_worker_threads_active        | 0                        |
+-------------------------------------+--------------------------+
[root@misc01 MID2016]# 

参考までに、MySQL ShellのSQLモードの場合とjavascriptモードの場合では接続が3306か33060からの接続かどうかは以下のステータス変数でも確認する事が可能です。

【SQLモードの場合】


[root@misc01 MID2016]# echo "INSERT INTO T_UC01 VALUES (1,'mysql shell'),(2,'マイエスキュウエルシェル');" | mysqlsh -u demo_user -ppassword --sql --schema=world_x2
mysqlx: [Warning] Using a password on the command line interface can be insecure.
[root@misc01 MID2016]# 


root@localhost [sys]> show status like 'Mysqlx_crud%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mysqlx_crud_delete | 0     |
| Mysqlx_crud_find   | 0     |
| Mysqlx_crud_insert | 0     |
| Mysqlx_crud_update | 0     |
+--------------------+-------+
4 rows in set (0.00 sec)

root@localhost [sys]> root@localhost [sys]> show status like 'Mysqlx_crud%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mysqlx_crud_delete | 0     |
| Mysqlx_crud_find   | 0     |
| Mysqlx_crud_insert | 0     |
| Mysqlx_crud_update | 0     |
+--------------------+-------+
4 rows in set (0.00 sec)

root@localhost [sys]> 


【JavaScriptモードの場合】


[root@misc01 MID2016]# mysqlsh --uri demo_user@localhost/NEW57 -ppassword
mysqlx: [Warning] Using a password on the command line interface can be insecure.
Creating an X Session to demo_user@localhost:33060/NEW57
Default schema `NEW57` accessible through db.

Welcome to MySQL Shell 1.0.3 Development Preview

Copyright (c) 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', '\h' or '\?' for help.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> db.createCollection("x_posts");
<Collection:x_posts>
mysql-js> db.x_posts.add({"title":"Hello World", "text":"This is the first post via mysqlx"});
Query OK, 1 item affected (0.01 sec)

mysql-js> db.x_posts.find("title = 'Hello World'").sort(["title"]);
[
    {
        "_id": "d2c6eb188b08e6113d110800279cea3c",
        "text": "This is the first post via mysqlx",
        "title": "Hello World"
    }
]
1 document in set (0.00 sec)

mysql-js> 

mysqlx経由のCRUDの実行数が確認出来る。
INSERT x 1回
SELECT x 1回


root@localhost [sys]> show status like 'Mysqlx_crud%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mysqlx_crud_delete | 0     |
| Mysqlx_crud_find   | 0     |
| Mysqlx_crud_insert | 0     |
| Mysqlx_crud_update | 0     |
+--------------------+-------+
4 rows in set (0.00 sec)

root@localhost [sys]> show status like 'Mysqlx_crud%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mysqlx_crud_delete | 0     |
| Mysqlx_crud_find   | 1     |
| Mysqlx_crud_insert | 1     |
| Mysqlx_crud_update | 0     |
+--------------------+-------+
4 rows in set (0.01 sec)

root@localhost [sys]> 


【参考】
http://dev.mysql.com/doc/refman/5.7/en/mysql-shell.html
http://dev.mysql.com/doc/refman/5.7/en/document-store.html
http://dev.mysql.com/doc/dev/connector-nodejs/
http://mysqlserverteam.com/mysql-5-7-12-part-3-more-than-just-sql/
http://mysqlserverteam.com/mysql-5-7-12-part-4-a-new-mysql-command-line-shell/

メモ:Node.jsのサンプルもあるので、後日、別途JSONドキュメント処理を実施してみる。
http://dev.mysql.com/doc/dev/connector-nodejs/


昨年からMySQLチームがオフィシャルレポジトリーをDockerに公開し、
最近少しずつ利用している方も増えてきて、
開発環境などでは簡単にDBが準備出来て工数削減も出来、
大活躍出来る仕組みなので簡単にレビューしておきます。

[Dockerレポジトリ]
https://hub.docker.com/_/mysql/ (Docker Team)
https://hub.docker.com/r/mysql/mysql-server/ (MySQL team at Oracle)

Tagに関しては、此方を確認下さい。(指定しない場合は、最新のイメージがダウンロードされます)
https://hub.docker.com/r/mysql/mysql-server/tags/

レポジトリーにはDockerのオフィシャルレポジトリーとMySQLチームが公開しているオフィシャルレポジトリーがあります。
イメージをPULLすると以下のような感じになります。

既に、docker pull mysql:5.7.10コマンドでDockerオフィシャルレポジトリからイメージをダウンロード済みの状態です。
追加で、MySQL@OracleのオフィシャルレポジトリーからDockerのイメージをダウンロードしてみます。
サイズだけ見ると、MySQL@Oracleのオフィシャルレポジトリーからダウンロードしたイメージの方がサイズは小さいです。


[root@DockerHost docker]# docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             VIRTUAL SIZE
mysql               5.7.10              ea0aca21950d        3 weeks ago         360.3 MB
[root@DockerHost docker]# docker pull mysql/mysql-server:5.7.10
5.7.10: Pulling from mysql/mysql-server
f359075ce4d8: Pull complete 
df9c05f5df07: Pull complete 
bea04efc3319: Pull complete 
14204cc431b4: Pull complete 
249d5b7b31c1: Pull complete 
70a368114c59: Pull complete 
c5b90de0636a: Pull complete 
59e355dc43f6: Pull complete 
f03e996fb5a3: Pull complete 
69497ffa3023: Pull complete 
e472f1765697: Pull complete 
Digest: sha256:c9266386460cfb302d21615bbe8f6a16166b2376eb371095df7cc9a1c9cf6da1
Status: Downloaded newer image for mysql/mysql-server:5.7.10
[root@DockerHost docker]# docker images
REPOSITORY           TAG                 IMAGE ID            CREATED             VIRTUAL SIZE
mysql                5.7.10              ea0aca21950d        3 weeks ago         360.3 MB
mysql/mysql-server   5.7.10              e472f1765697        4 weeks ago         294.6 MB
[root@DockerHost docker]# 

Dockerオフィシャルイメージを利用してMySQLを構築してみます。
インスタンス作成時に、データディレクトリーを指定、オプションファイルを指定、インスタンス作成と同時に
データベース作成する為に以下のスクリプトを配置してあります。ソースを確認すると*.sqlか*.shを配置しておくと、
DB作成時に読み込んでSQL文を実行してくれます。(初期共通アカウントやDB作成に使えます)

run


[root@DockerHost docker]# ls -l /docker/init_script/sakila-db/init-docker-sakila.sql 
-rw-r--r-- 1 root root 3421501 Jan  8 01:01 /docker/init_script/sakila-db/init-docker-sakila.sql
[root@DockerHost docker]# 


[root@DockerHost docker]# docker ps -a 
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS               NAMES
[root@DockerHost docker]# docker run --name my_docker01 -v /docker/docker01:/var/lib/mysql -v /docker/option1:/etc/mysql/conf.d -v /docker/init_script/sakila-db:/docker-entrypoint-initdb.d -e MYSQL_ROOT_PASSWORD=mysql -d mysql:5.7.10
5152a22b2aa0c724bd260552205ebf566a21b9d854a54921f02a96e40817a930
[root@DockerHost docker]# docker ps -a 
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES
5152a22b2aa0        mysql:5.7.10        "/entrypoint.sh mysql"   11 seconds ago      Up 9 seconds        3306/tcp            my_docker01
[root@DockerHost docker]# 

Docker Inspectコマンドでパスのマッピングを確認


[root@DockerHost docker]# docker inspect my_docker01 | egrep 'Source|Destination'
            "Source": "/docker/docker01",
            "Destination": "/var/lib/mysql",
            "Source": "/docker/option1",
            "Destination": "/etc/mysql/conf.d",
            "Source": "/docker/init_script/sakila-db",
            "Destination": "/docker-entrypoint-initdb.d",
[root@DockerHost docker]# 

作成したインスタンスに接続して、初期設定状態を確認してみます。
確認: インスタンス接続、オプションファイルの読み込み、初期DBの作成状態。

[root@DockerHost docker]# docker exec -it my_docker01 mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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.

root@localhost [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

root@localhost [(none)]> show tables from sakila;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
23 rows in set (0.07 sec)

root@localhost [(none)]> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.04 sec)

root@localhost [(none)]> 

次に、MySQL@Oracleチームがオフィシャルに提供しているオフィシャルイメージを利用してインスタンスを作成してみます。
※こちらのイメージを利用する場合は、オプションファイルの指定方法が少し異なります。(/etc/my.cnf)


[root@DockerHost docker]# docker ps -a 
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES
5152a22b2aa0        mysql:5.7.10        "/entrypoint.sh mysql"   53 minutes ago      Up 53 minutes       3306/tcp            my_docker01
[root@DockerHost docker]# docker run --name my_docker02 -v /docker/docker02:/var/lib/mysql -v /docker/init_script/sakila-db:/docker-entrypoint-initdb.d -e MYSQL_ROOT_PASSWORD=mysql -d mysql/mysql-server:5.7.10
f59d1fd222d7b15ff0f0dfa3879254730734e17451fde56aeb402279f355941e
[root@DockerHost docker]# docker ps -a 
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS              PORTS               NAMES
f59d1fd222d7        mysql/mysql-server:5.7.10   "/entrypoint.sh mysql"   2 seconds ago       Up 1 seconds        3306/tcp            my_docker02
5152a22b2aa0        mysql:5.7.10                "/entrypoint.sh mysql"   53 minutes ago      Up 53 minutes       3306/tcp            my_docker01
[root@DockerHost docker]# 

作成したインスタンスとDBの確認。(こちらは、OptionファイルはしていないのでDefaultです)


[root@DockerHost docker]# docker exec -it my_docker02 mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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 |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
+--------------------+
5 rows in set (0.02 sec)

mysql> show tables from sakila;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
23 rows in set (0.00 sec)

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

mysql> 

イメージは停止しても、削除(rm)しなければ継続利用出来るので以下のようにサービスを停止して再開しても、
作成したデータなどは無くならず利用する事が可能です。


[root@DockerHost docker]# docker ps -a 
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS              PORTS               NAMES
f59d1fd222d7        mysql/mysql-server:5.7.10   "/entrypoint.sh mysql"   10 minutes ago      Up 10 minutes       3306/tcp            my_docker02
5152a22b2aa0        mysql:5.7.10                "/entrypoint.sh mysql"   About an hour ago   Up About an hour    3306/tcp            my_docker01
[root@DockerHost docker]# docker stop f59d1fd222d7
f59d1fd222d7
[root@DockerHost docker]# docker stop 5152a22b2aa0
5152a22b2aa0
[root@DockerHost docker]# docker ps -a 
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                      PORTS               NAMES
f59d1fd222d7        mysql/mysql-server:5.7.10   "/entrypoint.sh mysql"   11 minutes ago      Exited (0) 13 seconds ago                       my_docker02
5152a22b2aa0        mysql:5.7.10                "/entrypoint.sh mysql"   About an hour ago   Exited (0) 3 seconds ago                        my_docker01
[root@DockerHost docker]# docker start f59d1fd222d7
f59d1fd222d7
[root@DockerHost docker]# docker ps -a 
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                      PORTS               NAMES
f59d1fd222d7        mysql/mysql-server:5.7.10   "/entrypoint.sh mysql"   11 minutes ago      Up 2 seconds                3306/tcp            my_docker02
5152a22b2aa0        mysql:5.7.10                "/entrypoint.sh mysql"   About an hour ago   Exited (0) 20 seconds ago                       my_docker01
[root@DockerHost docker]# docker exec -it my_docker02 mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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 |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> 

補足: MySql@oracleチームのオフィシャルイメージの場合にオプションファイルを指定する場合は、以下のように実行します。


[root@DockerHost docker]# docker run --name my_docker02 -v /docker/docker02:/var/lib/mysql -v /docker/option2/my.cnf:/etc/my.cnf -v /docker/init_script/sakila-db:/docker-entrypoint-initdb.d -e MYSQL_ROOT_PASSWORD=mysql -d mysql/mysql-server:5.7.10
4fde03dc4cb5966bbfee79de92c2351f2b04812df77bf763b0e14755c3d51261
[root@DockerHost docker]# docker ps -a 
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                      PORTS               NAMES
4fde03dc4cb5        mysql/mysql-server:5.7.10   "/entrypoint.sh mysql"   22 seconds ago      Up 21 seconds               3306/tcp            my_docker02
5152a22b2aa0        mysql:5.7.10                "/entrypoint.sh mysql"   About an hour ago   Exited (0) 24 minutes ago                       my_docker01
[root@DockerHost docker]# 

[root@DockerHost docker]# docker inspect my_docker02 | egrep 'Source|Destination'
            "Source": "/docker/docker02",
            "Destination": "/var/lib/mysql",
            "Source": "/docker/option2/my.cnf",
            "Destination": "/etc/my.cnf",
            "Source": "/docker/init_script/sakila-db",
            "Destination": "/docker-entrypoint-initdb.d",
[root@DockerHost docker]# 

参考:
https://hub.docker.com/r/mysql/mysql-server/
http://datacharmer.blogspot.jp/2015/10/mysql-docker-operations-part-1-getting.html


NoSQL with MySQL Cluster
本日、話をさせて頂いた、MySQL Clusterにおけるトランザクション対応NoSQLについての資料です。

MySQL ClusterはMySQLとは基本的には異なるデータベースです。
NDB(Network Database)に対して、MySQLからもNoSQLからもデータ処理出来るデータベースがMySQL Clusterです。
MySQLからはもともとストレージエンジンを選択出来るデータベースシステムですが、
MySQLはndbclusterというストレージエンジンとしてNDBを利用しています。

NDBはNDB API (C++)を経由してアクセスする事で,MySQLから独立して利用する事が可能です。
MySQLサーバの観点からは,NDB Clusterは行のテーブルを格納するためのストレージエンジンです。
NDB Clusterの観点からは、MySQLサーバインスタンスがクラスタに接続されているAPIのプロセスの一つです。

概要図
ndb_diag

Storage Engine
storage_engines

ndbinfo
データは、冗長化と拡張性の為にデータノード間で分散されています。
その状況は、ndbinfoを確認する事でも確認可能です。

ndbinfo

検証
先ずは、MySQL Clusterをダウンロードして頂き、MySQL Clsuterを設定して下さい。
設定が終了したら、MySQL ClusterにSQLで接続しスキーマとテスト用のテーブルを作成して下さい。
NDB APIからNoSQLでもNDBにコマンドでテーブルを作成出来ますが、その場合はMySQLからオブジェクトを見る事が出来ません。
MySQLとNoSQL両方から使いたい場合は、MySQLにて先ずはオブジェクト作成して下さい。

Download MySQL Cluster
https://dev.mysql.com/downloads/cluster/

18.2.2. Linux での MySQL Cluster のインストール
https://dev.mysql.com/doc/refman/5.6/ja/mysql-cluster-install-linux.html

5分で作るMySQL Cluster環境
http://www.ospn.jp/osc2013-kyoto/pdf/osc2013kyoto_mysql2.pdf


mysql> use TEST_DB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create table employee\G
*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `first` varchar(64) DEFAULT NULL,
  `last` varchar(64) DEFAULT NULL,
  `municipality` varchar(64) DEFAULT NULL,
  `started` varchar(64) DEFAULT NULL,
  `ended` varchar(64) DEFAULT NULL,
  `department` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_u_hash` (`first`,`last`) USING HASH,
  KEY `idx_municipality` (`municipality`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> 

ClusterJ サンプルソース
MySQLClusterJ_Sample

MySQL Clusterへの接続文字列は適宜変更して下さい。
— com.mysql.clusterj.connectstring
— com.mysql.clusterj.database=TEST_DB

[SELL]

[root@Labs01 java]# cat clusterj.properties
# Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

com.mysql.clusterj.connectstring=192.168.56.114:1186
com.mysql.clusterj.database=TEST_DB
com.mysql.clusterj.connect.retries=4
com.mysql.clusterj.connect.delay=5
com.mysql.clusterj.connect.verbose=1
com.mysql.clusterj.connect.timeout.before=30
com.mysql.clusterj.connect.timeout.after=20
com.mysql.clusterj.max.transactions=1024
[root@Labs01 java]#

[/SHELL]

コンパイル
※ javacにてコンパイル時には、clusterj-api-7.x.x.jarを含める必要があります。
※ パスは適宜書き換えて下さい。


javac -classpath /mysql-cluster-gpl-7.4.6/746bin/share/java/clusterj-api-7.4.6.jar:. Main.java Employee.java

実行例


java -classpath /mysql-cluster-gpl-7.4.6/746bin/share/java/clusterj-7.4.6.jar:. -Djava.library.path=/mysql-cluster-gpl-7.4.6/746bin/lib Main

上記の実行内容は、先にスキーマをSQLから作成してあるのでSQLでも見る事が出来ます。

del_in

それ以外の方法としては、NDB用にMySQL Clusterに用意されている以下のコマンドを利用すると良いでしょう。

ndb_show_tables

ndb_show_tables

ndb_select_all

ndb_select_all

SQLが得意な処理は、SQLで処理して、Primary Keyベースでの特定のデータに対しての処理などは、
MySQL ClusterでのNoSQLで処理するという方法も選択出来ます。
NDB APIはC++ですので、C++が得意な方はそのままC++で書いて高速な処理を検証してみるのも良いかと思います。
※ネットワークは重要です、トラフィックが多い場合は10Gなどの高速なネットワークをご利用下さい。


MySQL Clusterの良いところの一つに、Rolling Upgradeが可能なので、
サービスを停止せずに、MySQL Clusterのアップグレードが可能な部分があると思います。
やはり、アップグレードにサービス停止を伴うと、インフラ管理者もサービス担当者と
メンテナンス時間の調整をしなければならず、なかなか対応がが難しいと思います。

アップグレードには、MCM(MySQL Cluster Manager)というCommercial版のツールを使えば、
コマンド一つでアップグレード可能ですが、今回はマニュアルで一つ一つアップグレードしています。

MySQL Cluster Managerは、基本的な管理ダスクを自動化することによって、
MySQL データベースの作成と管理を簡易化します。
MCM参照:
https://www-jp.mysql.com/products/cluster/mcm/
その他:
How MySQL is able to scale to 200 Million QPS – MySQL Cluster
http://highscalability.com/blog/2015/5/18/how-mysql-is-able-to-scale-to-200-million-qps-mysql-cluster.html

■ 現状の構成確認
本番環境ではお勧めしませんが、デモ環境なので2台のサーバー上に共存して構成しています。

SQL Node x 2
Data Node x 2
Management Node x 1

+------+   +------+
| SQL1 |   | SQL2 |
+------+   +------+
+------+   +------+
| NDB1 |   | NDB2 |
+------+   +------+

※この検証環境では、管理ノードはSQL1, NDB1と同居してます。

before_one_node

1) アップグレード前の事前動作確認

mysql> select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| cluster74_01 |
+--------------+
1 row in set (0.00 sec)

mysql> select * from T20150319;
+----+---------------------------------------------+
| id | memo                                        |
+----+---------------------------------------------+
|  3 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  6 | SQL Connection 2015-03-24                   |
|  5 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  1 | This is MySQL Cluster 7.4.4 検証            |
|  2 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  4 | This is MySQL Cluster 7.4.4 検証 Restored   |
+----+---------------------------------------------+
6 rows in set (0.01 sec)

mysql> insert into T20150319(memo) values('rolling upgrade cluster step by step');
Query OK, 1 row affected (0.01 sec)

mysql> select * from T20150319 order by id;
+----+---------------------------------------------+
| id | memo                                        |
+----+---------------------------------------------+
|  1 | This is MySQL Cluster 7.4.4 検証            |
|  2 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  3 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  4 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  5 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  6 | SQL Connection 2015-03-24                   |
|  7 | rolling upgrade cluster step by step        |
+----+---------------------------------------------+
7 rows in set (0.00 sec)

mysql> explain select * from T20150319 where id = 1;
+----+-------------+-----------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table     | type   | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-----------+--------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | T20150319 | eq_ref | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-----------+--------+---------------+---------+---------+-------+------+-------+
1 row in set (0.01 sec)

mysql> explain select * from T20150319 where memo like 'This is MySQL%';
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra                             |
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------------+
|  1 | SIMPLE      | T20150319 | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where with pushed condition |
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------------+
1 row in set (0.00 sec)

mysql> 

2) Active/Active型のClusterなので、当然データは他のノードからも参照出来ます。

mysql> select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| cluster74_02 |
+--------------+
1 row in set (0.00 sec)

mysql> select * from T20150319 order by id;
+----+---------------------------------------------+
| id | memo                                        |
+----+---------------------------------------------+
|  1 | This is MySQL Cluster 7.4.4 検証            |
|  2 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  3 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  4 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  5 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  6 | SQL Connection 2015-03-24                   |
|  7 | rolling upgrade cluster step by step        |
+----+---------------------------------------------+
7 rows in set (0.00 sec)

mysql>

■ Rolling Upgradeの為、1/2のデータノードを停止しました。

[root@cluster74_01 cluster]# /usr/local/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 192.168.56.114:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2    @192.168.56.115  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)
id=51   @192.168.56.115  (mysql-5.6.23 ndb-7.4.4)

ndb_mgm> 2 stop
Node 2: Node shutdown initiated
Node 2: Node shutdown completed.
Node 2 has shutdown.

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2 (not connected, accepting connect from 192.168.56.115)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)
id=51   @192.168.56.115  (mysql-5.6.23 ndb-7.4.4)

—- Node 2で、SQLインスタンスも停止しました。

問題無く、アップグレード対象ノードが停止している事を確認。

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2 (not connected, accepting connect from 192.168.56.115)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)
id=51 (not connected, accepting connect from 192.168.56.115)

ndb_mgm> 

■1/2ノードにてバイナリーダウンロードと入れ替え

[root@cluster74_02 local]# tar zxvf /home/admin/mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64.tar.gz 

Snip

mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64/include/plugin_audit.h
mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64/include/mysql_com.h
mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64/include/my_dir.h
[root@cluster74_02 local]# ls -l
合計 8
drwxr-xr-x.  2 root root     6  6月 10  2014 bin
drwxr-xr-x.  2 root root     6  6月 10  2014 etc
drwxr-xr-x.  2 root root     6  6月 10  2014 games
drwxr-xr-x.  2 root root     6  6月 10  2014 include
drwxr-xr-x.  2 root root     6  6月 10  2014 lib
drwxr-xr-x.  2 root root     6  6月 10  2014 lib64
drwxr-xr-x.  2 root root     6  6月 10  2014 libexec
lrwxrwxrwx.  1 root root    51  3月 17 12:28 mysql -> mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64/
drwxr-xr-x. 15 root mysql 4096  3月 20 18:13 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64
drwxr-xr-x. 13 root root  4096  5月 14 00:21 mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64
drwxr-xr-x.  2 root root     6  6月 10  2014 sbin
drwxr-xr-x.  5 root root    46  3月 17 11:29 share
drwxr-xr-x.  2 root root    70  3月 17 12:28 src
[root@cluster74_02 local]# 

[root@cluster74_02 local]# rm mysql
rm: シンボリックリンク `mysql' を削除しますか? y
[root@cluster74_02 local]# ln -s mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64/ mysql
[root@cluster74_02 local]# ls -l
合計 8
drwxr-xr-x.  2 root root     6  6月 10  2014 bin
drwxr-xr-x.  2 root root     6  6月 10  2014 etc
drwxr-xr-x.  2 root root     6  6月 10  2014 games
drwxr-xr-x.  2 root root     6  6月 10  2014 include
drwxr-xr-x.  2 root root     6  6月 10  2014 lib
drwxr-xr-x.  2 root root     6  6月 10  2014 lib64
drwxr-xr-x.  2 root root     6  6月 10  2014 libexec
lrwxrwxrwx.  1 root root    51  5月 14 00:23 mysql -> mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64/
drwxr-xr-x. 15 root mysql 4096  3月 20 18:13 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64
drwxr-xr-x. 13 root root  4096  5月 14 00:21 mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64
drwxr-xr-x.  2 root root     6  6月 10  2014 sbin
drwxr-xr-x.  5 root root    46  3月 17 11:29 share
drwxr-xr-x.  2 root root    70  3月 17 12:28 src
[root@cluster74_02 local]# chown -R mysql:mysql mysql/
[root@cluster74_02 local]# chmod -R 755 mysql/
[root@cluster74_02 local]# ls -l
合計 8
drwxr-xr-x.  2 root  root     6  6月 10  2014 bin
drwxr-xr-x.  2 root  root     6  6月 10  2014 etc
drwxr-xr-x.  2 root  root     6  6月 10  2014 games
drwxr-xr-x.  2 root  root     6  6月 10  2014 include
drwxr-xr-x.  2 root  root     6  6月 10  2014 lib
drwxr-xr-x.  2 root  root     6  6月 10  2014 lib64
drwxr-xr-x.  2 root  root     6  6月 10  2014 libexec
lrwxrwxrwx.  1 root  root    51  5月 14 00:23 mysql -> mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64/
drwxr-xr-x. 15 root  mysql 4096  3月 20 18:13 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64
drwxr-xr-x. 13 mysql mysql 4096  5月 14 00:21 mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64
drwxr-xr-x.  2 root  root     6  6月 10  2014 sbin
drwxr-xr-x.  5 root  root    46  3月 17 11:29 share
drwxr-xr-x.  2 root  root    70  3月 17 12:28 src
[root@cluster74_02 local]# 

■旧バージョンからファイルのコピー
– DATAフォルダー
– MYSQL Cluster用フォルダー
– BACKUPフォルダー


[root@cluster74_02 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64]# cp -rp mysql-cluster/ /usr/local/mysql/
[root@cluster74_02 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64]# ls -l /usr/local/mysql/
合計 140
-rwxr-xr-x.  1 mysql mysql 102986  4月  2 02:09 INSTALL-BINARY
-rwxr-xr-x.  1 mysql mysql   2730  4月  2 02:09 LICENSE.mysql
-rwxr-xr-x.  1 mysql mysql   1446  4月  2 02:09 README
drwxr-xr-x.  2 mysql mysql   4096  5月 14 00:21 bin
drwxr-xr-x.  3 mysql mysql     17  5月 14 00:21 data
drwxr-xr-x.  2 mysql mysql     69  5月 14 00:21 docs
drwxr-xr-x.  4 mysql mysql   4096  5月 14 00:21 include
drwxr-xr-x.  3 mysql mysql   4096  5月 14 00:21 lib
drwxr-xr-x.  4 mysql mysql     28  5月 14 00:21 man
drwxr-xr-x.  2 root  root      27  3月 18 10:31 mysql-cluster
drwxr-xr-x. 10 mysql mysql   4096  5月 14 00:21 mysql-test
drwxr-xr-x.  2 mysql mysql     29  5月 14 00:21 scripts
drwxr-xr-x. 32 mysql mysql   4096  5月 14 00:21 share
drwxr-xr-x.  4 mysql mysql   4096  5月 14 00:21 sql-bench
drwxr-xr-x.  2 mysql mysql   4096  5月 14 00:21 support-files
[root@cluster74_02 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64]# 


[root@cluster74_02 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64]# cp -rp data/ /usr/local/mysql/
cp: `/usr/local/mysql/data/test/db.opt' を上書きしますか? y
[root@cluster74_02 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64]# ls -l /usr/local/mysql/data/
合計 111192
drwx------. 2 mysql mysql       59  3月 24 16:53 NDB01
-rw-rw----. 1 mysql mysql       56  3月 18 09:46 auto.cnf
-rw-r-----. 1 mysql root    156430  5月 13 22:23 cluster74_02.err
-rw-rw----. 1 mysql mysql 50331648  5月 13 22:23 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648  3月 18 09:45 ib_logfile1
-rw-rw----. 1 mysql mysql 12582912  5月 13 22:23 ibdata1
drwx------. 2 mysql mysql     4096  3月 20 22:13 mysql
-rw-r--r--. 1 root  root         4  5月 13 21:55 ndb_2.pid
drwxr-x---. 9 root  root        72  3月 19 23:26 ndb_2_fs
-rw-r--r--. 1 root  root    426545  5月 13 22:14 ndb_2_out.log
drwx------. 2 mysql mysql     4096  3月 20 22:13 ndbinfo
drwx------. 2 mysql mysql     4096  3月 19 23:45 ndbmemcache
drwx------. 2 mysql mysql     4096  3月 18 09:45 performance_schema
drwxr-xr-x. 2 mysql mysql       19  3月 17 12:26 test
[root@cluster74_02 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64]# 


[root@cluster74_02 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64]# cp -rp BACKUP/ /usr/local/mysql/
[root@cluster74_02 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64]# ls -l /usr/local/mysql/
合計 144
drwxr-x---.  3 root  root      19  3月 20 18:21 BACKUP
-rwxr-xr-x.  1 mysql mysql 102986  4月  2 02:09 INSTALL-BINARY
-rwxr-xr-x.  1 mysql mysql   2730  4月  2 02:09 LICENSE.mysql
-rwxr-xr-x.  1 mysql mysql   1446  4月  2 02:09 README
drwxr-xr-x.  2 mysql mysql   4096  5月 14 00:21 bin
drwxr-xr-x.  9 mysql mysql   4096  5月 13 22:23 data
drwxr-xr-x.  2 mysql mysql     69  5月 14 00:21 docs
drwxr-xr-x.  4 mysql mysql   4096  5月 14 00:21 include
drwxr-xr-x.  3 mysql mysql   4096  5月 14 00:21 lib
drwxr-xr-x.  4 mysql mysql     28  5月 14 00:21 man
drwxr-xr-x.  2 root  root      27  3月 18 10:31 mysql-cluster
drwxr-xr-x. 10 mysql mysql   4096  5月 14 00:21 mysql-test
drwxr-xr-x.  2 mysql mysql     29  5月 14 00:21 scripts
drwxr-xr-x. 32 mysql mysql   4096  5月 14 00:21 share
drwxr-xr-x.  4 mysql mysql   4096  5月 14 00:21 sql-bench
drwxr-xr-x.  2 mysql mysql   4096  5月 14 00:21 support-files
[root@cluster74_02 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64]#

■データノードとSQLノードを起動


[root@cluster74_02 mysql]# chown -R root .
[root@cluster74_02 mysql]# chown -R mysql data
[root@cluster74_02 mysql]# /usr/local/mysql/bin/ndbd --connect-string=192.168.56.114 --nostart
2015-05-14 00:34:31 [ndbd] INFO     -- Angel connected to '192.168.56.114:1186'
2015-05-14 00:34:32 [ndbd] INFO     -- Angel allocated nodeid: 2
[root@cluster74_02 mysql]# /usr/local/mysql/bin/mysqld_safe &
[1] 3801
[root@cluster74_02 mysql]# 150514 00:34:50 mysqld_safe Logging to '/usr/local/mysql/data/cluster74_02.err'.
150514 00:34:50 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@cluster74_02 mysql]# 



[root@cluster74_02 mysql]# ./bin/mysql_upgrade -u root -p
Enter password: 
Looking for 'mysql' as: ./bin/mysql
Looking for 'mysqlcheck' as: ./bin/mysqlcheck
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_apply_status                             OK
mysql.ndb_binlog_index                             OK
mysql.ndb_index_stat_head                          OK
mysql.ndb_index_stat_sample                        OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
NDB01.T20150319                                    OK
ndbinfo.blocks                                     OK
ndbinfo.config_params                              OK
ndbinfo.dict_obj_types                             OK
ndbmemcache.cache_policies                         OK
ndbmemcache.containers                             OK
ndbmemcache.demo_table                             OK
ndbmemcache.demo_table_large                       OK
ndbmemcache.demo_table_tabs                        OK
ndbmemcache.external_values                        OK
ndbmemcache.key_prefixes                           OK
ndbmemcache.last_memcached_signon                  OK
ndbmemcache.memcache_server_roles                  OK
ndbmemcache.meta                                   OK
ndbmemcache.ndb_clusters                           OK
OK
[root@cluster74_02 mysql]# 

After_one_node

■MySQL Cluster管理ノードにて確認してみる
– バージョンが混在している状態


ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2    @192.168.56.115  (mysql-5.6.24 ndb-7.4.6, not started)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)
id=51   @192.168.56.115  (mysql-5.6.24 ndb-7.4.6)

ndb_mgm> ndb_mgm> 2 start 
Node 2: Start initiated (version 7.4.6)
Database node 2 is being started.

ndb_mgm> Node 2: Started (version 7.4.6)

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2    @192.168.56.115  (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)
id=51   @192.168.56.115  (mysql-5.6.24 ndb-7.4.6)

ndb_mgm> 

Version
mysql

Data操作
insert_diff_version

■ アップグレード中
管理ノードをarbitratorに設定しているので、こちらは特に変更が無い事を確認。


mysql> select * from arbitrator_validity_detail;
+---------+------------+------------------+---------------+-----------+
| node_id | arbitrator | arb_ticket       | arb_connected | arb_state |
+---------+------------+------------------+---------------+-----------+
|       1 |        100 | 08320002001a414c | Yes           | ARBIT_RUN |
|       2 |        100 | 08320002001a414c | Yes           | ARBIT_RUN |
+---------+------------+------------------+---------------+-----------+
2 rows in set (0.01 sec)

mysql> select * from  arbitrator_validity_summary ;
+------------+------------------+---------------+-----------------+
| arbitrator | arb_ticket       | arb_connected | consensus_count |
+------------+------------------+---------------+-----------------+
|        100 | 08320002001a414c | Yes           |               2 |
+------------+------------------+---------------+-----------------+
1 row in set (0.01 sec)

mysql> 

■ 2/2のノードをアップグレード開始
– 対象ノードをSTOPコマンドで停止


ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2    @192.168.56.115  (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)
id=51   @192.168.56.115  (mysql-5.6.24 ndb-7.4.6)

ndb_mgm> 1 stop
Node 1: Node shutdown initiated
Node 1 has shutdown.

ndb_mgm> Node 1: Node shutdown completed.

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1 (not connected, accepting connect from 192.168.56.114)
id=2    @192.168.56.115  (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0, *)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)
id=51   @192.168.56.115  (mysql-5.6.24 ndb-7.4.6)

ndb_mgm> 100 stop
Node 100 has shutdown.
Disconnecting to allow Management Server to shutdown

ndb_mgm> 

arbitorator_shutdown1

その他の作業は、最初のノードと同様の手順でバイナリーの入れ替え
コマンドで管理ノード、データノード、SQLノードを起動します。


[root@cluster74_01 mysql]# /usr/local/mysql/bin/ndb_mgmd -f /usr/local/mysql/mysql-cluster/ndb-config.ini
MySQL Cluster Management Server mysql-5.6.24 ndb-7.4.6
[root@cluster74_01 mysql]# /usr/local/mysql/bin/ndbd --connect-string=192.168.56.114 --nostart
2015-05-14 02:19:06 [ndbd] INFO     -- Angel connected to '192.168.56.114:1186'
2015-05-14 02:19:06 [ndbd] INFO     -- Angel allocated nodeid: 1
[root@cluster74_01 mysql]# /usr/local/mysql/bin/mysqld_safe &
[1] 5066
[root@cluster74_01 mysql]# 150514 02:19:17 mysqld_safe Logging to '/usr/local/mysql/data/cluster74_01.err'.
150514 02:19:17 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@cluster74_01 mysql]# 

全ノードのMySQLのバイナリーが同じバージョンになっている事を確認
– STOPしているノードをSTARTコマンドで開始


[root@cluster74_01 mysql]# /usr/local/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 192.168.56.114:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.24 ndb-7.4.6, not started)
id=2    @192.168.56.115  (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.24 ndb-7.4.6)

[mysqld(API)]   2 node(s)
id=50 (not connected, accepting connect from 192.168.56.114)
id=51   @192.168.56.115  (mysql-5.6.24 ndb-7.4.6)

ndb_mgm> 1 start
Node 1: Start initiated (version 7.4.6)
Database node 1 is being started.

ndb_mgm> Node 1: Started (version 7.4.6)

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0)
id=2    @192.168.56.115  (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0, *)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.24 ndb-7.4.6)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.24 ndb-7.4.6)
id=51   @192.168.56.115  (mysql-5.6.24 ndb-7.4.6)

ndb_mgm> 

after_upgrade_fin


[root@cluster74_01 mysql]# ./bin/mysql_upgrade -u root -p
Enter password: 
Looking for 'mysql' as: ./bin/mysql
Looking for 'mysqlcheck' as: ./bin/mysqlcheck
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_apply_status                             OK
mysql.ndb_binlog_index                             OK
mysql.ndb_index_stat_head                          OK
mysql.ndb_index_stat_sample                        OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
NDB01.T20150319                                    OK
ndbinfo.blocks                                     OK
ndbinfo.config_params                              OK
ndbinfo.dict_obj_types                             OK
ndbmemcache.cache_policies                         OK
ndbmemcache.containers                             OK
ndbmemcache.demo_table                             OK
ndbmemcache.demo_table_large                       OK
ndbmemcache.demo_table_tabs                        OK
ndbmemcache.external_values                        OK
ndbmemcache.key_prefixes                           OK
ndbmemcache.last_memcached_signon                  OK
ndbmemcache.memcache_server_roles                  OK
ndbmemcache.meta                                   OK
ndbmemcache.ndb_clusters                           OK
OK
[root@cluster74_01 mysql]# 

アップグレード最終確認


ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0)
id=2    @192.168.56.115  (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0, *)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.24 ndb-7.4.6)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.24 ndb-7.4.6)
id=51   @192.168.56.115  (mysql-5.6.24 ndb-7.4.6)

ndb_mgm> 

基本動作確認し、ログを見てエラーが無い事を確認して終了
confirmSQLfinal

メモ: 本番では、arbitratorの設定をもう少し明確に設定しておく。

参照: 18.5.5 Performing a Rolling Restart of a MySQL Cluster


どのようなシステムにも完全が無いように、
Webサイトのセキュリティに気を付けてコーディングしたり、セキュリティ診断しながら運用しても、
どこかしら運用している中でセキュリティ対策漏れが出て来てしまう事が往々にしてあります。
また、それらのセキュリティ問題は、社内システムに関しても同様です。

個人情報を管理しているデータベースや企業秘密情報を管理するデータベースに関しては、
IPSやFW機能などのアプライアンスなどでカバーしてくれる製品もありますが、
非常に高価なものが多いです。本日、検証し共有させて頂くMySQL Enterprise Firewall機能に関しては、
White List方法(2015年4月現在)を用いたデータベース側で不正アクセスをブロックする
MySQLデータベースのセキュリティ対策追加モジュールです。
もし、ご興味を持たれたら、是非 Oracle Software Delivery Cloud から、
30日間無償で検証出来るトライアルを使って検証してみて頂ければと思います。

動作確認MySQL Version

admin@192.168.56.201 [(none)]> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.6.24-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

インストールとPluginの状況確認

[admin@GA01 ~]$ mysql -u root -p mysql < /usr/local/mysql/share/linux_install_firewall.sql 
Enter password: 
[admin@GA01 ~]$ cat /usr/local/mysql/share/linux_install_firewall.sql
# Copyright (c) 2015 Oracle and/or its affiliates. All rights reserved.
# Install firewall tables
USE mysql;
CREATE TABLE IF NOT EXISTS mysql.firewall_whitelist( USERHOST VARCHAR(80) NOT NULL, RULE text Nne= MyISAM;
CREATE TABLE IF NOT EXISTS mysql.firewall_users( USERHOST VARCHAR(80) PRIMARY KEY, MODE ENUM ('ING', 'PROTECTING', 'RESET') DEFAULT 'OFF') engine= MyISAM;

INSTALL PLUGIN mysql_firewall SONAME 'firewall.so';
INSTALL PLUGIN mysql_firewall_whitelist SONAME 'firewall.so';
INSTALL PLUGIN mysql_firewall_users SONAME 'firewall.so';

CREATE FUNCTION set_firewall_mode RETURNS STRING SONAME 'firewall.so';
CREATE FUNCTION normalize_statement RETURNS STRING SONAME 'firewall.so';
CREATE AGGREGATE FUNCTION read_firewall_whitelist RETURNS STRING SONAME 'firewall.so';
CREATE AGGREGATE FUNCTION read_firewall_users RETURNS STRING SONAME 'firewall.so';
delimiter //
CREATE PROCEDURE sp_set_firewall_mode (IN arg_userhost VARCHAR(80), IN arg_mode varchar(12))
BEGIN
IF arg_mode = "RECORDING" THEN
  SELECT read_firewall_whitelist(arg_userhost,FW.rule) FROM mysql.firewall_whitelist FW WHERE Fg_userhost;
END IF;
SELECT set_firewall_mode(arg_userhost, arg_mode);
if arg_mode = "RESET" THEN
  SET arg_mode = "OFF";
END IF;
INSERT IGNORE INTO mysql.firewall_users VALUES (arg_userhost, arg_mode);
UPDATE mysql.firewall_users SET mode=arg_mode WHERE userhost = arg_userhost;

IF arg_mode = "PROTECTING" OR arg_mode = "OFF" THEN
  DELETE FROM mysql.firewall_whitelist WHERE USERHOST = arg_userhost;
  INSERT INTO mysql.firewall_whitelist SELECT USERHOST,RULE FROM INFORMATION_SCHEMA.mysql_firew WHERE USERHOST=arg_userhost;
END IF;
END //
delimiter ;


[admin@GA01 ~]$ 


root@GA01 [(none)]> select PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_LIBRARY,PLUGIN_LICENSE,LOAD_OPTION
    -> from information_schema.plugins where PLUGIN_NAME like '%FIREWALL%';
+--------------------------+---------------+----------------+----------------+-------------+
| PLUGIN_NAME              | PLUGIN_STATUS | PLUGIN_LIBRARY | PLUGIN_LICENSE | LOAD_OPTION |
+--------------------------+---------------+----------------+----------------+-------------+
| MYSQL_FIREWALL           | ACTIVE        | firewall.so    | PROPRIETARY    | ON          |
| MYSQL_FIREWALL_WHITELIST | ACTIVE        | firewall.so    | PROPRIETARY    | ON          |
| MYSQL_FIREWALL_USERS     | ACTIVE        | firewall.so    | PROPRIETARY    | ON          |
+--------------------------+---------------+----------------+----------------+-------------+
3 rows in set (0.00 sec)

root@GA01 [(none)]> 

Install
install

Plugins
option

検証用ユーザー作成

root@GA01 [(none)]> GRANT ALL PRIVILEGES  ON test.* TO fw_user@localhost IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

root@GA01 [(none)]> 


root@GA01 [mysql]> select user,host,password from user;
+---------+-----------+-------------------------------------------+
| user    | host      | password                                  |
+---------+-----------+-------------------------------------------+
| root    | localhost | *B51ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 |
| root    | 127.0.0.1 | *B51ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 |
| admin   | %         | *B51ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 |
| fw_user | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+---------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

root@GA01 [mysql]> 

Firewall基本設定オプション
sp
詳細: 5.14.4.2 MySQL Enterprise Firewall Procedures and Functions

自動学習機能を有効にして、ステートメントをWhite Listに記録します


--- First register this account with the Firewall. 
--- You do this by calling the stored proceedure we created earlier:

root@GA01 [mysql]> CALL sp_set_firewall_mode('fw_user@localhost','RECORDING');
+-----------------------------------------------+
| read_firewall_whitelist(arg_userhost,FW.rule) |
+-----------------------------------------------+
| Imported users: 0   Imported rules: 0         |
+-----------------------------------------------+
1 row in set (0.01 sec)

+-------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode) |
+-------------------------------------------+
| OK                                        |
+-------------------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

root@GA01 [mysql]> 

※ CALL sp_set_firewall_mode(‘ユーザー名’,’RECORDING’)は、運用開始後にWhite Listに値を追加する時にも使用します。

記録対象ユーザー(検証用ユーザー)でアクセスし、SQLステートメントを実際に登録してみます。

[admin@GA01 ~]$ mysql -u fw_user -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.24-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, 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.

fw_user@localhost [(none)]> use test
Database changed

fw_user@localhost [test]> CREATE TABLE FW_DEMO
    -> (
    -> ID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> title VARCHAR(100)
    -> ) Engine=InnoDB CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.00 sec)

fw_user@localhost [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| FW_DEMO        |
+----------------+
1 row in set (0.00 sec)

fw_user@localhost [test]> 

fw_user@localhost [test]> insert into FW_DEMO(title) values(concat('test firewall',@@version));
Query OK, 1 row affected (0.00 sec)

fw_user@localhost [test]> insert into FW_DEMO(title) values(concat('test firewall2',@@version));
Query OK, 1 row affected (0.00 sec)

fw_user@localhost [test]> insert into FW_DEMO(title) values(concat('test firewall3',@@version));
Query OK, 1 row affected (0.00 sec)

fw_user@localhost [test]> select * from FW_DEMO where id = 1;
+----+--------------------------------------------------------+
| ID | title                                                  |
+----+--------------------------------------------------------+
|  1 | test firewall5.6.24-enterprise-commercial-advanced-log |
+----+--------------------------------------------------------+
1 row in set (0.00 sec)

fw_user@localhost [test]> select * from FW_DEMO where id = 2;
+----+---------------------------------------------------------+
| ID | title                                                   |
+----+---------------------------------------------------------+
|  2 | test firewall25.6.24-enterprise-commercial-advanced-log |
+----+---------------------------------------------------------+
1 row in set (0.00 sec)

fw_user@localhost [test]> 

学習が終了したので、White Listを有効にして、リストにあるステートメントのみを許可します。

root@GA01 [mysql]> CALL sp_set_firewall_mode('fw_user@localhost','PROTECTING');
+-------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode) |
+-------------------------------------------+
| OK                                        |
+-------------------------------------------+
1 row in set (0.00 sec)

Query OK, 10 rows affected (0.00 sec)

root@GA01 [mysql]> 

fw_on

登録された、White Listの確認。

root@GA01 [mysql]> SELECT userhost, substr(rule,1,80) FROM mysql.firewall_whitelist WHERE userhost= 'fw_user@localhost';
+-------------------+----------------------------------------------------------------------------------+
| userhost          | substr(rule,1,80)                                                                |
+-------------------+----------------------------------------------------------------------------------+
| fw_user@localhost | SELECT SCHEMA ( )                                                                |
| fw_user@localhost | SHOW TABLES                                                                      |
| fw_user@localhost | INSERT INTO `FW_DEMO` ( `title` ) VALUES ( `concat` ( ? , @@version ) )          |
| fw_user@localhost | DESC `FW_DEMO`                                                                   |
| fw_user@localhost | SELECT * FROM `FW_DEMO` WHERE `id` = ?                                           |
| fw_user@localhost | CREATE TABLE `FW_DEMO` ( `ID` INTEGER UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY K |
| fw_user@localhost | SHOW SCHEMAS                                                                     |
| fw_user@localhost | SELECT SYSTEM_USER ( )                                                           |
| fw_user@localhost | SELECT @@version_comment LIMIT ?                                                 |
| fw_user@localhost | SELECT * FROM SYSTEM_USER                                                        |
+-------------------+----------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

root@GA01 [mysql]> SELECT * FROM information_schema.mysql_firewall_users;
+-------------------+------------+
| USERHOST          | MODE       |
+-------------------+------------+
| fw_user@localhost | PROTECTING |
+-------------------+------------+
1 row in set (0.00 sec)

root@GA01 [mysql]> 

rule_status

実際に対象ユーザー(fw_user@localhost)でアクセスしてみて、データベースが防御されているか確認。


fw_user@localhost [test]> select * from FW_DEMO where id = 1;
+----+--------------------------------------------------------+
| ID | title                                                  |
+----+--------------------------------------------------------+
|  1 | test firewall5.6.24-enterprise-commercial-advanced-log |
+----+--------------------------------------------------------+
1 row in set (0.00 sec)

fw_user@localhost [test]> select * from FW_DEMO where id = 2;
+----+---------------------------------------------------------+
| ID | title                                                   |
+----+---------------------------------------------------------+
|  2 | test firewall25.6.24-enterprise-commercial-advanced-log |
+----+---------------------------------------------------------+
1 row in set (0.00 sec)

fw_user@localhost [test]> select * from FW_DEMO where id = 3;
+----+---------------------------------------------------------+
| ID | title                                                   |
+----+---------------------------------------------------------+
|  3 | test firewall35.6.24-enterprise-commercial-advanced-log |
+----+---------------------------------------------------------+
1 row in set (0.00 sec)

fw_user@localhost [test]> select * from FW_DEMO;
ERROR 1045 (28000): Statement was blocked by Firewall
fw_user@localhost [test]> 


root@GA01 [mysql]> SHOW STATUS LIKE 'Firewall%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Firewall_access_denied  | 10    |
| Firewall_access_granted | 6     |
| Firewall_cached_entries | 10    |
+-------------------------+-------+
3 rows in set (0.00 sec)

root@GA01 [mysql]> 

※ 上記を確認頂くと、テーブル全体のデータを持っていこうとする処理はブロックされています。

protected

SQLのエラーログにも記録されている事が確認出来ます。


[root@GA01 data]# tail -n 2 error.log 
2015-04-13 22:40:21 3321 [Note] Plugin MYSQL_FIREWALL reported: 'ACCESS DENIED for fw_user@localhost. Reason: No match in whitelist. Statement: SELECT * FROM `FW_DEMO` '
2015-04-13 22:41:25 3321 [Note] Plugin MYSQL_FIREWALL reported: 'ACCESS DENIED for fw_user@localhost. Reason: No match in whitelist. Statement: SELECT * FROM `FW_DEMO` '
[root@GA01 data]# 

White Listの設定をOFFにしたい場合は、以下のコマンドでリセットする事が可能です。

root@GA01 [mysql]> CALL sp_set_firewall_mode('fw_user@localhost','RESET');
+-------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode) |
+-------------------------------------------+
| OK                                        |
+-------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

root@GA01 [mysql]> SELECT * FROM information_schema.mysql_firewall_users;
+-------------------+------+
| USERHOST          | MODE |
+-------------------+------+
| fw_user@localhost | OFF  |
+-------------------+------+
1 row in set (0.00 sec)

root@GA01 [mysql]> 

リセット結果は、全部のデータをSELECTしてもブロックされて無い事で確認出来ます。
after_reset

補足:

 CALL sp_set_firewall_mode('fw_user@localhost','OFF'); 

でもWhite ListをOFFにしてSQLステートメントをブロックされないようにする事が可能です。

関連テーブルとProcedure
基本的には、以下のテーブルを利用してモジュールをコントロールしています。
今後、更にMySQL5.7との連携や機能拡張が行われて行く事になるかと思います。


root@GA01 [information_schema]> select TABLE_SCHEMA,TABLE_NAME from information_schema.tables
    -> where TABLE_NAME like '%fire%';
+--------------------+--------------------------+
| TABLE_SCHEMA       | TABLE_NAME               |
+--------------------+--------------------------+
| information_schema | MYSQL_FIREWALL_WHITELIST |
| information_schema | MYSQL_FIREWALL_USERS     |
| mysql              | firewall_users           |
| mysql              | firewall_whitelist       |
+--------------------+--------------------------+
4 rows in set (0.01 sec)

root@GA01 [information_schema]> 



root@GA01 [mysql]> show create procedure sp_set_firewall_mode\G
*************************** 1. row ***************************
           Procedure: sp_set_firewall_mode
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_set_firewall_mode`(IN arg_userhost VARCHAR(80), IN arg_mode varchar(12))
BEGIN
IF arg_mode = "RECORDING" THEN
  SELECT read_firewall_whitelist(arg_userhost,FW.rule) FROM mysql.firewall_whitelist FW WHERE FW.userhost=arg_userhost;
END IF;
SELECT set_firewall_mode(arg_userhost, arg_mode);
if arg_mode = "RESET" THEN
  SET arg_mode = "OFF";
END IF;
INSERT IGNORE INTO mysql.firewall_users VALUES (arg_userhost, arg_mode);
UPDATE mysql.firewall_users SET mode=arg_mode WHERE userhost = arg_userhost;

IF arg_mode = "PROTECTING" OR arg_mode = "OFF" THEN
  DELETE FROM mysql.firewall_whitelist WHERE USERHOST = arg_userhost;
  INSERT INTO mysql.firewall_whitelist SELECT USERHOST,RULE FROM INFORMATION_SCHEMA.mysql_firewall_whitelist WHERE USERHOST=arg_userhost;
END IF;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

root@GA01 [mysql]> 


Case Sensitve
大切なデータを守る為には、コーディングルールを決める必要がありますね。

fw_user@localhost [test]> select * from FW_DEMO where ID = 1;
+----+--------------------------------------------------------+
| ID | title                                                  |
+----+--------------------------------------------------------+
|  1 | test firewall5.6.24-enterprise-commercial-advanced-log |
+----+--------------------------------------------------------+
1 row in set (0.00 sec)

fw_user@localhost [test]> select * from FW_DEMO where id = 1;
ERROR 1045 (28000): Statement was blocked by Firewall
fw_user@localhost [test]> 

case_sensitive

OverHead (抜粋)
Firewall takes those digests and compares them against an in-memory hash.
Matching a query against a whitelist of course adds a little extra processing
and our preliminary testing has shown under concurrent stress level loads
only a 2-3% performance impact added by running the firewall.

参照:
New MySQL Enterprise Firewall – Prevent SQL Injection Attacks

5.14.3 Using MySQL Enterprise Firewall

5.14.4.2 MySQL Enterprise Firewall Procedures and Functions
5.14.4.3 MySQL Enterprise Firewall System Variables