MySQL InnoDB Clusterさわってみたい

こないだセミナーも行ったし、DBプロキシについて調査しといて―という依頼もあったりしたんで、MySQL InnoDB Cluster環境を作ってみようと考えた。

MySQL InnoDB Clusterとは

普通にサーバ立てて、構築する場合はここ(SmartStyle社のブログ)を参考に作ればいいと思うけど、手元でパフォーマンスを気にせず、技術的なことだけ知りたい場合はやっぱりDockerが便利。

幸いにも中の人がGitHub上に上げてくれてるんで、サクッとできるかな？と思いそれで作って試すことにした。 (これが意外と地獄だった・・・)

MySQL InnoDB Clusterを使うと、マルチマスターにもできるけど、今回はシングルマスター・残り2台はセカンダリにしてある。

環境・構成

名前	バージョン	備考
OS	CentOS Linux release 7.6.1810 (Core)
Docker	Docker version 18.09.0, build 4d60db4
Docker Compose	docker-compose version 1.23.2, build 1110ad01
MySQL	8.0.13	Docker内。3台構成
MySQL Shell	8.0.13	Docker内と外にも
MySQL Router	8.0.13	Docker内。

１．MySQL8.0.13の起動(docker-compose)

https://github.com/neumayer/mysql-docker-compose-examples.git からgit cloneするか、 https://github.com/neumayer/mysql-docker-compose-examples/archive/master.zip をダウンロードして適当なところに展開する。

docker-compose.yml (一部)

version: '3'
services:
  mysql-server-1:
    env_file:
      - mysql-server.env
    image: mysql/mysql-server:8.0.13
    ports:
      - "3301:3306"
    command: ["mysqld","--server_id=1","--binlog_checksum=NONE","--gtid_mode=ON","--enforce_gtid_consistency=ON","--log_bin","--log_slave_updates=ON","--master_info_repository=TABLE","--relay_log_info_repository=TABLE","--transaction_write_set_extraction=XXHASH64","--user=mysql","--skip-host-cache","--skip-name-resolve", "--default_authentication_plugin=mysql_native_password"]
  mysql-server-2:
    env_file:
      - mysql-server.env
    image: mysql/mysql-server:8.0.13
    command: ["mysqld","--server_id=2","--binlog_checksum=NONE","--gtid_mode=ON","--enforce_gtid_consistency=ON","--log_bin","--log_slave_updates=ON","--master_info_repository=TABLE","--relay_log_info_repository=TABLE","--transaction_write_set_extraction=XXHASH64","--user=mysql","--skip-host-cache","--skip-name-resolve", "--default_authentication_plugin=mysql_native_password"]
    ports:
      - "3302:3306"
  mysql-server-3:
    env_file:
      - mysql-server.env
    image: mysql/mysql-server:8.0.13
    command: ["mysqld","--server_id=3","--binlog_checksum=NONE","--gtid_mode=ON","--enforce_gtid_consistency=ON","--log_bin","--log_slave_updates=ON","--master_info_repository=TABLE","--relay_log_info_repository=TABLE","--transaction_write_set_extraction=XXHASH64","--user=mysql","--skip-host-cache","--skip-name-resolve", "--default_authentication_plugin=mysql_native_password"]
    ports:
      - "3303:3306"

パラメータについては環境によって編集したり、my.cnfを作って読み込ませても良いと思います。 (とりあえず試すならこのファイルについては編集不要です)

起動！

$ docker-compose -f docker-compose.yml up -d mysql-server-1 mysql-server-2 mysql-server-3
Pulling mysql-server-1 (mysql/mysql-server:8.0.13)...
~~(中略)~~
Creating innodb-cluster_mysql-server-3_1 ... done
Creating innodb-cluster_mysql-server-2_1 ... done
Creating innodb-cluster_mysql-server-1_1 ... done

(docker-composeコマンドについて、sudo外してますが必要な人は入れてください)

起動に時間がかかるので待ち

$ docker-compose -f docker-compose.yml ps
             Name                            Command                       State                         Ports
