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

最新推荐文章于 2024-11-01 12:20:35 发布

孙迅速

最新推荐文章于 2024-11-01 12:20:35 发布

阅读量300

点赞数

文章标签：数据库 mysql

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

概述:

这部分解释了如何使用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

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

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