[昌哥IT课堂]使用MySQL Shell 部署沙盒数据库实例详解

概述:
这部分解释了如何使用AdminAPI设置沙盒部署。部署和使用本地MySQL的沙盒实例是开始探索AdminAPI的好方法。在将功能部署到生产服务器之前，您可以在本地测试功能。AdminAPI具有内置功能，用于创建正确配置的沙箱实例，以便在本地部署的情况下与InnoDB Cluster、InnoDB ClusterSet和InnoDB ReplicaSet一起工作。
与生产部署不同，在生产部署中，您使用实例并通过连接字符串指定它们，而沙箱实例在与运行MySQL Shell的同一台机器上本地运行。要选择一个沙箱实例，您需要提供MySQL沙箱实例正在侦听的端口号。

一、部署跟本地数据库版本一样的沙盒实例：
启动mysqlsh
[root@node223 ~]# mysqlsh --py
MySQL Shell 8.4.1

1.创建一个沙盒实例：
MySQL Py > dba.deploy_sandbox_instance(3300);
A new MySQL sandbox instance will be created on this host in
/root/mysql-sandboxes/3300 #这个是数据存储的目录

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:3300 successfully deployed and started.
Use shell.connect('root@localhost:3300') to connect to the instance.

2.查看mysql-shell创建的相关目录和文件：
[root@node223 3300]# pwd
/root/mysql-sandboxes/3300
[root@node223 3300]# ls
3300.pid bin lib64 my.cnf mysql-files sandboxdata start.sh stop.sh

3.实例创建完成后，通过mysql-shell登录到新创建的实例中，端口号为3305
MySQL Py > \connect root@localhost:3300
Creating a session to 'root@localhost:3300'
Please provide the password for 'root@localhost:3300': ***********
Save password for 'root@localhost:3300'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 14
Server version: 8.0.33 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

MySQL localhost:3300 ssl Py > \sql #切换到SQL模式下，查看数据库的情况
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
4.查看数据库版本：
MySQL localhost:3350 ssl SQL > select version();
+-----------+
| version() |
+-----------+
| 8.0.33 |
+-----------+
1 row in set (0.0003 sec)

5.创建一个测试库和一张测试表：
MySQL localhost:3300 ssl SQL > create database test_3300;
Query OK, 1 row affected (0.0037 sec)
MySQL localhost:3300 ssl SQL > use test_3300;
Default schema set to `test_3300`.
Fetching global names, object names from `test_3300` for auto-completion... Press ^C to stop.
MySQL localhost:3300 ssl test_3300 SQL > create table t1(id int);
Query OK, 0 rows affected (0.0194 sec)
MySQL localhost:3300 ssl test_3300 SQL > show tables;
+---------------------+
| Tables_in_test_3300 |
+---------------------+
| t1 |
+---------------------+
1 row in set (0.0031 sec)

6.查看已创建的物理文件：
[root@node223 sandboxdata]# pwd
/root/mysql-sandboxes/3300/sandboxdata
[root@node223 sandboxdata]# ls
auto.cnf #ib_16384_0.dblwr #innodb_temp mysqlx.sock.lock public_key.pem undo_002
ca-key.pem #ib_16384_1.dblwr mysql node223-bin.000001 server-cert.pem
ca.pem ib_buffer_pool mysqld.sock node223-bin.000002 server-key.pem
client-cert.pem ibdata1 mysqld.sock.lock node223-bin.index sys
client-key.pem ibtmp1 mysql.ibd performance_schema test_3300
error.log #innodb_redo mysqlx.sock private_key.pem undo_001
[root@node223 test_3300]# pwd
/root/mysql-sandboxes/3300/sandboxdata/test_3300
[root@node223 test_3300]# ls
t1.ibd

以上显示，确定是在沙盒实例下创建了test_3300库和t1表

7.查看新实例的进程信息：
[root@node223 test_3300]# ps -ef | grep mysql
mysql 5675 1 0 17:10 ? 00:00:17 /usr/sbin/mysqld
root 6026 5982 0 17:14 pts/0 00:00:00 mysqlsh --py
root 6078 1 0 17:14 pts/0 00:00:00 /bin/bash /root/mysql-sandboxes/3300/start.sh --user=root
root 6079 6078 0 17:14 pts/0 00:00:15 /root/mysql-sandboxes/3300/bin/mysqld --defaults-file=/root/mysql-sandboxes/3300/my.cnf --user=root
root 6174 6131 0 17:46 pts/1 00:00:00 grep --color=auto mysql

