Connect clients and applications to RDS for MySQL over SSL - ApsaraDB RDS - Alibaba Cloud - ApsaraDB RDS

This topic describes how to connect to an ApsaraDB RDS for MySQL instance by using the MySQL CLI or JDBC after you enable SSL encryption.

Prerequisites

SSL encryption is enabled. If not, see Quickly enable SSL encryption by using a cloud certificate or Enable SSL encryption by using a custom certificate.
You have the database CA certificate. To obtain the certificate, see Download a CA certificate or Enable SSL encryption by using a custom certificate.

Procedure

Connect to MySQL using a cloud certificate

After you enable SSL encryption, whether the connection between your client and the ApsaraDB RDS for MySQL instance is encrypted depends on the client type and its configuration. You can modify the client settings or your application code to require an encrypted connection and verify the identity of the instance.

Important

Instances that run MySQL 8.4, 8.0, or 5.7 with a minor engine version of 20241130 or later support forced SSL encryption. If you enable forced SSL encryption, clients can connect to the instance only with an SSL-encrypted connection.

MySQL CLI

For clients that run MySQL 5.7.11 or later, you can add the --ssl-mode option to the connection command to configure SSL encryption.

Note

For MariaDB clients and MySQL clients that run a version earlier than 5.7.11, use options such as --ssl and --ssl-verify-server-cert. For more information, see the official documentation of MariaDB and MySQL.

--ssl-mode=DISABLED: The connection is not encrypted.
--ssl-mode=PREFERRED or without an --ssl-mode option: Attempts to establish an encrypted connection. If the attempt fails, the client uses an unencrypted connection.
--ssl-mode=REQUIRED: An encrypted connection is required. The connection fails if an encrypted connection cannot be established.
--ssl-mode=VERIFY_CA: Requires an encrypted connection and verifies the server certificate against the local CA certificate.
--ssl-mode=VERIFY_IDENTITY: Requires an encrypted connection, verifies the server certificate against the local CA certificate, and validates that the hostname or IP address in the server certificate matches the one used for the connection.

Example 1: Attempts to establish an encrypted connection. If the attempt fails, an unencrypted connection is used.

mysql -h {endpoint} -u {username} -p --ssl-mode=PREFERRED

Example 2: Requires an encrypted connection and verification of the server certificate.

mysql -h {endpoint} -u {username} -p --ssl-mode=VERIFY_CA --ssl-ca={path-to-ca-certificate}/ApsaraDB-CA-Chain.pem

Note

In the preceding examples, replace {endpoint}, {username}, and {path-to-ca-certificate} with your actual values.
For more information about the --ssl-mode option, see the official MySQL documentation.

MySQL Workbench

Open MySQL Workbench and choose Database > Manage Connections.
Enter the endpoint, username, and password.
On the SSL tab, configure the Use SSL parameter, specify the downloaded PEM-formatted CA certificate for the SSL CA File field, and then click Test Connection or OK.
Note
For a description of the Use SSL parameter options, see the --ssl-mode descriptions in the MySQL CLI section.

Data Management (DMS)

When you add an ApsaraDB RDS for MySQL instance in Data Management (DMS), you can set the Enable SSL parameter. For more information, see Register an Alibaba Cloud database instance.

You can also right-click an existing instance, select Edit Instance, and then configure the settings in the Advanced Information section.

Application code

Java

Connector/J (mysql-connector-java) is the official JDBC driver for MySQL. This example uses mysql-connector-java 8.0.19 as a dependency.

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.19</version>
</dependency>

Note

The following sample code uses the sslMode property to specify the SSL mode. This property is supported in mysql-connector-java 8.0.13 and later. If you use an earlier version, you must use the useSSL, requireSSL, and verifyServerCertificate properties instead. For more information, see the MySQL documentation.

Sample code:

package com.aliyun.sample;

