Chapter 18 MySQL Shell User Guide

MySQL Shell is a unified interface to operate MySQL Server through scripting languages such as JavaScript or Python. To maintain compatibility with previous versions, SQL can also be executed in certain modes. A connection to a MySQL server is required. In MySQL Shell these connections are handled by a Session object.

The following different types of Session objects are available:

MySQL Shell creates an XSession object by default. To choose which type of session should be created, use one of these options:

For more information, see Section 18.2.2.1, “Connecting using a URI String” and Section 18.2.2.2, “Connecting using Individual Parameters”.

You configure the MySQL Server which MySQL Shell connects to by passing the connection data in string format using the --uri command-line option.

Use the following format:

[dbuser[:[dbpassword]]@]host[:port][/schema]

Descriptions of these options:

If no password is specified using the URI, which is recommended, then the password is prompted for. The following examples show how to use these command-line options:

Although using a password-less account is not recommended, you can specify a user without a password using a : after the user name, for example:

shell> mysqlsh --uri user:@localhost

In addition to specifying a URI manually, you can use a stored session. See Section 18.5, “Stored Sessions” for more information.

In addition to specifying connection parameters using a URI, it is also possible to define the connection data using separate parameters for each value.

Use the following parameters:

The first 5 parameters match the tokens used in the URI format described at Section 18.2.2.1, “Connecting using a URI String”.

The --password parameter indicates the user should connect without a password.

For consistency, the following aliases are supported for some parameters:

Attempt to establish an XSession with a specified user at port 33065.

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

Attempt to establish a Classic Session with a specified user.

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

Attempt to establish a Node Session with a specified user.

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

Using SSL is possible when connecting to an SSL enabled MySQL server.

To configure an SSL connection, use the following command-line options:

The --ssl option is assumed to be 1 (enabled) if the other SSL options are set.

When the --uri option is specified in combination with some of the individual parameters, the address specified by the --uri option is used as the base connection data and the values provided using individual parameters override the corresponding value from the URI. If the --user option is used, it would replace any user specified as part of a URI.

For example to establish an XSession and override user from the URI:

shell> mysqlsh --uri user@localhost:33065 --user otheruser

If you open MySQL Shell without specifying connection parameters, MySQL Shell opens without an established global session. It is possible to establish a global session once MySQL Shell has been started using the following Shell Commands:

Configure the connection using the URI parameter, which follows the same syntax as for the --uri command-line option. For additional information, see Section 18.2.2.1, “Connecting using a URI String”.

For example:

mysql-js> \connect root@localhost
Creating XSession to root@localhost...
Enter password: ****
No default schema selected.
mysql-js>

mysql-js> \connect root@localhost?ssl_ca=/path/to/ca/file&\
ssl_cert=/path/to/cert/file&ssl_key=/path/to/key/file
Creating XSession to root@localhost...
Enter password: ****
No default schema selected.
mysql-js>

When a connection is made using the command-line options or by using any of the shell commands, a global session object is created. This session is global because once created, it can be used in any of the MySQL Shell execution modes.

Any global session object is available in JavaScript or Python modes because a variable called session holds a reference to it.

In SQL mode, both Node and Classic sessions can be used because they both expose SQL execution. In SQL mode an XSession cannot be used.

In addition to the global session object, sessions can be established and assigned to a different variable by using the functions available in the mysql and mysqlx JavaScript and Python modules.

For example, the following functions are provided by these modules:

The first of these functions is used to create an XSession which features the most comprehensive development API and supports X Protocol.

The second creates a Node Session which connects to a X Protocol enabled MySQL Server and allows SQL Execution.

The latter returns a Classic Session object which uses the traditional MySQL protocol and has a very limited development API.

connectionData can be either a URI as specified above or a dictionary containing the connection parameters. See Section 18.2.2.1, “Connecting using a URI String”.

The following example shows how to create a Node Session using the X Protocol:

