All Products
Search
Document Center

Migrate on-premises databases to Alibaba Cloud through dedicated lines

Last Updated: Aug 19, 2019

DTS allows you to migrate on-premises databases to the cloud through dedicated lines. This article describes how to migrate from on-premises MySQL to ApsaraDB for MySQL through dedicated lines as an example.

Prerequisites

Before the migration, configure the connection between the on-premises database and the DTS server as follows:

  1. Ensure that the on-premises database connected to an Apsara VPC through dedicated lines.
  2. On the edge router of the dedicated lines, add the route towards the DTS server IP segment. On the details page of the edge router, click Add Route and set the parameters:
    • Target segment: DTS server IP segment of the region where the Apsara VPC is located.
    • Next hop direction: the VPC.
    • Next hop: VBR router interface
  3. On the customer-side access device of the dedicated lines, add the static route pointing to the DTS server IP segment.

    ip route DTS IP segment {Apsara-side IP}

    For example, if the DTS server IP segment is 100.104.52.0/24, and the Apsara-side IP of the edge router is 10.100.0.1, then the static route is ip route 100.104.52.0/24 10.100.0.1

    The preceding DTS server IP segment depends on the region of the Apsara VPC connected to the on-premises database. See the following table.

    Region DTS server IP segment
    Hangzhou 100.104.52.0/24
    Beijing 100.104.183.0/24
    Qingdao 100.104.72.0/24
    Shanghai 100.104.205.0/24
    Shenzhen 100.104.205.0/24
    Shanghai Finance Cloud 100.104.175.0/24
    Shenzhen Finance Cloud 100.104.72.0/24
    US (Silicon Vally) 100.104.175.0/24
    China (Hong Kong) 100.104.233.0/24
    Singapore 100.104.188.0/24
    Malaysia 100.104.5.0/24

Required permissions

When you use DTS to migrate data from on-premises MySQL to RDS for MySQL, the accounts of the source and target instances are required to be granted permissions based on migration types. The required permissions are as follows.

Migration Type On-premises MySQL database Target RDS for MySQL database
Structure migration select Read and write permissions
Existing data migration select Read and write permissions
Data change replication
  • select
  • replication slave
  • replication client
Read and write permissions

Migration procedure

The following describes how to migrate from on-premises MySQL to ApsaraDB RDS for MySQL through dedicated lines as an example.

Create a database on the RDS instance

During a data migration, if the database to be migrated does not exist in the target RDS instance, DTS automatically creates one in the target RDS instance. However, in either of the following cases, you need to manually create the database before configuring a migration task:

  • The database name does not comply with the RDS defined specification (that is, the name has a maximum of 64 characters, starts with a letter, ends with a letter or digit, and consists of lowercase letters, digits, underscores and hyphens).
  • The database name on the source instance is different from that on the target instance.

Create accounts

When configuring a migration task, you need to provide the accounts of the source and target instances. The accounts must have the permissions listed in the Required permissions section.

  • If the source on-premises MySQL instance account does not have the required permissions, grant permissions by referring to GRANT Syntax.
  • If the target ApsaraDB RDS instance does not have an account with read/write permissions, create one by referring to Create an account.

Enable binlog

To perform incremental data replication, the binlog must be enabled for the source database. Follow these steps to enable and configure binlog.

  1. Run the following command to check whether Binlog is enabled for the source database.

    Confirm binlog Is Enabled for Source Database

    If log_bin is OFF, then binlog is not enabled for the source database. Do the following configuration in your local MySQL database to enable binlog.

    • Modify log_bin to mysql_bin
    • Modify binlog_format to row
    • Modify server_id to an integer greater than 1
    • If the version is 5.6, modify binlog_row_image to full

    Note: Restart the MySQL process after these changes.

  2. Run the following command to check whether binlog_format of the source database is row.

    Confirm binlog Is Enabled for Source Database

    If binlog_format is not row, run the following command to set it to row.

    Confirm binlog Is Enabled for Source Database

    Note: Disconnect and reconnect your MySQL after the modification to enable the change to take effect.

  3. If your MySQL version is 5.6.2 or later, run the following command to check whether binlog_row_image of the source database is full.

    Confirm binlog Is Enabled for Source Database

    If binlog_row_image is not full, run the following command to set it to full.

    Confirm binlog Is Enabled for Source Database

Configure a migration task

Follow these steps to configure a migration task:

  1. Log on to the Alibaba Cloud DTS console.

  2. Click Data migration in the left-side navigation pane.

  3. Click Create migration task.

  4. Configure the following connection information of the on-premises MySQL database and the target RDS instance, and then click Authorize whitelist and enter into next step.

    • Task name. By default, DTS automatically generates a name for the migration task. This name can be modified, and is not required to be unique.

    • Source database

      • Instance type: Express Connect DB.
      • Instance region: specifies the region of the Apsara VPC connected to the on-premises database.
      • VPC ID: specifies the ID of the Apsara VPC connected to the on-premises database.
      • Database engine: specifies MySQL.
      • Host name or IP address: specifies the accessing IP address to the source MySQL database.
      • Port: specifies the listener port of the MySQL database.
      • Database account: specifies the access account of the MySQL database.
      • Database password: specifies the password of the MySQL access account.
    • Target database

      • Instance type: RDS instance.
      • Instance region: specifies the region of the target RDS instance.
      • RDS instance ID: specifies the instance ID of the target RDS instance. The RDS instance can reside in a classic network or VPC network.
      • Database account: specifies the access account to the RDS instance.
      • Database password: specifies the password of the RDS access account.
  5. Configure the migration types and objects to be migrated.

    • Migration types

      To perform a migration without stopping services , select all the three migration types.

      To migrate only the existing data, select migrate object structure and migrate existing data.

    • Objects to be migrated

      The objects are databases, tables, and columns. By default, after an object is migrated, the object name in the source instance is the same as that in the target instance. If you want them to be different, use the Object name mapping function.

  6. Pre-check

    A pre-check is performed before the migration starts.

    If the pre-check fails, click Failed next to the failure items, solve the problem, and perform the pre-check again.

  7. Start the migration task

    If the pre-check succeeds, start the migration task. You can view the migration status and progress in the task list.

    Data change replication is a process of dynamic synchronization. When the data change replication has no latency, verify the data on the target database. If the data is correct, stop the migration task and switch services to the target database.