Connection Data

On the API exposed on the MySQL Shell, several functions require some connection data to be provided.

The connection data may be specified in the following formats:

• A URI string
• A dictionary with the connection options

A basic URI string has the following format:

[scheme://][user[:password]@]<host[:port]|socket>[/schema][?option=value&option=value...]

Connection Options

The following options are valid for use either in a URI or in a dictionary:

• ssl-mode: The SSL mode to be used in the connection.
• ssl-ca: The path to the X509 certificate authority file in PEM format.
• ssl-capath: The path to the directory that contains the X509 certificate authority files in PEM format.
• ssl-cert: The path to the SSL public key certificate file in PEM format.
• ssl-key: The path to the SSL private key file in PEM format.
• ssl-crl: The path to file that contains certificate revocation lists.
• ssl-crlpath: The path of directory that contains certificate revocation list files.
• ssl-cipher: The list of permissible encryption ciphers for connections that use TLS protocols up through TLSv1.2.
• tls-version: List of protocols permitted for secure connections.
• tls-ciphers: List of TLS v1.3 ciphers to use.
• auth-method: Authentication method.
• get-server-public-key: Request public key from the server required for RSA key pair-based password exchange. Use when connecting to MySQL 8.0 servers with classic MySQL sessions with SSL mode DISABLED.
• server-public-key-path: The path name to a file containing a client-side copy of the public key required by the server for RSA key pair-based password exchange. Use when connecting to MySQL 8.0 servers with classic MySQL sessions with SSL mode DISABLED.
• connect-timeout: The connection timeout in milliseconds. If not provided a default timeout of 10 seconds will be used. Specifying a value of 0 disables the connection timeout.
• compression: Enable compression in client/server protocol.
• compression-algorithms: Use compression algorithm in server/client protocol.
• compression-level: Use this compression level in the client/server protocol.
• connection-attributes: List of connection attributes to be registered at the PERFORMANCE_SCHEMA connection attributes tables.
• local-infile: Enable/disable LOAD DATA LOCAL INFILE.
• net-buffer-length: The buffer size for TCP/IP and socket communication.

When these options are defined in a URI, their values must be URL encoded.

The following options are also valid when a dictionary is used:

Base Connection Options

• uri: a URI string.
• scheme: the protocol to be used on the connection.
• user: the MySQL user name to be used on the connection.
• dbUser: alias for user.
• password: the password to be used on the connection.
• dbPassword: same as password.
• host: the hostname or IP address to be used on the connection.
• port: the port to be used in a TCP connection.
• socket: the socket file name to be used on a connection through unix sockets.
• schema: the schema to be selected once the connection is done.

SSH Tunnel Connection Options

• ssh: a SSHURI string used when SSH tunnel is required.
• ssh-password: the password the be used on the SSH connection.
• ssh-identity-file: the key file to be used on the SSH connection.
• ssh-identity-file-password: the SSH key file password.
• ssh-config-file: the SSH configuration file, default is the value of shell.options['ssh.configFile']

Attention

The dbUser and dbPassword options are will be removed in a future release.

The connection options have precedence over options specified in the connection options uri

The connection options are case insensitive and can only be defined once.

If an option is defined more than once, an error will be generated.

The options specified in the connection data determine the type of connection to be used.

The scheme option defines the protocol to be used on the connection, the following are the accepted values:

• mysql: for connections using the MySQL protocol.
• mysqlx: for connections using the X protocol.

If no protocol is specified in the connection data, the shell will first attempt connecting using the X protocol, if the connection fails it will then try to connect using the MySQL protocol.

In general, the Shell connects to the server using TCP connections, unless the connection data contains the options required to create any of the connections described below.

Unix-domain Socket Connections

To connect to a local MySQL server using a Unix-domain socket, the host must be set to 'localhost', no port number should be provided and the socket path should be provided.

When using the MySQL protocol, the socket path might not be provided, in such case a default path for the socket file will be used.

When using a connection dictionary, the socket path is set as the value for the socket option.

When using a URI, the socket path must be URL encoded as follows:

• user@/path%2Fto%2Fsocket.sock
• user@./path%2Fto%2Fsocket.sock
• user@../path%2Fto%2Fsocket.sock

It is possible to skip the URL encoding by enclosing the socket path in parenthesis:

• user@(/path/to/socket.sock)
• user@(./path/to/socket.sock)
• user@(../path/to/socket.sock)

Windows Named Pipe Connections

To connect to MySQL server using a named pipe the host must be set to '.', no port number should be provided.

If the pipe name is not provided the default pipe name will be used: MySQL.

When using a connection dictionary, the named pipe name is set as the value for the socket option.

When using a URI, if the named pipe has invalid characters for a URL, they must be URL encoded. URL encoding can be skipped by enclosing the pipe name in parenthesis:

• user@\\.\named.pipe
• user@(\\.\named.pipe)

Named pipe connections are only supported on the MySQL protocol.

Windows Shared Memory Connections

Shared memory connections are only allowed if the server has shared memory connections enabled using the default shared memory base name: MySQL.

To connect to a local MySQL server using shared memory the host should be set to 'localhost' and no port should be provided.

If the server does not have shared memory connections enabled using the default base name, the connection will be done using TCP.

Shared memory connections are only supported on the MySQL protocol.

SSL Mode

The ssl-mode option accepts the following values:

• DISABLED
• PREFERRED
• REQUIRED
• VERIFY_CA
• VERIFY_IDENTITY

TLS Version

The tls-version option accepts values in the following format: TLSv<version>, e.g. TLSv1.2, TLSv1.3.

Authentication method

In case of classic session, this is the name of the authentication plugin to use, i.e. caching_sha2_password. In case of X protocol session, it should be one of:

• AUTO,
• FROM_CAPABILITIES,
• FALLBACK,
• MYSQL41,
• PLAIN,
• SHA256_MEMORY.

Connection Compression

Connection compression is governed by following connection options: "compression", "compression-algorithms", and "compression-level".

"compression" accepts following values:

• REQUIRED: connection will only be made when compression negotiation is succesful.
• PREFFERED: (default for X protocol connections) shell will attempt to establish connection with compression enabled, but if compression negotiation fails, connection will be established without compression.
• DISABLED: (defalut for classic protocol connections) connection will be established without compression.

For convenience "compression" also accepts Boolean: 'True', 'False', '1', and '0' values which map to REQUIRED and DISABLED respectively.

"compression-algorithms" expects comma separated list of algorithms. Supported algorithms include:

• zstd
• zlib
• lz4 (X protocol only)
• uncompressed - special value, which if it appears in the list, causes connection to succeed even if compression negotiation fails.

If "compression" connection option is not defined, its value will be deduced from "compression-algorithms" value when it is provided.

"compression-level" expects an integer value. Valid range depends on the compression algorithm and server configuration, but generally following is expected:

• zstd: 1-22 (default 3)
• zlib: 1-9 (default 3), supported only by X protocol
• lz4: 0-16 (default 2), supported only by X protocol.

Connection Attributes

Connection attributes are key-value pairs to be sent to the server at connect time. They are stored at the following PERFORMANCE_SCHEMA tables:

• session_account_connect_attrs: attributes for the current session, and other sessions associated with the session account.
• session_connect_attrs: attributes for all sessions.

These attributes should be defined when creating a session and are immutable during the life-time of the session.

To define connection attributes on a URI, the connection-attributes should be defined as part of the URI as follows:

root@localhost:port/schema?connection-attributes=[att1=value1,att2=val2,...]

Note that the characters used for the attribute name and value must follow the URI standard, this is, if the character is not allowed it must be percent encoded.

To define connection attributes when creating a session using a dictionary the connection-attributes option should be defined, its value can be set in the following formats:

• Array of "key=value" pairs.
• Dictionary containing the key-value pairs.

Note that the connection-attribute values are expected to be strings, if other data type is used in the dictionary, the string representation of the used data will be stored on the database.

URL Encoding

URL encoded values only accept alphanumeric characters and the next symbols: -._~!$'()*+;

Any other character must be URL encoded.

URL encoding is done by replacing the character being encoded by the sequence: %XX

Where XX is the hexadecimal ASCII value of the character being encoded.

If host is a literal IPv6 address it should be enclosed in "[" and "]" characters.

If host is a literal IPv6 address with zone ID, the '%' character separating address from the zone ID needs to be URL encoded.