MySQL Shell のログを活用しよう

2019年11月8日2022年4月11日

この記事は最終更新から2年以上経過しています。内容が古くなっている可能性があります。

MySQL Shell を使用した事があっても、MySQL Shell のログファイルは見たことがない方もおられるのではないでしょうか。
MySQL Shell のログファイルはUnix系OSでは操作ユーザのホームディレクトリ配下 ~/.mysqlsh/mysqlsh.log に出力されます。
Windows系OSでは C:\Users\<ユーザ名>\AppData\Roaming\MySQL\mysqlsh\mysqlsh.log に出力され、保存先を変更したい場合は、いずれも環境変数 MYSQLSH_USER_CONFIG_HOME に出力先を設定する事でログファイルの保存先を変更できます。

先月GAとなった MySQL Shell 8.0.18 からAPI操作によって実行されるSQLステートメントが出力できるようになりました。
これによって何が嬉しいかというと、例えば本ブログで何度か紹介している MySQL InnoDB Cluster のクラスタ作成・制御時に実行したコマンドが各ノードにどのようなステートメントを発行しているのかを確認したい場合、今までは各ノードの General Query Log を出力して確認したりする必要がありました。
それが、MySQL Shell のログを確認するだけで、どのノードにどのようなSQLステートメントが発行されているかを確認できるようになります。

具体的にどのような出力がされるのか、簡単に MySQL InnoDB Cluster を構築して確認してみます。

1. 構成

シングルプライマリモードとし、MySQL Shell は MySQL Router と同一サーバにインストールします。
サーバOSは全て CentOS7.5 となります。

ホスト名	IPアドレス	用途
mysqlrouter8018	192.168.33.25	MySQL Router MySQL Shell
node8018-1	192.168.33.26	クラスタノード（プライマリ）
node8018-2	192.168.33.27	クラスタノード（セカンダリ）
node8018-3	192.168.33.28	クラスタノード（セカンダリ）

2. 構築

2-1. 共通の設定

このセクションの設定内容は全てのノードで実行します。

SELinuxを無効にします。

# setenforce 0
# sed -i -e 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

1 2	# setenforce 0 # sed -i -e 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

各ノードのホスト情報を /etc/hosts に追加します。

# cp /etc/hosts{,.org}
# cat <<EOF >> /etc/hosts
> 192.168.33.25   mysqlrouter8018
> 192.168.33.26   node8018-1
> 192.168.33.27   node8018-2
> 192.168.33.28   node8018-3
> EOF

# cp /etc/hosts{,.org}

# cat <<EOF >> /etc/hosts

> 192.168.33.25 mysqlrouter8018

> 192.168.33.26 node8018-1

> 192.168.33.27 node8018-2

> 192.168.33.28 node8018-3

> EOF

MySQLの公式レポジトリを追加します。

# yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

1	# yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

2-2. クラスタノードの設定

このセクションの内容はクラスタノードとなる node8018-1 ～ node8018-3 の各ノードで実行します。

Firewalldが起動している場合はMySQL InnoDB Cluster で使用するポートを解放します。

# firewall-cmd --add-port=3306/tcp
# firewall-cmd --add-port=33060/tcp
# firewall-cmd --add-port=33061/tcp
# firewall-cmd --runtime-to-permanent

# firewall-cmd --add-port=3306/tcp

# firewall-cmd --add-port=33060/tcp

# firewall-cmd --add-port=33061/tcp

# firewall-cmd --runtime-to-permanent

MySQLサーバをインストールします。

# yum install -y mysql-community-server

1	# yum install -y mysql-community-server

my.cnf に最低限の必要な情報のみ設定しておきます。
※ server_idのみ各ノードで異なる値を設定します。

# cp /etc/my.cnf{,.org}
# cat <<EOF > /etc/my.cnf
> [mysqld]
> server_id=1001  # node8018-1:1001 / node8018-2:1002 / node8018-1:1003
> binlog_checksum=NONE
> enforce_gtid_consistency=ON
> gtid_mode=ON
> EOF

# cp /etc/my.cnf{,.org}

# cat <<EOF > /etc/my.cnf

> [mysqld]

> server_id=1001 # node8018-1:1001 / node8018-2:1002 / node8018-1:1003

> binlog_checksum=NONE

> enforce_gtid_consistency=ON

> gtid_mode=ON

> EOF

MySQLサーバを起動します。

# systemctl start mysqld

1	# systemctl start mysqld

MySQLサーバにログインし、root ユーザのパスワード変更と、MySQL Shellから実行するユーザを作成します。

# mysql -u root -p$(grep "temporary password" /var/log/messages | tail -n 1 | rev | cut -f 1 -d " " | rev)
mysql> set password = 'Password1!';
mysql> create user sheller@'192.168.33.%' identified by 'Password1!';
mysql> grant all on *.* to sheller@'192.168.33.%' with grant option;
mysql> \q

# mysql -u root -p$(grep "temporary password" /var/log/messages | tail -n 1 | rev | cut -f 1 -d " " | rev)

mysql> set password = 'Password1!';

mysql> create user sheller@'192.168.33.%' identified by 'Password1!';

mysql> grant all on *.* to sheller@'192.168.33.%' with grant option;

mysql> \q

2-3. MySQL Router + MySQL Shell の設定

このセクションの内容は mysqlrouter8018 ノードのみで実行します。

Firewalldが起動している場合はMySQL Router に必要なポートを解放します。

# firewall-cmd --add-port=6446/tcp
# firewall-cmd --add-port=6447/tcp
# firewall-cmd --add-port=64460/tcp
# firewall-cmd --add-port=64470/tcp
# firewall-cmd --runtime-to-permanent

# firewall-cmd --add-port=6446/tcp

# firewall-cmd --add-port=6447/tcp

# firewall-cmd --add-port=64460/tcp

# firewall-cmd --add-port=64470/tcp

# firewall-cmd --runtime-to-permanent

MySQL Router + MySQL Shell + MySQL Client をインストールします。

# yum install -y mysql-router-community mysql-community-client mysql-shell

1	# yum install -y mysql-router-community mysql-community-client mysql-shell

ここからは、MySQL Shell でクラスタを作成し、本題となるログ出力の確認となります。
MySQL Shell の –dba-log-sql オプションで MySQL Shell が発行するSQLステートメントの出力を制御します。

設定値	説明
0	SQLステートメントの出力をしない。（デフォルト）
1	SELECTステートメントと SHOWステートメントは出力しない。
2	実行するSQLステートメントを全て出力する。

2 を設定すると非常に多くのログが出力されるので、1 を設定して、まずはプライマリノード（node8018-1）にログインします。

# mysqlsh sheller@node8018-1 --dba-log-sql=1

1	# mysqlsh sheller@node8018-1 --dba-log-sql=1

クラスタの作成

dba.createCluster()でクラスタを作成してみます。

 MySQL  node8018-1:33060+ ssl  JS > cluster = dba.createCluster('mycluster')
A new InnoDB cluster will be created on instance 'node8018-1:3306'.

Validating instance at node8018-1:3306...

This instance reports its own address as node8018-1:3306

Instance configuration is suitable.
Creating InnoDB cluster 'mycluster' on 'node8018-1: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.

<Cluster:mycluster>

MySQL node8018-1:33060+ ssl JS > cluster = dba.createCluster('mycluster')

A new InnoDB cluster will be created on instance 'node8018-1:3306'.

Validating instance at node8018-1:3306...

This instance reports its own address as node8018-1:3306

Instance configuration is suitable.

Creating InnoDB cluster 'mycluster' on 'node8018-1: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.

<Cluster:mycluster>

正常に完了したので、MySQL Shell のログファイル ~/.mysqlsh/mysqlsh.log を確認してみましょう。

