MySQL Shell

Stone大约 11 分钟

MySQL Shell

注意：
此文档对应的 MySQL Shell 版本为 8.0.32 社区版。

概述

MySQL Shell 是 MySQL 官方推出的一种交互式的命令行工具，可以用于管理和监控 MySQL 数据库。MySQL Shell 提供了多种语言接口，包括 JavaScript、Python 和 SQL，可以通过这些语言接口来执行管理和监控任务。同时，MySQL Shell 还支持 X DevAPI ，将 MySQL 作为 Document Store。

使用 MySQL Shell，用户可以执行各种操作，例如创建数据库和表、导入数据、备份和恢复数据等。此外，MySQL Shell 还支持 AdminAPI，能够与 MySQL InnoDB Cluster 集群进行交互，并提供了一些工具来监控和管理集群状态和健康状况。

总之，MySQL Shell 是一个很强大的管理和监控 MySQL 数据库的工具，它提供了多种语言接口和 API，能够满足不同用户的需求。

安装

在官方网站open in new window下载最新版本的 MySQL Shell，此处为 8.0.32。

上传到服务器进行安装：

[root@s1 ~]# yum localinstall mysql-shell-8.0.32-1.el7.x86_64.rpm

命令

MySQL Shell 的命令以反斜杠 \ 开头，具体如下表：

