This topic describes how to migrate a MySQL database to MaxCompute.

The database migration feature allows you to quickly upload all tables in a MySQL database to MaxCompute in an efficient and cost-effective manner. For more information, see Rules and restrictions.

Procedure

  1. Log on to the DataWorks console. In the left-side navigation pane, click Workspaces. On the Workspaces page, find the target workspace and click Data Integration in the Actions column.
  2. On the Data Integration page, click Connection in the left-side navigation pane. The Data Source page appears.
  3. Click Add Connection in the upper-right corner and add a MySQL connection named clone_database for database migration. For more information, see Configure a MySQL connection.
  4. Go back to the Data Integration page and click Migrate Database in the left-side navigation pane.
  5. On the Migrate Database page, find the added MySQL connection and click Migrate Tables in the Actions column.

    The database migration settings page consists of three functional modules.

    No. Functional module Description
    1 Tables to migrate This module lists all the tables in the MySQL connection named clone_database. Selected tables will be migrated.
    2 Advanced settings You can configure the rules for converting the table names, column names, and data types between MySQL and MaxCompute data tables.
    3 Basic settings You can specify whether to synchronize full or incremental data on a daily basis, whether to upload data in one or more batches, and the synchronization frequency and efficiency. You can also view the migration progress and results of each table after committing the sync node.
    If you select Synchronize Incremental Data Daily, you must set the Incremental configuration mode parameter. Valid values:
    • Incremental field: Data Integration automatically generates a WHERE clause based on the specified field.
    • Where condition for incremental extraction?: Explicitly specify a WHERE clause to extract incremental data.
  6. Click Advanced Settings and configure conversion rules based on your needs. For example, you can add an ods_ prefix to the name of each MaxCompute table.
  7. Specify basic settings. Set Sync Method to Synchronize Incremental Data Daily, and configure the incremental data to be determined based on the gmt_modified column. Data Integration will generate WHERE clauses based on the specified column and DataWorks scheduling parameters such as ${bdp.system.bizdate}.
    Data Integration reads data from MySQL tables by connecting to a remote MySQL database over Java Database Connectivity (JDBC) and executing SELECT statements. Data Integration uses standard SQL statements, and therefore you can configure WHERE clauses to filter data. The WHERE clause used in this example is provided as follows:
    STR_TO_DATE('${bdp.system.bizdate}', '%Y%m%d') <= gmt_modified AND gmt_modified < DATE_ADD(STR_TO_DATE('${bdp.system.bizdate}', '%Y%m%d'), interval 1 day)

    Select data upload in batches to protect the MySQL database from being overloaded. Configure Data Integration to start data synchronization for three tables every one hour from 00:00 each day.

    Click Commit Sync Node. Then, you can view the migration progress and results of each table.

  8. Find table a1 and click View Node to view the migration results.

You have configured a node for migrating a MySQL connection named clone_database to MaxCompute. This node is run based on the specified schedule, daily by default. You can also create retroactive node instances to transmit historical data. The database migration feature of Data Integration significantly simplifies the initial configurations for migrating your data to the cloud and reduces data migration costs.

The log in the following figure shows that table a1 is migrated successfully.