2019-11-01 08:22:35: Info: Validating account sheller@192.168.33.%...
2019-11-01 08:22:36: Info: sheller user has 1 accounts with wildcard and 0 without
2019-11-01 08:22:36: Info: Validating InnoDB page size of instance 'node8018-1:3306'.
2019-11-01 08:22:36: Info: Checking if performance_schema is enabled on instance 'node8018-1:3306'.
2019-11-01 08:22:36: Info: Validating configuration of node8018-1:3306 (mycnf = )
2019-11-01 08:22:36: Warning: SSL mode used to configure the cluster: 'REQUIRED'
2019-11-01 08:22:36: Info: Using Group Replication single primary mode: TRUE
2019-11-01 08:22:36: Info: Using Group Replication SSL mode: REQUIRED
2019-11-01 08:22:36: Info: Using Group Replication group name: 43449d1e-fa25-11e9-8dd1-0800278bc93f
2019-11-01 08:22:36: Info: Using Group Replication local address: node8018-1:33061
2019-11-01 08:22:36: Info: Using Group Replication exit state action: READ_ONLY
2019-11-01 08:22:36: Info: node8018-1:3306: INSTALL PLUGIN <code>group_replication</code> SONAME 'group_replication.so'
2019-11-01 08:22:36: Info: node8018-1:3306: SET PERSIST <code>group_replication_group_name</code> = '43449d1e-fa25-11e9-8dd1-0800278bc93f'
2019-11-01 08:22:36: Info: node8018-1:3306: SET PERSIST <code>group_replication_single_primary_mode</code> = 'ON'
2019-11-01 08:22:36: Info: node8018-1:3306: SET PERSIST <code>group_replication_recovery_use_ssl</code> = 'ON'
2019-11-01 08:22:36: Info: node8018-1:3306: SET PERSIST <code>group_replication_ssl_mode</code> = 'REQUIRED'
2019-11-01 08:22:36: Info: node8018-1:3306: SET PERSIST <code>group_replication_local_address</code> = 'node8018-1:33061'
2019-11-01 08:22:36: Info: node8018-1:3306: SET PERSIST <code>group_replication_exit_state_action</code> = 'READ_ONLY'
2019-11-01 08:22:36: Info: node8018-1:3306: SET PERSIST <code>group_replication_start_on_boot</code> = 'ON'
2019-11-01 08:22:36: Info: node8018-1:3306: SET PERSIST <code>auto_increment_increment</code> = 1
2019-11-01 08:22:36: Info: node8018-1:3306: SET PERSIST <code>auto_increment_offset</code> = 2
2019-11-01 08:22:36: Info: node8018-1:3306: SET GLOBAL <code>group_replication_bootstrap_group</code> = 'ON'
2019-11-01 08:22:36: Info: node8018-1:3306: START GROUP_REPLICATION
2019-11-01 08:22:39: Info: node8018-1:3306: SET GLOBAL <code>group_replication_bootstrap_group</code> = 'OFF'
2019-11-01 08:22:40: Info: node8018-1:3306: DROP SCHEMA IF EXISTS mysql_innodb_cluster_metadata
2019-11-01 08:22:40: Info: node8018-1:3306: CREATE DATABASE mysql_innodb_cluster_metadata
2019-11-01 08:22:40: Info: node8018-1:3306: USE mysql_innodb_cluster_metadata
2019-11-01 08:22:40: Info: node8018-1:3306:
・・・（略）
CREATE VIEW schema_version (major, minor, patch) AS SELECT 1, 0, 1
・・・（略）
CREATE TABLE clusters (
・・・（略）
2019-11-01 08:22:40: Info: node8018-1:3306: /*
 ・・・（略）
CREATE TABLE replicasets (
 ・・・（略）
2019-11-01 08:22:40: Info: node8018-1:3306: /*
・・・（略）
CREATE TABLE hosts (
・・・（略）
2019-11-01 08:22:40: Info: node8018-1:3306: /*
・・・（略）
CREATE TABLE instances (
・・・（略）
2019-11-01 08:22:40: Info: node8018-1:3306: /*
・・・（略）
CREATE TABLE routers (
 ・・・（略）
2019-11-01 08:22:40: Info: node8018-1:3306: START TRANSACTION
2019-11-01 08:22:40: Info: node8018-1:3306: INSERT INTO mysql_innodb_cluster_metadata.clusters (cluster_name, description, attributes) VALUES ('mycluster', 'Default Cluster', '{}')
2019-11-01 08:22:40: Info: node8018-1:3306: INSERT INTO mysql_innodb_cluster_metadata.replicasets (cluster_id, replicaset_type, topology_type, replicaset_name, active, attributes) VALUES (1, 'gr', 'pm', 'default', 1,JSON_OBJECT('adopted', '0',             'group_replication_group_name', '43449d1e-fa25-11e9-8dd1-0800278bc93f'))
2019-11-01 08:22:40: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.clusters SET default_replicaset = 1 WHERE cluster_id = 1
2019-11-01 08:22:40: Info: node8018-1:3306: COMMIT
2019-11-01 08:22:40: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.clusters SET attributes = json_set(attributes, '$.opt_gtidSetIsComplete', CAST('false' as JSON)) WHERE cluster_id = 1
2019-11-01 08:22:40: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.clusters SET attributes = json_set(attributes, '$.default', CAST('true' as JSON)) WHERE cluster_id = 1
2019-11-01 08:22:40: Info: node8018-1:3306: START TRANSACTION
2019-11-01 08:22:40: Info: node8018-1:3306: INSERT INTO mysql_innodb_cluster_metadata.hosts (host_name, ip_address, location) VALUES ('node8018-1', '', '')
2019-11-01 08:22:40: Info: node8018-1:3306: INSERT INTO mysql_innodb_cluster_metadata.instances (host_id, replicaset_id, mysql_server_uuid, instance_name, role, addresses, attributes) VALUES (1, 1, 'c00dde17-fa24-11e9-bfec-0800278bc93f', 'node8018-1:3306', 'HA', json_object('mysqlClassic', 'node8018-1:3306', 'mysqlX', 'node8018-1:33060', 'grLocal', 'node8018-1:33061'), '{}')
2019-11-01 08:22:40: Info: node8018-1:3306: COMMIT
2019-11-01 08:22:40: Info: Creating recovery account 'mysql_innodb_cluster_1001'@'%' for instance 'node8018-1:3306'
2019-11-01 08:22:40: Info: node8018-1:3306: CREATE USER IF NOT EXISTS 'mysql_innodb_cluster_1001'@'%' IDENTIFIED BY **** PASSWORD EXPIRE NEVER
2019-11-01 08:22:40: Info: node8018-1:3306: GRANT REPLICATION SLAVE, BACKUP_ADMIN ON *.* TO 'mysql_innodb_cluster_1001'@'%'
2019-11-01 08:22:40: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.instances SET attributes = json_set(COALESCE(attributes, '{}'),  '$.recoveryAccountUser', 'mysql_innodb_cluster_1001',  '$.recoveryAccountHost', '%') WHERE mysql_server_uuid = 'c00dde17-fa24-11e9-bfec-0800278bc93f'
2019-11-01 08:22:40: Info: node8018-1:3306: CHANGE MASTER TO MASTER_USER = /*(*/ 'mysql_innodb_cluster_1001' /*)*/, MASTER_PASSWORD = **** FOR CHANNEL 'group_replication_recovery'
2019-11-01 08:22:40: Info: node8018-1:3306: INSTALL PLUGIN <code>clone</code> SONAME 'mysql_clone.so'

2019-11-01 08:22:35: Info: Validating account sheller@192.168.33.%...

2019-11-01 08:22:36: Info: sheller user has 1 accounts with wildcard and 0 without

2019-11-01 08:22:36: Info: Validating InnoDB page size of instance 'node8018-1:3306'.

2019-11-01 08:22:36: Info: Checking if performance_schema is enabled on instance 'node8018-1:3306'.

2019-11-01 08:22:36: Info: Validating configuration of node8018-1:3306 (mycnf = )

2019-11-01 08:22:36: Warning: SSL mode used to configure the cluster: 'REQUIRED'

2019-11-01 08:22:36: Info: Using Group Replication single primary mode: TRUE

2019-11-01 08:22:36: Info: Using Group Replication SSL mode: REQUIRED

2019-11-01 08:22:36: Info: Using Group Replication group name: 43449d1e-fa25-11e9-8dd1-0800278bc93f

2019-11-01 08:22:36: Info: Using Group Replication local address: node8018-1:33061

2019-11-01 08:22:36: Info: Using Group Replication exit state action: READ_ONLY

2019-11-01 08:22:36: Info: node8018-1:3306: INSTALL PLUGIN <code>group_replication</code> SONAME 'group_replication.so'

2019-11-01 08:22:36: Info: node8018-1:3306: SET PERSIST <code>group_replication_group_name</code> = '43449d1e-fa25-11e9-8dd1-0800278bc93f'

2019-11-01 08:22:36: Info: node8018-1:3306: SET PERSIST <code>group_replication_single_primary_mode</code> = 'ON'

2019-11-01 08:22:36: Info: node8018-1:3306: SET PERSIST <code>group_replication_recovery_use_ssl</code> = 'ON'

2019-11-01 08:22:36: Info: node8018-1:3306: SET PERSIST <code>group_replication_ssl_mode</code> = 'REQUIRED'

2019-11-01 08:22:36: Info: node8018-1:3306: SET PERSIST <code>group_replication_local_address</code> = 'node8018-1:33061'

2019-11-01 08:22:36: Info: node8018-1:3306: SET PERSIST <code>group_replication_exit_state_action</code> = 'READ_ONLY'

2019-11-01 08:22:36: Info: node8018-1:3306: SET PERSIST <code>group_replication_start_on_boot</code> = 'ON'

2019-11-01 08:22:36: Info: node8018-1:3306: SET PERSIST <code>auto_increment_increment</code> = 1

2019-11-01 08:22:36: Info: node8018-1:3306: SET PERSIST <code>auto_increment_offset</code> = 2

2019-11-01 08:22:36: Info: node8018-1:3306: SET GLOBAL <code>group_replication_bootstrap_group</code> = 'ON'

2019-11-01 08:22:36: Info: node8018-1:3306: START GROUP_REPLICATION

2019-11-01 08:22:39: Info: node8018-1:3306: SET GLOBAL <code>group_replication_bootstrap_group</code> = 'OFF'

2019-11-01 08:22:40: Info: node8018-1:3306: DROP SCHEMA IF EXISTS mysql_innodb_cluster_metadata

2019-11-01 08:22:40: Info: node8018-1:3306: CREATE DATABASE mysql_innodb_cluster_metadata

2019-11-01 08:22:40: Info: node8018-1:3306: USE mysql_innodb_cluster_metadata

2019-11-01 08:22:40: Info: node8018-1:3306:

・・・（略）

CREATE VIEW schema_version (major, minor, patch) AS SELECT 1, 0, 1

・・・（略）

CREATE TABLE clusters (

・・・（略）

2019-11-01 08:22:40: Info: node8018-1:3306: /*

・・・（略）

CREATE TABLE replicasets (

・・・（略）

2019-11-01 08:22:40: Info: node8018-1:3306: /*

・・・（略）

CREATE TABLE hosts (

・・・（略）

2019-11-01 08:22:40: Info: node8018-1:3306: /*

・・・（略）

CREATE TABLE instances (

・・・（略）

2019-11-01 08:22:40: Info: node8018-1:3306: /*

・・・（略）

CREATE TABLE routers (

・・・（略）

2019-11-01 08:22:40: Info: node8018-1:3306: START TRANSACTION

2019-11-01 08:22:40: Info: node8018-1:3306: INSERT INTO mysql_innodb_cluster_metadata.clusters (cluster_name, description, attributes) VALUES ('mycluster', 'Default Cluster', '{}')

2019-11-01 08:22:40: Info: node8018-1:3306: INSERT INTO mysql_innodb_cluster_metadata.replicasets (cluster_id, replicaset_type, topology_type, replicaset_name, active, attributes) VALUES (1, 'gr', 'pm', 'default', 1,JSON_OBJECT('adopted', '0', 'group_replication_group_name', '43449d1e-fa25-11e9-8dd1-0800278bc93f'))

2019-11-01 08:22:40: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.clusters SET default_replicaset = 1 WHERE cluster_id = 1

2019-11-01 08:22:40: Info: node8018-1:3306: COMMIT

2019-11-01 08:22:40: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.clusters SET attributes = json_set(attributes, '$.opt_gtidSetIsComplete', CAST('false' as JSON)) WHERE cluster_id = 1

2019-11-01 08:22:40: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.clusters SET attributes = json_set(attributes, '$.default', CAST('true' as JSON)) WHERE cluster_id = 1

2019-11-01 08:22:40: Info: node8018-1:3306: START TRANSACTION

2019-11-01 08:22:40: Info: node8018-1:3306: INSERT INTO mysql_innodb_cluster_metadata.hosts (host_name, ip_address, location) VALUES ('node8018-1', '', '')

2019-11-01 08:22:40: Info: node8018-1:3306: INSERT INTO mysql_innodb_cluster_metadata.instances (host_id, replicaset_id, mysql_server_uuid, instance_name, role, addresses, attributes) VALUES (1, 1, 'c00dde17-fa24-11e9-bfec-0800278bc93f', 'node8018-1:3306', 'HA', json_object('mysqlClassic', 'node8018-1:3306', 'mysqlX', 'node8018-1:33060', 'grLocal', 'node8018-1:33061'), '{}')

2019-11-01 08:22:40: Info: node8018-1:3306: COMMIT

2019-11-01 08:22:40: Info: Creating recovery account 'mysql_innodb_cluster_1001'@'%' for instance 'node8018-1:3306'

2019-11-01 08:22:40: Info: node8018-1:3306: CREATE USER IF NOT EXISTS 'mysql_innodb_cluster_1001'@'%' IDENTIFIED BY **** PASSWORD EXPIRE NEVER

2019-11-01 08:22:40: Info: node8018-1:3306: GRANT REPLICATION SLAVE, BACKUP_ADMIN ON *.* TO 'mysql_innodb_cluster_1001'@'%'

2019-11-01 08:22:40: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.instances SET attributes = json_set(COALESCE(attributes, '{}'), '$.recoveryAccountUser', 'mysql_innodb_cluster_1001', '$.recoveryAccountHost', '%') WHERE mysql_server_uuid = 'c00dde17-fa24-11e9-bfec-0800278bc93f'

2019-11-01 08:22:40: Info: node8018-1:3306: CHANGE MASTER TO MASTER_USER = /*(*/ 'mysql_innodb_cluster_1001' /*)*/, MASTER_PASSWORD = **** FOR CHANNEL 'group_replication_recovery'

2019-11-01 08:22:40: Info: node8018-1:3306: INSTALL PLUGIN <code>clone</code> SONAME 'mysql_clone.so'

node8018-1 で group_replication プラグインのインストールやグループレプリケーションユーザの作成、SET PERSIST ステートメントでグループレプリケーション関連のパラメータを設定している事がわかります。
また、クラスタのメタ情報等を保持するテーブルを mysql_innodb_cluster_metadata データベースに作成してレコードを管理している事も読み取れます。

クラスタにノードを追加

＜cluster＞.addInstance()で node8018-2 をクラスタに追加します。
recovery method には 8.0.17 で追加された Cloneプラグインを選択してみます。

 MySQL  node8018-1:33060+ ssl  JS > cluster.addInstance('sheller@node8018-2')
Please provide the password for 'sheller@node8018-2': **********
Save password for 'sheller@node8018-2'? [Y]es/[N]o/Ne[v]er (default No): Y
・・・（略）
Please select a recovery method [C]lone/[A]bort (default Abort): C
Validating instance at node8018-2:3306...
・・・（略）
The instance 'node8018-2' was successfully added to the cluster.

MySQL node8018-1:33060+ ssl JS > cluster.addInstance('sheller@node8018-2')

Please provide the password for 'sheller@node8018-2': **********

Save password for 'sheller@node8018-2'? [Y]es/[N]o/Ne[v]er (default No): Y

・・・（略）

Please select a recovery method [C]lone/[A]bort (default Abort): C

Validating instance at node8018-2:3306...

・・・（略）

The instance 'node8018-2' was successfully added to the cluster.

addInstance 時のログを確認してみましょう。

2019-11-01 08:26:00: Warning: SSL mode used to configure the instance: 'REQUIRED'
2019-11-01 08:26:00: Info: node8018-1:3306: SET @gtidset_a='43449d1e-fa25-11e9-8dd1-0800278bc93f:1-17,\nc00dde17-fa24-11e9-bfec-0800278bc93f:1-3'
2019-11-01 08:26:00: Info: node8018-1:3306: SET @gtidset_b='c1c8bc1b-fa24-11e9-bb89-0800278bc93f:1-3'
2019-11-01 08:26:00: Warning: A GTID set check of the MySQL instance at 'node8018-2:3306' determined that it
contains transactions that do not originate from the cluster, which must be
discarded before it can join the cluster.
2019-11-01 08:26:00: Warning: Discarding these extra GTID events can either be done manually or by completely
overwriting the state of node8018-2:3306 with a physical snapshot from an
existing cluster member. To use this method by default, set the
'recoveryMethod' option to 'clone'.

Having extra GTID events is not expected, and it is recommended to investigate
this further and ensure that the data can be removed prior to choosing the
clone recovery method.
2019-11-01 08:26:03: Info: Validating account sheller@192.168.33.%...
2019-11-01 08:26:03: Info: sheller user has 1 accounts with wildcard and 0 without
2019-11-01 08:26:03: Info: Validating InnoDB page size of instance 'node8018-2:3306'.
2019-11-01 08:26:03: Info: Checking if performance_schema is enabled on instance 'node8018-2:3306'.
2019-11-01 08:26:03: Info: Validating configuration of node8018-2:3306 (mycnf = )
2019-11-01 08:26:03: Info: Using Group Replication local address: node8018-2:33061
2019-11-01 08:26:03: Info: Using Group Replication exit state action: READ_ONLY
2019-11-01 08:26:03: Info: Using Group Replication failover consistency: EVENTUAL
2019-11-01 08:26:03: Info: Using Group Replication expel timeout: 0
2019-11-01 08:26:03: Info: node8018-2:3306: INSTALL PLUGIN <code>group_replication</code> SONAME 'group_replication.so'
2019-11-01 08:26:03: Info: node8018-2:3306: SET GLOBAL <code>group_replication_clone_threshold</code> = 1
2019-11-01 08:26:03: Info: node8018-2:3306: RESET MASTER
2019-11-01 08:26:03: Info: Installing the clone plugin on instance 'node8018-2:3306'.
2019-11-01 08:26:03: Info: node8018-2:3306: INSTALL PLUGIN <code>clone</code> SONAME 'mysql_clone.so'
2019-11-01 08:26:03: Info: Installing the clone plugin on instance 'node8018-1:3306'.
2019-11-01 08:26:03: Info: node8018-1:3306: GRANT BACKUP_ADMIN ON *.* TO 'mysql_innodb_cluster_1001'@'%'
2019-11-01 08:26:03: Info: Creating recovery account 'mysql_innodb_cluster_1002'@'%' for instance 'node8018-2:3306'
2019-11-01 08:26:03: Info: node8018-1:3306: CREATE USER IF NOT EXISTS 'mysql_innodb_cluster_1002'@'%' IDENTIFIED BY **** PASSWORD EXPIRE NEVER
2019-11-01 08:26:03: Info: node8018-1:3306: GRANT REPLICATION SLAVE, BACKUP_ADMIN ON *.* TO 'mysql_innodb_cluster_1002'@'%'
2019-11-01 08:26:03: Info: Joining 'node8018-2' to ReplicaSet using account sheller to peer 'node8018-1:3306'.
2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_group_name</code> = '43449d1e-fa25-11e9-8dd1-0800278bc93f'
2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_single_primary_mode</code> = 'ON'
2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_recovery_use_ssl</code> = 'ON'
2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_ssl_mode</code> = 'REQUIRED'
2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_local_address</code> = 'node8018-2:33061'
2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_group_seeds</code> = 'node8018-1:33061'
2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_exit_state_action</code> = 'READ_ONLY'
2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_consistency</code> = 'EVENTUAL'
2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_member_expel_timeout</code> = 0
2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_start_on_boot</code> = 'ON'
2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>auto_increment_increment</code> = 1
2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>auto_increment_offset</code> = 2
2019-11-01 08:26:03: Info: node8018-2:3306: CHANGE MASTER TO MASTER_USER = /*(*/ 'mysql_innodb_cluster_1002' /*)*/, MASTER_PASSWORD = **** FOR CHANNEL 'group_replication_recovery'
2019-11-01 08:26:03: Info: node8018-2:3306: START GROUP_REPLICATION
2019-11-01 08:26:07: Info: Waiting for GR recovery to start for node8018-2:3306...
2019-11-01 08:26:07: Info: node8018-2:3306 has started
2019-11-01 08:26:24: Info: node8018-2:3306 has started
2019-11-01 08:26:24: Info: Member node8018-2:3306 is in state OFFLINE, when recovery was expected (clone_state=Completed@2019-11-01 08:26:08.219).
2019-11-01 08:26:25: Info: Member node8018-2:3306 is in state OFFLINE, when recovery was expected (clone_state=Completed@2019-11-01 08:26:08.219).
2019-11-01 08:26:26: Info: Member node8018-2:3306 is in state OFFLINE, when recovery was expected (clone_state=Completed@2019-11-01 08:26:08.219).
2019-11-01 08:26:28: Info: node8018-1:3306: START TRANSACTION
2019-11-01 08:26:28: Info: node8018-1:3306: INSERT INTO mysql_innodb_cluster_metadata.hosts (host_name, ip_address, location) VALUES ('node8018-2', '', '')
2019-11-01 08:26:28: Info: node8018-1:3306: INSERT INTO mysql_innodb_cluster_metadata.instances (host_id, replicaset_id, mysql_server_uuid, instance_name, role, addresses, attributes) VALUES (2, 1, 'c1c8bc1b-fa24-11e9-bb89-0800278bc93f', 'node8018-2:3306', 'HA', json_object('mysqlClassic', 'node8018-2:3306', 'mysqlX', 'node8018-2:33060', 'grLocal', 'node8018-2:33061'), '{}')
2019-11-01 08:26:28: Info: node8018-1:3306: COMMIT
2019-11-01 08:26:28: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.instances SET attributes = json_set(attributes, '$.joinTime', CAST('\"2019-11-01 08:26:03.901345\"' as JSON)) WHERE mysql_server_uuid = 'c1c8bc1b-fa24-11e9-bb89-0800278bc93f'
2019-11-01 08:26:28: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.instances SET attributes = json_set(COALESCE(attributes, '{}'),  '$.recoveryAccountUser', 'mysql_innodb_cluster_1001',  '$.recoveryAccountHost', '%') WHERE mysql_server_uuid = 'c1c8bc1b-fa24-11e9-bb89-0800278bc93f'
2019-11-01 08:26:28: Info: node8018-1:3306: SET PERSIST <code>group_replication_group_seeds</code> = 'node8018-2:33061'
2019-11-01 08:26:28: Info: node8018-2:3306: CHANGE MASTER TO MASTER_USER = /*(*/ 'mysql_innodb_cluster_1002' /*)*/, MASTER_PASSWORD = **** FOR CHANNEL 'group_replication_recovery'
2019-11-01 08:26:28: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.instances SET attributes = json_set(COALESCE(attributes, '{}'),  '$.recoveryAccountUser', 'mysql_innodb_cluster_1002',  '$.recoveryAccountHost', '%') WHERE mysql_server_uuid = 'c1c8bc1b-fa24-11e9-bb89-0800278bc93f'
2019-11-01 08:26:28: Info: node8018-2:3306: SET GLOBAL <code>group_replication_clone_threshold</code> = 9223372036854775807

2019-11-01 08:26:00: Warning: SSL mode used to configure the instance: 'REQUIRED'

2019-11-01 08:26:00: Info: node8018-1:3306: SET @gtidset_a='43449d1e-fa25-11e9-8dd1-0800278bc93f:1-17,\nc00dde17-fa24-11e9-bfec-0800278bc93f:1-3'

2019-11-01 08:26:00: Info: node8018-1:3306: SET @gtidset_b='c1c8bc1b-fa24-11e9-bb89-0800278bc93f:1-3'

2019-11-01 08:26:00: Warning: A GTID set check of the MySQL instance at 'node8018-2:3306' determined that it

contains transactions that do not originate from the cluster, which must be

discarded before it can join the cluster.

2019-11-01 08:26:00: Warning: Discarding these extra GTID events can either be done manually or by completely

overwriting the state of node8018-2:3306 with a physical snapshot from an

existing cluster member. To use this method by default, set the

'recoveryMethod' option to 'clone'.

Having extra GTID events is not expected, and it is recommended to investigate

this further and ensure that the data can be removed prior to choosing the

clone recovery method.

2019-11-01 08:26:03: Info: Validating account sheller@192.168.33.%...

2019-11-01 08:26:03: Info: sheller user has 1 accounts with wildcard and 0 without

2019-11-01 08:26:03: Info: Validating InnoDB page size of instance 'node8018-2:3306'.

2019-11-01 08:26:03: Info: Checking if performance_schema is enabled on instance 'node8018-2:3306'.

2019-11-01 08:26:03: Info: Validating configuration of node8018-2:3306 (mycnf = )

2019-11-01 08:26:03: Info: Using Group Replication local address: node8018-2:33061

2019-11-01 08:26:03: Info: Using Group Replication exit state action: READ_ONLY

2019-11-01 08:26:03: Info: Using Group Replication failover consistency: EVENTUAL

2019-11-01 08:26:03: Info: Using Group Replication expel timeout: 0

2019-11-01 08:26:03: Info: node8018-2:3306: INSTALL PLUGIN <code>group_replication</code> SONAME 'group_replication.so'

2019-11-01 08:26:03: Info: node8018-2:3306: SET GLOBAL <code>group_replication_clone_threshold</code> = 1

2019-11-01 08:26:03: Info: node8018-2:3306: RESET MASTER

2019-11-01 08:26:03: Info: Installing the clone plugin on instance 'node8018-2:3306'.

2019-11-01 08:26:03: Info: node8018-2:3306: INSTALL PLUGIN <code>clone</code> SONAME 'mysql_clone.so'

2019-11-01 08:26:03: Info: Installing the clone plugin on instance 'node8018-1:3306'.

2019-11-01 08:26:03: Info: node8018-1:3306: GRANT BACKUP_ADMIN ON *.* TO 'mysql_innodb_cluster_1001'@'%'

2019-11-01 08:26:03: Info: Creating recovery account 'mysql_innodb_cluster_1002'@'%' for instance 'node8018-2:3306'

2019-11-01 08:26:03: Info: node8018-1:3306: CREATE USER IF NOT EXISTS 'mysql_innodb_cluster_1002'@'%' IDENTIFIED BY **** PASSWORD EXPIRE NEVER

2019-11-01 08:26:03: Info: node8018-1:3306: GRANT REPLICATION SLAVE, BACKUP_ADMIN ON *.* TO 'mysql_innodb_cluster_1002'@'%'

2019-11-01 08:26:03: Info: Joining 'node8018-2' to ReplicaSet using account sheller to peer 'node8018-1:3306'.

2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_group_name</code> = '43449d1e-fa25-11e9-8dd1-0800278bc93f'

2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_single_primary_mode</code> = 'ON'

2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_recovery_use_ssl</code> = 'ON'

2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_ssl_mode</code> = 'REQUIRED'

2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_local_address</code> = 'node8018-2:33061'

2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_group_seeds</code> = 'node8018-1:33061'

2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_exit_state_action</code> = 'READ_ONLY'

2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_consistency</code> = 'EVENTUAL'

2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_member_expel_timeout</code> = 0

2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>group_replication_start_on_boot</code> = 'ON'

2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>auto_increment_increment</code> = 1

2019-11-01 08:26:03: Info: node8018-2:3306: SET PERSIST <code>auto_increment_offset</code> = 2

2019-11-01 08:26:03: Info: node8018-2:3306: CHANGE MASTER TO MASTER_USER = /*(*/ 'mysql_innodb_cluster_1002' /*)*/, MASTER_PASSWORD = **** FOR CHANNEL 'group_replication_recovery'

2019-11-01 08:26:03: Info: node8018-2:3306: START GROUP_REPLICATION

2019-11-01 08:26:07: Info: Waiting for GR recovery to start for node8018-2:3306...

2019-11-01 08:26:07: Info: node8018-2:3306 has started

2019-11-01 08:26:24: Info: node8018-2:3306 has started

2019-11-01 08:26:24: Info: Member node8018-2:3306 is in state OFFLINE, when recovery was expected (clone_state=Completed@2019-11-01 08:26:08.219).

2019-11-01 08:26:25: Info: Member node8018-2:3306 is in state OFFLINE, when recovery was expected (clone_state=Completed@2019-11-01 08:26:08.219).

2019-11-01 08:26:26: Info: Member node8018-2:3306 is in state OFFLINE, when recovery was expected (clone_state=Completed@2019-11-01 08:26:08.219).

2019-11-01 08:26:28: Info: node8018-1:3306: START TRANSACTION

2019-11-01 08:26:28: Info: node8018-1:3306: INSERT INTO mysql_innodb_cluster_metadata.hosts (host_name, ip_address, location) VALUES ('node8018-2', '', '')

2019-11-01 08:26:28: Info: node8018-1:3306: INSERT INTO mysql_innodb_cluster_metadata.instances (host_id, replicaset_id, mysql_server_uuid, instance_name, role, addresses, attributes) VALUES (2, 1, 'c1c8bc1b-fa24-11e9-bb89-0800278bc93f', 'node8018-2:3306', 'HA', json_object('mysqlClassic', 'node8018-2:3306', 'mysqlX', 'node8018-2:33060', 'grLocal', 'node8018-2:33061'), '{}')

2019-11-01 08:26:28: Info: node8018-1:3306: COMMIT

2019-11-01 08:26:28: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.instances SET attributes = json_set(attributes, '$.joinTime', CAST('\"2019-11-01 08:26:03.901345\"' as JSON)) WHERE mysql_server_uuid = 'c1c8bc1b-fa24-11e9-bb89-0800278bc93f'

2019-11-01 08:26:28: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.instances SET attributes = json_set(COALESCE(attributes, '{}'), '$.recoveryAccountUser', 'mysql_innodb_cluster_1001', '$.recoveryAccountHost', '%') WHERE mysql_server_uuid = 'c1c8bc1b-fa24-11e9-bb89-0800278bc93f'

2019-11-01 08:26:28: Info: node8018-1:3306: SET PERSIST <code>group_replication_group_seeds</code> = 'node8018-2:33061'

2019-11-01 08:26:28: Info: node8018-2:3306: CHANGE MASTER TO MASTER_USER = /*(*/ 'mysql_innodb_cluster_1002' /*)*/, MASTER_PASSWORD = **** FOR CHANNEL 'group_replication_recovery'

2019-11-01 08:26:28: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.instances SET attributes = json_set(COALESCE(attributes, '{}'), '$.recoveryAccountUser', 'mysql_innodb_cluster_1002', '$.recoveryAccountHost', '%') WHERE mysql_server_uuid = 'c1c8bc1b-fa24-11e9-bb89-0800278bc93f'

2019-11-01 08:26:28: Info: node8018-2:3306: SET GLOBAL <code>group_replication_clone_threshold</code> = 9223372036854775807

node8018-2 でCLONEプラグイン、グループレプリケーションの設定をし、node8018-1 に対してメタ情報を登録している事が分かります。

node8018-3 もクラスタに追加してみます。

 MySQL  node8018-1:33060+ ssl  JS > cluster.addInstance('sheller@node8018-3')
Please provide the password for 'sheller@node8018-3': **********
Save password for 'sheller@node8018-3'? [Y]es/[N]o/Ne[v]er (default No): Y
・・・（略）
Please select a recovery method [C]lone/[A]bort (default Abort): C
・・・（略）
The instance 'node8018-3' was successfully added to the cluster.

MySQL node8018-1:33060+ ssl JS > cluster.addInstance('sheller@node8018-3')

Please provide the password for 'sheller@node8018-3': **********

Save password for 'sheller@node8018-3'? [Y]es/[N]o/Ne[v]er (default No): Y

・・・（略）

Please select a recovery method [C]lone/[A]bort (default Abort): C

・・・（略）

The instance 'node8018-3' was successfully added to the cluster.

追加した際のログを確認してみます。

2019-11-01 08:28:45: Warning: SSL mode used to configure the instance: 'REQUIRED'
2019-11-01 08:28:45: Info: node8018-1:3306: SET @gtidset_a='43449d1e-fa25-11e9-8dd1-0800278bc93f:1-26,\nc00dde17-fa24-11e9-bfec-0800278bc93f:1-3'
2019-11-01 08:28:45: Info: node8018-1:3306: SET @gtidset_b='c3a46b45-fa24-11e9-aad3-0800278bc93f:1-3'
2019-11-01 08:28:45: Warning: A GTID set check of the MySQL instance at 'node8018-3:3306' determined that it
contains transactions that do not originate from the cluster, which must be
discarded before it can join the cluster.
2019-11-01 08:28:45: Warning: Discarding these extra GTID events can either be done manually or by completely
overwriting the state of node8018-3:3306 with a physical snapshot from an
existing cluster member. To use this method by default, set the
'recoveryMethod' option to 'clone'.

Having extra GTID events is not expected, and it is recommended to investigate
this further and ensure that the data can be removed prior to choosing the
clone recovery method.
2019-11-01 08:28:48: Info: Validating account sheller@192.168.33.%...
2019-11-01 08:28:48: Info: sheller user has 1 accounts with wildcard and 0 without
2019-11-01 08:28:48: Info: Validating InnoDB page size of instance 'node8018-3:3306'.
2019-11-01 08:28:48: Info: Checking if performance_schema is enabled on instance 'node8018-3:3306'.
2019-11-01 08:28:48: Info: Validating configuration of node8018-3:3306 (mycnf = )
2019-11-01 08:28:48: Info: Using Group Replication local address: node8018-3:33061
2019-11-01 08:28:48: Info: Using Group Replication exit state action: READ_ONLY
2019-11-01 08:28:48: Info: Using Group Replication failover consistency: EVENTUAL
2019-11-01 08:28:48: Info: Using Group Replication expel timeout: 0
2019-11-01 08:28:48: Info: node8018-3:3306: INSTALL PLUGIN <code>group_replication</code> SONAME 'group_replication.so'
2019-11-01 08:28:48: Info: node8018-3:3306: SET GLOBAL <code>group_replication_clone_threshold</code> = 1
2019-11-01 08:28:48: Info: node8018-3:3306: RESET MASTER
2019-11-01 08:28:48: Info: Installing the clone plugin on instance 'node8018-3:3306'.
2019-11-01 08:28:48: Info: node8018-3:3306: INSTALL PLUGIN <code>clone</code> SONAME 'mysql_clone.so'
2019-11-01 08:28:48: Info: Installing the clone plugin on instance 'node8018-1:3306'.
2019-11-01 08:28:48: Info: node8018-1:3306: GRANT BACKUP_ADMIN ON *.* TO 'mysql_innodb_cluster_1001'@'%'
2019-11-01 08:28:48: Info: Installing the clone plugin on instance 'node8018-2:3306'.
2019-11-01 08:28:48: Info: node8018-1:3306: GRANT BACKUP_ADMIN ON *.* TO 'mysql_innodb_cluster_1002'@'%'
2019-11-01 08:28:48: Info: Creating recovery account 'mysql_innodb_cluster_1003'@'%' for instance 'node8018-3:3306'
2019-11-01 08:28:48: Info: node8018-1:3306: CREATE USER IF NOT EXISTS 'mysql_innodb_cluster_1003'@'%' IDENTIFIED BY **** PASSWORD EXPIRE NEVER
2019-11-01 08:28:48: Info: node8018-1:3306: GRANT REPLICATION SLAVE, BACKUP_ADMIN ON *.* TO 'mysql_innodb_cluster_1003'@'%'
2019-11-01 08:28:48: Info: Joining 'node8018-3' to ReplicaSet using account sheller to peer 'node8018-1:3306'.
2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_group_name</code> = '43449d1e-fa25-11e9-8dd1-0800278bc93f'
2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_single_primary_mode</code> = 'ON'
2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_recovery_use_ssl</code> = 'ON'
2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_ssl_mode</code> = 'REQUIRED'
2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_local_address</code> = 'node8018-3:33061'
2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_group_seeds</code> = 'node8018-1:33061,node8018-2:33061'
2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_exit_state_action</code> = 'READ_ONLY'
2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_consistency</code> = 'EVENTUAL'
2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_member_expel_timeout</code> = 0
2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_start_on_boot</code> = 'ON'
2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>auto_increment_increment</code> = 1
2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>auto_increment_offset</code> = 2
2019-11-01 08:28:48: Info: node8018-3:3306: CHANGE MASTER TO MASTER_USER = /*(*/ 'mysql_innodb_cluster_1003' /*)*/, MASTER_PASSWORD = **** FOR CHANNEL 'group_replication_recovery'
2019-11-01 08:28:48: Info: node8018-3:3306: START GROUP_REPLICATION
2019-11-01 08:28:53: Info: Waiting for GR recovery to start for node8018-3:3306...
2019-11-01 08:28:53: Info: node8018-3:3306 has started
2019-11-01 08:29:09: Info: node8018-3:3306 has started
2019-11-01 08:29:09: Info: Member node8018-3:3306 is in state OFFLINE, when recovery was expected (clone_state=Completed@2019-11-01 08:28:54.169).
2019-11-01 08:29:10: Info: Member node8018-3:3306 is in state OFFLINE, when recovery was expected (clone_state=Completed@2019-11-01 08:28:54.169).
2019-11-01 08:29:11: Info: Member node8018-3:3306 is in state OFFLINE, when recovery was expected (clone_state=Completed@2019-11-01 08:28:54.169).
2019-11-01 08:29:13: Info: node8018-1:3306: START TRANSACTION
2019-11-01 08:29:13: Info: node8018-1:3306: INSERT INTO mysql_innodb_cluster_metadata.hosts (host_name, ip_address, location) VALUES ('node8018-3', '', '')
2019-11-01 08:29:13: Info: node8018-1:3306: INSERT INTO mysql_innodb_cluster_metadata.instances (host_id, replicaset_id, mysql_server_uuid, instance_name, role, addresses, attributes) VALUES (3, 1, 'c3a46b45-fa24-11e9-aad3-0800278bc93f', 'node8018-3:3306', 'HA', json_object('mysqlClassic', 'node8018-3:3306', 'mysqlX', 'node8018-3:33060', 'grLocal', 'node8018-3:33061'), '{}')
2019-11-01 08:29:13: Info: node8018-1:3306: COMMIT
2019-11-01 08:29:13: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.instances SET attributes = json_set(attributes, '$.joinTime', CAST('\"2019-11-01 08:28:48.623015\"' as JSON)) WHERE mysql_server_uuid = 'c3a46b45-fa24-11e9-aad3-0800278bc93f'
2019-11-01 08:29:13: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.instances SET attributes = json_set(COALESCE(attributes, '{}'),  '$.recoveryAccountUser', 'mysql_innodb_cluster_1001',  '$.recoveryAccountHost', '%') WHERE mysql_server_uuid = 'c3a46b45-fa24-11e9-aad3-0800278bc93f'
2019-11-01 08:29:13: Info: node8018-1:3306: SET PERSIST <code>group_replication_group_seeds</code> = 'node8018-2:33061,node8018-3:33061'
2019-11-01 08:29:13: Info: node8018-2:3306: SET PERSIST <code>group_replication_group_seeds</code> = 'node8018-1:33061,node8018-3:33061'
2019-11-01 08:29:13: Info: node8018-3:3306: CHANGE MASTER TO MASTER_USER = /*(*/ 'mysql_innodb_cluster_1003' /*)*/, MASTER_PASSWORD = **** FOR CHANNEL 'group_replication_recovery'
2019-11-01 08:29:13: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.instances SET attributes = json_set(COALESCE(attributes, '{}'),  '$.recoveryAccountUser', 'mysql_innodb_cluster_1003',  '$.recoveryAccountHost', '%') WHERE mysql_server_uuid = 'c3a46b45-fa24-11e9-aad3-0800278bc93f'
2019-11-01 08:29:13: Info: node8018-3:3306: SET GLOBAL <code>group_replication_clone_threshold</code> = 9223372036854775807

2019-11-01 08:28:45: Warning: SSL mode used to configure the instance: 'REQUIRED'

2019-11-01 08:28:45: Info: node8018-1:3306: SET @gtidset_a='43449d1e-fa25-11e9-8dd1-0800278bc93f:1-26,\nc00dde17-fa24-11e9-bfec-0800278bc93f:1-3'

2019-11-01 08:28:45: Info: node8018-1:3306: SET @gtidset_b='c3a46b45-fa24-11e9-aad3-0800278bc93f:1-3'

2019-11-01 08:28:45: Warning: A GTID set check of the MySQL instance at 'node8018-3:3306' determined that it

contains transactions that do not originate from the cluster, which must be

discarded before it can join the cluster.

2019-11-01 08:28:45: Warning: Discarding these extra GTID events can either be done manually or by completely

overwriting the state of node8018-3:3306 with a physical snapshot from an

existing cluster member. To use this method by default, set the

'recoveryMethod' option to 'clone'.

Having extra GTID events is not expected, and it is recommended to investigate

this further and ensure that the data can be removed prior to choosing the

clone recovery method.

2019-11-01 08:28:48: Info: Validating account sheller@192.168.33.%...

2019-11-01 08:28:48: Info: sheller user has 1 accounts with wildcard and 0 without

2019-11-01 08:28:48: Info: Validating InnoDB page size of instance 'node8018-3:3306'.

2019-11-01 08:28:48: Info: Checking if performance_schema is enabled on instance 'node8018-3:3306'.

2019-11-01 08:28:48: Info: Validating configuration of node8018-3:3306 (mycnf = )

2019-11-01 08:28:48: Info: Using Group Replication local address: node8018-3:33061

2019-11-01 08:28:48: Info: Using Group Replication exit state action: READ_ONLY

2019-11-01 08:28:48: Info: Using Group Replication failover consistency: EVENTUAL

2019-11-01 08:28:48: Info: Using Group Replication expel timeout: 0

2019-11-01 08:28:48: Info: node8018-3:3306: INSTALL PLUGIN <code>group_replication</code> SONAME 'group_replication.so'

2019-11-01 08:28:48: Info: node8018-3:3306: SET GLOBAL <code>group_replication_clone_threshold</code> = 1

2019-11-01 08:28:48: Info: node8018-3:3306: RESET MASTER

2019-11-01 08:28:48: Info: Installing the clone plugin on instance 'node8018-3:3306'.

2019-11-01 08:28:48: Info: node8018-3:3306: INSTALL PLUGIN <code>clone</code> SONAME 'mysql_clone.so'

2019-11-01 08:28:48: Info: Installing the clone plugin on instance 'node8018-1:3306'.

2019-11-01 08:28:48: Info: node8018-1:3306: GRANT BACKUP_ADMIN ON *.* TO 'mysql_innodb_cluster_1001'@'%'

2019-11-01 08:28:48: Info: Installing the clone plugin on instance 'node8018-2:3306'.

2019-11-01 08:28:48: Info: node8018-1:3306: GRANT BACKUP_ADMIN ON *.* TO 'mysql_innodb_cluster_1002'@'%'

2019-11-01 08:28:48: Info: Creating recovery account 'mysql_innodb_cluster_1003'@'%' for instance 'node8018-3:3306'

2019-11-01 08:28:48: Info: node8018-1:3306: CREATE USER IF NOT EXISTS 'mysql_innodb_cluster_1003'@'%' IDENTIFIED BY **** PASSWORD EXPIRE NEVER

2019-11-01 08:28:48: Info: node8018-1:3306: GRANT REPLICATION SLAVE, BACKUP_ADMIN ON *.* TO 'mysql_innodb_cluster_1003'@'%'

2019-11-01 08:28:48: Info: Joining 'node8018-3' to ReplicaSet using account sheller to peer 'node8018-1:3306'.

2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_group_name</code> = '43449d1e-fa25-11e9-8dd1-0800278bc93f'

2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_single_primary_mode</code> = 'ON'

2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_recovery_use_ssl</code> = 'ON'

2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_ssl_mode</code> = 'REQUIRED'

2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_local_address</code> = 'node8018-3:33061'

2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_group_seeds</code> = 'node8018-1:33061,node8018-2:33061'

2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_exit_state_action</code> = 'READ_ONLY'

2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_consistency</code> = 'EVENTUAL'

2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_member_expel_timeout</code> = 0

2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>group_replication_start_on_boot</code> = 'ON'

2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>auto_increment_increment</code> = 1

2019-11-01 08:28:48: Info: node8018-3:3306: SET PERSIST <code>auto_increment_offset</code> = 2

2019-11-01 08:28:48: Info: node8018-3:3306: CHANGE MASTER TO MASTER_USER = /*(*/ 'mysql_innodb_cluster_1003' /*)*/, MASTER_PASSWORD = **** FOR CHANNEL 'group_replication_recovery'

2019-11-01 08:28:48: Info: node8018-3:3306: START GROUP_REPLICATION

2019-11-01 08:28:53: Info: Waiting for GR recovery to start for node8018-3:3306...

2019-11-01 08:28:53: Info: node8018-3:3306 has started

2019-11-01 08:29:09: Info: node8018-3:3306 has started

2019-11-01 08:29:09: Info: Member node8018-3:3306 is in state OFFLINE, when recovery was expected (clone_state=Completed@2019-11-01 08:28:54.169).

2019-11-01 08:29:10: Info: Member node8018-3:3306 is in state OFFLINE, when recovery was expected (clone_state=Completed@2019-11-01 08:28:54.169).

2019-11-01 08:29:11: Info: Member node8018-3:3306 is in state OFFLINE, when recovery was expected (clone_state=Completed@2019-11-01 08:28:54.169).

2019-11-01 08:29:13: Info: node8018-1:3306: START TRANSACTION

2019-11-01 08:29:13: Info: node8018-1:3306: INSERT INTO mysql_innodb_cluster_metadata.hosts (host_name, ip_address, location) VALUES ('node8018-3', '', '')

2019-11-01 08:29:13: Info: node8018-1:3306: INSERT INTO mysql_innodb_cluster_metadata.instances (host_id, replicaset_id, mysql_server_uuid, instance_name, role, addresses, attributes) VALUES (3, 1, 'c3a46b45-fa24-11e9-aad3-0800278bc93f', 'node8018-3:3306', 'HA', json_object('mysqlClassic', 'node8018-3:3306', 'mysqlX', 'node8018-3:33060', 'grLocal', 'node8018-3:33061'), '{}')

2019-11-01 08:29:13: Info: node8018-1:3306: COMMIT

2019-11-01 08:29:13: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.instances SET attributes = json_set(attributes, '$.joinTime', CAST('\"2019-11-01 08:28:48.623015\"' as JSON)) WHERE mysql_server_uuid = 'c3a46b45-fa24-11e9-aad3-0800278bc93f'

2019-11-01 08:29:13: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.instances SET attributes = json_set(COALESCE(attributes, '{}'), '$.recoveryAccountUser', 'mysql_innodb_cluster_1001', '$.recoveryAccountHost', '%') WHERE mysql_server_uuid = 'c3a46b45-fa24-11e9-aad3-0800278bc93f'

2019-11-01 08:29:13: Info: node8018-1:3306: SET PERSIST <code>group_replication_group_seeds</code> = 'node8018-2:33061,node8018-3:33061'

2019-11-01 08:29:13: Info: node8018-2:3306: SET PERSIST <code>group_replication_group_seeds</code> = 'node8018-1:33061,node8018-3:33061'

2019-11-01 08:29:13: Info: node8018-3:3306: CHANGE MASTER TO MASTER_USER = /*(*/ 'mysql_innodb_cluster_1003' /*)*/, MASTER_PASSWORD = **** FOR CHANNEL 'group_replication_recovery'

2019-11-01 08:29:13: Info: node8018-1:3306: UPDATE mysql_innodb_cluster_metadata.instances SET attributes = json_set(COALESCE(attributes, '{}'), '$.recoveryAccountUser', 'mysql_innodb_cluster_1003', '$.recoveryAccountHost', '%') WHERE mysql_server_uuid = 'c3a46b45-fa24-11e9-aad3-0800278bc93f'

2019-11-01 08:29:13: Info: node8018-3:3306: SET GLOBAL <code>group_replication_clone_threshold</code> = 9223372036854775807

node8018-2 を追加した際と特に変わりのある操作はないようです。
SQLステートメントとして出力される事で具体的な操作内容が分かり易く、各ノードのログを確認する必要もなくお手軽に確認できるのが嬉しいですね。

折角なので、MySQL Routerからクラスタノードにアクセスするまで確認しておきます。

 MySQL  node8018-1:33060+ ssl  JS > cluster.status()
{
    "clusterName": "mycluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "node8018-1:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "node8018-1:3306": {
                "address": "node8018-1:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.18"
            },
            "node8018-2:3306": {
                "address": "node8018-2:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.18"
            },
            "node8018-3:3306": {
                "address": "node8018-3:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.18"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "node8018-1:3306"
}
 MySQL  node8018-1:33060+ ssl  JS >

MySQL node8018-1:33060+ ssl JS > cluster.status()

{

"clusterName": "mycluster",

"defaultReplicaSet": {

"name": "default",

"primary": "node8018-1:3306",

"ssl": "REQUIRED",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"node8018-1:3306": {

"address": "node8018-1:3306",

"mode": "R/W",

"readReplicas": {},

"replicationLag": null,

"role": "HA",

"status": "ONLINE",

"version": "8.0.18"

"node8018-2:3306": {

"address": "node8018-2:3306",

"mode": "R/O",

"readReplicas": {},

"replicationLag": null,

"role": "HA",

"status": "ONLINE",

"version": "8.0.18"

"node8018-3:3306": {

"address": "node8018-3:3306",

"mode": "R/O",

"readReplicas": {},

"replicationLag": null,

"role": "HA",

"status": "ONLINE",

"version": "8.0.18"

}

"topologyMode": "Single-Primary"

"groupInformationSourceMember": "node8018-1:3306"

}

MySQL node8018-1:33060+ ssl JS >

正常にクラスタノードが稼働している事が確認できます。

続いて MySQL Router を起動し、アクセスしてみましょう。

 MySQL  node8018-1:33060+ ssl  JS > \q
Bye!
# mysqlrouter --bootstrap sheller@node8018-1 --user=mysqlrouter
Please enter MySQL password for sheller:  # パスワードを入力
# systemctl start mysqlrouter
# mysql -u sheller -p -h 127.0.0.1 -P 6446 -e "select @@hostname;"
Enter password:  # パスワードを入力
+------------+
| @@hostname |
+------------+
| node8018-1 |
+------------+
# mysql -u sheller -p -h 127.0.0.1 -P 6447 -e "select @@hostname;"
Enter password:  # パスワードを入力
+------------+
| @@hostname |
+------------+
| node8018-2 |
+------------+
# mysql -u sheller -p -h 127.0.0.1 -P 6447 -e "select @@hostname;"
Enter password:  # パスワードを入力
+------------+
| @@hostname |
+------------+
| node8018-3 |
+------------+

MySQL node8018-1:33060+ ssl JS > \q

Bye!

# mysqlrouter --bootstrap sheller@node8018-1 --user=mysqlrouter

Please enter MySQL password for sheller: # パスワードを入力

# systemctl start mysqlrouter

# mysql -u sheller -p -h 127.0.0.1 -P 6446 -e "select @@hostname;"

Enter password: # パスワードを入力

+------------+

| @@hostname |

+------------+

| node8018-1 |

+------------+

# mysql -u sheller -p -h 127.0.0.1 -P 6447 -e "select @@hostname;"

Enter password: # パスワードを入力

+------------+

| @@hostname |

+------------+

| node8018-2 |

+------------+

# mysql -u sheller -p -h 127.0.0.1 -P 6447 -e "select @@hostname;"

Enter password: # パスワードを入力

+------------+

| @@hostname |

+------------+

| node8018-3 |

+------------+

6446ポートへのアクセスがプライマリノードへ、6447ポートへのアクセスがセカンダリノードへ振り分けられていますね。

まとめ

MySQL Shell で InnoDB Cluster を操作するコマンドは、リファレンスに言葉としての説明はありますが、自分が思ったような挙動をしない場合もありました。
そんな時は、今回確認した --dba-log-sql オプションで実行しているSQLステートメントをログに出力して確認する事で、内部的な動作を理解するのに役立つように思います。

SQLステートメントを出力する以外にも、–log-level オプションでデフォルト設定より詳細なログ情報を出力する事もできるので、困った時は MySQL Shell のログを確認してみましょう。

よかったらシェアしてね！

URLをコピーしました！

MySQL Shell のログを活用しよう

1. 構成

2. 構築

2-1. 共通の設定

2-2. クラスタノードの設定

2-3. MySQL Router + MySQL Shell の設定

クラスタの作成

クラスタにノードを追加

まとめ

スマートスタイルTECHブログについて

関連記事