MySQL Shell 8.4
MySQL Shell provides commands which enable you to modify the
execution environment of the code editor, for example to configure
the active programming language or a MySQL Server connection. The
following table lists the commands that are available regardless
of the currently selected language. As commands need to be
available independent of the execution mode,
they start with an escape sequence, the \
character.
Command | Alias/Shortcut | Description |
---|---|---|
|
|
Print help about MySQL Shell, or search the online help. |
|
|
Exit MySQL Shell. |
|
In SQL mode, begin multiple-line mode. Code is cached and executed when an empty line is entered. |
|
|
|
Show the current MySQL Shell status. |
|
Switch execution mode to JavaScript. |
|
|
Switch execution mode to Python. |
|
|
Switch execution mode to SQL. |
|
|
|
Connect to a MySQL instance. |
|
Reconnect to the same MySQL instance. |
|
|
Disconnect the global session. |
|
|
|
Specify the schema to use. |
|
|
Execute a script file using the active language. |
|
|
Show any warnings generated by a statement. |
|
|
Do not show any warnings generated by a statement. |
|
View and edit command line history. |
|
|
Manually update the autocomplete name cache. |
|
|
Query and change MySQL Shell configuration options. |
|
|
Run the specified report using the provided options and arguments. |
|
|
Run the specified report using the provided options and arguments, and refresh the results at regular intervals. |
|
|
|
Open a command in the default system editor then present it in MySQL Shell. |
|
|
Configure the pager which MySQL Shell uses to display text. |
|
Disable any pager which MySQL Shell was configured to use. |
|
|
|
Run the specified operating system command and display the results in MySQL Shell. |
|
|
Enables you to define query attributes for your SQL queries. The MySQL Shell functionality is identical to that of the MySQL client. |
The \help
command can be used with or without
a parameter. When used without a parameter a general help
message is printed including information about the available
MySQL Shell commands, global objects and main help categories.
When used with a parameter, the parameter is used to search the available help based on the mode which the MySQL Shell is currently running in. The parameter can be a word, a command, an API function, or part of an SQL statement. The following categories exist:
AdminAPI
- details the
dba
global object and the AdminAPI,
which enables you to work with InnoDB Cluster,
InnoDB ClusterSet, and InnoDB ReplicaSet.
X DevAPI
- details the
mysqlx
module as well as the capabilities
of the X DevAPI, which enable you to work with MySQL as a
Document Store
Shell Commands
- provides details about
the available built-in MySQL 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.
To search for help on a topic, for example an API function, use
the function name as a pattern
. You
can use the wildcard characters ?
to match
any single character and *
to match multiple
characters in a search. The wildcard characters can be used one
or more times in the pattern. The following namespaces can also
be used when searching for help:
dba
for AdminAPI
mysqlx
for X DevAPI
mysql
for ShellAPI for classic MySQL protocol
shell
for other ShellAPI classes:
Shell
, Sys
,
Options
commands
for MySQL Shell commands
cmdline
for the
mysqlsh command interface
For example to search for help on a topic, issue \help
and:
pattern
use x devapi
to search for help
on the X DevAPI
use \c
to search for help on the
MySQL Shell \connect
command
use getCluster
or
dba.getCluster
to search for help
on the AdminAPI dba.getCluster()
operation
use Table
or
mysqlx.Table
to search for help
on the X DevAPI Table
class
when MySQL Shell is running in JavaScript mode, use
isView
,
Table.isView
or
mysqlx.Table.isView
to search for
help on the isView
function of the
Table
object
when MySQL Shell is running in Python mode, use
is_view
,
Table.is_view
or
mysqlx.Table.is_view
to search
for help on the isView
function of the
Table
object
when MySQL Shell is running in SQL mode, if a global
session to a MySQL server exists SQL help is displayed. For
an overview use sql syntax
as the
search pattern.
Depending on the search pattern provided, one or more results could be found. If only one help topic contains the search pattern in its title, that help topic is displayed. If multiple topic titles match the pattern but one is an exact match, that help topic is displayed, followed by a list of the other topics with pattern matches in their titles. If no exact match is identified, a list of topics with pattern matches in their titles is displayed. If a list of topics is returned, you can select a topic to view from the list by entering the command again with an extended search pattern that matches the title of the relevant topic.
The \connect
command is used to connect to a
MySQL Server. See Section 4.3, “MySQL Shell Connections”.
For example:
\connect root@localhost:3306
If a password is required you are prompted for it.
Use the --mysqlx
(--mx
)
option to create a session using the X Protocol to connect to
MySQL server instance. For example:
\connect --mysqlx root@localhost:33060
Use the --mysql
(--mc
)
option to create a ClassicSession, enabling you to use
classic MySQL protocol to issue SQL directly on a server. For
example:
\connect --mysql root@localhost:3306
Use the --ssh
option to create or reuse an
SSH tunnel that provides an encrypted connection to the MySQL
server instance. The use of AdminAPI commands is not supported
over connections made from MySQL Shell using SSH tunneling.
Supply the URI for connection to the SSH server in the format
[user@]hostname[:port]
, followed by the MySQL
instance URI, for example:
\connect --ssh root@198.51.100.4:2222 root@localhost:3306
When you use the --ssh
option, the port for
connection to the MySQL server instance must be specified in the
MySQL instance URI.
An SSH tunnel set up using the \connect
command must use the default SSH configuration file and identity
file. For instructions to set these and further information on
SSH tunnel connections from MySQL Shell, see
Section 4.3.6, “Using an SSH Tunnel”. You can set up an
SSH tunnel using the shell.connect()
method
or on the command line to get additional setup options. Once
established, an SSH tunnel can be shared between connections to
the same host from the same user connecting from the same
instance, whatever setup method was originally used.
The \reconnect
command is specified without
any parameters or options. If the connection to the server is
lost, you can use the \reconnect
command,
which makes MySQL Shell try several reconnection attempts for
the session using the existing connection parameters. If those
attempts are unsuccessful, you can make a fresh connection using
the \connect
command and specifying the
connection parameters.
The \disconnect
command, is also specified
without any parameters or options. The command disconnects
MySQL Shell's global session (the session represented by the
session
global object) from the currently
connected MySQL server instance, so that you can close the
connection but still continue to use MySQL Shell.
If the connection to the server is lost, you can use the
\reconnect
command, which makes MySQL Shell
try several reconnection attempts for the session using the
existing connection parameters. If those attempts are
unsuccessful, you can make a fresh connection using the
\connect
command and specifying the
connection parameters.
The \status
command displays information
about the current global connection. This includes information
about the server connected to, the character set in use, uptime,
and so on.
The \source
command or its alias
\.
can be used in MySQL Shell's interactive
mode to execute code from a script file at a given path. For
example:
\source /tmp/mydata.sql
You can execute either SQL, JavaScript or Python code. The code in the file is executed using the active language, so to process SQL code the MySQL Shell must be in SQL mode.
As the code is executed using the active language, executing a script in a different language than the currently selected execution mode language could lead to unexpected results.
For compatibility with the mysql client, in
SQL mode only, you can execute code from a script file using the
source
command with no backslash and an
optional SQL delimiter. source
or the alias
\.
(which does not use an SQL delimiter) can
be used both in MySQL Shell's interactive mode for SQL, to
execute a script directly, and in a file of SQL code processed
in batch mode, to execute a further script from within the file.
So with MySQL Shell in SQL mode, you could now execute the
script in the /tmp/mydata.sql
file from
either interactive mode or batch mode using any of these three
commands:
source /tmp/mydata.sql; source /tmp/mydata.sql \. /tmp/mydata.sql
The command \source /tmp/mydata.sql
is also
valid, but in interactive mode only.
In interactive mode, the \source
,
\.
or source
command
itself is added to the MySQL Shell history, but the contents of
the executed script file are not added to the history.
The \use
command enables you to choose which
schema is active, for example:
\use schema_name
The \use
command requires a global
development session to be active. The \
use
command sets the current schema to the specified
schema_name
and updates the
db
variable to the object that represents the
selected schema.
The \history
command lists the commands you
have issued previously in MySQL Shell. Issuing
\history
shows history entries in the order
that they were issued with their history entry number, which can
be used with the \history delete
command.
entry_number
The \history
command provides the following
options:
Use \history save
to save the history
manually.
Use \history delete entrynumber
to delete
the individual history entry with the given number.
Use \history delete
to delete history entries within the range of the given
entry numbers. If
firstnumber
-lastnumber
goes past the last found history entry number, history
entries are deleted up to and including the last entry.
lastnumber
Use \history delete
to delete the
history entries from
number
-
up to
and including the last entry.
number
Use \history delete
-
to delete the
specified number of history entries starting with the last
entry and working back. For example, number
\history
delete -10
deletes the last 10 history entries.
Use \history clear
to delete the entire
history.
The history is not saved between sessions by default, so when
you exit MySQL Shell the history of what you issued during the
current session is lost. If you want to keep the history across
sessions, enable the MySQL Shell
history.autoSave
option. For more
information, see
Section 5.5, “Code History”.
When you have disabled the autocomplete name cache feature, use
the \rehash
command to manually update the
cache. For example, after you load a new schema by issuing the
\use
command, issue schema
\rehash
to update the
autocomplete name cache. After this autocomplete is aware of the
names used in the database, and you can autocomplete text such
as table names and so on. See
Section 5.3, “Code Autocompletion”.
The \option
command enables you to query and
change MySQL Shell configuration options in all modes. You can
use the \option
command to list the
configuration options that have been set and show how their
value was last changed. You can also use it to set and unset
options, either for the session, or persistently in the
MySQL Shell configuration file. For instructions and a list of
the configuration options, see
Section 13.4, “Configuring MySQL Shell Options”.
You can configure MySQL Shell to use an external pager to read long onscreen output, such as the online help or the results of SQL queries. See Section 4.6, “Using a Pager”.
The \show
command runs the named report,
which can be either a built-in MySQL Shell report or a
user-defined report that has been registered with MySQL Shell.
You can specify the standard options for the command, and any
options or additional arguments that the report supports. The
\watch
command runs a report in the same way
as the \show
command, but then refreshes the
results at regular intervals until you cancel the command using
Ctrl + C. For instructions, see
Section 10.1.5, “Running MySQL Shell Reports”.
The \edit
(\e
) command
opens a command in the default system editor for editing, then
presents the edited command in MySQL Shell for execution. The
command can also be invoked using the key combination
Ctrl-X Ctrl-E. For details, see
Section 5.4, “Editing Code”.
The \system
(\!
) command
runs the operating system command that you specify as an
argument to the command, then displays the output from the
command in MySQL Shell. MySQL Shell returns an error if it was
unable to execute the command. The output from the command is
returned as given by the operating system, and is not processed
by MySQL Shell's JSON wrapping function or by any external
pager tool that you have specified to display output.
The query_attribute
command, and
session.setQueryAttributes
method, enable you
to define query attributes for your SQL queries. The
MySQL Shell functionality is identical to that of the MySQL
client.
Setting query attributes is only supported on the classic MySQL protocol. It is not supported for X protocol sessions.
For more information, see the following:
The following examples set the attributes
att1
and att2
with the
values val1
and val2
respectively:
SQL
SQL> \query_attributes att1 val1 att2 val2
JavaScript
JS> session.setQueryAttributes({att1:"val1",att2:"val2"})
Python
PY> session.set_query_attributes({att1:"val1",att2:"val2"})
Attributes can be retrieved using the
mysql_query_attribute_string()
function.
For example:
SQL
SQL> select mysql_query_attribute_string("att1") as "Attribute 1", mysql_query_attribute_string("att2")as "Attribute 2" +-------------+-------------+ | Attribute 1 | Attribute 2 | +-------------+-------------+ | v1 | v2 | +-------------+-------------+
JavaScript
JS> session.runSql("select mysql_query_attribute_string("att1") as "Attribute 1", mysql_query_attribute_string("att2")as "Attribute 2"')") +-------------+-------------+ | Attribute 1 | Attribute 2 | +-------------+-------------+ | v1 | v2 | +-------------+-------------+