DocsICP FilingConsole
Documentation
Home Page Data Management User Guide Data+AI Task orchestration Task development Integrate MySQL into OSS

Integrate MySQL into OSS

Updated at:
Copy as MD
Product
My Favorites

This topic describes how to integrate data from a self-managed MySQL database into a specified bucket in Object Storage Service (OSS).

Prerequisites

Notes

  • Because this feature is in public beta, no Service Level Agreement (SLA) guarantee is provided.

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

  • A data integration task supports only one data table.

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

Preparations

Before you configure a data integration task, create the required bucket, RAM user, and AnalyticDB for MySQL 3.0 cluster.

Note

You need to purchase an AnalyticDB for MySQL 3.0 cluster only if you want to store the metadata of the destination OSS instance in the cluster.

Create a bucket

  1. Log on to the OSS console.

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

  3. Configure the bucket parameters.

    1. Enter a Bucket name.

    2. For Region, select the region where the data integration task will run.

    3. For Read/Write Permissions, select Private.

    4. Configure other parameters as required.

      For more information, see Create buckets.

  4. After you configure the parameters, click OK.

    Follow the on-screen instructions to complete the process.

  5. Create a directory.

    1. In the left-side navigation pane, click Buckets. Then, click the name of the destination bucket.

    2. In the left-side navigation pane, choose Files > Objects.

    3. On the Objects page, click Create directory.

    4. In the Create directory panel, enter a Directory name.

      Note

      For more information about the requirements for directory names, see Manage directories.

    5. Click OK.

RAM user and permissions

  1. Go to the Users page of the RAM console and create a RAM user.

    1. Click Create User.

    2. Set the Logon name.

      For example, enter dts_testuser.

    3. For Access mode, select Programmatic Access.

    4. Click OK.

    5. After you create the RAM user, save its AccessKey ID and AccessKey secret.

  2. Grant permissions to the RAM user.

    1. Return to the Users page. Find the RAM user that you created, and click Add permissions in the Actions column.

    2. Select permission policies.

      Permission policy

      Description

      AliyunDTSFullAccess

      The permissions to manage Data Transmission Service (DTS).

      AliyunOSSFullAccess

      The permissions to manage Object Storage Service (OSS).

      Note

      You can also grant a RAM user read-only access to Object Storage Service (OSS) (that is, AliyunOSSReadOnlyAccess) and authorize the RAM user on the Bucket Policy page in the OSS console. Set Applied To to Whole Bucket, select the RAM user from Subaccount for Authorized User, and select Full Access or Read/Write for Authorized Operation. For more information, see Bucket Policy.

    3. Click OK.

    4. After the permissions are granted, click Complete.

(Optional) Configure AnalyticDB for MySQL

  1. Purchase an AnalyticDB for MySQL 3.0 cluster.

    For more information, see Create a Data Lakehouse Edition cluster.

  2. Create a privileged account.

    For more information, see Create a database account.

  3. Create a resource group.

    Create a resource group for the AnalyticDB for MySQL 3.0 cluster to enable fast queries after data integration. The Job Type must be Interactive and the Engine must be Spark.

    For more information, see Create a resource group.

Procedure

Step 1: Add MySQL instance to DMS

  1. Log in to DMS 5.0.

  2. In the Database Instances area on the left side of the console home page, click the Add Instanceadd icon.

    Note

    Alternatively, on the top menu bar, choose Data Assets > Instances and then click Add.

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

    Note

    For more information about how to add an RDS MySQL instance to DMS, see Add a cloud database.

  4. In the Relational database section, click MySQL and 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.

    Instance region

    Select the region where the MySQL database resides.

    Endpoint

    Enter the public endpoint of the MySQL database.

    Port

    Enter the service port of the MySQL database.

    Control mode

    Select Manual.

    Database account

    Enter the logon account for the MySQL database. The account must have the SELECT privilege on the source data.

    Database password

    Enter the password for the database account.

    Note

    You can configure other parameters as required. For more information, see Add a self-managed database that is hosted on a third-party cloud.

  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. From the Data integration section of the Task types list, drag the Lakehouse integration node 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 MySQL instance that you added to DMS is located.

      DMS database instance

      Select the MySQL instance that you added to DMS.

      Destination Database

      Database Type

      This parameter is fixed to OSS.

      Access Method

      This parameter is fixed to Alibaba Cloud Instance.

      Instance Region

      Select the region where the OSS instance that you added to DMS is located.

      DMS database instance

      Select the OSS instance that you added to DMS.

      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 business requirements.

      Note

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

      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 Accounts page of the cluster).

      • None skips metadata storage.

      Source Objects

      In the Source Objects box, select the source table and then click 向右小箭头 to move it to the Selected Objects box.

      Note

      You can select only one table.

      Selected Objects

      By default, data is stored in the /<Target Path>/<Table Name> directory of the destination bucket. To change the subdirectory for data storage or set a data filter, right-click the table in the Selected Objects box and configure the table information in the Edit Table dialog box. For more information about the configuration, see Configuration details.

    6. Click OK.

  3. Click Next Step: Complete.

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

  5. Verify the output.

    1. Log on to the OSS console.

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

    3. In the Bucket name text box, enter the name of your bucket and search for it.

    4. Click the name of the target bucket.

    5. In the /<Target Path>/<Table Name> directory, view the result.

      Note

      <Target Path> is the Target path that you configured in Step 4: Configure data integration task. <Table Name> is the name of the table to be integrated or the Table Name specified in Configuration details.

Configuration

Parameter

Description

Table Name

This name defines the subdirectory within the destination bucket where data is stored.

Filter Conditions

No configuration is required for this example. For more information about how to configure a filter condition, see Set filter conditions.

Column

No configuration is required for this example. You can use Destination Column Name to set the field name for the corresponding column in the destination bucket. You can also clear the Sync All Columns checkbox and select the specific columns to integrate.

Partition Information

No configuration is required for this example. You can select one or more columns to serve as partition keys in the destination bucket.

Primary key

The record key (primary key) and pre-combine key used when data is written 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