Command	Alias/Shortcut	Description
`\help`	`\h` or `\?`	Print help about MySQL Shell, or search the online help.
`\quit`	`\q` or `\exit`	Exit MySQL Shell.
`\`		In SQL mode, begin multiple-line mode. Code is cached and executed when an empty line is entered.
`\status`	`\s`	Show the current MySQL Shell status.
`\js`		Switch execution mode to JavaScript.
`\py`		Switch execution mode to Python.
`\sql`		Switch execution mode to SQL.
`\connect`	`\c`	Connect to a MySQL instance.
`\reconnect`		Reconnect to the same MySQL instance.
`\disconnect`		Disconnect the global session.
`\use`	`\u`	Specify the schema to use.
`\source`	`\.` or `source` (no backslash)	Execute a script file using the active language.
`\warnings`	`\W`	Show any warnings generated by a statement.
`\nowarnings`	`\w`	Do not show any warnings generated by a statement.
`\history`		View and edit command line history.
`\rehash`		Manually update the autocomplete name cache.
`\option`		Query and change MySQL Shell configuration options.
`\show`		Run the specified report using the provided options and arguments.
`\watch`		Run the specified report using the provided options and arguments, and refresh the results at regular intervals.
`\edit`	`\e`	Open a command in the default system editor then present it in MySQL Shell.
`\pager`	`\P`	Configure the pager which MySQL Shell uses to display text.
`\nopager`		Disable any pager which MySQL Shell was configured to use.
`\system`	`\!`	Run the specified operating system command and display the results in MySQL Shell.

例子：使用 mysqlsh 命令启动 MySQL Shell

[root@s1 ~]# mysqlsh
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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.
Creating a Classic session to 'root@/data%2Fmysql%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 11
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost  JS >

例子：查看状态

 MySQL  localhost  JS > \status
MySQL Shell version 8.0.32

Connection Id:                11
Current schema:               
Current user:                 root@localhost
SSL:                          Not in use.
Using delimiter:              ;
Server version:               8.0.32 MySQL Community Server - GPL
Protocol version:             Classic 10
Client library:               8.0.32
Connection:                   Localhost via UNIX socket
Unix socket:                  /data/mysql/mysql.sock
Server characterset:          utf8mb4
Schema characterset:          utf8mb4
Client characterset:          utf8mb4
Conn. characterset:           utf8mb4
Result characterset:          utf8mb4
Compression:                  Disabled
Uptime:                       1 hour 40 min 29.0000 sec

Threads: 3  Questions: 40  Slow queries: 0  Opens: 152  Flush tables: 3  Open tables: 69  Queries per second avg: 0.006

会话

使用 MySQL Shell 连接到 MySQL Server 后，会创建一个名称为 session 的 MySQL Shell 全局对象，表示用户与 MySQL Server 之间交互的一个会话。

例子：查看 session

 MySQL  localhost  JS > session
<ClassicSession:root@/data%2Fmysql%2Fmysql.sock>

session 分为两类：

Session：使用此会话对象类型与 X 协议可用的 MySQL Server 实例进行通信。要使 X 协议可用，必须在 MySQL Server 实例上安装并启用 X 插件，MySQL 8.0 默认安装，MySQL 5.7 需手动安装。X 插件监听由参数 mysqlx_port 指定的端口，默认为 33060。
ClassicSession：使用此会话对象类型与不使用 X 协议的 MySQL Server 实例进行通信，使用传统 MySQL 协议。

例子：使用 --mysqlx 选项创建 Session 会话对象

[root@s1 ~]# mysqlsh mysqlx://stone@192.168.44.135
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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.
Creating an X protocol session to 'stone@192.168.44.135'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 18 (X protocol)
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  192.168.44.135:33060+ ssl  JS >

例子：使用 --sqlx 选项创建 Session 会话对象，并切换到 SQL 模式

[root@s1 ~]# mysqlsh --sqlx stone@192.168.44.135
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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.
Creating an X protocol session to 'stone@192.168.44.135'
Fetching global names for auto-completion... Press ^C to stop.
Your MySQL connection id is 19 (X protocol)
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  192.168.44.135:33060+ ssl  SQL >

例子：不指定协议，使用 URL 格式的连接串创建 ClassicSession 会话对象

[root@s1 ~]# mysqlsh stone@192.168.44.135
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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.
Creating a Classic session to 'stone@192.168.44.135'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 20
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  192.168.44.135:3306 ssl  JS > session
<ClassicSession:stone@192.168.44.135:3306>

例子：启动 MySQL Shell 后，使用 \connect 命令创建 Session 会话对象

[root@s1 ~]# mysqlsh
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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.
Creating a Classic session to 'root@/data%2Fmysql%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 41
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  localhost  JS > \connect mysqlx://stone@192.168.44.135
Creating an X protocol session to 'stone@192.168.44.135'
Please provide the password for 'stone@192.168.44.135': ***********
Save password for 'stone@192.168.44.135'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for auto-completion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 42 (X protocol)
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  192.168.44.135:33060+ ssl  JS > session
<Session:stone@192.168.44.135:33060>

例子：启动 MySQL Shell 后，使用 shell.connect() 方法创建 Session 会话对象

[root@s1 ~]# mysqlsh
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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.
Creating a Classic session to 'root@/data%2Fmysql%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 43
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  localhost  JS > shell.connect('mysqlx://stone@192.168.44.135')
Creating an X protocol session to 'stone@192.168.44.135'
Fetching schema names for auto-completion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 44 (X protocol)
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

<Session:stone@192.168.44.135:33060>
 MySQL  192.168.44.135:33060+ ssl  JS >

模式

MySQL Shell 有三种模式：

SQL 模式，用户可以在这个模式下输入 SQL 命令来操作 MySQL 数据库。在 SQL 模式下，用户可以使用各种 SQL 语句和命令，如 SELECT、INSERT、UPDATE、DELETE、SHOW 等。
JavaScript 模式，是 MySQL Shell 的默认模式，允许用户使用 JavaScript 代码来操作 MySQL 数据库。在 JavaScript 模式下，用户可以通过调用 MySQL Shell 提供的 API 来与数据库交互。此外，用户还可以使用 JavaScript 自带的标准库和第三方库来扩展 MySQL Shell 的功能。
Python 模式，允许用户使用 Python 代码来操作 MySQL 数据库。在 Python 模式下，用户也可以通过调用 MySQL Shell 提供的 API 来与数据库交互。此外，用户还可以使用 Python 自带的标准库和第三方库来扩展 MySQL Shell 的功能。

需要注意的是，在不同的模式下，MySQL Shell 提供的命令和 API 不完全相同。因此，在选择模式时，需要考虑自己的需求和熟练程度，并选择最适合自己的模式。

在参数文件中配置默认模式为 SQL 模式：

[root@s1 ~]# vi /etc/my.cnf
[mysqlsh]
sql

再次连接，就是 SQL 模式了：

[root@s1 ~]# mysqlsh mysqlx://stone@192.168.44.135
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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.
Creating an X protocol session to 'stone@192.168.44.135'
Fetching global names for auto-completion... Press ^C to stop.
Your MySQL connection id is 45 (X protocol)
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  192.168.44.135:33060+ ssl  SQL >

也可以在连接时指定模式：

[root@s1 ~]# mysqlsh mysqlx://stone@192.168.44.135 --js
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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.
Creating an X protocol session to 'stone@192.168.44.135'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 46 (X protocol)
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  192.168.44.135:33060+ ssl  JS >

API

MySQL Shell 的内置对象有以下几类：

[root@s1 ~]# mysqlsh -- --help
The following objects provide command line operations:

   cluster
      Represents an InnoDB Cluster.

   clusterset
      Represents an InnoDB ClusterSet.

   dba
      InnoDB Cluster, ReplicaSet, and ClusterSet management functions.

   rs
      Represents an InnoDB ReplicaSet.

   shell
      Gives access to general purpose functions and properties.

   util
      Global object that groups miscellaneous tools like upgrade checker and
      JSON import.

例子：查看 shell 对象的方法

[root@s1 ~]# mysqlsh -- shell --help
The following object provides command line operations at 'shell':

   options
      Gives access to options impacting shell behavior.

The following operations are available at 'shell':

   delete-all-credentials
      Deletes all credentials managed by the configured helper.

   delete-credential
      Deletes credential for the given URL using the configured helper.

   list-credential-helpers
      Returns a list of strings, where each string is a name of a helper
      available on the current platform.

   list-credentials
      Retrieves a list of all URLs stored by the configured helper.

   status
      Shows connection status info for the shell.

   store-credential
      Stores given credential using the configured helper.

例子：使用 shell 对象的 status 方法查看 MySQL Server 状态

[root@s1 ~]# mysqlsh -- shell status
MySQL Shell version 8.0.32

Connection Id:                55
Current schema:               
Current user:                 root@localhost
SSL:                          Not in use.
Using delimiter:              ;
Server version:               8.0.32 MySQL Community Server - GPL
Protocol version:             Classic 10
Client library:               8.0.32
Connection:                   Localhost via UNIX socket
Unix socket:                  /data/mysql/mysql.sock
Server characterset:          utf8mb4
Schema characterset:          utf8mb4
Client characterset:          utf8mb4
Conn. characterset:           utf8mb4
Result characterset:          utf8mb4
Compression:                  Disabled
Uptime:                       5 hours 10 min 21.0000 sec

Threads: 4  Questions: 281  Slow queries: 0  Opens: 270  Flush tables: 3  Open tables: 187  Queries per second avg: 0.015

也可以在 MySQL Shell 里调用：

[root@s1 ~]# mysqlsh
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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.
Creating a Classic session to 'root@/data%2Fmysql%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 61
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  localhost  JS > shell.status();
MySQL Shell version 8.0.32

Connection Id:                61
Current schema:               
Current user:                 root@localhost
SSL:                          Not in use.
Using delimiter:              ;
Server version:               8.0.32 MySQL Community Server - GPL
Protocol version:             Classic 10
Client library:               8.0.32
Connection:                   Localhost via UNIX socket
Unix socket:                  /data/mysql/mysql.sock
Server characterset:          utf8mb4
Schema characterset:          utf8mb4
Client characterset:          utf8mb4
Conn. characterset:           utf8mb4
Result characterset:          utf8mb4
Compression:                  Disabled
Uptime:                       5 hours 15 min 13.0000 sec

Threads: 4  Questions: 334  Slow queries: 0  Opens: 277  Flush tables: 3  Open tables: 194  Queries per second avg: 0.017

AdminAPI

通过 dba 这个内置对象访问的 API 为 AdminAPI，是一组用于管理 InnoDB Cluster，InnoDB ClusterSet 和 InnoDB ReplicaSet 环境的 JavaScript API。使用 AdminAPI，只能使用 TCP 连接和传统 MySQL 协议来创建、配置、监控和维护这些环境，并实现自动化运维。

例子：查看 AdminAPI 的用法

[root@s1 ~]# mysqlsh
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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.
Creating a Classic session to 'root@/data%2Fmysql%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 64
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  localhost  JS > dba.help()
NAME
      dba - InnoDB Cluster, ReplicaSet, and ClusterSet management functions.

DESCRIPTION
      Entry point for AdminAPI functions, including InnoDB Clusters,
      ReplicaSets, and ClusterSets.

      InnoDB Clusters

      The dba.configureInstance() function can be used to configure a MySQL
      instance with the settings required to use it in an InnoDB Cluster.

      InnoDB Clusters can be created with the dba.createCluster() function.

      Once created, InnoDB Cluster management objects can be obtained with the
      dba.getCluster() function.

      InnoDB ReplicaSets

      The dba.configureReplicaSetInstance() function can be used to configure a
      MySQL instance with the settings required to use it in a ReplicaSet.

      ReplicaSets can be created with the dba.createReplicaSet() function.

      Once created, ReplicaSet management objects can be obtained with the
      dba.getReplicaSet() function.

      InnoDB ClusterSets

      ClusterSets can be created with the <Cluster>.createClusterSet()
      function.

      Once created, ClusterSet management objected can be obtained with the
      dba.getClusterSet() or <Cluster>.getClusterSet() functions.

      Sandboxes

      Utility functions are provided to create sandbox MySQL instances, which
      can be used to create test Clusters and ReplicaSets.

PROPERTIES
      session
            The session the dba object will use by default.

      verbose
            Controls debug message verbosity for sandbox related dba
            operations.

FUNCTIONS
      checkInstanceConfiguration(instance[, options])
            Validates an instance for MySQL InnoDB Cluster usage.

      configureInstance([instance][, options])
            Validates and configures an instance for MySQL InnoDB Cluster
            usage.

      configureLocalInstance([instance][, options])
            Validates and configures a local instance for MySQL InnoDB Cluster
            usage.

      configureReplicaSetInstance([instance][, options])
            Validates and configures an instance for use in an InnoDB
            ReplicaSet.

      createCluster(name[, options])
            Creates a MySQL InnoDB cluster.

      createReplicaSet(name[, options])
            Creates a MySQL InnoDB ReplicaSet.

      deleteSandboxInstance(port[, options])
            Deletes an existing MySQL Server instance on localhost.

      deploySandboxInstance(port[, options])
            Creates a new MySQL Server instance on localhost.

      dropMetadataSchema(options)
            Drops the Metadata Schema.

      getCluster([name][, options])
            Returns an object representing a Cluster.

      getClusterSet()
            Returns an object representing a ClusterSet.

      getReplicaSet()
            Returns an object representing a ReplicaSet.

      help([member])
            Provides help about this object and it's members

      killSandboxInstance(port[, options])
            Kills a running MySQL Server instance on localhost.

      rebootClusterFromCompleteOutage([clusterName][, options])
            Brings a cluster back ONLINE when all members are OFFLINE.

      startSandboxInstance(port[, options])
            Starts an existing MySQL Server instance on localhost.

      stopSandboxInstance(port[, options])
            Stops a running MySQL Server instance on localhost.

      upgradeMetadata([options])
            Upgrades (or restores) the metadata to the version supported by the
            Shell.

      SEE ALSO

      - For general information about the AdminAPI use: \? AdminAPI
      - For help on a specific function use: \? dba.<functionName>

      e.g. \? dba.deploySandboxInstance

MySQL Shell

# MySQL Shell

# 概述

# 安装

# 命令

# 会话

# 模式

# API

# AdminAPI

MySQL Shell

概述

安装

命令

会话

模式

API

AdminAPI