DocsICP FilingConsole
官方文档
首页

Integrate MySQL into OSS

更新时间：
复制 MD 格式
产品详情
我的收藏

This document shows how to integrate data from a self-managed MySQL database into a specified bucket in Alibaba Cloud Object Storage Service (OSS).

Prerequisites

Usage notes

  • This feature is in public preview. During the public preview, no SLA is provided.

  • This feature is available only in the China (Hangzhou), China (Shanghai), and China (Beijing) regions.

  • A data integration task supports only one table.

  • Use the same Alibaba Cloud account (root account) to create the bucket, purchase the AnalyticDB for MySQL 3.0 cluster, create the RAM user, and log on to DMS.

Preparations

Create an OSS bucket

  1. Log on to the OSS console.

  2. In the left-side navigation pane, click Buckets, then click Create Bucket.

  3. Configure the bucket parameters:

    • Bucket Name: Enter a name for the bucket.

    • Region: Select the Singapore region.

    • ACL: Select Private.

    • Configure other parameters based on your requirements.

    For more information, see Create a bucket.

  4. Click OK.

  5. Create a directory in the bucket:

    1. In the left-side navigation pane, click Buckets. Find and click the bucket you created.

    2. In the left-side navigation tree, choose Object Management > Objects.

    3. On the Objects page, click Create Directory.

    4. In the Create Directory panel, enter a value for Directory Name. > Note: For directory naming requirements, see Manage directories.

    5. Click OK.

Create a RAM user and grant permissions

  1. On the Users page, click Create User and configure the following:

    • Logon Name: Enter a logon name, for example, dts_testuser.

    • Access Mode: Select Using permanent AccessKey to access.

  2. Click OK. Record the AccessKey ID and AccessKey secret.

  3. Grant permissions to the RAM user:

    1. On the Users page, find the RAM user and click Add Permissions in the Actions column.

    2. Select the policies you need:

      Policy

      Description

      AliyunDTSFullAccess

      Permissions to manage DTS

      AliyunOSSFullAccess

      Permissions to manage OSS

      AliyunKMSReadOnlyAccess

      (Optional) Read-only permission on KMS

      AliyunKMSSecretUserAccess

      (Optional) Permission to obtain secrets from KMS

      AliyunKMSCryptoUserAccess

      (Optional) Permission to use KMS keys for cryptographic operations
      Note

      Instead of AliyunOSSFullAccess, you can grant AliyunOSSReadOnlyAccess and then configure fine-grained access on the Bucket Policy tab in the OSS console. Set Applied To to Whole Bucket, Authorized User to RAM User, and Authorized Operation to Full Access or Read/Write. See Bucket policies.

    3. Click Grant permissions, then click Close.

(Optional) Purchase and configure a KMS instance

Complete this section only if you want to encrypt the integrated data with Key Management Service (KMS).

Purchase a KMS instance

  1. Log on to the KMS console and select the Singapore region.

  2. In the left-side navigation pane, choose Resource > Instances.

  3. On the Instances page, click Create Instance and configure the following:

    • Key Management Type: Select Software Key Management.

    • Region: Select the Singapore region.

    • Configure other parameters based on your requirements. See Purchase and enable a KMS instance.

  4. Click Buy Now and complete the purchase.

Enable the KMS instance

  1. Return to the Instances page.

  2. On the Software Key Management tab, find the instance you purchased.

  3. Click Enable in the Actions column and complete the configuration. See Enable the KMS instance.

  4. Wait until the instance status changes to enabled.

Create a customer master key (CMK)

  1. In the left-side navigation pane, choose Resource > Keys.

  2. Click the Keys tab. On the right side of Create Key, set Instance ID to the KMS instance you created.

  3. Click Create Key and configure the following:

    • KMS Instance: Select the KMS instance you purchased.

    • Key Type: Select Symmetric Key.

    • Key Specifications: Select Aliyun_AES_256.

    • Configure other parameters based on your requirements. See Create a CMK.

  4. Click OK.

Create a generic secret

  1. In the left-side navigation pane, choose Resource > Secrets.

  2. Click the Generic Secrets tab. On the right side of Create Secret, set Instance ID to the KMS instance you created.

  3. Click Create Secret and configure the following:

    • Secret Value: Select Plain Text and enter a secret value in the Plain Text field. > Note: Run openssl rand 32 | base64 in an environment with OpenSSL installed to generate a random secret value.

    • CMK: Select the key you created in the previous step.

    • Configure other parameters based on your requirements. See Manage and use generic secrets.

  4. Click OK.