import com.mysql.cj.jdbc.MysqlDataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class Sample {

    public static void main(String[] args) {

        Connection conn = null;
        MysqlDataSource mysqlDS=null;

        try{
            mysqlDS = new MysqlDataSource();
            // Set SslMode as needed. For available options, see the MySQL CLI section.
            mysqlDS.setSslMode("VERIFY_IDENTITY");
          
            // The truststore stores the CA certificate. The truststore type is set to JKS.
            mysqlDS.setTrustCertificateKeyStoreType("JKS");
            // Replace the path after file:/ with the path to your ApsaraDB-CA-Chain.jks file.
            mysqlDS.setTrustCertificateKeyStoreUrl("file:/D:\\ApsaraDB-CA-Chain\\ApsaraDB-CA-Chain.jks");
            // The password for the downloaded JKS file is apsaradb and cannot be changed.
            mysqlDS.setTrustCertificateKeyStorePassword("apsaradb");
          
            // Your database endpoint
            mysqlDS.setServerName("rm-xxxxxx.mysql.rds.aliyuncs.com");
            // Your database port
            mysqlDS.setPort(3306);
            // Your database username
            mysqlDS.setUser("xxxxxx");
            // Your database password
            mysqlDS.setPassword("xxxxxx");
            // Your database name
            mysqlDS.setDatabaseName("xxxxxx");

            conn = mysqlDS.getConnection();

        }catch(Exception e){
            e.printStackTrace();
        } finally {
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

Python

# Install pymysql: pip install pymysql

import pymysql

try:
    ssl_config = {"ca":"/path/to/path/ca.crt", "mode":"VERIFY_CA"}   # "ca" is the CA certificate path. "mode" is ssl-mode.
    conn = pymysql.connect(host='******.mysql.rds.aliyuncs.com', user='*****', passwd='******', db='*****', ssl=ssl_config)
    cursor = conn.cursor()
    cursor.execute('select version()')
    data = cursor.fetchone()
    print('Database version:', data[0])
    cursor.close()
except pymysql.Error as e:
    print(e)

Connect to MySQL using a custom certificate

MySQL CLI

For clients that run MySQL 5.7.11 or later, you can add the --ssl-mode option to the connection command to configure SSL encryption.

Note

--ssl-mode=DISABLED: The connection is not encrypted.
--ssl-mode=PREFERRED or without an --ssl-mode option: Attempts to establish an encrypted connection. If the attempt fails, the client uses an unencrypted connection.
--ssl-mode=REQUIRED: An encrypted connection is required. The connection fails if an encrypted connection cannot be established.
--ssl-mode=VERIFY_CA: Requires an encrypted connection and verifies the server certificate against the local CA certificate.
--ssl-mode=VERIFY_IDENTITY: Requires an encrypted connection, verifies the server certificate against the local CA certificate, and validates that the hostname or IP address in the server certificate matches the one used for the connection.

Examples:

Attempts to establish an encrypted connection. If the attempt fails, an unencrypted connection is used.
```
mysql -h {endpoint} -u {username} -p --ssl-mode=PREFERRED
```

Requires an encrypted connection and verification of the server certificate.

mysql -h {endpoint} -u {username} -p --ssl-mode=VERIFY_CA --ssl-ca={path-to-custom-ca-certificate}

Note

In the preceding example, you must replace {RDS endpoint}, {RDS username}, {CA certificate path}, and {custom CA certificate path} with their actual values.
For more information about the --ssl-mode option, see the official MySQL documentation.

MySQL Workbench

Open MySQL Workbench and choose Database > Manage Connections.
Enter the endpoint, username, and password.
On the SSL tab, configure the Use SSL parameter and specify your custom CA certificate for the SSL CA File field. Then, click Test Connection or OK.

Note

For a description of the Use SSL parameter options, see the --ssl-mode descriptions in the MySQL CLI section.

Data Management (DMS)

When you add an ApsaraDB RDS for MySQL instance in Data Management (DMS), you can set the Enable SSL parameter. For more information, see Register an Alibaba Cloud database instance.

You can also right-click an existing instance, select Edit Instance, and then configure the settings in the Advanced Information section.

Application code

Java

Connector/J (mysql-connector-java) is the official JDBC driver for MySQL. This example uses mysql-connector-java 8.0.19 as a dependency.

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.19</version>
</dependency>

Important

If you use a custom certificate, you must manually generate a JKS file as follows:

Convert your client certificate and private key to PKCS#12 format because the keytool utility does not directly support importing a private key and certificate from PEM format. Use OpenSSL to perform the conversion:

openssl pkcs12 -export -in {custom-ca-certificate} -inkey {custom-ca-private-key} -out keystore.p12 -name ganyang -CAfile {custom-ca-certificate}
# Enter a password. This password is required when you establish a connection.
Enter Encryption Password:
Verifying - Enter Encryption Password:

Use the keytool utility to import the PKCS#12 file to a new JKS file:

keytool -importkeystore -deststorepass JKS-password -destkeypass key-password -destkeystore keystore.jks -deststoretype pkcs12 -srckeystore keystore.p12 -srcstoretype pkcs12 -srcstorepass P12-password -alias your-alias

Parameters:

-deststorepass: The password for the JKS file.
-destkeypass: The password for the key in the JKS file.
-destkeystore: The JKS file to create or an existing JKS file.
-deststoretype: The destination file type (PKCS#12).
-srckeystore: The source PKCS#12 file.
-srcstoretype: The source file type (PKCS#12).
-srcstorepass: The password for the source PKCS#12 file.
-alias: The alias for the client certificate and private key.

Obtain the JKS file: After the import is successful, find the keystore.jks file in the execution directory.

Note

package com.aliyun.sample;

import com.mysql.cj.jdbc.MysqlDataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class Sample {

    public static void main(String[] args) {

        Connection conn = null;
        MysqlDataSource mysqlDS=null;

        try{
            mysqlDS = new MysqlDataSource();
            // Set SslMode as needed. For available options, see the MySQL CLI section.
            mysqlDS.setSslMode("VERIFY_IDENTITY");

            // The following example shows the connection parameters. Replace the truststore values with the path and password of the JKS file that you generated from your custom certificate.
            // The truststore stores the CA certificate. The truststore type is set to JKS.
            mysqlDS.setTrustCertificateKeyStoreType("JKS");
            // Replace the path after file:/ with the path to your custom JKS file.
            mysqlDS.setTrustCertificateKeyStoreUrl("file:/D:\\ApsaraDB-CA-Chain\\ApsaraDB-CA-Chain.jks");
            // Use the password you created for your custom JKS file.
            mysqlDS.setTrustCertificateKeyStorePassword("apsaradb");
          
            // Your database endpoint
            mysqlDS.setServerName("rm-xxxxxx.mysql.rds.aliyuncs.com");
            // Your database port
            mysqlDS.setPort(3306);
            // Your database username
            mysqlDS.setUser("xxxxxx");
            // Your database password
            mysqlDS.setPassword("xxxxxx");
            // Your database name
            mysqlDS.setDatabaseName("xxxxxx");

            conn = mysqlDS.getConnection();

        }catch(Exception e){
            e.printStackTrace();
        } finally {
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

Python

# Install pymysql: pip install pymysql

import pymysql

try:
    ssl_config = {"ca":"/path/to/path/ca.crt", "mode":"VERIFY_CA"}   # "ca" is the CA certificate path. "mode" is ssl-mode.
    conn = pymysql.connect(host='******.mysql.rds.aliyuncs.com', user='*****', passwd='******', db='*****', ssl=ssl_config)
    cursor = conn.cursor()
    cursor.execute('select version()')
    data = cursor.fetchone()
    print('Database version:', data[0])
    cursor.close()
except pymysql.Error as e:
    print(e)