Chapter 19 MySQL Shell User Guide

MySQL Shell can connect to MySQL Server using both the X Protocol and the classic MySQL protocol. The address of the MySQL Server which you want to connect to can be specified using individual parameters, such as user, hostname and port, or using a Uniform Resource Identifier (URI) type string. The following sections describe these connection methods. See Section 4.2.2, “Connecting to the MySQL Server” for more background information.

You can configure the MySQL server instance that MySQL Shell is connected to in the following ways:

These methods all support Section 19.2.1.2, “Connecting using a URI String”.

If you do not specify parameters for a connection the following defaults are used:

MySQL Shell connections using X Protocol can use either TCP or Unix sockets as of version 8.0.4. Previous versions use only TCP for X Protocol connections and using Unix sockets is not supported. MySQL Shell connections using MySQL Protocol default to using Unix sockets when the following conditions are met:

If a host name is specified but it is not localhost, a TCP connection is established. In this case, if a TCP port is not specified the default value of 3306 is used. If the conditions are met for a socket connection but a path to a socket file is not specified then the default socket is used. See Section 4.2.2, “Connecting to the MySQL Server”.

If the connection to the server is lost, MySQL Shell does not attempt to reconnect automatically. You can use the MySQL Shell \reconnect command, which makes MySQL Shell try several reconnection attempts for the current global session with the previously supplied parameters.

scheme://[user[:[password]]@]target[:port][/schema][?attribute1=value1&attribute2=value2...

mysqlx://user:@localhost

mysqlx://user:@localhost

shell> mysqlsh --mysqlx -u user -h localhost -P 33065

shell> mysqlsh --mysql -u user -h localhost

ssluser@127.0.0.1?ssl-ca%3D%2Froot%2Fclientcert%2Fca-cert.pem%26ssl-cert%3D%2Fro\
ot%2Fclientcert%2Fclient-cert.pem%26ssl-key%3D%2Froot%2Fclientcert%2Fclient-key
.pem

mysql-js> var mysession1=mysqlx.getSession('root@localhost:33060', 'password');
mysql-js> session
<Session:root@localhost>
mysql-js>

mysql-js> var mysession2=mysql.getClassicSession('root@localhost:3306', 'password');
mysql-js> session
<ClassicSession:root@localhost:3306>
mysql-js>

mysql-js> var session=mysqlx.getSession({host: 'localhost',
                                             user: 'root',
                                             password: 'password',
                                             ssl_ca: "path_to_ca_file",
                                             ssl_cert: "path_to_cert_file",
                                             ssl_key: "path_to_key_file"});

This section explains the different types of sessions in MySQL Shell and how to create and configure them.

mysql-js> \connect mysqlx://user@localhost

mysql-js> shell.connect('mysqlx://user@localhost')

MySQL Shell reserves certain variables as global variables, which are assigned to commonly used objects in scripting. This section describes the available global variables and provides examples of working with them. The global variables are:

By using these global objects, MySQL Shell provides interactive error resolution for common situations. For example:

mysql-js> session
<Session:root@localhost:33060>
mysql-js>

mysql-js> session
<Undefined>
mysql-js>

mysql-js> session.uri
The global session is not set, do you want to establish a session?

   1) MySQL Document Store Session through X Protocol
   2) Classic MySQL Session

Please select the session type or ENTER to cancel: 2
Please specify the MySQL server URI: root@localhost
Enter password: 
root@localhost:
mysql-js> session
<ClassicSession:root@localhost:>

mysql-js> db
<Schema:world_x>
mysql-js>

mysql-js> db
<Undefined>
mysql-js>

mysql-js> db.getCollections()
LogicError: The db variable is not set, establish a global session first.
at (shell):1:2
in db.getCollections()
^

mysql-js> db.getCollections()
The db variable is not set, do you want to set the active schema? [y/N]:y
Please specify the schema:world_x
[
<Collection:countryinfo>
]
mysql-js> db
<Schema:world_x>
mysql-js>

mysql-js> var mySchema = session.getSchema('my_test')
The schema my_test does not exist, do you want to create it? [y/N]: y

mysql-js> mySchema
<Schema:my_test>
mysql-js>

