mysql-shell 测试

原创黄江平 2021-07-06

2265

1.部署sandbox实例
MySQL JS > dba.deploySandboxInstance(3310)
A new MySQL sandbox instance will be created on this host in
/root/mysql-sandboxes/3310

Warning: Sandbox instances are only suitable for deploying and
running on your local machine for testing purposes and are not
accessible from external networks.

Please enter a MySQL root password for the new instance: ****

Deploying new MySQL instance…

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

2.创建 innodb cluster
MySQL JS > \connect root@localhost:3310
Creating a session to ‘root@localhost:3310’
Please provide the password for ‘root@localhost:3310’: ****
Save password for ‘root@localhost:3310’? [Y]es/[N]o/Ne[v]er (default No): yes
Fetching schema names for autocompletion… Press ^C to stop.
Your MySQL connection id is 12
Server version: 8.0.22 MySQL Community Server - GPL
No default schema selected; type \use to set one.

MySQL localhost:3310 ssl JS > var cluster = dba.createCluster(‘testCluster’)
A new InnoDB cluster will be created on instance ‘localhost:3310’.

Validating instance configuration at localhost:3310…
NOTE: Instance detected as a sandbox.
Please note that sandbox instances are only suitable for deploying test clusters for use within the same host.

This instance reports its own address as 127.0.0.1:3310

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using ‘127.0.0.1:33101’. Use the localAddress option to override.

Creating InnoDB cluster ‘testCluster’ on ‘127.0.0.1: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.

创建innodb cluster 时，提示说至少要3台DB实例，我再添加2台
MySQL localhost:3310 ssl JS > cluster.addInstance(‘root@127.0.0.1:3311’)

NOTE: The target instance ‘127.0.0.1:3311’ has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of ‘127.0.0.1:3311’ with a physical snapshot from an existing cluster member. To use this method by default, set the ‘recoveryMethod’ option to ‘clone’.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the ‘recoveryMethod’ option to ‘incremental’.

Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone):
Validating instance configuration at 127.0.0.1:3311…
NOTE: Instance detected as a sandbox.
Please note that sandbox instances are only suitable for deploying test clusters for use within the same host.

This instance reports its own address as 127.0.0.1:3311

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using ‘127.0.0.1:33111’. Use the localAddress option to override.

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…

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.

Waiting for clone to finish…
NOTE: 127.0.0.1:3311 is being cloned from 127.0.0.1:3310
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed

NOTE: 127.0.0.1:3311 is shutting down…

Waiting for server restart… ready
127.0.0.1:3311 has restarted, waiting for clone to finish…
** Stage RESTART: Completed
Clone process has finished: 59.62 MB transferred in about 1 second (~59.62 MB/s)

State recovery already finished for ‘127.0.0.1:3311’

The instance ‘127.0.0.1:3311’ was successfully added to the cluster.

MySQL localhost:3310 ssl JS > cluster.addInstance(‘root@127.0.0.1:3312’)

NOTE: The target instance ‘127.0.0.1:3312’ has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of ‘127.0.0.1:3312’ with a physical snapshot from an existing cluster member. To use this method by default, set the ‘recoveryMethod’ option to ‘clone’.

Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone):
Validating instance configuration at 127.0.0.1:3312…
NOTE: Instance detected as a sandbox.
Please note that sandbox instances are only suitable for deploying test clusters for use within the same host.

This instance reports its own address as 127.0.0.1:3312

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using ‘127.0.0.1:33121’. Use the localAddress option to override.

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…

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.

Waiting for clone to finish…
NOTE: 127.0.0.1:3312 is being cloned from 127.0.0.1:3311
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed

NOTE: 127.0.0.1:3312 is shutting down…

Waiting for server restart… ready
127.0.0.1:3312 has restarted, waiting for clone to finish…
** Stage RESTART: Completed
Clone process has finished: 59.62 MB transferred in about 1 second (~59.62 MB/s)

State recovery already finished for ‘127.0.0.1:3312’

The instance ‘127.0.0.1:3312’ was successfully added to the cluster.