----------------------------------------------------------------------------------------------------------------------------
innodb-cluster_mysql-server-1_1   /entrypoint.sh mysqld --se ...   Up (health: starting)   0.0.0.0:3301->3306/tcp, 33060/tcp
innodb-cluster_mysql-server-2_1   /entrypoint.sh mysqld --se ...   Up (health: starting)   0.0.0.0:3302->3306/tcp, 33060/tcp
innodb-cluster_mysql-server-3_1   /entrypoint.sh mysqld --se ...   Up (health: starting)   0.0.0.0:3303->3306/tcp, 33060/tcp

StateがUp (healthy) になったらOK

$ docker-compose -f docker-compose.yml ps
             Name                            Command                  State                     Ports
-------------------------------------------------------------------------------------------------------------------
innodb-cluster_mysql-server-1_1   /entrypoint.sh mysqld --se ...   Up (healthy)   0.0.0.0:3301->3306/tcp, 33060/tcp
innodb-cluster_mysql-server-2_1   /entrypoint.sh mysqld --se ...   Up (healthy)   0.0.0.0:3302->3306/tcp, 33060/tcp
innodb-cluster_mysql-server-3_1   /entrypoint.sh mysqld --se ...   Up (healthy)   0.0.0.0:3303->3306/tcp, 33060/tcp

２．MySQL Shellによるクラスタ登録

最初は、先述のGitHub上にあるdocker-compose.ymlに記載されているMySQL Shellを使おうと思っていたが、数点めんどくさい罠があった。

docker-composeのdepends_onが起動完了を待つのではなく、単なる順番のため、MySQLが起動し切る前にシェルが動いて失敗。
MySQL Shellによるクラスタ作成が失敗すると全て最初からやり直し。

前者はMySQL ShellのあとのMySQL Routerの起動でも同様で、かつMySQL Routerの起動時にクラスタ登録が失敗しているとそれまた最初からやり直しという罠があった。そのため「docker-compose -f docker-compose.yml up -d」の時に依存対象が起動・正常終了したことを確認した上で、起動対象を指定して起動という手順で進めている。

２－１．mysql-shellの起動(docker-compose)

docker-compose.ymlに記載されているMySQL Shellをそのまま使う。注意点は２つ。 - MySQL が起動した後に起動する - MySQL Shellが実行するスクリプトが正常に完了したかログで確認する

docker-compose.yml (一部)

(このファイルについては編集不要です)

  mysql-shell:
    env_file:
      - mysql-shell.env
    image: neumayer/mysql-shell-batch
    volumes:
        - ./scripts/:/scripts/
    depends_on:
      - mysql-server-1
      - mysql-server-2
      - mysql-server-3

mysql-shell.env

MYSQL_USER=root
MYSQL_HOST=mysql-server-1
MYSQL_PORT=3306
MYSQL_PASSWORD=xxxxxx
MYSQLSH_SCRIPT=/scripts/setupCluster.js
# MYSQL_SCRIPT=/scripts/db.sql

ちなみに、mysql-shell.env内部で呼ばれているMYSQL_SCRIPT=/scripts/db.sqlは不要なのでコメントアウトもしくは削除していいです。

setupCluster.js

(このファイルについては編集不要です)

var dbPass = "xxxxxxx"
var clusterName = "devCluster"

try {
  print('Setting up InnoDB cluster...\n');
  shell.connect('root@mysql-server-1:3306', dbPass)
  var cluster = dba.createCluster(clusterName);
  print('Adding instances to the cluster.');
  cluster.addInstance({user: "root", host: "mysql-server-2", password: dbPass})
  print('.');
  cluster.addInstance({user: "root", host: "mysql-server-3", password: dbPass})
  print('.\nInstances successfully added to the cluster.');
  print('\nInnoDB cluster deployed successfully.\n');
} catch(e) {
  print('\nThe InnoDB cluster could not be created.\n\nError: ' + e.message + '\n');
}