Code which you issue in MySQL Shell is saved to the history, which can then be accessed using the up and down arrow keys. The shell.options["history.autoSave"] option must be set to ensure history is automatically saved.

History entries are saved to the ~/.mysqlsh/history file on Linux and Mac, or the %AppData%\MySQL\mysqlsh\history file on Windows. The user configuration path can be overridden on all platforms by defining the environment variable MYSQL_USER_CONFIG_HOME. The value of this variable replaces %AppData%\MySQL\mysqlsh\ on Windows or ~/.mysqlsh/ on Unix.

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 entry_number command. The numbering of history entries only resets after restarting MySQL Shell (or after issuing \history clear), meaning each entry keeps its entry number even when the oldest entries are discarded.

You can customize the entries added to the history using the --histignore command option.

You can search the history using the incremental history search feature. To search the history, use control-r to search backwards, or control-s to search forwards through the history. Once the search is active, typing characters searches for any strings that match them in the history and displays the first match. Use control-s or control-r to search for further matches to the current search term. Typing more characters further refines the search. During a search you can press the arrow keys to continue stepping through the history from the current search result. Press Enter to accept the displayed match. Use control-c to cancel the search.

History entries are saved to the history file, which is readable only by the owner user and is created automatically by MySQL Shell. Only code which you type interactively at the MySQL Shell prompt is saved to the history. Code that is executed indirectly or internally, for example when the \source command is executed, is not added to the history. When you issue multi-line code, the new line characters are stripped in the history entry. If the same code is issued multiple times it is only stored in the history once, reducing duplication. The shell.options["history.maxSize"]=number option sets the maximum number of entries to store in the history, the default is 1000. If the number of history entries exceeds the configured maximum, the oldest entries are removed. If the maximum is set to 0, no history entries are saved. Old history entries are also deleted when MySQL Shell exits. If the history file cannot be read or written to, MySQL Shell logs an error message and skips the read or write operation.

Additionally, when using MySQL Shell in SQL mode, you can configure strings which should not be added to the history. By default strings that match the glob patterns IDENTIFIED, or PASSWORD are not added to the history. To configure further strings to match use either the --histignore command option, or shell.options["history.sql.ignorePattern"]. Multiple strings can be specified, separated by a colon (:). The history matching uses case insensitive glob pattern like matching. Supported wildcards are * (match any 0 or more characters) and ? (match exactly 1 character). The default strings are specified as PASSWORD:IDENTIFIED.

The default mode of MySQL Shell provides interactive execution of database operations that you type at the command prompt. These operations can be written in JavaScript, Python or SQL depending on the curent Section 19.3.5, “Active Language”. When executed, the results of the operation are displayed on-screen.

As with any other language interpreter, MySQL Shell is very strict regarding syntax. For example, the following JavaScript snippet reads and prints the documents in a collection:

var mysqlx = require('mysqlx').mysqlx;
var mySession = mysqlx.getSession('user:pwd@localhost');
var result = mySession.world_x.countryinfo.find().execute();
var record = result.fetchOne();
while(record){
  print(record);
  record = result.fetchOne();
}

As seen above, the call to find() is followed by the execute() function. CRUD database commands are only actually executed on the MySQL Server when execute() is called. However, when working with MySQL Shell interactively, execute() is implicitly called whenever you press Return on a statement. Then the results of the operation are fetched and displayed on-screen. The rules for when you need to call execute() or not are as follows:

Based on the above rules, the statements needed in the MySQL Shell in interactive mode to establish a session, query, and print the documents in a collection are:

mysql-js> var mysqlx = require('mysqlx').mysqlx;
mysql-js> var mySession = mysqlx.getSession('user:pwd@localhost');

No call to execute() is needed and the Result object is automatically printed.

mysql-js> mySession.world_x.countryinfo.find();

MySQL Shell supports autocompletion of text preceding the cursor by pressing the Tab key. The Section 19.4.1, “MySQL Shell Commands” can be autocompleted in any of the language modes. For example typing \con and pressing the Tab key autocompletes to \connect. Autocompletion is available for SQL, JavaScript and Python language keywords depending on the current Section 19.3.5, “Active Language”.

Autocompletion supports the following text objects:

