How do I migrate to an Amazon RDS or Aurora MySQL DB instance using AWS DMS?

Last updated: 2020-04-30

I need a migration strategy for my database that has a simplified approach with minimal downtime. How do I migrate to an Amazon Relational Database Service (Amazon RDS) or Amazon Aurora MySQL DB instance using AWS Database Migration Service (AWS DMS)?

Short Description

Note: If you're performing a homogeneous migration, use your engine’s native tools (such as MySQL dump or MySQL replication) whenever possible.

To migrate to an Amazon RDS or a provisioned Aurora MySQL DB instance using AWS DMS:

  • Create a replication instance
  • Create target and source endpoints
  • Refresh the source endpoint schemas
  • Create a migration task
  • Monitor your migration task

Resolution

Note: AWS DMS only creates a table with a primary key on the target if necessary before migrating table data. To generate a complete target schema, use the AWS Schema Conversion Tool. For more information, see Converting Schema.

(Optional) Enable logging with Amazon CloudWatch

Amazon CloudWatch Logs can alert you to potential issues when migrating. For more information, see Monitoring Replication Tasks Using Amazon CloudWatch.

Create a replication instance

  1. Open the AWS DMS console, and choose Replication Instances from the navigation pane.
  2. Choose Create replication instance.
  3. Enter your replication instance name, description, instance class, Amazon Virtual Private Cloud (Amazon VPC), and Multi-AZ preference.
    Note: Be sure to choose an instance class that's sufficient for your migration workload. If the instance isn't sufficient for your workload, you can modify the replication instance later.
  4. From the Advanced section, choose your VPC security groups, or choose the default option.
  5. Choose Create replication instance.

Create target and source endpoints

  1. Open the AWS DMS console, and choose Endpoints from the navigation pane.
  2. Choose Create endpoint to create the source and target database.
  3. For Endpoint type, choose Source.
  4. Enter the endpoint's engine-specific information, such as the server name, port, SSL mode, or the Amazon Simple Storage Service (Amazon S3) bucket name, if you use Amazon S3 as the source.
  5. Choose Run Test.
  6. After the test is complete, choose Save.
  7. Repeat steps 3-6, but for Endpoint type, choose Target.
    Note: Be sure to complete this step for both the target and the source.

Refresh the source endpoint schemas

  1. Open the AWS DMS console, and choose Endpoints from the navigation pane.
  2. Select the source endpoint, and choose Refresh schemas.
  3. Choose Refresh schemas.
    Note: You must refresh the source so the source schemas appear in the table mappings when you create an AWS DMS task.

Create a migration task

  1. Open the AWS DMS console, and choose Database migration tasks from the navigation pane.
  2. Choose Create task.
  3. Specify the Task identifier, Replication instance, Source database endpoint, Target database endpoint, and Migration type. Choose one of the following migration types:
    Migrate existing data only—Use this migration type for one-time migrations.
    Migrate existing data and replicate ongoing changes—Use this migration type to migrate large databases to the AWS Cloud with minimal downtime.
    Migrate ongoing replication changes—Use this migration type when you have already migrated the existing data and want to synchronize the source database with the target MySQL database hosted on the AWS Cloud.
  4. From the Task Settings section, modify the task as needed. 
  5. From the Table mappings section, choose Guided UI.
  6. Choose Add new selection rule, and specify your Schema and Table name.
    Note: To change or transform the source schema, table, or column name of some or all of the selected objects, expand the Transformation rules section. Choose Add new transformation rule. Then select the Target, Schema name, and Action.
  7. Choose Create task.

Note: If you have large object (LOBs) columns, Limited LOB Mode is recommended. For more information, see Setting LOB Support for Source Databases in an AWS DMS Task.

Monitor your migration task

  1. Use the Task Monitoring view to monitor the migration tasks. You can see which tables have been migrated successfully and which tables are in the process of migration. Pay attention to the following message types:
    I - indicates an informational message
    W - indicates warnings
    E - indicates errors that occurred when migrating the database
  2. Verify that the databases have been migrated successfully by connecting to the source and target instances through the terminal.

Migrating Oracle

When using Oracle as the source database, the tables are migrated to the specified target endpoint user. You can change the schema for an Oracle target by using transformation rules. For more information, see Changing the User and Schema for an Oracle Target.

Migrating MySQL

When migrating from MySQL to Amazon Aurora, use engine native tools when possible. During migration, schemas and tables are migrated to the same name on the target. If you want to migrate tables to a different schema on target, create a mapping rule to specify the new schema on the target database:

{
  "rules": [{
      "rule-type": "selection",
      "rule-id": "1",
      "rule-name": "1",
      "object-locator": {
        "schema-name": "test",
        "table-name": "%"
      },
      "rule-action": "include"
    }, {
      "rule-type": "transformation",
      "rule-id": "2",
      "rule-name": "2",
      "rule-action": "rename",
      "rule-target": "schema",
      "object-locator": {
        "schema-name": "test"
      },
      "value": "newtest"
    }
  ]
}

Check the logs to confirm that there are no errors.

Monitor latency and compare data counts on the source and the target databases before switching to the new target database. For more information, see Troubleshooting Migration Tasks.