dockerコンテナ同士はdocker-compose.ymlに記載したヘッダをホスト名とみなしますので、それを使ってshell.connectやcluster.addInstanceの部分で設定が行われます。 (後述のdocker外から行うような場合は、IPアドレスで指定したほうが良いかと)

起動

$ docker-compose -f docker-compose.yml up -d mysql-shell

起動ログ確認

$ docker-compose -f docker-compose.yml logs | grep shell
Attaching to innodb-cluster_mysql-shell_1, innodb-cluster_mysql-server-2_1, innodb-cluster_mysql-server-1_1, innodb-cluster_mysql-server-3_1[0] 1:bash- 2:docker-compose* 3:bash
mysql-shell_1      | mysqlx: [Warning] Using a password on the command line interface can be insecure.
mysql-shell_1      | Setting up InnoDB cluster...
mysql-shell_1      | Adding instances to the cluster...
mysql-shell_1      | Instances successfully added to the cluster.
mysql-shell_1      | InnoDB cluster deployed successfully.

もし

The InnoDB cluster could not be created.

とか

Error:

という文字が表示されていた場合はクラスタ登録失敗しているのでクラスタ登録をやり直しなのだが、その場合は「２－２．MySQL Shellのインストール」をした後設定を変更するか、起動したコンテナを全て停止＆削除してやり直し。(この作業のdocker-compose.ymlに無いものは対象外)

まあ、後者のほうがぶっちゃけ早い。

２－２．MySQL Shellのインストール＆クラスタ設定

docker内ではなく、ローカルにMySQL Shellをインストールしてそこから手動でクラスタ登録する場合の手順は以下の通り。 (２－１．の手順で登録した人はスルーしていいですが、後日クラスタの設定を変更する際などに使うのでそういうことをする人はどこかしらの環境でMySQL Shellを別途インストールすることになります)

以下のコマンドを実行

$ sudo yum install -y mysql-shell

読み込んだプラグイン:fastestmirror, langpacks
Determining fastest mirrors
 * base: ftp.riken.jp
 * extras: ftp.riken.jp
 * updates: ftp.riken.jp
base                                                                                                                                                                                                                  | 3.6 kB  00:00:00
docker-ce-stable                                                                                                                                                                                                      | 3.5 kB  00:00:00
extras                                                                                                                                                                                                                | 3.4 kB  00:00:00
mysql-connectors-community                                                                                                                                                                                            | 2.5 kB  00:00:00
mysql-tools-community                                                                                                                                                                                                 | 2.5 kB  00:00:00
mysql56-community                                                                                                                                                                                                     | 2.5 kB  00:00:00
updates                                                                                                                                                                                                               | 3.4 kB  00:00:00
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ mysql-shell.x86_64 0:8.0.13-1.el7 を インストール
--> 依存性解決を終了しました。

依存性を解決しました

=============================================================================================================================================================================================================================================
 Package                                                 アーキテクチャー                                   バージョン                                               リポジトリー                                                       容量
=============================================================================================================================================================================================================================================
インストール中:
 mysql-shell                                             x86_64                                             8.0.13-1.el7                                             mysql-tools-community                                             6.1 M

トランザクションの要約
=============================================================================================================================================================================================================================================
インストール  1 パッケージ

総ダウンロード容量: 6.1 M
インストール容量: 25 M
Downloading packages:
mysql-shell-8.0.13-1.el7.x86_64.rpm                                                                                                                                                                                   | 6.1 MB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  インストール中          : mysql-shell-8.0.13-1.el7.x86_64                                                                                                                                                                              1/1
  検証中                  : mysql-shell-8.0.13-1.el7.x86_64                                                                                                                                                                              1/1

インストール:
  mysql-shell.x86_64 0:8.0.13-1.el7

完了しました!

MySQL Shellのインストール終わり

クラスタの設定

各DBコンテナ内に入ってIPアドレスを確認しておく

$ sudo docker exec -it innodb-cluster_mysql-server-1_1 bash
bash-4.2# cat /etc/hosts
127.0.0.1       localhost
::1     localhost ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
172.23.0.3      1897ffaf93e4

