MySQL Shell 9.1  /  ...  /  Using an SSH Tunnel

4.3.7 Using an SSH Tunnel

MySQL Shell supports SSH tunneling for connections to MySQL server instances. An SSH tunnel lets unencrypted traffic pass over an encrypted connection, and enables authorized remote access to servers that are protected from outside connections by a firewall.

The use of AdminAPI commands is not supported over connections made from MySQL Shell using SSH tunneling, with the exception of the commands to deploy, start, stop, kill, and delete sandbox instances (dba.deploySandboxInstance, dba.startSandboxInstance, dba.stopSandboxInstance, dba.killSandboxInstance, and dba.deleteSandboxInstance). The sandbox commands are always executed locally to the MySQL Shell instance.

Once established, an SSH tunnel can be shared between connections to the same host from the same user connecting from the same remote server instance. The MySQL Shell function shell.listSshConnections() lists the currently connected and active SSH tunnels from the MySQL Shell session, with the URI of the SSH server and of the connected MySQL server instance. If you specify the same SSH connection details, MySQL Shell automatically reuses the existing tunnel.

You can select the SSH configuration file and identity file (private key) that are used for the connection. When you set up an SSH tunnel, MySQL Shell selects an SSH configuration file in the following order of priority:

  1. An SSH configuration file that you specify as a connection option.

  2. An SSH configuration file that you set as a default using the MySQL Shell configuration option ssh.configFile. For instructions to set this option, see Section 13.4, “Configuring MySQL Shell Options”.

  3. The standard SSH configuration file ~/.ssh/config.

The known hosts file is read from the default location (~/.ssh/known_hosts) unless a different configuration is set in the SSH configuration file.

For the identity file (private key), you can specify a custom file with the ssh-identity-file option at connection time. There is no option to set a custom default for the identity file. If you do not specify one, the SSH library uses the following sequence of authentication attempts until one succeeds:

  1. If an SSH agent is in use, authentication is attempted with the identity files configured there if available.

  2. If an identity file is specified for the target host in the SSH configuration file, authentication is attempted using that file.

  3. If neither of those options is available or the authentication attempt fails, authentication is attempted using the standard private key file in the SSH configuration folder (~/.ssh/id_rsa).

The default buffer size for data transfer through the SSH tunnel is 10240 bytes. You can change this by setting the MySQL Shell configuration option ssh.bufferSize. For instructions to set this option, see Section 13.4, “Configuring MySQL Shell Options”.

SSH tunneling is available when you use any of the MySQL Shell connection methods - the shell.connect() method, mysqlsh command parameters, or the \connect MySQL Shell command.

shell.connect()

When you use the shell.connect() method to connect while MySQL Shell is running, you can specify a URI for connection to the SSH server, or use key-value pairs for the connection data. The following options are available with this method:

  • ssh: The URI for connection to the SSH server. The URI format is [user@]host[:port].

  • uri: The URI for the MySQL server instance that is to be accessed through the SSH tunnel. The URI format is [scheme://] [user@]host:port. Do not use the base connection parameters (scheme, user, host, port) to specify the MySQL server connection for SSH tunneling, just use this option. The port must be specified.

  • ssh-password: The password for the connection to the SSH server.

  • ssh-config-file: An SSH configuration file for the connection to the SSH server.

  • ssh-identity-file: An identity file to use for the connection to the SSH server.

  • ssh-identity-pass: The passphrase for the identity file specified by the ssh-identity-file option.

These options are also available when you use the shell.openSession() method, which works in the same way as shell.connect() but creates and returns a session object, rather than setting it as the global session for MySQL Shell. For full instructions to use this connection method and the other options that are available, see Connecting to the Server Using URI-Like Strings or Key-Value Pairs.

mysqlsh command parameters

When you connect using command-line options while MySQL is starting up, you can specify a URI for connection to the SSH server. The following options are available with this method:

  • --ssh: The URI for connection to the SSH server. The URI format is [user@]host[:port]. When you use this option, the port for connection to the MySQL server instance must be specified in the MySQL instance URI.

  • --ssh-config-file: An SSH configuration file for the connection to the SSH server. If you specify this option with an empty value, the custom default SSH configuration file specified by --ssh.configFile is ignored, and the ~/.ssh/config file is used instead.

  • --ssh-identity-file: An identity file to use for the connection to the SSH server.

For full instructions to use this connection method and the other options that are available, see Section 4.3.1, “Connecting using Individual Parameters”.

\connect MySQL Shell command

When you connect using the \connect command while MySQL Shell is running, you can specify a URI for connection to the SSH server. There are no additional options for the SSH connection, so you must use the default identity file ~/.ssh/id_rsa, and the default SSH configuration file, which can be either the standard file ~/.ssh/config or a custom default that you set using the MySQL Shell configuration option ssh.configFile (see Section 13.4, “Configuring MySQL Shell Options”).

To get additional setup options you can create an SSH tunnel using the shell.connect() method or on the command line while MySQL Shell is starting, and then reuse it with the \connect command. When you are in a MySQL Shell session, you can view the currently connected SSH tunnels using the shell.listSshConnections() command.

MySQL Shell’s Secret Store can store passwords and passphrases for connection to the SSH server and for the identity file, to be automatically retrieved for future connections. If you provide a password or passphrase in the connection options it is used instead of any password that is stored in the Secret Store. Note that although there are options to allow it, specifying an explicit password in the connection data is insecure and not recommended. MySQL Shell prompts for a password interactively when one is required, either for the connection to the SSH server or for the identity file. For example:

mysql-js> shell.connect({uri:"mysql://root:sandbox@192.0.2.3:3306",
        > ssh:"root@198.51.100.4:2222", "ssh-identity-file":"/home/hanna/.ssh/config_pw"})
Creating a Classic session to 'root@192.0.2.3:3306'
Opening SSH tunnel to 198.51.100.4:2222...
Please provide key passphrase for /home/hanna/.ssh/config_pw: ********
Save password for 'file:/home/hanna/.ssh/config_pw'? 
[Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 7869
Server version: 8.0.28 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
<ClassicSession:root@192.0.2.3:3306>