mysql-js> var mysqlx=require('mysqlx').mysqlx;

mysql-js> var session=mysqlx.getNodeSession('root@localhost');

mysql-js> print(session)
<NodeSession:root@localhost>
mysql-js>

The following example shows how to create a Node Session using the X Protocol so that you can execute SQL:

mysql-js> var mysqlx=require('mysqlx').mysqlx;

mysql-js> var session=mysqlx.getNodeSession({host: 'localhost', dbUser: 'root'});

mysql-js> print(session)
<NodeSession:root@localhost>
mysql-js>

The following example shows how to create a Classic Session:

mysql-js> var mysql=require('mysql').mysql;

mysql-js> var session = mysql.getClassicSession('root@localhost:3307');
mysql-js> print(session)
<ClassicSession:root@localhost:3307>
mysql-js>

mysql-js> var mysqlx=require('mysqlx').mysqlx;

mysql-js> var session=mysqlx.getNodeSession({host: 'localhost',
                                             dbUser: 'root',
                                             dbPassword: 'mypasswd',
                                             ssl_ca: "path_to_ca_file",
                                             ssl_cert: "path_to_cert_file",
                                             ssl_key: "path_to_key_file"});

mysql-js> print(session)
<NodeSession:root@localhost>
mysql-js>

The table format is used by default when MySQL Shell is in interactive mode. The output is presented as a formatted table for a better view and to aid analysis.

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>

To get this output format when running in batch mode, use the --table command-line option.

This format is used by default when running MySQL Shell in batch mode, to have better output for automated analysis.

>echo "select * from sakila.actor limit 3;" | mysqlsh --classic --uri root@192.168.1.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

MySQL Shell supports the JSON format for output and it is available both in interactive and batch mode. This output format can be enabled using the --json command-line option:

JSON Format in Batch Mode

shell>echo "select * from sakila.actor limit 3;" | mysqlsh --json --sqlc --uri root@192.168.1.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@192.168.1.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@192.168.1.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>

JSON Format in Interactive Mode (started with --json=raw)

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>

JSON Format in Interactive Mode (started with --json=pretty)

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>

When an operation is executed, in addition to any results returned, some additional information is available. This includes information such as the number of affected rows, warnings, duration, and so on, when any of these conditions is true:

It is possible to specify statements over multiple lines. When in Python or JavaScript mode, multiple-line mode is automatically enabled when a block of statements starts like in function definitions, if/then statements, for loops, and so on. In SQL mode multiple line mode starts when the command \ is issued.

Once multiple-line mode is started, the subsequently entered statements are cached.

For example:

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

To add a new connection use:

shell.storedSessions.add(name, connectionData[, override])

To update the connection data for a specific session name use:

shell.storedSessions.update(name, connectionData)

To remove a connection use:

shell.storedSessions.remove(name)

To list the stored connections use:

shell.storedSessions

When the shell.storedSessions object is initialized, it loads the stored session data and creates a property to access them as shell.storedSessions.name

var session = mysqlx.getSession(shell.storedSessions.name)

When used this way, if the connection data has no password it is not requested. It is the developers responsibility to provide the password either using the stored session data or as an additional parameter as follows:

var session = mysqlx.getSession(shell.storedSessions.name, password)

Python uses the PYTHONPATH environment variable to allow extending the search paths for python modules. The value of this variable is a list of paths separated by:

To achieve this in JavaScript, MySQL Shell supports defining additional JavaScript module paths using the MYSQLSH_JS_MODULE_PATH environment variable. The value of this variable is a list of semicolon separated paths.

The addition of module search paths can be achieved for both languages through the corresponding startup script.

For Python modify the mysqlshrc.py file and append the required paths into the sys.path array.

# Import the sys module
import sys

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

For JavaScript the same task is achieved by adding code into the mysqlshrc.js file to append the required paths into the predefined shell.js_module_paths array.

// 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';