$ sudo docker exec -it innodb-cluster_mysql-server-2_1 bash
bash-4.2# cat /etc/hosts
127.0.0.1       localhost
::1     localhost ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
172.23.0.2      c1c79efae23a

$ sudo docker exec -it innodb-cluster_mysql-server-3_1 bash
bash-4.2# cat /etc/hosts
127.0.0.1       localhost
::1     localhost ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
172.23.0.4      e09882973930

MySQL Shellを起動して、クラスタの登録を行う

 MySQL  JS > var dbPass = "xxxxxx"
 MySQL  JS > var clusterName = "devCluster"
 MySQL  JS > shell.connect('root@172.23.0.3:3306', dbPass)
Creating a session to 'root@172.23.0.3:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 29
Server version: 8.0.13 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
<ClassicSession:root@172.23.0.3:3306>

 MySQL  172.23.0.3:3306 ssl  JS > var cluster = dba.createCluster(clusterName);
A new InnoDB cluster will be created on instance 'root@172.23.0.3:3306'.

Validating instance at 172.23.0.3:3306...

This instance reports its own address as 1897ffaf93e4

Instance configuration is suitable.
Creating InnoDB cluster 'devCluster' on 'root@172.23.0.3:3306'...
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  172.23.0.3:3306 ssl  JS > cluster.addInstance({user: "root", host: "172.23.0.2", password: dbPass})
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.

Adding instance to the cluster ...

Validating instance at 172.23.0.2:3306...

This instance reports its own address as c1c79efae23a

Instance configuration is suitable.
The instance 'root@172.23.0.2' was successfully added to the cluster.


 MySQL  172.23.0.3:3306 ssl  JS > cluster.addInstance({user: "root", host: "172.23.0.4", password: dbPass})
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.

Adding instance to the cluster ...

Validating instance at 172.23.0.4:3306...

This instance reports its own address as e09882973930

Instance configuration is suitable.
The instance 'root@172.23.0.4' was successfully added to the cluster.

MySQL Routerの起動

やっと本命。

docker-compose.yml (一部)

depends_onの「- mysql-shell」をコメントアウトもしくは削除すべし

理由としては

mysql-shellが起動している＝まだクラスタ登録完了してない＝MySQL Router失敗する(最悪変な登録になってやり直し)
mysql-shellが起動していない＝それが起動してないとルータが起動しない

から

  mysql-router:
    env_file:
      - mysql-router.env
    image: mysql/mysql-router:8.0
    ports:
      - "6446:6446"
    depends_on:
      - mysql-server-1
      - mysql-server-2
      - mysql-server-3
#      - mysql-shell　←こいつが邪魔
    restart: on-failure

mysql-router.env

(このファイルについては編集不要です)

MYSQL_USER=root
MYSQL_HOST=mysql-server-1
MYSQL_PORT=3306
MYSQL_PASSWORD=xxxxxx
MYSQL_INNODB_NUM_MEMBERS=3

起動コマンド

$ docker-compose -f docker-compose.yml up -d mysql-router
innodb-cluster_mysql-server-2_1 is up-to-date
innodb-cluster_mysql-server-1_1 is up-to-date
innodb-cluster_mysql-server-3_1 is up-to-date
Creating innodb-cluster_mysql-router_1 ... done

起動ログ確認