Create an application access policy

  1. In the left-side navigation pane, choose Application Access > AAPs.

  2. Click the Policy tab, then click Create Policy.

  3. Configure the following:

    • Scope: Select the KMS instance you created.

    • RBAC Permissions: Select all available options.

    • Accessible Resources: Select the CMK and generic secret you created.

    • Configure other parameters based on your requirements. See Create an AAP.

  4. Click OK.

(Optional) Purchase and configure an AnalyticDB MySQL 3.0 cluster

Complete this section only if you want to store OSS metadata in an AnalyticDB MySQL 3.0 cluster.

  1. Purchase an AnalyticDB MySQL 3.0 cluster. See Create a cluster.

  2. Create a privileged account. See Create a database account.

  3. Create a resource group for the cluster. Set Task Type to Interactive and Engine to Spark — this lets you query the integrated data after the task runs. See Create a resource group.

Procedure

Step 1: Add MySQL instance

  1. Log in to DMS 5.0.

  2. In the left-side navigation pane, click the add icon next to Database Instances.

    Alternatively, choose Data Assets > Instances in the top navigation bar. On the Instance List tab, click New.

  3. On the Add Instance page, click the Third-party/Self-managed tab.

    Note

    To add an RDS for MySQL instance to DMS, see Add a cloud database instance.

  4. Under Relational Database, click MySQL, and then configure the instance parameters.

    Category

    Parameter

    Description

    Basic information

    Database type

    Select MySQL.

    Instance source

    Select Public Network.

    Note

    If your MySQL database is connected to Alibaba Cloud through a VPC, you can select VPC and configure the related parameters based on your requirements.

    Instance region

    Select the region where the MySQL database is located.

    Endpoint

    Enter the public endpoint of the MySQL database.

    Port

    Enter the service port of the MySQL database.

    Control mode

    Select Security Collaboration.

    Database account

    This account must have SELECT permissions on the data to be integrated.

    Database password

    Enter the password for the database account.

    Note

    You can configure other parameters as needed. For more information, see Add a third-party or self-managed database instance.

  5. At the bottom of the page, click Submit.

Step 2: Register the OSS instance

  1. Log on to the DMS console V5.0.

  2. Click the add icon next to Database Instances in the left-side navigation pane.

    Alternatively, choose Data Assets > Instances in the top navigation bar. On the Instance List tab, click New.

  3. In the Add Instance dialog box, click the Alibaba Cloud tab.

  4. In the File and Log section, select OSS and configure the following parameters:

    Configure other parameters based on your requirements. See Register an Alibaba Cloud database instance.
    Parameter Description
    File and Log Storage Select OSS
    Instance Region Select the region of the OSS bucket you created
    Connection Method Select Connection String Address
    Connection String Address DMS automatically populates this field based on the selected region. For example, if you set the Instance Region parameter to China (Hangzhou), the Connection String Address parameter is automatically set to oss-cn-hangzhou.aliyuncs.com
    Bucket (Optional) Select the bucket you created
    AccessKey ID Enter the AccessKey ID of the RAM user you created
    AccessKey secret Enter the AccessKey secret of the RAM user you created

  5. Click Submit.

Step 3: Register the AnalyticDB MySQL 3.0 cluster (optional)

Complete this step only if you want to store OSS metadata in the AnalyticDB MySQL 3.0 cluster.

  1. Log on to the DMS console V5.0.

  2. Click the add icon next to Database Instances in the left-side navigation pane.

    Alternatively, choose Data Assets > Instances in the top navigation bar. On the Instance List tab, click New.

  3. In the Add Instance dialog box, click the Alibaba Cloud tab.

  4. In the Data Warehouse section, select AnalyticDB MySQL 3.0 and configure the following parameters:

    Configure other parameters based on your requirements. See Register an Alibaba Cloud database instance.
    Parameter Description
    Database Type Select AnalyticDB MySQL 3.0
    Instance Region Select the region where the cluster resides
    Connection Method Select Instance ID
    Instance ID Select the ID of the AnalyticDB MySQL 3.0 cluster
    Database Account Enter the privileged account of the cluster
    Database Password Enter the password for the account

  5. Click Submit.

