How to use DMS for continuous replication from MySQL to Aurora

Scenario

We'll cover the prerequisites you need, how to configure your MySQL source database and Aurora target cluster, and the necessary steps to set up and run your DMS replication task. Let's dive in and get started on making your data replication seamless and efficient.

Step 1: Preparing the MySQL Source Database

Configure binary logging:

• Ensure binary logging is enabled on the MySQL database.
• Configure binary logging retention period

After creating our MySQL database, we must make sure we have several options enabled.

Step 2: Setting Up the Amazon Aurora Cluster

Create an Aurora cluster:

• Create Snapshot from MySQL RDS instance
• Migrate snapshot to Aurora Instance

Now, to set up our cluster we will use snapshot from our current MySQL database “swo-mysql-db", this way we will have prepopulated tables which is a requirement for MySQL. Another option would be to create cluster via read replica.

To achieve this, we will use the option Migrate Snapshot and select our desired specification:

Configure the instance by choosing instance type and VPC:

Choose security group, AZ and Certificate Authority. The example below uses the default values:

Choose parameter group and click migrate:

Step 3: Configuring AWS DMS

Next step is to create DMS peripherals which includes a Replication instance, source endpoint, target endpoint and a migration task:

• Create a replication instance:
• Create source and target endpoints:
  • Source endpoint (MySQL):
    • Configure the source endpoint for MySQL, including hostname, port, username, and password.
    • Test the connection to ensure it’s working.
  • Target endpoint (Aurora):
    • Configure the target endpoint for the Aurora cluster.
    • Test the connection to ensure it’s working.

Let’s start with the replication instance:

First, choose appropriate name and description:

Next, configure instance class, engine version and storage capacity. You can also choose whether to have high availability or not, in my case I used single availability zone:

To create a source endpoint, and since the instance is already in the AWS Account, we are using the “Select RDS DB instance”:

We would need to select source engine server name, port and provide the username and password, we are not using the SSL option as my information is made up and it will slow the replication.

After creation, test the source endpoint connection:

Step 4: Creating a DMS Task

After the steps above are completed successfully, the migration task follows up.

To create a database migration task, we need to follow the task configuration and input the replication instance, source and target endpoint and for migration type we will use continuous replication thus selecting “replicate data changes only”

We would need to use custom CDC start mode and then select the “Specify log sequence number” to start the replication process from the specific time that we want. This can be seen in the example below:

When specifying a log sequence number, we will enable the replication to start from that specific log which was taken in the desired time.

For the LOB column settings, we have several options, to understand why we chose the specific one here is a short explanation.

Full LOB mode – AWS DMS migrates all LOBs from source to target regardless of size. In this configuration, AWS DMS has no information about the maximum size of LOBs to expect. Thus, LOBs are migrated one at a time, piece by piece. Full LOB mode can be quite slow.

Limited LOB mode – You choose the maximum LOB size for DMS to accept. That enables DMS to pre-allocate memory and load the LOB data in bulk. LOBs that exceed the maximum LOB size are truncated, and a warning is issued to the log file. This option allows you to gain significant performance over full LOB mode.

Table preparation we will leave do nothing as we do not want our information to be deleted from the database, also for LOB we choose Limited as it is the fastest, and the data validation is turned off.

For table mappings we will use the default value for a schema “%”

The last step is to select when to start our migration task, choose Automatic. Manual can be used if you need to do pre-migration checks.

Step 5: Replication

The replication will continue until it is stopped manually. It is recommended to stop your applications first so that no more changes are made to the tables before stopping the replication.