$ docker-compose -f docker-compose.yml logs | grep mysql-router_1
Attaching to innodb-cluster_mysql-router_1, innodb-cluster_mysql-server-3_1, innodb-cluster_mysql-server-1_1, innodb-cluster_mysql-server-2_1
mysql-router_1    | Succesfully contacted mysql server at mysql-server-1. Checking for cluster state.
mysql-router_1    | Succesfully contacted mysql server at mysql-server-1. Trying to bootstrap.
mysql-router_1    | Please enter MySQL password for root:
mysql-router_1    |
mysql-router_1    | Bootstrapping MySQL Router instance at '/tmp/mysqlrouter'...
mysql-router_1    | Checking for old Router accounts
mysql-router_1    | Creating account mysql_router1_kmpjsdztdoir@'%'
mysql-router_1    | MySQL Router  has now been configured for the InnoDB cluster 'devCluster'.
mysql-router_1    |
mysql-router_1    | The following connection information can be used to connect to the cluster after MySQL Router has been started with generated configuration..
mysql-router_1    |
mysql-router_1    | Classic MySQL protocol connections to cluster 'devCluster':
mysql-router_1    | - Read/Write Connections: localhost:6446
mysql-router_1    | - Read/Only Connections: localhost:6447
mysql-router_1    | X protocol connections to cluster 'devCluster':
mysql-router_1    | - Read/Write Connections: localhost:64460
mysql-router_1    | - Read/Only Connections: localhost:64470
mysql-router_1    | Starting mysql-router.
mysql-router_1    | 2019-01-07 07:47:20 main INFO [7f87057e0880] Loading all plugins.
mysql-router_1    | 2019-01-07 07:47:20 main INFO [7f87057e0880]   plugin 'metadata_cache:devCluster' loading
mysql-router_1    | 2019-01-07 07:47:20 main INFO [7f87057e0880]   plugin 'routing:devCluster_default_ro' loading
mysql-router_1    | 2019-01-07 07:47:20 main INFO [7f87057e0880]   plugin 'routing:devCluster_default_rw' loading
mysql-router_1    | 2019-01-07 07:47:20 main INFO [7f87057e0880]   plugin 'routing:devCluster_default_x_ro' loading
mysql-router_1    | 2019-01-07 07:47:20 main INFO [7f87057e0880]   plugin 'routing:devCluster_default_x_rw' loading
mysql-router_1    | 2019-01-07 07:47:20 main INFO [7f87057e0880] Initializing all plugins.
mysql-router_1    | 2019-01-07 07:47:20 main INFO [7f87057e0880]   plugin 'metadata_cache' initializing
mysql-router_1    | 2019-01-07 07:47:20 main INFO [7f87057e0880]   plugin 'routing' initializing
mysql-router_1    | 2019-01-07 07:47:20 main INFO [7f87057e0880] Starting all plugins.
mysql-router_1    | 2019-01-07 07:47:20 main INFO [7f87013b0700]   plugin 'metadata_cache:devCluster' starting
mysql-router_1    | 2019-01-07 07:47:20 main INFO [7f8700baf700]   plugin 'routing:devCluster_default_ro' starting
mysql-router_1    | 2019-01-07 07:47:20 metadata_cache INFO [7f87013b0700] Starting Metadata Cache
mysql-router_1    | 2019-01-07 07:47:20 metadata_cache INFO [7f87013b0700] Connections using ssl_mode 'PREFERRED'
mysql-router_1    | 2019-01-07 07:47:20 main INFO [7f86fbfff700]   plugin 'routing:devCluster_default_rw' starting
mysql-router_1    | 2019-01-07 07:47:20 main INFO [7f86fb7fe700]   plugin 'routing:devCluster_default_x_ro' starting
mysql-router_1    | 2019-01-07 07:47:20 routing INFO [7f86fb7fe700] [routing:devCluster_default_x_ro] started: listening on 0.0.0.0:64470
mysql-router_1    | 2019-01-07 07:47:20 routing INFO [7f8700baf700] [routing:devCluster_default_ro] started: listening on 0.0.0.0:6447
mysql-router_1    | 2019-01-07 07:47:20 main INFO [7f86faffd700]   plugin 'routing:devCluster_default_x_rw' starting
mysql-router_1    | 2019-01-07 07:47:20 main INFO [7f87057e0880] Running.
mysql-router_1    | 2019-01-07 07:47:20 routing INFO [7f86faffd700] [routing:devCluster_default_x_rw] started: listening on 0.0.0.0:64460
mysql-router_1    | 2019-01-07 07:47:20 routing INFO [7f86fbfff700] [routing:devCluster_default_rw] started: listening on 0.0.0.0:6446
mysql-router_1    | 2019-01-07 07:47:20 metadata_cache INFO [7f87013b0700] Potential changes detected in cluster 'devCluster' after metadata refresh
mysql-router_1    | 2019-01-07 07:47:20 metadata_cache INFO [7f87013b0700] Metadata for cluster 'devCluster' has 1 replicasets:
mysql-router_1    | 2019-01-07 07:47:20 metadata_cache INFO [7f87013b0700] 'default' (3 members, single-master)
mysql-router_1    | 2019-01-07 07:47:20 metadata_cache INFO [7f87013b0700]     172.23.0.3:3306 / 33060 - role=HA mode=RW
mysql-router_1    | 2019-01-07 07:47:20 metadata_cache INFO [7f87013b0700]     172.23.0.2:3306 / 33060 - role=HA mode=RO
mysql-router_1    | 2019-01-07 07:47:20 metadata_cache INFO [7f87013b0700]     172.23.0.4:3306 / 33060 - role=HA mode=RO
mysql-router_1    | 2019-01-07 07:47:20 metadata_cache INFO [7f87057de700] Starting metadata cache refresh thread