要部署另一个沙盒服务器实例，请重复为端口3300的沙盒实例所遵循的步骤，为每个实例选择不同的端口号。

二、在同一台主机上部署多个不同版本 MySQL沙盒实例：
如果想同时部署多个不同版本 MySQL实例，只需要把对应版本的 mysqld 路径放入$PATH即可。
1.创建MySQL5.7.36的沙合实例
MySQL5.7.36安装包目录为：/usr/local/mysql/bin
添加这个目录的子目录 bin 到环境变量 $PATH 即可：
[root@node223 ~]# export PATH=/usr/local/mysql/bin:$PATH

2.重新进入 MySQL Shell 环境，和上面 MySQL 8.4.1相同的部署方式：
[root@node223 ~]# mysqlsh --py
MySQL Shell 8.4.1

Copyright (c) 2016, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
MySQL Py > dba.deploy_sandbox_instance(3351)
A new MySQL sandbox instance will be created on this host in
/root/mysql-sandboxes/3351

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:3351 successfully deployed and started.
Use shell.connect('root@localhost:3351') to connect to the instance.

3.登录已创建的实例，查看数据库的版本：
MySQL Py > \connect root@localhost:3351
Creating a session to 'root@localhost:3351'
Please provide the password for 'root@localhost:3351': ***********
Save password for 'root@localhost:3351'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 7
Server version: 5.7.36-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
MySQL localhost:3351 ssl Py > \sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
MySQL localhost:3351 ssl SQL > select version();
+------------+
| version() |
+------------+
| 5.7.36-log |
+------------+
1 row in set (0.0005 sec)

4.创建测试库表：
MySQL localhost:3351 ssl SQL > create database test;
Query OK, 1 row affected (0.0014 sec)
MySQL localhost:3351 ssl SQL > use test;
Default schema set to `test`.
Fetching global names, object names from `test` for auto-completion... Press ^C to stop.
MySQL localhost:3351 ssl test SQL > create table t(id int);
Query OK, 0 rows affected (0.0086 sec)
MySQL localhost:3351 ssl test SQL > show tables;
+----------------+
| Tables_in_test |
+----------------+
| t |
+----------------+
1 row in set (0.0007 sec)

5.查看数据库的相关文件：
[root@node223 3351]# pwd
/root/mysql-sandboxes/3351
[root@node223 3351]# cd sandboxdata/
[root@node223 sandboxdata]# ls
auto.cnf client-key.pem ib_logfile0 mysqld.sock node223-bin.000001 private_key.pem sys
ca-key.pem error.log ib_logfile1 mysqld.sock.lock node223-bin.000002 public_key.pem test
ca.pem ib_buffer_pool ibtmp1 mysqlx.sock node223-bin.index server-cert.pem
client-cert.pem ibdata1 mysql mysqlx.sock.lock performance_schema server-key.pem

6.查看相关的进程情况：
[root@node223 sandboxdata]# ps -ef | grep mysqld
mysql 5675 1 0 17:10 ? 00:00:27 /usr/sbin/mysqld
root 6079 6078 0 17:14 pts/0 00:00:24 /root/mysql-sandboxes/3300/bin/mysqld --defaults-file=/root/mysql-sandboxes/3300/my.cnf --user=root
root 6251 6250 0 18:05 pts/0 00:00:00 /root/mysql-sandboxes/3351/bin/mysqld --defaults-file=/root/mysql-sandboxes/3351/my.cnf --user=root
root 6317 6131 0 18:08 pts/1 00:00:00 grep --color=auto mysqld

以上就在一台主机上部署了两个不同版本的数据库实例

三、更改部署实例的基本目录：
默认部署实例文件在~/mysql-sandboxes下，按照实例端口划分，每个端口一个子目录。比如之前部署的两个 MySQL 实例，分别对应目录 /root/mysql-sandboxes/3300、/root/mysql-sandboxes/3351 。

有两种方法可以更改部署实例的基本目录：
方式一：
调用 dba.deploy_sandbox_instance 时，显式指定部署目录：
- sandboxDir: path where the new instance will be deployed.

例如部署一个新实例3352，指定基本目录为： /usr/local/mysql-sandboxes
这个目录要事先创建好：
[root@node223 ~]# mkdir /usr/local/mysql-sandboxes
[root@node223 ~]# mysqlsh --py
MySQL Shell 8.4.1

Copyright (c) 2016, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
MySQL Py > dba.deploy_sandbox_instance(3352,{"sandboxDir":"/usr/local/mysql-sandboxes"})
A new MySQL sandbox instance will be created on this host in
/usr/local/mysql-sandboxes/3352

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:3352 successfully deployed and started.
Use shell.connect('root@localhost:3352') to connect to the instance.

