edit-icon download-icon

Migrate data from on-premises MySQL to RDS for MySQL

Last Updated: Dec 21, 2017

DTS can migrate data from on-premises MySQL databases to RDS for MySQL databases without server outage. This document describes how to configure a data migration task from on-premises MySQL to RDS for MySQL.

Background

Migration types

Migration from on-premises MySQL to RDS for MySQL supports schema migration, existing data migration, and incremental data replication. The descriptions and restrictions of these migration types are as follows.

  • Schema migration

    • DTS migrates the structure definition of the migrated object to the target instance.
    • The supported objects include table, view, synonym, trigger, procedure, and function.
  • Existing data migration

    • DTS migrates all existing data of the migrated object from the source to the target, excluding the incremental data written to the local MySQL database during migration.
    • If the selected migration type includes incremental data replication, then in the existing data migration stage, the non-transactional tables without primary keys are locked to ensure data consistency. The locking period duration depends on the data volume of the tables, and during this period, the locked tables cannot be written to. After being migrated to the target, the locked tables are released.
  • Incremental data replication

    In this stage, DTS synchronizes the change data captured on the local MySQL database during migration to the target RDS instance. The source and target become continuously synchronized.

Migration restrictions

  • DDL operations are not allowed during migration.
  • Schema migration does not support event.
  • If you use object name mapping when adding a migration object, then the other objects associated with the added object may fail to be migrated.
  • To perform incremental data replication, the binlog of the source MySQL database must be enabled, and the binlog_format of the source database must be row.
  • To perform incremental data replication from a source MySQL with version 5.6, the binlog_row_image of the source MySQL must be full.

Migration process

DTS performs the migration from MySQL to RDS for MySQL through the following steps:

  1. Schema migration: migrates structure objects, including tables and views.
  2. Existing data migration: migrates all existing data.
  3. Schema migration: migrates structure objects, including procedures, functions, triggers, and foreign keys.
  4. Incremental data migration: replicates the change data captured since the existing data migration.

If the migration task does not include the incremental data migration, then after the existing data migration is complete, the status of the migration progress becomes Schema migration 100%, Existing data migration 100%, but the migration status remains migrating. This means that the migration task is migrating procedures, functions, triggers, and foreign keys (step 3). Do not end the task manually in this situation, otherwise it may cause migration data inconsistency.

Prerequisite

Required access privileges

When you use DTS to migrate data from on-premises MySQL to RDS for MySQL, the migration 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 Local MySQL database Target RDS for MySQL database
Schema Migration select Read and write permissions
Existing Data Migration select Read and write permissions
Incremental Data Replication
  • select
  • replication slave
  • replication client
Read and write permissions

Enabling 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

Procedure

The following sections describe how to configure a migration task to migrate data from on-premises MySQL to RDS for MySQL.

Create a database on the target RDS instance

During data migration, if the database to be migrated is not defined in the target RDS instance, DTS automatically creates it. In some cases where the database name does not comply with the RDS Definition Standard, you must create the target RDS database in the RDS console before configuring the migration task.

According to the RDS Definition Standard, the RDS database name consists of up to 64 characters and contains lowercase letters, numbers, underscores, and hyphens. It must begin with a letter and end with a letter or number.

Create a migration account

When configuring a migration task, you must provide the migration accounts of the on-premises MySQL database and the target RDS database. Specific access privileges are required for the accounts, as described in the section Required access privileges.

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.

    configuration

    • 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: specifies On premises databases.
      • Instance region: specifies the closest region to the source instance.
      • 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: specifies 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.

    In this stage, DTS adds the IP address of DTS server to the whitelist of the target RDS instance.

  5. Configure the migration object and migration type, and then click Pre-check and start.

    • Migration type: includes schema migration and existing data migration. For migration without server outage, all these types are required. For existing data migration, schema migration and existing data migration are required.

    • Migration object: specifies the object to be migrated (database, table, or column).

    Note: After the object is migrated to the RDS instance, the object name remains the same with that in the source MySQL database. If the object bears different names in source and target, use the object name mapping function.

  6. A pre-check is performed before the migration task is formally started. If the pre-check fails, click Failed to check the reason and solution. Troubleshoot the problem and perform pre-check again until it is successful.

    pre-check

  7. After pre-check is successful, start the migration task. The migration status and progress can be viewed in the task list.

  8. Perform data verification and switch your business to the target database.

    Because the incremental replication is a continuous synchronization process and cannot be automatically terminated, we recommend that you switch your business to the target instance after performing data verification.

    Perform data verification to confirm that the date in target and source is consistent at certain point when the incremental migration is stable and of zero latency. If the data is verified successfully, you can disable the migration task and switch to the target database.

Thank you! We've received your feedback.