とりあえずできたっぽいぞ。

MySQL Router経由で接続確認

ルータのIPアドレス取得

$ docker exec -it innodb-cluster_mysql-router_1 bash
bash-4.2# cat /etc/hosts
127.0.0.1       localhost
::1     localhost ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
172.23.0.5      8f193d4b8fff

ルータで使ってるポート番号確認

bash-4.2# mysqlrouter --bootstrap root@mysql-server-1 --user=root
Please enter MySQL password for root:

Bootstrapping system MySQL Router instance...
Checking for old Router accounts
Creating account mysql_router2_jnzrnqr85zfv@'%'
MySQL Router  has now been configured for the InnoDB cluster 'devCluster'.

The following connection information can be used to connect to the cluster after MySQL Router has been started with generated configuration..

Classic MySQL protocol connections to cluster 'devCluster':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
X protocol connections to cluster 'devCluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470

Existing configurations backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak'
bash-4.2# exit

通常の接続時は読み書きだと6446、読専だと6447らしい。 X protocolだと読み書きだと64460、読専だと64470となっている。

ルータ経由で繋げば、6446はDBサーバに読み書きするために、6447は読専でDBサーバにラウンドロビンでルーティングするはず。

試してみる。

$ mysql -uroot -p -h172.23.0.5 -P6446 -e "show variables like 'hostname'"
Enter password:
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| hostname      | 1897ffaf93e4 |
+---------------+--------------+
$ mysql -uroot -p -h127.0.0.1 -P3301 -e "show variables like 'hostname'"
Enter password:
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| hostname      | 1897ffaf93e4 |
+---------------+--------------+

書き込みできるマスターは1個なのでこれでOK。 (この後何回か叩いたが、Value値は変わらず)

$ mysql -uroot -p -h127.0.0.1 -P3302 -e "show variables like 'hostname'"
Enter password:
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| hostname      | c1c79efae23a |
+---------------+--------------+
$ mysql -uroot -p -h127.0.0.1 -P3303 -e "show variables like 'hostname'"
Enter password:
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| hostname      | e09882973930 |
+---------------+--------------+

読専のサーバ2台は上のホスト名になっている。

$ mysql -uroot -p -h172.23.0.5 -P6447 -e "show variables like 'hostname'"
Enter password:
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| hostname      | c1c79efae23a |
+---------------+--------------+
$ mysql -uroot -p -h172.23.0.5 -P6447 -e "show variables like 'hostname'"
Enter password:
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| hostname      | e09882973930 |
+---------------+--------------+

ルータに対しポート番号6447で繋ぎに言った場合、両方に繋がるか確認したがちゃんと振り分けられて繋がっていた。

(おまけ)MySQL Shellでも接続確認してみる

MySQL Shellでルータ経由で接続

MySQL ShellはXプロトコル対応なので、そっちのポート確認ができる。