配置完成后，在操作系统指定的目录生成了数据库相关文件：
[root@node223 mysql-sandboxes]# pwd
/usr/local/mysql-sandboxes
[root@node223 mysql-sandboxes]# ls
3352

此方法最大的缺点就是对于后续新实例的部署不具备通用性，需要针对每个新实例分别指定 sandboxDir 选项才可以。如果不显式指定，则继续使用默认目录：~/mysql-sandboxes。例如下面部署实例3600，依然使用默认目录。
MySQL Py > dba.deploy_sandbox_instance(3600);
A new MySQL sandbox instance will be created on this host in
/root/mysql-sandboxes/3600

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:3600 successfully deployed and started.
Use shell.connect('root@localhost:3600') to connect to the instance.

当没有指定文件目录时，默认使用~/mysql-sandboxes做为数据库文件的目录
[root@node223 3600]# pwd
/root/mysql-sandboxes/3600
[root@node223 3600]# ls
3600.pid bin lib64 my.cnf mysql-files sandboxdata start.sh stop.sh

方式二：
直接在 MySQL Shell 的Shell 组件里指定基本部署目录，这种配置为全局有效。

2. 显式设置shell 组件的 options 字典属性，修改 KEY 名为 sandboxDir 的值为指定目录：
- sandboxDir: default path where the new sandbox instances for InnoDB cluster will be deployed

设置 sandboxDir 为 /usr/local/mysql-sandboxes：
--persist 表示永久生效
MySQL Py > \option --persist sandboxDir /usr/local/mysql-sandboxes
退出当前会话

重新进入 MySQL Shell 环境，部署两个新实例，对应端口分别为3353和3354：这两个实例都被部署在目录/tmp/mysql-sandbox下。

MySQL Py > dba.deploy_sandbox_instance(3353);
A new MySQL sandbox instance will be created on this host in
/usr/local/mysql-sandboxes/3353

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:3353 successfully deployed and started.
Use shell.connect('root@localhost:3353') to connect to the instance.

MySQL Py > dba.deploy_sandbox_instance(3355);
A new MySQL sandbox instance will be created on this host in
/usr/local/mysql-sandboxes/3355

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:3355 successfully deployed and started.
Use shell.connect('root@localhost:3355') to connect to the instance.

查看指定数据库目录情况：
[root@node223 mysql-sandboxes]# pwd
/usr/local/mysql-sandboxes
[root@node223 mysql-sandboxes]# ls
3353 3355

以上配置说明，配置的参数已生效了

四、更改新部署的实例参数：
上面部署的几个实例都没有设定具体参数，全部使用了默认值。以上将演示如只可更新参数。
更改参数有以下两种方式：

1.部署实例的同时对参数进行配置：适合更改少量参数。
比如新部署一个实例3365，分别指定以下参数：
server-id=100
tmp_table_size=128M
read_buffer_size=2M

添加这几个参数到 mysqldOptions 数组即可。
MySQL Py > dba.deploy_sandbox_instance(3365,{"mysqldOptions":["server_id=100","tmp_table_size=128M","read_buffer_size=2M"]})
A new MySQL sandbox instance will be created on this host in
/usr/local/mysql-sandboxes/3365

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:3365 successfully deployed and started.
Use shell.connect('root@localhost:3365') to connect to the instance.

在数据目录下的 my.cnf中，确定参数是否修改成功：
[root@node223 3365]# pwd
/usr/local/mysql-sandboxes/3365
[root@node223 3365]# grep "server_id\|tmp_table_size\|read_buffer_size" my.cnf
server_id = 100
tmp_table_size = 128M
read_buffer_size = 2M
说明以下配置已生效了

2. 部署实例后对参数进行配置：适合更改大量参数。
先停止数据库实例
MySQL Py > dba.stop_sandbox_instance(3365);
The MySQL sandbox instance on this host in
3365 will be stopped

Please enter the MySQL root password for the instance 'localhost:3365': ***********

Stopping MySQL instance...

Instance localhost:3365 successfully stopped.

修改3365实例对应的my.cnf文件：
[root@node223 3365]# pwd
/usr/local/mysql-sandboxes/3365
[root@node223 3365]# sed -i "s/100/200/g" ./my.cnf

再重启上面已停止的实例
MySQL Py > dba.start_sandbox_instance(3365)

Starting MySQL instance...

Instance localhost:3365 successfully started.