Step 4: Configure data integration task

  1. Create a task flow

    1. Log on to the DMS console V5.0.

    2. In the top navigation bar, choose Data+AI > Data+AI > Task Orchestration.

      In simple mode, hover over the 2023-01-28_15-57-17.png icon in the upper-left corner and choose All Features > Data+AI > Data Development > Task Orchestration.

    3. Click Create Task Flow. In the dialog box, set the Business Scenario and Task Flow Name, then click OK.

  2. Configure the data integration task.

    1. In the Task Type list on the left, drag the Data Lake Data Integration node from the Data Integration Nodes section onto the canvas.

    2. Double-click the node on the canvas.

    3. Configure the source and destination databases.

      Category

      Parameter

      Description

      Source Database

      Database Type

      Select MySQL.

      Access Method

      In this example, select Public IP Address.

      Instance Region

      Select the region where the DMS-managed MySQL instance is located.

      DMS database instance

      Select the DMS-managed MySQL instance.

      Destination Database

      Database Type

      Fixed as OSS.

      Access Method

      Fixed as Alibaba Cloud Instance.

      Instance Region

      Select the region where the DMS-managed OSS instance is located.

      DMS database instance

      Select the DMS-managed OSS instance.

      OSS bucket

      Select the destination bucket that you created in the "Preparations" section.

    4. Click Next Step: Configure Objects.

    5. Configure the task objects.

      Parameter

      Description

      Destination OSS Database Integration Format

      Select Delta, Parquet, or Hudi based on your requirements.

      Note

      If you select Hudi, you must right-click the table to be integrated in the Selected Objects list to configure the Primary key and Pre-merger key.

      Destination OSS Database Path

      Enter the directory that you created in the destination bucket in the "Preparations" section.

      Storage location of destination OSS metadata.

      Select where to store metadata (databases, tables, columns): DMS stores metadata in DMS; AnalyticDB MySQL 3.0 stores metadata in the cluster (select the registered cluster and enter the RAM User ID from the RAM User ID page of the cluster); None skips metadata storage.

      Source Objects

      In the Source Objects box, click the table that you want to integrate, and then click 向右小箭头 to move it to the Selected Objects box.

      Note

      You can select only one table.

      Selected Objects

      By default, the data is stored in the /<Destination OSS Database Path>/<Table Name> directory of the destination bucket. To change the subdirectory for data storage or set conditions to filter data, right-click the table in the Selected Objects box and configure the table information in the Edit Table dialog box. For more information, see Configuration reference.

    6. Click OK.

  3. Click Next Step: Complete.

  4. Run the task and verify results.

    1. In the upper-left corner of the page, click Try Run.

    2. Click the Execution Logs tab at the bottom of the canvas to monitor progress:

      • status SUCCEEDED — the task completed successfully.

      • status FAILED — the task failed. Review the error in the Execution Logs tab, correct the configuration, and run the task again.

    3. Verify the output in OSS: image

      1. Log on to the OSS console.

      2. In the left-side navigation pane, click Buckets. Search for and click the bucket you created.

      3. Navigate to the /<Destination OSS database path>/<Table name> directory to confirm the integrated data is present.

      <Destination OSS database path> is the Destination OSS Database Path you configured, and <Table name> is the value of Table Name in the Edit Table dialog box.

Configuration

Parameter

Description

Table Name

This name defines the subdirectory in the destination bucket for data storage.

Filter Conditions

This parameter is not configured in this example. For more information about how to configure filter conditions, see Set filter conditions.

Column

This parameter is not configured in this example. You can set the field name for the corresponding column in the destination bucket in Destination Column Name, or clear the Sync All Columns checkbox and select the columns that you want to integrate.

Partition Information

This parameter is not configured in this example. You can select one or more columns to use as partition keys in the destination bucket.

Primary key

The primary key (record key) and pre-combine key for writing data to the destination bucket in Hudi format.

Note

  • This parameter is available only when Destination OSS Database Integration Format is set to Hudi.

  • You can select one or more columns for the Primary key, but only one column for the Pre-merger key.

Pre-merger key

Previous：Configure a batch data integration nodeNext：Integrate data from Oracle to OSS