$ mysqlsh -uroot -pxxxxxx -h172.23.0.5 -P64460 --sql -e "show variables like 'hostname'"
Variable_name   Value
hostname        1897ffaf93e4
$ mysqlsh -uroot -pxxxxxx -h172.23.0.5 -P64470 --sql -e "show variables like 'hostname'"
Variable_name   Value
hostname        c1c79efae23a
$ mysqlsh -uroot -pxxxxxx -h172.23.0.5 -P64470 --sql -e "show variables like 'hostname'"
Variable_name   Value
hostname        e09882973930

うん、振り分けしてる。

さらにおまけ(MySQL Routerのヘルプ)

bash-4.2# mysqlrouter --help
MySQL Router v8.0.13 on Linux (64-bit) (MySQL Community Server - GPL)
Copyright (c) 2015, 2018, 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.

Configuration read from the following files in the given order (enclosed
in parentheses means not available for reading):
  /etc/mysqlrouter/mysqlrouter.conf
  (/root/.mysqlrouter.conf)
Plugins Path:
  /usr/lib64/mysqlrouter
Default Log Directory:
  /var/log/mysqlrouter
Default Persistent Data Directory:
  /var/lib/mysqlrouter
Default Runtime State Directory:
  /run/mysqlrouter

Usage: mysqlrouter [-V|--version] [-?|--help]
                   [-B|--bootstrap=<server_url>]
                   [--bootstrap-socket=<socket_name>]
                   [-d|--directory=<directory>]
                   [--conf-use-sockets]
                   [--conf-skip-tcp]
                   [--conf-base-port=<port>]
                   [--conf-bind-address=<address>]
                   [--master-key-reader=<VALUE>]
                   [--master-key-writer=<VALUE>]
                   [--connect-timeout=[<VALUE>]]
                   [--read-timeout=[<VALUE>]]
                   [-u|--user=<username>]
                   [--name=[<name>]]
                   [--force-password-validation]
                   [--password-retries=[<password-retries>]]
                   [--account-host=<account-host>]
                   [--report-host=<report-host>]
                   [--force] [--ssl-mode=<mode>]
                   [--ssl-cipher=<ciphers>]
                   [--tls-version=<versions>]
                   [--ssl-ca=<path>]
                   [--ssl-capath=<directory>]
                   [--ssl-crl=<path>]
                   [--ssl-crlpath=<directory>]
                   [--ssl-cert=<path>]
                   [--ssl-key=<path>]
                   [-c|--config=<path>]
                   [-a|--extra-config=<path>]