3.检查innodb cluster状态
MySQL localhost:3310 ssl JS > cluster.status()
{
“clusterName”: “testCluster”,
“defaultReplicaSet”: {
“name”: “default”,
“primary”: “127.0.0.1:3310”,
“ssl”: “REQUIRED”,
“status”: “OK”,
“statusText”: “Cluster is ONLINE and can tolerate up to ONE failure.”,
“topology”: {
“127.0.0.1:3310”: {
“address”: “127.0.0.1:3310”,
“memberRole”: “PRIMARY”,
“mode”: “R/W”,
“readReplicas”: {},
“replicationLag”: null,
“role”: “HA”,
“status”: “ONLINE”,
“version”: “8.0.22”
},
“127.0.0.1:3311”: {
“address”: “127.0.0.1:3311”,
“memberRole”: “SECONDARY”,
“mode”: “R/O”,
“readReplicas”: {},
“replicationLag”: null,
“role”: “HA”,
“status”: “ONLINE”,
“version”: “8.0.22”
},
“127.0.0.1:3312”: {
“address”: “127.0.0.1:3312”,
“memberRole”: “SECONDARY”,
“mode”: “R/O”,
“readReplicas”: {},
“replicationLag”: null,
“role”: “HA”,
“status”: “ONLINE”,
“version”: “8.0.22”
}
},
“topologyMode”: “Single-Primary”
},
“groupInformationSourceMember”: “127.0.0.1:3310”
}

4.配置并行复制进程

在开并行前，相关的参数还是要作相应的设置
binlog_transaction_dependency_tracking=WRITESET
slave_preserve_commit_order=ON
slave_parallel_type=LOGICAL_CLOCK
transaction_write_set_extraction=XXHASH64

提示说要8.0.23版本才能如下操作，我的是8.0.22版本
MySQL localhost:3310 ssl JS > dba.configureInstance(‘root@127.0.0.1:3311’, {applierWorkerThreads: 8, restart: true})
The instance ‘127.0.0.1:3311’ belongs to an InnoDB Cluster.
Configuring local MySQL instance listening at port 3311 for use in an InnoDB cluster…
NOTE: Instance detected as a sandbox.
Please note that sandbox instances are only suitable for deploying test clusters for use within the same host.

This instance reports its own address as 127.0.0.1:3311
Dba.configureInstance: Option ‘applierWorkerThreads’ not supported on target server version: ‘8.0.22’ (RuntimeError)

5.Mysql router
MySQL router是InnoDB集群的一部分，是一种轻量级的中间件，
它提供了应用程序和后端MySQL服务器之间透明的路由。它可以用于各种各样的用例，
例如通过有效地将数据库流量路由到适当的后端MySQL服务器来提供高可用性和可伸缩性。
可插式架构还允许开发人员为自定义用例扩展MySQL router。
5.1设置mysql router 账号
MySQL localhost:3310 ssl mysql JS > var cluster1 = dba.getCluster(‘testCluster’, {connectToPrimary:false})
MySQL localhost:3310 ssl mysql JS > cluster1.setupRouterAccount(‘myRouter1’)

Missing the password for new account myRouter1@%. Please provide one.
Password for new account: *********
Confirm password:
Passwords don’t match, please try again.
Password for new account: *********
Confirm password: *********

Creating user myRouter1@%.
Account myRouter1@% was successfully created.
5.2部署mysql router
用root账号启动mysql router 会自动创建mysql router的配置文件，
[root@localhost bin]# ./mysqlrouter --bootstrap root@127.0.0.1:3310 --user=root
Please enter MySQL password for root:

Reconfiguring system MySQL Router instance…

Fetching password for current account (mysql_router1_usuwxmqg8xse) from keyring
Creating account(s) (only those that are needed, if any)
Using existing certificates from the ‘/usr/local/mysql-router/var/lib/mysqlrouter’ directory
Verifying account (using it to run SQL queries that would be run by Router)
Storing account in keyring
Adjusting permissions of generated files
Creating configuration /usr/local/mysql-router/mysqlrouter.conf

Existing configuration backed up to ‘/usr/local/mysql-router/mysqlrouter.conf.bak’

MySQL Router configured for the InnoDB Cluster ‘testCluster’

After this MySQL Router has been started with the generated configuration

$ /etc/init.d/mysqlrouter restart

or
$ systemctl start mysqlrouter
or
$ ./mysqlrouter -c /usr/local/mysql-router/mysqlrouter.conf

the cluster ‘testCluster’ can be reached by connecting to:

MySQL Classic protocol

Read/Write Connections: localhost:6446
Read/Only Connections: localhost:6447

MySQL X protocol

Read/Write Connections: localhost:6448
Read/Only Connections: localhost:6449