查看对应的参数是否生效：
MySQL Py > \connect root@localhost:3365
Creating a session to 'root@localhost:3365'
Please provide the password for 'root@localhost:3365': ***********
Save password for 'root@localhost:3365'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 9
Server version: 8.0.33 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:3365 ssl Py > \sql
Switching to SQL mode... Commands end with ;
MySQL localhost:3365 ssl SQL > show variables like '%server_id%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| server_id | 200 |
| server_id_bits | 32 |
+----------------+-------+
2 rows in set (0.0042 sec)
以上说明，通过手动修改my.cnf的参数已生效了

五、管理沙盒实例
一旦沙盒实例正在运行，就可以随时使用以下命令更改其状态。指定实例的端口号以识别它：
• 使用JavaScript停止沙盒实例，输入dba.stopSandboxInstance(instance)。这会优雅地停止实例，不像dba.killSandboxInstance(instance)。
• 使用Python停止沙盒实例，输入：dba.stop_sandbox_instance(instance)。这会优雅地停止实例，不像dba.kill_sandbox_instance(instance)。
分别停止端口号为3300，3351 两个实例
MySQL Py > dba.stop_sandbox_instance(3300);
The MySQL sandbox instance on this host in
3300 will be stopped

Please enter the MySQL root password for the instance 'localhost:3300': ***********

Stopping MySQL instance...

Instance localhost:3300 successfully stopped.

MySQL Py > dba.stop_sandbox_instance(3351);
The MySQL sandbox instance on this host in
3351 will be stopped

Please enter the MySQL root password for the instance 'localhost:3351': ***********

Stopping MySQL instance...

Instance localhost:3351 successfully stopped.
• 使用JavaScript启动沙盒实例，输入：dba.startSandboxInstance(instance)。
• 使用Python启动沙盒实例，输入：dba.start_sandbox_instance(instance)。
MySQL Py > dba.start_sandbox_instance(3300);

Starting MySQL instance...

Instance localhost:3300 successfully started.

• 使用JavaScript终止沙盒实例，输入：dba.killSandboxInstance(instance)。这会在不优雅地停止实例的情况下终止实例，并且可用于模拟意外停机。
• 使用Python终止沙盒实例，输入：dba.kill_sandbox_instance(instance)。这会在不优雅地停止实例的情况下终止实例，并且可用于模拟意外停机。
MySQL Py > dba.kill_sandbox_instance(3300);

Killing MySQL instance...

Instance localhost:3300 successfully killed.

• 使用JavaScript删除沙盒实例，输入：dba.deleteSandboxInstance(instance)。这会从您的文件系统完全删除沙盒实例。
• 使用Python删除沙盒实例，输入：dba.delete_SandboxInstance(instance)。这会从您的文件系统完全删除沙盒实例。
MySQL Py > dba.delete_sandbox_instance(3300)

Deleting MySQL instance...

Instance localhost:3300 successfully deleted.

总结：
沙盒实例被视为瞬时的，不适用于生产用途。因此，它们不支持版本升级。在沙盒部署中，每个沙盒实例使用在本地mysql-sandboxes目录中找到的$PATH中的mysqld二进制文件的副本。如果mysqld的版本发生变化，例如升级后，基于先前版本的沙箱将无法启动。这是因为与basedir下的依赖项相比，沙箱二进制文件已过时。

如果您希望在升级后保留沙盒实例，一个解决方法是手动将升级后的mysqld二进制文件复制到每个沙箱的bin目录中。然后通过发出dba.startSandboxInstance()来启动沙盒。操作会因超时而失败，错误日志中包含：
2020-03-26T11:43:12.969131Z 5 [System] [MY-013381] [Server] Server upgrade
from '80019' to '80020' started.
2020-03-26T11:44:03.543082Z 5 [System] [MY-013381] [Server] Server upgrade
from '80019' to '80020' completed.
尽管操作似乎因超时而失败，但沙盒已成功启动。

文章看完了，如果觉得本文对您的工作或生活有用，希望分享给你身边的朋友，一起学习，共同进步哈~~~

欢迎关注我的公众号【数库信息技术】，你的关注是我写作的动力源泉

各大平台都可以找到我：
————————————————————————————
公众号：数库信息技术
墨天轮：https://www.modb.pro/u/427810
百家号：https://author.baidu.com/home/1780697309880431
CSDN ：https://blog.csdn.net/rscpass
51CTO: https://blog.51cto.com/u_16068254
博客园：https://www.cnblogs.com/shukuinfo
知乎:https://www.zhihu.com/people/shukuinfo
————————————————————————————