Access ApsaraDB RDS for MySQL data over an ENI with optional SSL encryption - AnalyticDB

Prerequisites

An AnalyticDB for MySQL Enterprise Edition, Basic Edition, or Data Lakehouse Edition cluster is created.
The AnalyticDB for MySQL cluster and the ApsaraDB RDS for MySQL instance must be in the same region. For more information, see Create a cluster and Create an ApsaraDB RDS for MySQL instance.
A job resource group is created for the AnalyticDB for MySQL cluster.
A database account is created for the AnalyticDB for MySQL cluster.
- If you use an Alibaba Cloud account, you need to only create a privileged account.
- If you use a Resource Access Management (RAM) user, you must create a privileged account and a standard account and associate the standard account with the RAM user.
The ApsaraDB RDS for MySQL instance must be in a security group with rules that allow access to the instance's port. For more information, see Set security group rules and Add a security group rule.
You have activated the OSS service and created a bucket in the same region as the AnalyticDB for MySQL cluster. For more information, see Activate OSS and Create a bucket.
You must have the required permissions. For more information, see Account authorization.

Important
For same-account access, the AliyunADBSparkProcessingDataRole role is required. For cross-account access, you must grant permissions to the other Alibaba Cloud account.

Prepare data

In your ApsaraDB RDS for MySQL instance, create a database and a table, and then insert data. For example:

CREATE DATABASE `test`;

