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版なので、仕様は変わると思います。
ダウンロード
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/