MySQL :: MySQL 8.0 Reference Manual :: 4.2.5 Connecting to the Server Using URI-Like Strings or Key-Value Pairs

MySQL 8.0 Reference Manual / ... / Connecting to the Server Using URI-Like Strings or Key-Value Pairs

4.2.5 Connecting to the Server Using URI-Like Strings or Key-Value Pairs

This section describes use of URI-like connection strings or key-value pairs to specify how to establish connections to the MySQL server, for clients such as MySQL Shell. For information on establishing connections using command-line options, for clients such as mysql or mysqldump, see Section 4.2.4, “Connecting to the MySQL Server Using Command Options”. For additional information if you are unable to connect, see Section 6.2.21, “Troubleshooting Problems Connecting to MySQL”.

Note

The term “URI-like” signifies connection-string syntax that is similar to but not identical to the URI (uniform resource identifier) syntax defined by RFC 3986.

The following MySQL clients support connecting to a MySQL server using a URI-like connection string or key-value pairs:

MySQL Shell
MySQL Router
MySQL Connectors which implement X DevAPI

This section documents all valid URI-like string and key-value pair connection parameters, many of which are similar to those specified with command-line options:

Parameters specified with a URI-like string use a syntax such as myuser@example.com:3306/main-schema. For the full syntax, see Connecting Using URI-Like Connection Strings.
Parameters specified with key-value pairs use a syntax such as {user:'myuser', host:'example.com', port:3306, schema:'main-schema'}. For the full syntax, see Connecting Using Key-Value Pairs.

Connection parameters are not case-sensitive. Each parameter, if specified, can be given only once. If a parameter is specified more than once, an error occurs.

This section covers the following topics:

Base Connection Parameters

The following discussion describes the parameters available when specifying a connection to MySQL. These parameters can be provided using either a string that conforms to the base URI-like syntax (see Connecting Using URI-Like Connection Strings), or as key-value pairs (see Connecting Using Key-Value Pairs).

scheme: The connection protocol to use. Use mysqlx for X Protocol connections and mysql for classic MySQL protocol connections. If no protocol is specified, the server attempts to guess the protocol.
user: The MySQL user account to use for the authentication process.
password: The password to use for the authentication process.

Warning

Specifying an explicit password in the connection specification is insecure and not recommended. Later discussion shows how to cause the password to be prompted for interactively.
host: The host where the server instance is running. The value can be either an IPv4 address, an IPv6 address, or a host name. If no host is specified, the default is localhost.
port: The TCP/IP network port on which the target MySQL server is listening for connections. If no part is specified, the default is 33060 for X Protocol connections and 3306 for classic MySQL protocol connections.
socket: The path to a Unix socket file or the name of a Windows named pipe. Values are local file paths. In URI-like strings, they must be encoded, using either percent encoding or by surrounding the path with parentheses. Parentheses eliminate the need to percent encode characters such as the / directory separator character. For example, to connect as root@localhost using the Unix socket /tmp/mysql.sock, specify the path using percent encoding as root@localhost?socket=%2Ftmp%2Fmysql.sock, or using parentheses as root@localhost?socket=(/tmp/mysql.sock).
schema: The default database for the connection. If no database is specified, the connection has no default database.

Additional Connection parameters

You can specify options for the connection, either as attributes in a URI-like string by appending ?attribute=value, or as key-value pairs. The following options are available:

ssl-mode: The desired security state for the connection. The following modes are permissible:
- DISABLED
- PREFERRED
- REQUIRED
- VERIFY_CA
- VERIFY_IDENTITY
For information about these modes, see the --ssl-mode option description in Command Options for Encrypted Connections.
ssl-ca: The path to the X.509 certificate authority in PEM format.
ssl-capath: The path to the directory that contains the X.509 certificates authorities in PEM format.
ssl-cert: The path to the X.509 certificate in PEM format.
ssl-key: The path to the X.509 key in PEM format.
ssl-crl: The path to the file that contains certificate revocation lists.
ssl-crlpath: The path to the directory that contains certificate revocation-list files.
ssl-cipher: The SSL cipher to use.
tls-version: The permissible TLS protocols for encrypted connections. The value is a list of one or more comma-separated protocol names. For details, see Section 6.3.5, “Encrypted Connection Protocols and Ciphers”. The following versions are permissible:
- TLSv1
- TLSv1.1
- TLSv1.2 (Supported only by commercial edition)
auth-method: The authentication method to use for the connection. The default is AUTO, meaning that the server attempts to guess. The following methods are permissible:
- AUTO
- MYSQL41
- SHA256_MEMORY
- FROM_CAPABILITIES
- FALLBACK
- PLAIN
For X Protocol connections, any configured auth-method is overridden to this sequence of authentication methods: MYSQL41, SHA256_MEMORY, PLAIN.
get-server-public-key: Request from the server the public key required for RSA key pair-based password exchange. Use when connecting to MySQL 8.0 servers over classic MySQL protocol with SSL mode DISABLED. You must specify the protocol in this case. For example:
```
mysql://user@localhost:3306?get-server-public-key=true
```
If server-public-key-path=file_name is given and specifies a valid public key file, it takes precedence over get-server-public-key.
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 over classic MySQL protocol with SSL mode DISABLED.
If server-public-key-path=file_name is given and specifies a valid public key file, it takes precedence over get-server-public-key.
connect-timeout: An integer value used to configure the number of seconds that clients, such as MySQL Shell, wait until they stop trying to connect to an unresponsive MySQL server.
compression: When set to true (or 1), this option enables compression of all information sent between the client and the server if possible. The default is no compression (false or 0). This option is available for MySQL Shell connections using classic MySQL protocol only. See Section 4.2.6, “Connection Compression Control”.

