Skip to main content

Migration using AWS Database Migration Service(DMS)

This tutorial covers how to setting up AWS DMS to migrate data from exsiting SQL database into MonoSQL. The SQL databases include MySQL On-Premise, MySQL RDS and AWS Aurora for MySQL. Other SQL databases like PostgreSQL, Microsoft SQL Server are also supported, but needs additional migration work with expert help.

For a detailed guide about using AWS DMS and information about specific migration tasks, see the AWS DMS documentation site.

Preparation​

Please complete the following setup work:

  1. Setup replication instance in AWS console.
  2. Setup source point and point it to your source database.
  3. Setup target point following the below detailed instructions and point it to MonoSQL.
  4. Disable MonoSQL transaction feature temporarily, since MonoSQL inherits the 100 items transaction limit from DynamoDB, but data migration job depends on large transaction. Future data validation can be used to verify whether all the data are loaded into the new database.
set global monosql_enable_transaction=off;
  1. Create the database basic schema like database, table object etc.. Note that MonoSQL has its schema best practice and you may need to using AWS DMS transformation rule to convert the schema for MonoSQL. Follow MonoSQL schema best practice for details.
  2. Create secret in Secret Manager

create secret in Secret Manager

  1. Create the IAM resource for homogeneous data migrations in AWS DMS.

Create target point for MonoSQL​

  1. In the AWS Console, choose AWS DMS.
  2. Choose Endpoints in the sidebar.
  3. Click Create endpoint
  4. In the Endpoint type section, select Target endpoint.
  5. Supply an Endpoint identifier to identify the new target endpoint.
  6. In the Target engine dropdown, select MySQL.
  7. In the Access to endpoint database section, select AWS Secrets Manager
  8. In the Secret ID section, supply the Amazon Resource Name(arn) of the previously created secret in Secret Manager.
  9. In IAM role section, supply the arn of your migration role.

  1. Optionally you can test the connection by clicking Test endpoint connection.
  2. Click Create endpoint.

Config source database to enable DMS feature​

Follow CHAP_Source to config source MySQL database.

  1. Ensure that the binary logs are available to AWS DMS. Because AWS-managed MySQL-compatible databases purge the binary logs as soon as possible, you should increase the length of time that the logs remain available. For example, to increase log retention to 24 hours, run the following command.
call mysql.rds_set_configuration('binlog retention hours', 24);

  1. Set the binlog_format parameter to "ROW".

  2. Set the binlog_row_image parameter to "Full".

  3. Set the binlog_checksum parameter to "NONE".

Apply the above change in the Parameter groups of Amazon RDS console.

Create data migration task​

  1. In AWS DMS console, select Database migration tasks in the sidebar.
  2. Click Create Task in the top-right portion of the window.

  1. Supply a Task identifier to identify the replication task.
  2. Select Replication instance
  3. Select Source database endpoint created before.
  4. Select Target database endpoint, use MonoSQL endpoint created before.
  5. Select Migration type based on your needs. Here we choose Migrate existing data and replicate ongoing changes.

  1. In Task setting section, configure the migration task.
  2. For the Editing mode button, keep Wizard selected.
  3. Select Disable customer CDC stop mode in Custom CDC stop mode section.
  4. Select Do nothing in Target table preparation mode section.
  5. Select Don't stop in Stop task after full load completes section.
  6. Not click Enable validation check and user can compare the data in the source and target database manually.
  7. Click Enable CloudWatch logs check for debug purpose.

  1. In Table mappings section, configure migration rules.
  2. For the Editing mode button, keep Wizard selected.
  3. Select Add new selection rule.
  4. In the Schema dropdown, select Enter a schema.
  5. Supply the appropriate Source name (database name in MySQL), Table name, and Action.
  6. Optionally, add transformation rule in Transformation rules section.

  1. Click Create task button to start the migration job.
  2. In AWS DMS console, select Database migration tasks in the sidebar. check the migration task status in the panel.

Troubleshooting​

  1. If you encounter errors like the following:
Error 1236 (Could not find first log file name in binary log index file) reading binlog

Try to execute show master status; in source MySQL database to reset the CDC start point in migration task section.

  1. If you encounter errors like the following:
Monosql: reach dynamodb transaction size limit.

Try to execute set global monosql_enable_transaction=off; on MonoSQL database to disable the transaction feature temporarily and restart the migration task.