MySQL Secure Deployment Guide / Connecting to the Server

Chapter 14 Connecting to the Server

This section describes two connection methods. The first method uses the TLS (Transport Layer Security) protocol to establish an encrypted connection. The second method uses RSA key pair-based password exchange over an unencrypted connection.

The following procedures assume that mysql clients are available on remote client hosts. The procedures also assume that you have distributed client certificate and key files to the remote clients as described in Distributing Client Certificate and Key Files.

The user accounts created previously are used to connect to the server. See Chapter 13, Creating User Accounts.

Using an Encrypted Connection

MySQL client programs attempt to establish an encrypted connection if the server supports encrypted connections. In this deployment, the --ssl option is enabled for the server, which means encrypted connections are supported.

Using the mysql client program, establish a connection for the user1@203.0.113.11 account that you created previously. The user1@203.0.113.11 account was created with the REQUIRE X509 option, which requires that the user presents a valid certificate.
```
$> cd /usr/local/mysql
$> bin/mysql  --user=user1 -p --host=192.0.2.24 --ssl-mode=VERIFY_CA
       --ssl-ca=/path/to/ca.pem --ssl-cert=/path/to/client-cert.pem
       --ssl-key=/path/to/client-key.pem
```
- The --host option specifies the host where the MySQL server is running.
- The --ssl-mode=VERIFY_CA option ensures that an encrypted connection is established and verifies the TLS certificate against the configured Certificate Authority (CA) certificates; it ensures that client and server trust a common CA and thus are likely communicating with the correct party.
  
  Note
  
  Ideally, --ssl-mode should be set to VERIFY_IDENTITY. This option is like VERIFY_CA but it additionally requires that the server certificate matches the host to which the connection is attempted, which means that the server certificate must be signed by a valid Certificate Authority (CA) and have your server host as the Common Name (CN). The MySQL-generated certificates used in this deployment do not support this mode.
- The --ssl-ca, --ssl-cert, and --ssl-key options define the path to the distributed client certificate and key files, as described in Distributing Client Certificate and Key Files.
After connecting successfully, verify that the current connection uses encryption by checking the value of the Ssl_cipher status variable. If the value is empty, the connection is not encrypted. Otherwise, the connection is encrypted and the value indicates the encryption cipher or ciphersuite. For example:
```
mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_128_GCM_SHA256 |
+---------------+------------------------+
```

To view the TLS version and the cipher or ciphersuite for all connections, query the Sys schema session_ssl_status view as the MySQL root user:

$> cd /usr/local/mysql 
$> bin/mysql -u root -p
Enter password: (enter the root password here)

mysql> SELECT * FROM sys.session_ssl_status;
+-----------+-------------+------------------------+---------------------+
| thread_id | ssl_version | ssl_cipher             | ssl_sessions_reused |
+-----------+-------------+------------------------+---------------------+
|        51 |             |                        | 0                   |
|        52 | TLSv1.3     | TLS_AES_128_GCM_SHA256 | 0                   |
+-----------+-------------+------------------------+---------------------+

For more information about encrypted connections, see Configuring MySQL to Use Encrypted Connections.

Using RSA Key Pair-Based Password Exchange Over an Unencrypted Connection

Clients that authenticate using the caching_sha2_password plugin can connect to the server over an unencrypted connection using RSA key pair-based password exchange. (Both the client and server must be compiled using OpenSSL.)

To support RSA encryption, the server generates RSA public and private key files in the data directory:

$> cd /usr/local/mysql/data
$> ls *_key.pem
private_key.pem  public_key.pem

By default, the server also exposes variables for defining the RSA private key and public key paths:

caching_sha2_password_private_key_path

Defines the path name of the RSA private key file for the caching_sha2_password authentication plugin.
caching_sha2_password_public_key_path

Defines the path name of the RSA public key file for the caching_sha2_password authentication plugin.

If the RSA public key and private key files are located in the MySQL data directory and are named private_key.pem and public_key.pem, as they are in this deployment, the caching_sha2_password_private_key_path and caching_sha2_password_private_key_path options are configured by default.

When a client that uses the caching_sha2_password plugin attempts an unencrypted connection, the caching_sha2_password plugin sends the RSA public key to the client, but the key transfer can be avoided if the RSA public key is distributed to the client host and its location is defined using the --server-public-key-path option when establishing a connection. Avoiding the key transfer saves a round trip in the client/server protocol. This option is used in the instructions that follow. For information about distributing key files, see Distributing Client Certificate and Key Files.

To establish an unencrypted connection that uses RSA key pair-based password exchange, use the mysql client program and the user2@203.0.113.12 account that you created previously. The user2@203.0.113.12 account was created without SSL/TLS options to permit the account to establish an unencrypted connection to the server.

$> cd /usr/local/mysql
$> bin/mysql  --user=user2 -p --ssl-mode=DISABLED --host=192.0.2.24
       --server-public-key-path=/path/to/public_key.pem

The --host option specifies the host where the MySQL server is running.
The --ssl-mode=DISABLE option ensures that the connection is unencrypted.
The --server-public-key-path option defines the path name to the file on the client host (public_key.pem) that contains the same RSA public key used by the server.

PREV HOME UP NEXT