How to migrate RDS to RDS via DMS

We, at Pratilipi, had been thinking about migrating one schema from an RDS instance to a separate instance for quite some time. We had been looking at three different solutions for this problem. These included:

1. MySQL dump
2. DB snapshot
3. DMS

We had already migrated multiple schemas in the past using mysqldump, but this always had a downtime. In our current usecase, we couldn’t afford to have any downtime at all. There were other dependencies including Kafka and KSQL streams, which would’ve taken sometime to build and test on the new RDS instance which barred us from going forward with mysqldump.

We had to rule out DB snapshot since the db had multiple schemas which were not supposed to be migrated. Remember, we had to migrate just one schema.

Finally we decided to have a look at AWS DMS. I have pretty decent experience with setting up CDC on DMS. I’m using it to push events to Kinesis streams in multiple usecases. So, it was obvious of us to explore this option.

Let’s talk about DMS for a while now.

AWS Database Migration Service (AWS DMS) is a cloud service that makes it easy to migrate relational databases, data warehouses, NoSQL databases, and other types of data stores. You can use AWS DMS to migrate your data into the AWS Cloud or between combinations of cloud and on-premises setups. With AWS DMS, you can perform one-time migrations, and you can replicate ongoing changes to keep sources and targets in sync.

DMS has 4 main building blocks:

1. Source Endpoint
2. Target Endpoint
3. Replication Instance
4. Database Migration Task

Source Endpoint is the place from where the data is supposed to be read from.

Target Endpoint is the place where the data is supposed to be written to.

Replication Instance is the ec2 instance which will be required for all the processing.

Database Migration Task is the process which will read from source endpoint, write to target endpoint while running on the Replication Instance.

DMS supports Full-load replication, CDC changes replication, and full load plus CDC changes replication. Since our usecase was supposed to be near 0 downtime solution, full-load along with CDC replication was the perfect candidate for us.

In this approach as soon as the task starts, DMS starts to move all the data from the source endpoint to the target endpoint via the replication instance. In the meantime, all the changes which are happening on the source instance are cached in memory of replication instance. Once the full load operation is completed, DMS then applies these cached changes on the target DB. Post this, the DBs are in sync. Any incoming changes on the source instance are replicated instantly on the target instance via the CDC replication.

For deep understanding of DMS Components, refer https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Introduction.Components.html

This approach gave us time to set up new Kafka connectors, set up KSQL streams on the new RDS instance while applications were still pointed towards the older instance. Once setup, it was merely pointing all the applications to the newly created resources and voila!, the migration was complete in near zero downtime.

The very first thing we did was to create a Replication Instance. Choose the instance type and size depending on the amount of data being moved and the volume of write ops happening on the DB. If the writes happening are high, look for higher memory since CDC changes are cached there. If machine runs out of memory, it starts writing to disk. Loading CDC changes from disk would take a little more time than from memory, so just keep in mind the trade-offs.

Once the replication instance was ready, next thing was to create source and target endpoints. I’m assuming that the new RDS instance is ready at this stage.

For any RDS instance to be used by DMS, there are a few pre-requisites.

1. In the parameter group, binlog_format should be set to ROW. This is mandatory.
2. Backup needs to be enabled on the RDS instance.
3. Need to run the following stored procedure with suitable value for hours. (0–7 days)

call mysql.rds_set_configuration(‘binlog retention hours’, $hours);

4. Need to have REPLICATION-CLIENT and REPLICATION-SAVE permissions enabled for the mysql user.

Once created, make sure to test the connection by using the test endpoint option and choosing the replication instance created earlier.

Next part would be creating the target endpoint. It’s a fairly straightforward process.

DMS Replication Task

The final piece of the puzzle is to create a task using the replication instance and the endpoints created above.

Make sure to select the Replication type as Migrate existing data and replicate ongoing changes.

There are 2 caveats in DMS.

1. DMS doesn’t add the attribute of autoIncrement on target schema.
2. DMS doesn’t migrate a number of LOB datatype fields.

autoIncrement was a deal-breaker for us since we had multiple tables where it was being used in source schema. To bypass this problem, we simply created relevant schemas in target db beforehand with all the required attributes. Then we just migrated the data, we didn’t allow DMS to create tables.

To use this option, select Do nothing in Target table preparation mode.

If you are trying to migrate any supported LOB type fields, make sure to select either Full LOB mode or Limited LOB mode under Include LOB columns in replication depending on your usecase.

Once all the configs are done, start the task. Monitor the task for the progress.

CLOSING THE MIGRATION

This is undoubtedly the most important part of the migration. We have to make sure that once DBs are in sync, all the services/workers are pointed to new instances without any data mismatch.

We had to create KSQL streams/tables on the new DB instance. Once both the DBs were in sync, this was the time to create them without having to incur large downtime(Creating raw topics and derived streams/tables takes a lot of time when data being ingested is in 100s of millions of rows).

While migrating, we chose to create the indexes once the full load operation and replication changes were complete and DBs were in sync. This helped us save time as creating index while data was being migrated would have taken a lot more time.

Once the Kafka connectors and KSQL topics/tables/streams were up, it was time to close the migration.To do so, we followed a slightly lenient approach with a little downtime.

Once the DBs were in sync, we stopped all the writes to the older instance. We then verified that no writes were going through to source instance by checking max(id) on multiple tables. Once confirmed that this was constant, we again made sure that DBs were in sync. Post this we stopped the DMS task. At this time, we pointed our services in a test environment to the new DB instance, verified that services were performing as expected. Then we pointed services and workers in production environment to new DB instance.

How to make sure the new instance is 100% updated?

This can be confirmed by checking the max(id)/count(id), assuming id is an autoIncrementing primary key, on different tables.

To summarise closing the migration:

1. Stop writes to the old DB instance.
2. Check if new and old DB instances are in sync.
3. If in sync, stop DMS task.
4. Enable writes and point services in test environment to new DB instance.
5. Make sure service is working as expected.
6. Enable writes and point services in production environment to new DB instance.
7. Have a beer.

I hope this article helps you. Do share feedback. I would be writing another one on DMS as CDC. Be curious, wait for the next part.

Happy migrating 🍻