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

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

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

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


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

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

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

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

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

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

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

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

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


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

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

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

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

mysql-js> 

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


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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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


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

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

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

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

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

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


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

[logger]
level = INFO

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

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

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

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

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

-bash-4.2$ 

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


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

mysql> 

■ MySQL Routerを起動します。

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


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

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


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

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

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

-bash-4.2$ 

Comments are closed.

Post Navigation