Using DMS to replicate data from RDS(MySQL) to Open Search

Hi everyone,

**Scenario: **Before building anything on top of the Open Search, We need data in Open Search. Generally, we will store data in databases like MySQL, MongoDB, etc. We will query this data for other services also. So to build a better search, we have to clone this data to Open Search. On top of the Open Search Indexes, we can build a better search.

We can use DMS to replicate the data to Open Search Indexes. Let’s dive into the implementation. We can divide this task into 3 parts.

• Creating an RDS instance of MySQL

• Creating an Open Search Domain

• Creating Database Migration Task in DMS

Note: At the bottom of the article, I added the errors I faced when working on this. Please visit that section if you face any errors during creation.

Creating an RDS Instance of MySQL:

• Go to AWS Console and open the RDS console

• Use standard create and select MySQL database

• For now, you can Select Dev/Test Template

• You can select a single DB instance for now

• Give a name to the database, you can give a username and store the password to a secret

• For storage, you can keep 50GB for now

• For connectivity, you can keep it private

• For VPC and Database Authentication, Keep the default options as it is

• Click on the create database and it will take some time for DB to become available.

• Once the database is created create a DB named testing, create a table named users, and insert some sample data like this. We will use that table to replicate in an open search

Creating an Open Search Domain:

• From the AWS search visit Open Search Service and click on the Create Domain button

• Give a name for the domain, select standard create, and select dev/test template

• We can deploy it in a single AZ for now

• For data nodes select t3.medium.search, number of nodes as 1 and keep the storage max of 50GB

• Deploy it in a VPC instead of public. Because DMS supports only domains without fine-grained access control. To disable it at least we need to deploy it in VPC.

• Un-Check Enable fine-grained access

• Edit access policy like below

• Click on the Create Domain button and wait for some time for the domain to come into availability

Creating Database Migration Task in DMS:

Creating a Database Migration Task requires the following

• Replication Instance

• Endpoints for source and target

• Database migration task

*Replication Instance: *

• Visit the DMS service from AWS Search and click on the Create Replication Instance button

• Give a name for the replication instance. You can leave other fields as they are optional

• For instance configuration, keep instance class as dms.t3.medium and High availability as dev/test

• For storage keep storage as 50GB. For connectivity keep the settings as it is

• Click on the Create Replication Instance button and wait for some time for the replication instance to become available

Endpoints for source and target:

Source Endpoint:

• From the side panel click on the Endpoints and click the Create Endpoint

• For source, check Select RDS DB instance, and select the DB instance we created previously.

• Give a name for the endpoint, You can either use the secret manager or you can give creds for RDS manually.

• Click on Create Endpoint. After creating the endpoint the configuration looks like this

Target endpoint:

• Now select the Target endpoint. Give a name for the endpoint and select OpenSearch as target engine

• Create an IAM role that can access Open Search services and paste that role ARN in below ARN input box

• Take the Open search endpoint and paste it below the input box. Make sure that there are no extra white spaces or slashes and click on Create endpoint

• ****After creating the endpoint, the configuration will look like this

• Now before creating a migration task please check the connection status. Both should be successful otherwise task won’t run

Database Migration Task:

• From the side panel click on database migration task and click on create database migration task button

• Fill the form like this below, give a name, select source and target endpoints

• From task settings, select Do Nothing and turn on Cloud watch logs for tracking any errors during migration

• From the selection rules, include a schema to select which DB, and table you want to migrate from RDS

• Click on Create task and it will appear in the dashboard in a few seconds

Now select this task and from the Actions button click Resume/Restart. The task will start execution.

Once the task is done you can visit the Open Search dashboard and check the indices that the task created. From the dashboard side panel visit the Query Workbench and you can write the query to see the records

That’s it. We successfully created a data migration task and successfully verified the replication.

Issues I faced while working on this task:

RDS endpoint connection issue: SSL handshake error: It is because of the certificate I added for RDS. We have to use the default certificate only. Using other certificates causes the issues. I created a question on the AWS repost and notified the AWS team about the issue. You can check that at this link https://repost.aws/questions/QUplP5OFKxSAWBDNngapx2Gg/sslv3-alert-handshake-failure

**Open Search endpoint connection issue: **Make sure there are no extra characters in endpoint pasting and Check the IAm roles and open search access policy. DMS doesn’t work with open search domains with Fine-grained access. So remove that.

**No handler found for [PUT]: **While running the task I got this error. For this, we need to make sure that while creating an endpoint for open search add an endpoint setting UseNewMappingType to true