Migrate self-managed MySQL on ECS to MaxCompute

Procedure

1. Create a MySQL data source in DataWorks.

   1. 1. Log on to the DataWorks console. In the target region, click More > Management Center in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Management Center.

      2. On the Workspace Management page, click Data Sources in the left-side navigation pane to open the data source page.

   2. Click Add Data Source.

   3. In the Add Data Source dialog box, click MySQL.

   4. In the Create MySQL Data Source dialog box, configure the parameters. For more information, see Configure a MySQL data source.

      This example uses the User-created Data Store with Public IP Addresses. For Connection Address, enter the private IP address of your ECS instance and the default MySQL port number, 3306.

      Note

      The Test Connectivity feature is not supported for self-managed MySQL data sources within a VPC. A failed connectivity test is expected at this stage.

   5. Click Test Connectivity for the required resource group.

      You can use an exclusive resource group for Data Integration to synchronize data. Each task can use only one resource group. If multiple resource groups are available, you must test the connectivity of each resource group that you want to use. This ensures that the resource group used for the synchronization task can connect to the data source. Otherwise, the data synchronization task will fail. For more information, see Network connectivity solutions.

   6. After the connectivity test passes, click Finish.

2. Create a MaxCompute table.

   You need to create a table in DataWorks to receive the test data from MySQL.

   1. Click the icon in the upper-left corner and choose All Products > Data Development and O&M > DataStudio.

   2. Create a Workflow. For more information, see Create a scheduled workflow.

   3. Right-click the new workflow and choose Create Table > Table.

   4. Enter a name for the MaxCompute table. This example uses the name good_sale, which is the same as the MySQL source table. Click DDL, enter the DDL statement, and then click Generate Table Schema.

      This example uses the following DDL statement. Note the data type conversions.

      CREATE TABLE IF NOT EXISTS good_sale(
         create_time string,
         category STRING,
         brand STRING,
         buyer_id STRING,
         trans_num BIGINT,
         trans_amount DOUBLE,
         click_cnt BIGINT
         );

   5. Enter a Display Name for the table and click Commit to Production Environment to create the good_sale table in MaxCompute.

3. Configure a Data Integration task.

   1. Right-click the workflow and choose Create Node > Data Integration > Batch Synchronization to create a Data Integration task.

   2. Select the MySQL data source that you added as the source and the MaxCompute data source as the destination. Click Conversion script to switch the Data Integration task to script mode.

      At this point, if an error occurs or you cannot select a Table from the data source, this is normal. Click Script Conversion in the toolbar to switch to script mode.

   3. In the right-side pane, click Resource Group configuration and select the exclusive resource group that you purchased.

      If you do not switch the task to the exclusive resource group for Data Integration, the task will fail.

   4. Enter the following script for the Data Integration task.

      {
          "type": "job",
          "steps": [
              {
                  "stepType": "mysql",
                  "parameter": {
                      "column": [//Source column names
                          "create_time",
                          "category",
                          "brand",
                          "buyer_id",
                          "trans_num",
                          "trans_amount",
                          "click_cnt"
                      ],
                      "connection": [
                          {
                              "datasource": "shuai",//Source data source
                              "table": [
                                  "good_sale"//Source database table name. This must be an array in square brackets.
                              ]
                          }
                      ],
                      "where": "",
                      "splitPk": "",
                      "encoding": "UTF-8"
                  },
                  "name": "Reader",
                  "category": "reader"
              },
              {
                  "stepType": "odps",
                  "parameter": {
                      "partition": "",
                      "truncate": true,
                      "datasource": "odps_source",//Destination MaxCompute data source name
                      "column": [//Destination column names
                          "create_time",
                          "category",
                          "brand",
                          "buyer_id",
                          "trans_num",
                          "trans_amount",
                          "click_cnt"
                      ],
                      "emptyAsNull": false,
                      "table": "good_sale"//Destination table name
                  },
                  "name": "Writer",
                  "category": "writer"
              }
          ],
          "version": "2.0",
          "order": {
              "hops": [
                  {
                      "from": "Reader",
                      "to": "Writer"
                  }
              ]
          },
          "setting": {
              "errorLimit": {
                  "record": "0"
              },
              "speed": {
                  "throttle": false,
                  "concurrent": 2
              }
          }
      }

   5. Click Run. You can check the Operational Logs at the bottom of the page to confirm that the data has been transferred to MaxCompute.