Connecting Using URI-Like Connection Strings

You can specify a connection to MySQL Server using a URI-like string. Such strings can be used with the MySQL Shell with the --uri command option, the MySQL Shell \connect command, MySQL Connectors which implement X DevAPI, and tools such as MySQL Router.

Note

The term “URI-like” signifies connection-string syntax that is similar to but not identical to the URI (uniform resource identifier) syntax defined by RFC 3986.

A URI-like connection string has the following syntax:

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

Important

Percent encoding must be used for reserved characters in the elements of the URI-like string. For example, if you specify a string that includes the @ character, the character must be replaced by %40. If you include a zone ID in an IPv6 address, the % character used as the separator must be replaced with %25.

The parameters you can use in a URI-like connection string are described at Base Connection Parameters.

If no password is specified in the URI-like string, which is recommended, interactive clients prompt for the password. The following examples show how to specify URI-like strings with the user name user_name. In each case, the password is prompted for.

An X Protocol connection to a local server instance listening at port 33065.
```
mysqlx://user_name@localhost:33065
```
A classic MySQL protocol connection to a local server instance listening at port 3333.
```
mysql://user_name@localhost:3333
```

An X Protocol connection to a remote server instance, using a host name, an IPv4 address, and an IPv6 address.

mysqlx://user_name@server.example.com/
mysqlx://user_name@198.51.100.14:123
mysqlx://user_name@[2001:db8:85a3:8d3:1319:8a2e:370:7348]

An X Protocol connection using a socket, with the path provided using either percent encoding or parentheses.
```
mysqlx://user_name@/path%2Fto%2Fsocket.sock
mysqlx://user_name@(/path/to/socket.sock)
```

An optional path can be specified, which represents a database.

# use 'world' as the default database
mysqlx://user_name@198.51.100.1/world

# use 'world_x' as the default database, encoding _ as %5F
mysqlx://user_name@198.51.100.2:33060/world%5Fx

An optional query can be specified, consisting of values each given as a key=value pair or as a single key. To specify multiple values, separate them by , characters. A mix of key=value and key values is permissible. Values can be of type list, with list values ordered by appearance. Strings must be either percent encoded or surrounded by parentheses. The following are equivalent.
```
ssluser@127.0.0.1?ssl-ca=%2Froot%2Fclientcert%2Fca-cert.pem\
&ssl-cert=%2Froot%2Fclientcert%2Fclient-cert.pem\
&ssl-key=%2Froot%2Fclientcert%2Fclient-key

ssluser@127.0.0.1?ssl-ca=(/root/clientcert/ca-cert.pem)\
&ssl-cert=(/root/clientcert/client-cert.pem)\
&ssl-key=(/root/clientcert/client-key)
```

The previous examples assume that connections require a password. With interactive clients, the specified user's password is requested at the login prompt. If the user account has no password (which is insecure and not recommended), or if socket peer-credential authentication is in use (for example, with Unix socket connections), you must explicitly specify in the connection string that no password is being provided and the password prompt is not required. To do this, place a : after the user_name in the string but do not specify a password after it. For example:

mysqlx://user_name:@localhost

Connecting Using Key-Value Pairs

You can specify a connection to MySQL Server using key-value pairs, supplied in language-natural constructs for the implementation. For example, you can supply connection parameters using key-value pairs as a JSON object in JavaScript, or as a dictionary in Python. Regardless of the way the key-value pairs are supplied, the concept remains the same: the keys as described in this section can be assigned values that are used to specify a connection. You can specify connections using key-value pairs in MySQL Shell's shell.connect() method or InnoDB cluster's dba.createCluster() method, and with some of the MySQL Connectors which implement X DevAPI.

Generally, key-value pairs are surrounded by { and } characters and the , character is used as a separator between key-value pairs. The : character is used between keys and values, and strings must be delimited (for example, using the ' character). It is not necessary to percent encode strings, unlike URI-like connection strings.

A connection specified as key-value pairs has the following format:

{ key: value, key: value, ...}

The parameters you can use as keys for a connection are described at Base Connection Parameters.

If no password is specified in the key-value pairs, which is recommended, interactive clients prompt for the password. The following examples show how to specify connections using key-value pairs with the user name 'user_name'. In each case, the password is prompted for.

An X Protocol connection to a local server instance listening at port 33065.
```
{user:'user_name', host:'localhost', port:33065}
```
A classic MySQL protocol connection to a local server instance listening at port 3333.
```
{user:'user_name', host:'localhost', port:3333}
```

An X Protocol connection to a remote server instance, using a host name, an IPv4 address, and an IPv6 address.

{user:'user_name', host:'server.example.com'}
{user:'user_name', host:198.51.100.14:123}
{user:'user_name', host:[2001:db8:85a3:8d3:1319:8a2e:370:7348]}

An X Protocol connection using a socket.

{user:'user_name', socket:'/path/to/socket/file'}

An optional schema can be specified, which represents a database.
```
{user:'user_name', host:'localhost', schema:'world'}
```

The previous examples assume that connections require a password. With interactive clients, the specified user's password is requested at the login prompt. If the user account has no password (which is insecure and not recommended), or if socket peer-credential authentication is in use (for example, with Unix socket connections), you must explicitly specify that no password is being provided and the password prompt is not required. To do this, provide an empty string using '' after the password key. For example:

{user:'user_name', password:'', host:'localhost'}

PREV HOME UP NEXT