5.3启动mysql router 并验证
启动mysql router
[root@localhost bin]# ./mysqlrouter &
[1] 2221

验证mysql rouer的连接
这个6446端口是mysql router配置的端口，在配置文件/usr/local/mysql-router/mysqlrouter.conf中
[root@localhost bin]# ./mysqlsh --uri root@127.0.0.1:6446
Please provide the password for ‘root@127.0.0.1:6446’: ****
Save password for ‘root@127.0.0.1:6446’? [Y]es/[N]o/Ne[v]er (default No): yes
MySQL Shell 8.0.24

Type ‘\help’ or ‘?’ for help; ‘\quit’ to exit.
Creating a session to ‘root@127.0.0.1:6446’
Fetching schema names for autocompletion… Press ^C to stop.
Your MySQL connection id is 644
Server version: 8.0.22 MySQL Community Server - GPL
No default schema selected; type \use to set one.
MySQL 127.0.0.1:6446 ssl JS > \sql
Switching to SQL mode… Commands end with ;
MySQL 127.0.0.1:6446 ssl SQL > select @@port;
±-------+
| @@port |
±-------+
| 3310 |
±-------+
1 row in set (0.0004 sec)

6.故障切换
关掉主节点
MySQL JS > dba.killSandboxInstance(3310)

Killing MySQL instance…

Instance localhost:3310 successfully killed.
通过mysql router连接，发现已经连接到3311端品的实例了
./mysqlsh --uri root@127.0.0.1:6446
MySQL Shell 8.0.24

Type ‘\help’ or ‘?’ for help; ‘\quit’ to exit.
Creating a session to ‘root@127.0.0.1:6446’
Fetching schema names for autocompletion… Press ^C to stop.
Your MySQL connection id is 294
Server version: 8.0.22 MySQL Community Server - GPL
No default schema selected; type \use to set one.
MySQL 127.0.0.1:6446 ssl JS > \sql
Switching to SQL mode… Commands end with ;
MySQL 127.0.0.1:6446 ssl SQL > select @@port;
±-------+
| @@port |
±-------+
| 3311 |
±-------+
1 row in set (0.0004 sec)

检查inndb cluster集群，发现一台已经不可用了
MySQL 127.0.0.1:6446 ssl SQL > \js
Switching to JavaScript mode…
MySQL 127.0.0.1:6446 ssl JS > var cluster1 = dba.getCluster(‘testCluster’, {connectToPrimary:false})
MySQL 127.0.0.1:6446 ssl JS > cluster1.status()
{
“clusterName”: “testCluster”,
“defaultReplicaSet”: {
“name”: “default”,
“primary”: “127.0.0.1:3311”,
“ssl”: “REQUIRED”,
“status”: “OK_NO_TOLERANCE”,
“statusText”: “Cluster is NOT tolerant to any failures. 1 member is not active.”,
“topology”: {
“127.0.0.1:3310”: {
“address”: “127.0.0.1:3310”,
“memberRole”: “SECONDARY”,
“mode”: “n/a”,
“readReplicas”: {},
“role”: “HA”,
“shellConnectError”: “MySQL Error 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1:3310’ (111)”,
“status”: “(MISSING)”
},
“127.0.0.1:3311”: {
“address”: “127.0.0.1:3311”,
“memberRole”: “PRIMARY”,
“mode”: “R/W”,
“readReplicas”: {},
“replicationLag”: null,
“role”: “HA”,
“status”: “ONLINE”,
“version”: “8.0.22”
},
“127.0.0.1:3312”: {
“address”: “127.0.0.1:3312”,
“memberRole”: “SECONDARY”,
“mode”: “R/O”,
“readReplicas”: {},
“replicationLag”: null,
“role”: “HA”,
“status”: “ONLINE”,
“version”: “8.0.22”
}
},
“topologyMode”: “Single-Primary”
},
“groupInformationSourceMember”: “127.0.0.1:3311”
}

6.总结

mysql shell 除了提供的SQL功能，类似于mysql, mysql Shell还提供了JavaScript和Python的脚本功能，并包含了用于处理mysql的api，还是很方便的，可以参考官方文档https://dev.mysql.com/doc/mysql-shell/8.0/en/

mysql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

mysql-shell 测试

Reconfiguring system MySQL Router instance…

MySQL Router configured for the InnoDB Cluster ‘testCluster’

MySQL Classic protocol

MySQL X protocol

评论