CREATE TABLE `test`.`persons` (
  `id` int(11) DEFAULT NULL,
  `first_name` varchar(32) DEFAULT NULL,
  `last_name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

INSERT INTO persons VALUES(1,'a','b',5);
INSERT INTO persons VALUES(2,'c','d',6);
INSERT INTO persons VALUES(3,'e','f',7);

Access RDS MySQL data over an ENI

Upload the driver and JAR dependency

Write a sample program to access an ApsaraDB RDS for MySQL table, and then compile and package it. The generated JAR package is a dependency for the Spark job, and is named rds_test.jar in this article. The sample code is as follows:

package com.aliyun.spark
import org.apache.spark.sql.SparkSession
object SparkRDS {
  def main(args: Array[String]): Unit = {
    val sparkSession = SparkSession.builder()
    .appName("rds mysql test")
    .getOrCreate()
    // The internal endpoint of the ApsaraDB RDS for MySQL instance. For information about how to view the endpoint, see View or change internal and public endpoints and port numbers.
    val url = "jdbc:mysql://rm-bp11mpql1e01****.mysql.rds.aliyuncs.com"
    // The name of the ApsaraDB RDS for MySQL table in the "db_name.table_name" format.
    val dbtable = "test.persons"
    // The username for connecting to the ApsaraDB RDS for MySQL database.
    val user = "mysql_username"
    // The password for the database username.
    val password = "mysql_password"
    val jdbcDF = sparkSession.read
    .format("jdbc")
    .option("url", url)
    .option("driver", "com.mysql.jdbc.Driver")
    .option("dbtable", dbtable)
    .option("user", user)
    .option("password", password)
    .load()
    jdbcDF.show()
  }
}

Download the driver that is compatible with your ApsaraDB RDS for MySQL version from the official MySQL website.

This topic uses mysql-connector-java-8.0.11.jar as an example.
Upload the Spark job JAR dependency and the ApsaraDB RDS for MySQL driver to OSS. For more information, see Upload objects.

Same-account access

Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. Find the cluster that you want to manage and click the cluster ID.
In the left-side navigation pane, click Job Development > Spark JAR Development.
Above the editor, select a job resource group and a job type. This topic uses Batch as an example.

Enter the following job configuration in the editor.

{
    "name": "rds-mysql-example",
    "jars": [
        "oss://testBucketName/mysql-connector-java-8.0.11.jar"
    ],
    "file": "oss://testBucketName/rds_test.jar",
    "className": "com.aliyun.spark.SparkRDS",
    "conf": {
        "spark.adb.eni.enabled": "true",
        "spark.adb.eni.vswitchId": "vsw-bp17jqw3lrrobn6y****",
        "spark.adb.eni.securityGroupId": "sg-bp163uxgt4zandx****",
        "spark.driver.resourceSpec": "small",
        "spark.executor.instances": 1,
        "spark.executor.resourceSpec": "small"
    }
}

The following table describes the parameters.

Parameter	Description
name	The name of the Spark job.
jars	The OSS path of the ApsaraDB RDS for MySQL driver. This example uses the OSS path of the mysql-connector-java-8.0.11.jar package.
file	The OSS path of the Spark job's dependency JAR file.
className	The name of the entry class of the Java or Scala program. This example uses com.aliyun.spark.SparkRDS.
spark.adb.eni.enabled	Specifies whether to enable access over an ENI. Set to true to enable.
spark.adb.eni.vswitchId	The ID of the vSwitch. On the Database Connection page of the ApsaraDB RDS for MySQL instance, hover over the Virtual Private Cloud (VPC) to obtain the vSwitch ID.
spark.adb.eni.securityGroupId	The ID of the security group to which the ApsaraDB RDS for MySQL instance is added. If a security group is not added, see Set security group rules.
Other conf parameters	These configurations are mostly the same as those in open-source Spark. The parameters are in the `key:value` format. Separate multiple parameters with a comma. For more information about application configuration parameters, see Spark application configuration parameters.

Click Run Now.
After the Spark job runs successfully, you can view the data from the ApsaraDB RDS for MySQL table in the Spark logs. For information about how to view logs, see View information about a Spark application.

Cross-account access

Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. Find the cluster that you want to manage and click the cluster ID.
In the left-side navigation pane, click Job Development > Spark JAR Development.
Above the editor, select a job resource group and a job type. This topic uses Batch as an example.

Enter the following job configuration in the editor.

{
    "name": "rds-mysql-example",
    "jars": [
        "oss://testBucketName/mysql-connector-java-8.0.11.jar"
    ],
    "file": "oss://testBucketName/rds_test.jar",
    "className": "com.aliyun.spark.SparkRDS",
    "conf": {
        "spark.adb.eni.enabled": "true",
        "spark.adb.eni.vswitchId": "vsw-bp17jqw3lrrobn6y****",
        "spark.adb.eni.securityGroupId": "sg-bp163uxgt4zandx****",
        "spark.driver.resourceSpec": "small",
        "spark.executor.instances": 1,
        "spark.executor.resourceSpec": "small",
        "spark.adb.eni.roleArn":"acs:ram::testAccountID:role/testUserName"
    }
}

The following table describes the parameters.

Parameter

Description

spark.adb.eni.roleArn

The RAM role used for cross-account access to the ApsaraDB RDS for MySQL data source. Separate multiple roles with commas (,). The format is acs:ram::testAccountID:role/testUserName.

testAccountID: The ID of the Alibaba Cloud account that owns the ApsaraDB RDS for MySQL data source.
testUserName: The RAM role that is created for cross-account authorization. For more information, see Cross-account authorization.

For more information about the other parameters, see the parameter description table in the previous section.

Click Run Now.
After the Spark job runs successfully, you can view the data from the ApsaraDB RDS for MySQL table in the Spark logs. For information about how to view logs, see View information about a Spark application.

Access data over an SSL connection

To access data in an ApsaraDB RDS for MySQL instance over an SSL connection, you must enable SSL encryption for the instance and ensure the internal endpoint is encrypted. For more information, see Enable SSL encryption for an ApsaraDB RDS for MySQL instance.

Download and upload the CA certificate

Log on to the ApsaraDB RDS console. In the upper-left corner, select your instance's region. In the left-side navigation pane, click Instances and then click the ID of the target instance.
In the left-side navigation pane, click Security Controls.
Click Download CA Certificate.

Important
A CA certificate is valid for one year. To maintain SSL access, you must replace the certificate before it expires.
Decompress the CA certificate package and upload the JKS file to OSS. For more information, see Upload objects.

Upload the driver and JAR dependency

Write a sample program to access the ApsaraDB RDS for MySQL table, then compile and package it. In this example, the generated JAR package is named test.jar. The following sample code is used:

package org.example
import org.apache.spark.sql.SparkSession

object Test {
  def main(args: Array[String]): Unit = {
    // The OSS path of the JKS file, for example, oss://testBucketName/folder/ApsaraDB-CA-Chain.jks.
    val JKS_FILE_PATH = args(0)
    
    // The username for connecting to the ApsaraDB RDS for MySQL database.
    val USERNAME = args(1)

    // The password for the database username.
    val PASSWORD = args(2)

    // The name of the ApsaraDB RDS for MySQL database.
    val DATABASE_NAME = args(3)

    // The name of the table in the database.
    val TABLE_NAME = args(4)
    // The internal endpoint of the ApsaraDB RDS for MySQL instance.
    val mysqlUrl = "jdbc:mysql://rm-bp11mpql1e01****.mysql.rds.aliyuncs.com:3306/?" +
      "useSSL=true" +
      s"&trustCertificateKeyStoreUrl=file:///tmp/testBucketName/folder/ApsaraDB-CA-Chain.jks" +
      "&trustCertificateKeyStorePassword=apsaradb" +
      "&trustCertificateKeyStoreType=JKS"
    
    val spark = SparkSession.builder().getOrCreate()

     spark.read.format("jdbc")
      .option("driver", "com.mysql.cj.jdbc.Driver")
      .option("url", mysqlUrl)
      .option("user", USERNAME)
      .option("password",  PASSWORD)
      .option("dbtable", s"${DATABASE_NAME}.${TABLE_NAME}")
      .load()
      .show()
  }
}

The following table describes the connection parameters.

Parameter	Description
useSSL	Specifies whether to use an SSL-encrypted connection. Valid values: true: An SSL-encrypted connection is used. false (default): An SSL-encrypted connection is not used. In this example, set this parameter to true.
trustCertificateKeyStoreUrl	The local path of the JKS certificate file in the format `file:///tmp/<JKS_FILE_PATH>`, where `<JKS_FILE_PATH>` is the OSS path of the JKS certificate. For example, if the OSS path of the JKS certificate is `oss://testBucketName/folder/ApsaraDB-CA-Chain.jks`, the local path is `file:///tmp/testBucketName/folder/ApsaraDB-CA-Chain.jks`.
trustCertificateKeyStorePassword	The password of the JKS certificate. The value is fixed as apsaradb.
trustCertificateKeyStoreType	The storage format of the certificate. The value is fixed as JKS.

Upload the test.jar package to OSS. For more information, see Upload objects.

Same-account access

Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. Find the cluster that you want to manage and click the cluster ID.
In the left-side navigation pane, click Job Development > Spark JAR Development.
Above the editor, select a job resource group and a job type. This topic uses Batch as an example.

Enter the following job configuration in the editor.

{
    "file": "oss://testBucketName/test.jar",
    "className": "org.example.Test",
    "name": "MYSQL SSL Test",
    "conf": {
        "spark.kubernetes.driverEnv.ADB_SPARK_DOWNLOAD_FILES": "oss://testBucketName/folder/ApsaraDB-CA-Chain.jks",
        "spark.executorEnv.ADB_SPARK_DOWNLOAD_FILES": "oss://testBucketName/folder/ApsaraDB-CA-Chain.jks",
        "spark.driver.resourceSpec": "small",
        "spark.executor.instances": 1,
        "spark.executor.resourceSpec": "small",
        "spark.adb.eni.enabled": "true",
        "spark.adb.eni.vswitchId": "vsw-bp17jqw3lrrobn6y****",
        "spark.adb.eni.securityGroupId": "sg-bp163uxgt4zandx****"
    }
}

The following table describes the parameters.

Parameter	Description
name	The name of the Spark job.
file	The OSS path of the Spark job's dependency JAR file.
className	The name of the entry class of the Java or Scala program. This example uses org.example.Test.
spark.kubernetes.driverEnv.ADB_SPARK_DOWNLOAD_FILES	A Spark driver parameter that specifies the OSS path of the JKS certificate file. To specify multiple JKS certificate files, separate their paths with a comma. Example: `spark.kubernetes.driverEnv.ADB_SPARK_DOWNLOAD_FILES: "oss://testBucketName/a.jks,oss://testBucketName/b.jks"`.
spark.executorEnv.ADB_SPARK_DOWNLOAD_FILES	A Spark executor parameter that specifies the OSS path of the JKS certificate file. To specify multiple JKS certificate files, separate their paths with a comma. Example: `spark.executorEnv.ADB_SPARK_DOWNLOAD_FILES: "oss://testBucketName/a.jks,oss://testBucketName/b.jks"`.
spark.adb.eni.enabled	Specifies whether to enable access over an ENI. Set to true to enable.
spark.adb.eni.vswitchId	The ID of the vSwitch. On the Database Connection page of the ApsaraDB RDS for MySQL instance, hover over the VPC to obtain the vSwitch ID.
spark.adb.eni.securityGroupId	The ID of the security group to which the ApsaraDB RDS for MySQL instance is added. If a security group is not added, see Set security group rules.
Other conf parameters	These configurations are mostly the same as those in open-source Spark. The parameters are in the `key:value` format. Separate multiple parameters with a comma. For more information about application configuration parameters, see Spark application configuration parameters.

Click Run Now.
After the Spark job runs successfully, you can view the data from the ApsaraDB RDS for MySQL table in the Spark logs. For information about how to view logs, see View information about a Spark application.

Cross-account access

Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. Find the cluster that you want to manage and click the cluster ID.
In the left-side navigation pane, click Job Development > Spark JAR Development.
Above the editor, select a job resource group and a job type. This topic uses Batch as an example.

Enter the following job configuration in the editor.

{
    "file": "oss://testBucketName/test.jar",
    "className": "org.example.Test",
    "name": "MYSQL SSL Test",
    "conf": {
        "spark.kubernetes.driverEnv.ADB_SPARK_DOWNLOAD_FILES": "oss://testBucketName/folder/ApsaraDB-CA-Chain.jks",
        "spark.executorEnv.ADB_SPARK_DOWNLOAD_FILES": "oss://testBucketName/folder/ApsaraDB-CA-Chain.jks",
        "spark.driver.resourceSpec": "small",
        "spark.executor.instances": 1,
        "spark.executor.resourceSpec": "small",
        "spark.adb.eni.enabled": "true",
        "spark.adb.eni.vswitchId": "vsw-bp17jqw3lrrobn6y****",
        "spark.adb.eni.securityGroupId": "sg-bp163uxgt4zandx****",
        "spark.adb.eni.roleArn": "acs:ram::testAccountID:role/testUserName"
    }
}

The following table describes the parameters.

Parameter

Description

spark.adb.eni.roleArn

The RAM role used for cross-account access to the ApsaraDB RDS for MySQL data source. Separate multiple roles with commas (,). The format is acs:ram::testAccountID:role/testUserName.

testAccountID: The ID of the Alibaba Cloud account that owns the ApsaraDB RDS for MySQL data source.
testUserName: The RAM role that is created for cross-account authorization. For more information, see Cross-account authorization.

For more information about the other parameters, see the parameter description table in the previous section.

Click Run Now.
After the Spark job runs successfully, you can view the data from the ApsaraDB RDS for MySQL table in the Spark logs. For information about how to view logs, see View information about a Spark application.