MySQL Shell8.0数据库命令

伴随MySQL8.0同时发布了MySQL Shell，这款神器具有支持多种语言（JS、python、SQL），交互式界面，多行批量执行等特点。

MySQL Shell 命令

命令	简写/别名	描述
\help	\h or \?	显示帮助信息
\quit	\q or \exit	退出
\		SQL模式中，“\”分割多行代码以缓冲执行
\status	\s	显示MySQL当前状态
\js		转换到JavaScript执行模式
\py		转换到Python执行模式
\sql		转换到SQL执行模式
\connect	\c	连接到SQL服务器
\reconnect		重连至同一个MySQL服务器
\use	\u	指定要用的图表
\source	.	执行当前语言编写的脚本文件
\warnings	\W	显示一个声明生成的警告
\nowarnings	\w	不显示任何警告
\history		查看和编辑之前的命令行
\rehash		手动更新自动完成名称缓存

Help命令

\help

The Shell Help is organized in categories and topics. To get help for
a specific category or topic use: \?

The argument should be the name of a category or a topic.

The pattern is a filter to identify topics for which help is required,
it can use the following wildcards:

• ? matches any single charecter.
• • matches any character sequence.

The following are the main help categories:

• AdminAPI Introduces to the dba global object and the InnoDB cluster
  administration API.
• Shell Commands Provides details about the available built-in shell commands.
• ShellAPI Contains information about the shell and util global objects
  as well as the mysql module that enables executing SQL on
  MySQL Servers.
• SQL Syntax Entry point to retrieve syntax help on SQL statements.
• X DevAPI Details the mysqlx module as well as the capabilities of the
  X DevAPI which enable working with MySQL as a Document Store

The available topics include:

• The dba global object and the classes available at the AdminAPI.
• The mysqlx module and the classes available at the X DevAPI.
• The mysql module and the global objects and classes available at the ShellAPI.
• The functions and properties of the classes exposed by the APIs.
• The available shell commands.
• Any word that is part of an SQL statement.

SHELL COMMANDS

The shell commands allow executing specific operations including
updating the shell configuration.

The following shell commands are available:

• \ Start multi-line input when in SQL mode.
• \connect (\c) Connects the shell to a MySQL server and assigns the
  global session.
• \exit Exits the MySQL Shell, same as \quit.
• \help (\?,\h) Prints help information about a specific topic.
• \history View and edit command line history.
• \js Switches to JavaScript processing mode.
• \nowarnings (\w) Don’t show warnings after every statement.
• \option Allows working with the available shell options.
• \py Switches to Python processing mode.
• \quit (\q) Exits the MySQL Shell.
• \reconnect Reconnects the global session.
• \rehash Refresh the autocompletion cache.
• \source (.) Loads and executes a script from a file.
• \sql Switches to SQL processing mode.
• \status (\s) Print information about the current global session.
• \use (\u) Sets the active schema.
• \warnings (\W) Show warnings after every statement.

GLOBAL OBJEECTS

The following modules and objects are ready for use when the shell
starts:

• dba Used for InnoDB cluster administration.
• mysql Support for connecting to MySQL servers using the classic MySQL
  protocol.
• mysqlx Used to work with X Protocol sessions using the MySQL X DevAPI.
• session Represents the currently open MySQL session.
• shell Gives access to general purpose functions and properties.
• sys Gives access to system specific parameters.
• util Global object that groups miscellaneous tools like upgrade checker.

For additional information on these global objects use:
.help()

EXAMPLES \? AdminAPI
Displays information about the AdminAPI.

\? \connect
Displays usage details for the \connect command.

\? checkInstanceConfiguration
Displays usage details for the dba.checkInstanceConfiguration function.

\? sql syntax
Displays the main SQL help categories.

\help命令可以有以下参数

• AdminAPI ：引入dba全局对象及InnoDB cluster AdminAPI。如： \？ dba。

  如：\? AdminAPI

显示： MySQL InnoDB cluster provides a complete high availability solution for MySQL. The AdminAPI is an interactive API that enables configuring and administering InnoDB clusters. Use the dba global object to: - Verify if a MySQL server is suitable for InnoDB cluster.- Configure a MySQL server to be used as an InnoDB cluster instance.- Create an InnoDB cluster.

\? dba

NAME

  dba - Global variable for InnoDB cluster management.

  The global variable dba is used to access the AdminAPI functionality and
  perform DBA operations. It is used for managing MySQL InnoDB clusters.

PROPERTIES

  verbose
        Enables verbose mode on the 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.

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

  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])
        Retrieves a cluster from the Metadata Store.

  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.

  For more help on a specific function use: dba.help('<functionName>')

  e.g. dba.help('deploySandboxInstance')

• Shell命令 ：提供内置于MySQL Shell命令的细节。
• ShellAPI ：包含shell和util全局对象的信息，比如在MySQL服务器上执行SQL语言的mysql模块。
• SQL语法 ：SQL语句上的语法help信息。
• X DevAPI：mysqlx模块及作为文档库与MySQL共同起作用的X DevAPI。

MySQL Shell连接/重连命令

连接mysql服务器，首先确保mysql服务器开启，Windows用户可以查看本地服务mysql手动开启，也可以通过命令行在Windows shell中应用‘’net start mysql‘’命令开启。MySQL Shell连接命令如下：
\connect命令后面跟一个URL字符串连接MySQL服务器。

\connect root@localhost:3306

也可以用–mysqlx (–mx) 选项创建基于X协议连接至MySQL服务器实例的会话：

\connect --mysqlx root@localhost:33060

还可以用–mysql (–mc)选项创建类会话ClassicSession，可以用MySQL协议直接连接服务器：

\connect --mysql root@localhost:3306

连接成功显示：

\connect root@localhost:3306

Creating a session to ‘root@localhost:3306’ Fetching schema names for
autocompletion… Press ^C to stop. Closing old connection… Your
MySQL connection id is 32 Server version: 8.0.12 MySQL Community
Server - GPL No default schema selected; type \use to set
one.

连接断掉可以用\reconnect重新连接，不用加任何参数。

source命令

\source用来执行指定路径的脚本文件代码：

\source /tmp/mydata.sql

在相应模式下可以执行SQL、JavaScript或Python任一种语言代码，当然用MySQL Shell执行SQL代码必须要在SQL模式下的。

\use 命令

要求活动的全局发展会话，\use命令将当前图表设置为特定的schema_name ，并将db变量更新到代表所选图表的对象中。

\use schema_name 

\history命令

• \history save 用以存储历史记录
• \history delete entrynumber 用以删除给定序号的历史条目
• \history delete firstnumber-[lastnumber] 用以删除指定范围的历史条目
• \history clear 用以清空历史记录

Rehash命令

\rehash 手动更新缓存，比如\use schema下载了一个新的图表到内存，\rehash更新自动完成的缓存。

Pager命令

可以设置MySQL Shell用一个外部的Pager命令来显示屏幕输出，如在线帮助或SQL检索结果。