By default autocompletion is enabled, to change this behavior see Configuring Autocompletion.

Once you activate autocompletion, if the text preceding the cursor has exactly one possible match, the text is automatically completed. If autocompletion finds multiple possible matches, it beeps or flashes the terminal. If the Tab key is pressed again, a list of the possible completions is displayed. If no match is found then no autocompletion happens.

print(db.user.select().where("user in ('foo', 'bar')").e

shell.options['autocomplete.nameCache']=true

shell.options['devapi.dbObjectHandles']=true

As well as interactive code execution, MySQL Shell provides batch code execution from:

The input is processed based on the current programming language selected in MySQL Shell, which defaults to JavaScript. You can change the default programming language using the defaultMode MySQL Shell configuration option. Files with the extensions .js, .py, and .sql are always processed in the appropriate language mode, regardless of the default programming language.

This example shows how to load JavaScript code from a file for batch processing:

shell> mysqlsh --file code.js

Here, a JavaScript file is redirected to standard input for execution:

shell> mysqlsh < code.js

This example shows how to redirect SQL code to standard input for execution:

shell> echo "show databases;" | mysqlsh --sql --uri root@198.51.100.141:33060

#!/usr/local/mysql-shell/bin/mysqlsh --file
print("Hello World\n");

The output of the commands processed on the server can be formatted in different ways. This section details the different available output formats.

mysql-sql> select * from sakila.actor limit 3;
+----------+-------------+----------------+----------------------+
| actor_id | first_name  | last_name      | last_update          |
+----------+-------------+----------------+----------------------+
|        1 | PENELOPE    | GUINESS        | 2006-02-15 4:34:33   |
|        2 | NICK        | WAHLBERG       | 2006-02-15 4:34:33   |
|        3 | ED          | CHASE          | 2006-02-15 4:34:33   |
+----------+-------------+----------------+----------------------+
3 rows in set (0.00 sec)

mysql-sql>

>echo "select * from sakila.actor limit 3;" | mysqlsh --classic --uri root@198.51.100.141:33460
actor_id        first_name      last_name       last_update
1       PENELOPE        GUINESS 2006-02-15 4:34:33
2       NICK    WAHLBERG        2006-02-15 4:34:33
3       ED      CHASE   2006-02-15 4:34:33

shell>echo "select * from sakila.actor limit 3;" | mysqlsh --json --sqlc --uri root@198.51.100.141:3306
{"duration":"0.00 sec","info":"","row_count":3,"rows":[[1,"PENELOPE","GUINESS",{"year":2006,"month":1,"day":15,"hour":4,"minute":34,"second":33.0}],[2,"NICK","WAHLBERG",{"year":2006,"month":1,"day":15,"hour":4,"minute":34,"second":33.0}],[3,"ED","CHASE",{"year":2006,"month":1,"day":15,"hour":4,"minute":34,"second":33.0}]],"warning_count":0}

shell>echo "select * from sakila.actor limit 3;" | mysqlsh --json=raw --sqlc --uri root@198.51.100.141:3306
{"duration":"0.00 sec","info":"","row_count":3,"rows":[[1,"PENELOPE","GUINESS",{"year":2006,"month":1,"day":15,"hour":4,"minute":34,"second":33.0}],[2,"NICK","WAHLBERG",{"year":2006,"month":1,"day":15,"hour":4,"minute":34,"second":33.0}],[3,"ED","CHASE",{"year":2006,"month":1,"day":15,"hour":4,"minute":34,"second":33.0}]],"warning_count":0}

shell>echo "select * from sakila.actor limit 3;" | mysqlsh --json=pretty --sqlc --uri root@198.51.100.141:3306
{
    "duration": "0.00 sec",
    "info": "",
    "row_count": 3,
    "rows": [
        [
            1,
            "PENELOPE",
            "GUINESS",
            {
                "year": 2006,
                "month": 1,
                "day": 15,
                "hour": 4,
                "minute": 34,
                "second": 33.0
            }
        ],
        [
            2,
            "NICK",
            "WAHLBERG",
            {
                "year": 2006,
                "month": 1,
                "day": 15,
                "hour": 4,
                "minute": 34,
                "second": 33.0
            }
        ],
        [
            3,
            "ED",
            "CHASE",
            {
                "year": 2006,
                "month": 1,
                "day": 15,
                "hour": 4,
                "minute": 34,
                "second": 33.0
            }
        ]
    ],
    "warning_count": 0
}
shell>

mysql-sql> select * from sakila.actor limit 3;
{"duration":"0.00 sec","info":"","row_count":3,"rows":[[1,"PENELOPE","GUINESS",{"year":2006,"month":1,"day":15,"hour":4,"minute":34,"second":33.0}],[2,"NICK","WAHLBERG",{"year":2006,"month":1,"day":15,"hour":4,"minute":34,"second":33.0}],[3,"ED","CHASE",{"year":2006,"month":1,"day":15,"hour":4,"minute":34,"second":33.0}]],"warning_count":0}

mysql-sql>

mysql-sql> select * from sakila.actor limit 3;
{
    "duration": "0.00 sec",
    "info": "",
    "row_count": 3,
    "rows": [
        [
            1,
            "PENELOPE",
            "GUINESS",
            {
                "year": 2006,
                "month": 1,
                "day": 15,
                "hour": 4,
                "minute": 34,
                "second": 33.0
            }
        ],
        [
            2,
            "NICK",
            "WAHLBERG",
            {
                "year": 2006,
                "month": 1,
                "day": 15,
                "hour": 4,
                "minute": 34,
                "second": 33.0
            }
        ],
        [
            3,
            "ED",
            "CHASE",
            {
                "year": 2006,
                "month": 1,
                "day": 15,
                "hour": 4,
                "minute": 34,
                "second": 33.0
            }
        ]
    ],
    "warning_count": 0
}

mysql-sql>

MySQL Shell can execute SQL, JavaScript or Python code, but only one language can be active at a time. The active mode determines how the executed statements are processed:

When running MySQL Shell in interactive mode, activate a specific language by entering the commands: \sql, \js, \py.

When running MySQL Shell in batch mode, activate a specific language by passing any of these command-line options: --js, --py or --sql. The default mode if none is specified is JavaScript.

Use MySQL Shell to execute the content of the file code.sql as SQL.

shell> mysqlsh --sql < code.sql

Use MySQL Shell to execute the content of the file code.js as JavaScript code.

shell> mysqlsh < code.js

Use MySQL Shell to execute the content of the file code.py as Python code.

shell> mysqlsh --py < code.py

This section describes code execution in batch mode.

Use the --interactive (or -i) command-line option to configure MySQL Shell to process the input source as if it were being issued in interactive mode; this enables all the features provided by the Interactive mode to be used in batch processing.

mysql-sql> \
... create procedure get_actors()
... begin
...   select first_name from sakila.actor;
... end
...
mysql-sql>

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.

\help connect

\connect root@localhost:3306

\connect --mysqlx root@localhost:33060

\connect --mysql root@localhost:3306

\source /tmp/mydata.sql

\use schema_name

MySQL Shell can be configured to generate an application log file with information about issues of varying severity. You can use this information to verify the state of MySQL Shell while it is running.

The location of the log file is the user configuration path and the file is named mysqlsh.log.

Log File on Windows

On Windows, the default path to the log file is %APPDATA%\MySQL\mysqlsh\mysqlsh.log

To find the location of %APPDATA% on your system, echo it from the comand-line. For example:

C:>echo %APPDATA%

C:\Users\exampleuser\AppData\Roaming

On Windows, the path is determined by the result of gathering the %APPDATA% folder specific to that user, and then appending MySQL\mysqlsh. Using the above example, we end up with:

C:\Users\exampleuser\AppData\Roaming\MySQL\mysqlsh\mysqlsh.log

Log File on Unix-based Systems

For a machine running Unix, the default path is ~/.mysqlsh/mysqlsh.log where “~” represents the user's home directory. The environment variable HOME also represents the user's home directory. Appending .mysqlsh to the user's home directory determines the default path to the logs. For example:

C:>echo $HOME

/home/exampleuser

shell> less /home/exampleuser/.mysqlsh/mysqlsh.log

The default user configuration path can be overridden on all platforms by defining the environment variable MYSQL_USER_CONFIG_HOME. The value of this variable replaces %AppData%\MySQL\mysqlsh\ on Windows or ~/.mysqlsh/ on Unix.

By default, logging is disabled in MySQL Shell. To enable logging use the --log-level command-line option when starting MySQL Shell. For example:

shell> mysqlsh --log-level=4

The number assigned to --log-level controls the level of detail in the log. The following logging levels are supported:

The log format is plain text and entries contain a timestamp and description of the problem, along with the log level from the above list. For example:

2016-04-05 22:23:01: Error: Default Domain: (shell):1:8: MySQLError: You have an error
in your SQL syntax; check the manual that corresponds to your MySQL server version for
the right syntax to use near '' at line 1 (1064) in session.sql("select * from t
limit").execute().all();

When MySQL Shell enters either into JavaScript or Python mode, it searches for startup scripts to be executed. The startup scripts are JavaScript or Python specific scripts containing the instructions to be executed when the corresponding mode is initialized.

Startup scripts must be named as follows:

MySQL Shell searches the following paths for these files (in order of execution).

On Windows:

On Linux and OSX:

The environment variable MYSQLSH_HOME, used in option 2, defines the root folder of a standard setup of MySQL Shell. If MYSQLSH_HOME is not defined it is automatically calculated based on the location of the MySQL Shell binary, therefore on many standard setups it is not required to define MYSQLSH_HOME.

If MYSQLSH_HOME is not defined and the MySQL Shell binary is not in a standard install folder structure, then the path defined in option 3 in the above lists is used. If using a standard install or if MYSQLSH_HOME points to a standard install folder structure, then the path defined in option 3 is not used.

The user configuration path in option 4 can be overridden on all platforms by defining the environment variable MYSQL_USER_CONFIG_HOME. The value of this variable replaces %AppData%\MySQL\mysqlsh\ on Windows or ~/.mysqlsh/ on Unix.

There are two ways to add additional module search paths:

# Import the sys module
import sys

# Append the additional module paths
sys.path.append('~/custom/python')
sys.path.append('~/other/custom/modules')

// Append the additional module paths
shell.js.module_paths[shell.js.module_paths.length] = '~/custom/js';
shell.js.module_paths[shell.js.module_paths.length] = '~/other/custom/modules';

The prompt of MySQL Shell can be customized using prompt theme files. To customze the prompt theme file, either set the MYSQLSH_PROMPT_THEME environment variable to a prompt theme file name, or copy a theme file to the ~/.mysqlsh/prompt.json directory on Linux and Mac, or the %AppData%\MySQL\mysqlsh\prompt.json directory on Windows.

The user configuration path for the directory can be overridden on all platforms by defining the environment variable MYSQL_USER_CONFIG_HOME. The value of this variable replaces %AppData%\MySQL\mysqlsh\ on Windows or ~/.mysqlsh/ on Unix.

The format of the prompt theme file is described in the README.prompt file, and some sample prompt theme files are included. Some of the sample prompt theme files require a special font (for example SourceCodePro+Powerline+Awesome+Regular.ttf), or support from the terminal for color display. Most terminals support 256 colors in Linux and Mac. In Windows, color support requires either a 3rd party terminal program with support for ANSI/VT100 escapes, or Windows 10. On startup, if an error is found in the prompt theme file, an error message is printed and a default prompt theme is used.

You can configure MySQL Shell to match your preferences, for example to start up to a certain programming language or to customize output and so on. Configuration options can be set for only the current session, or options can be set permanently by persisting changes to the MySQL Shell configuration file. Online help for all options is provided. You can configure options using either MySQL Shell command, which is available in all MySQL Shell modes for querying and changing configuration options. Alternatively in JavaScript and Python modes, use the shell.options object.

This section describes which options are available and how to configure them.

MySQL JS > shell.options['history.autoSave']=1

MySQL JS > shell.options.set('history.maxSize', 5000)
MySQL JS > shell.options.set_persist('useWizards', 'true')
MySQL JS > shell.options.set_persist('history.autoSave', 1)

MySQL JS > shell.options.unset('history.maxSize')
MySQL JS > shell.options.unset_persist('useWizards')