Chapter 18 MySQL Shell User Guide

You can configure the MySQL Server which MySQL Shell connects to by passing the connection data in URI type string format. Such strings can be used with the --uri command option, the MySQL Shell \connect command, and the shell.connect() method.

The URI type string should use the following format:

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

The elements of a URI type string for a MySQL Shell connection are:

If no password is specified using the URI type string, which is recommended, then the password is prompted for. The following examples show how to specify URI type strings with the user name user, in each case the password is prompted for:

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

mysqlx://user:@localhost

In addition to specifying connection parameters using a URI type string, it is also possible to define the connection data when starting MySQL Shell using separate command parameters for each value. For a full reference of MySQL Shell command options see Section 4.5.7, “mysqlsh — The MySQL Shell”.

Use the following connection related parameters:

The first 5 parameters match the elements used in the URI type string format described at Section 18.2.1.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:

When parameters are specified in multiple ways, for example using both the --uri option and specifying individual parameters such as --user, the following rules apply:

The following examples show how to use command parameters to specify connections. Attempt to establish an X Protocol connection with a specified user at port 33065.

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

Attempt to establish a classic MySQL protocol connection with a specified user.

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

Using encrypted connections is possible when connecting to a TLS (sometimes referred to as SSL) enabled MySQL server. Much of the configuration of MySQL Shell is based on the options used by MySQL server, see Section 6.4, “Using Encrypted Connections” for more information.

To configure an encrypted connection at startup of MySQL Shell, use the following command options:

Alternatively the SSL options can be encoded as part of a URI type string as part of the query element. The available SSL options are the same as those listed above, but written without the proceeding hyphens. For example ssl-ca is the equivalent of --ssl-ca.

Paths specified in a URI type string must be percent encoded, for example:

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

See Section 18.2.1.1, “Connecting using a URI String” for more information.

When a connection is made using the command options or by using any of the MySQL 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 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:

connectionData can be either a URI type string as specified at Section 18.2.1.1, “Connecting using a URI String” or a dictionary containing the connection parameters.

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

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

The following example shows how to create a ClassicSession:

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',
                                             dbUser: 'root',
                                             dbPassword: 'password',
                                             ssl_ca: "path_to_ca_file",
                                             ssl_cert: "path_to_cert_file",
                                             ssl_key: "path_to_key_file"});

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 types of Session object are available:

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

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

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@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

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@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>

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>

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