Options:
  -V, --version
        Display version information and exit.
  -?, --help
        Display this help and exit.
  -B <server_url>, --bootstrap <server_url>
        Bootstrap and configure Router for operation with a MySQL InnoDB
        cluster.
  --bootstrap-socket <socket_name>
        Bootstrap and configure Router via a Unix socket
  -d <directory>, --directory <directory>
        Creates a self-contained directory for a new instance of the
        Router. (bootstrap)
  --conf-use-sockets
        Whether to use Unix domain sockets. (bootstrap)
  --conf-skip-tcp
        Whether to disable binding of a TCP port for incoming
        connections. (bootstrap)
  --conf-base-port <port>
        Base port to use for listening router ports. (bootstrap)
  --conf-bind-address <address>
        IP address of the interface to which router's listening sockets
        should bind. (bootstrap)
  --master-key-reader <VALUE>
        The tool that can be used to read master key, it has to be used
        together with --master-key-writer. (bootstrap)
  --master-key-writer <VALUE>
        The tool that can be used to store master key, it has to be used
        together with --master-key-reader. (bootstrap)
  --connect-timeout [ <VALUE>]
        The time in seconds after which trying to connect to metadata
        server should timeout. It applies to bootstrap mode and is
        written to configuration file. It is also used in normal mode.
  --read-timeout [ <VALUE>]
        The time in seconds after which read from metadata server should
        timeout. It applies to bootstrap mode and is written to
        configuration file. It is also used in normal mode.
  -u <username>, --user <username>
        Run the mysqlrouter as the user having the name user_name.
  --name [ <name>]
        Gives a symbolic name for the router instance. (bootstrap)
  --force-password-validation
        When autocreating database account do not use HASHED password.
        (bootstrap)
  --password-retries [ <password-retries>]
        Number of the retries for generating the router's user password.
        (bootstrap)
  --account-host <account-host>
        Host pattern to be used when creating Router's database user,
        default='%'. It can be used multiple times to provide multiple
        patterns. (bootstrap)
  --report-host <report-host>
        Host name of this computer (it will be queried from OS if not
        provided). It is used as suffix (the part after '@') in Router's
        database user name; should match host name as seen by the
        cluster nodes (bootstrap)
  --force
        Force reconfiguration of a possibly existing instance of the
        router. (bootstrap)
  --ssl-mode <mode>
        SSL connection mode for use during bootstrap and normal
        operation, when connecting to the metadata server. Analogous to
        --ssl-mode in mysql client. One of
        DISABLED|PREFERRED|REQUIRED|VERIFY_CA|VERIFY_IDENTITY. Default =
        PREFERRED. (bootstrap)
  --ssl-cipher <ciphers>
        : separated list of SSL ciphers to allow, if SSL is enabeld.
  --tls-version <versions>
        , separated list of TLS versions to request, if SSL is enabled.
  --ssl-ca <path>
        Path to SSL CA file to verify server's certificate against.
  --ssl-capath <directory>
        Path to directory containing SSL CA files to verify server's
        certificate against.
  --ssl-crl <path>
        Path to SSL CRL file to use when verifying server certificate.
  --ssl-crlpath <directory>
        Path to directory containing SSL CRL files to use when verifying
        server certificate.
  --ssl-cert <path>
        Path to client SSL certificate, to be used if client certificate
        verification is required. Used during bootstrap only.
  --ssl-key <path>
        Path to private key for client SSL certificate, to be used if
        client certificate verification is required. Used during
        bootstrap only.
  -c <path>, --config <path>
        Only read configuration from given file.
  -a <path>, --extra-config <path>
        Read this file after configuration files are read from either
        default locations or from files specified by the --config
        option.

Examples:
  Bootstrap for use with InnoDB cluster into system-wide installation
    sudo mysqlrouter --bootstrap root@clusterinstance01 --user=mysqlrouter
  Start router
    sudo mysqlrouter --user=mysqlrouter&

  Bootstrap for use with InnoDb cluster in a self-contained directory
    mysqlrouter --bootstrap root@clusterinstance01 -d myrouter
  Start router
    myrouter/start.sh

今のところそこまで複雑なオプションは無さそうな感じ。接続回りのオプションが大半だし。

最後に

MySQL Router 自体はまだ機能的にルーティングの制御とHA監視程度しかないのでそれ以上の複雑なことはできないが、サーバをHA構成にしつつ、アクセスのLB化をしたいのであれば手軽に作れるし、悪くなさそう。

41から始めました

MySQL InnoDB Clusterの構築(by docker)

MySQL InnoDB Clusterさわってみたい

環境・構成

１．MySQL8.0.13の起動(docker-compose)

docker-compose.yml (一部)

起動！

起動に時間がかかるので待ち

StateがUp (healthy) になったらOK

２．MySQL Shellによるクラスタ登録

２－１．mysql-shellの起動(docker-compose)

docker-compose.yml (一部)

mysql-shell.env

setupCluster.js

起動

起動ログ確認

２－２．MySQL Shellのインストール＆クラスタ設定

以下のコマンドを実行

クラスタの設定

各DBコンテナ内に入ってIPアドレスを確認しておく

MySQL Shellを起動して、クラスタの登録を行う

MySQL Routerの起動

docker-compose.yml (一部)

mysql-router.env

起動コマンド

起動ログ確認

MySQL Router経由で接続確認

ルータのIPアドレス取得

ルータで使ってるポート番号確認

試してみる。

(おまけ)MySQL Shellでも接続確認してみる

MySQL Shellでルータ経由で接続

さらにおまけ(MySQL Routerのヘルプ)

最後に