Migrating a MySQL database to AWS RDS using AWS DMS with zero downtime
Tags: dms, aws, mysql, database
Published: 2022-10-01 14:31:52

Recently, at [Engagespot](https://engagespot.co), we had to migrate our MySQL database which was hosted in an EC2 instance to AWS RDS to ensure high availability, automated backups and get more visibility through Cloudwatch monitoring and logs.

But the challenge was the size of the database. We had several Gigabytes of data with millions of rows. The next challenge is to ensure sync between both databases. By the time we take the backup and restore it, the data on our original database would have been changed. And, we cannot lock the original database from new writes as it would affect the uptime of our service.

## Alternative Methods

We tried several solutions like doing a `mysqldump` and piping it to the RDS server but it continuously broke the pipe.

We came across this useful service from Amazon called [AWS DMS (Database Migration Service)](https://aws.amazon.com/dms/) that helps you migrate any database from one server to another. Not just MySQL, but any other databases like Postgres, MSSQL, etc.

By using DMS, we were able to migrate our entire production database to RDS without any downtime. 

In this article, I'll explain how to migrate a MySQL database hosted in an EC2 instance (or any other remote server) to RDS. The steps to migrate other databases like Postgres are almost the same, however, the configurations might be different.

## Prerequisites

1. AWS Account
2. Target RDS instance already created with the schema and table structure. (_This is because DMS is not great at replicating your table's auto-increment and other constraints_)
3. Source MySQL database that can be accessed remotely.
4. Binary log enabled in source MySQL database with few parameters in your `my.cnf` file configured as per AWS documentation [here](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.MySQL.html#CHAP_Source.MySQL.CustomerManaged).

## Copy the entire table structure from your source DB to target manually.

As I mentioned above, you must copy your tables, foreign keys, primary keys, Indexes, and other constraints from your source database to RDS manually, using mysqldump, or any other tools. This is because DMS won't migrate the table structure and its constraints properly.

You can simply do this using a MySQL client like Tableplus.

After you've migrated all the table structures, let's proceed to transfer the actual data.

## Creating a Replication Instance

Goto AWS Console, and open the [DMS service](https://us-west-2.console.aws.amazon.com/dms/v2/home).

To migrate your database, you must first create a DMS replication instance. Navigate to Replication instances -> Create replication instance.

Specify the name and class of the replication instance. You can select the class identical to the RDS instance or your source database. Don't worry about incurring costs because you can quickly delete the replication instance after the migration is completed.

## Creating Source endpoint.

After creating the replication instance, you must create the source and target database endpoints. For this, navigate to Endpoints -> create endpoint.

First, we'll create the source endpoint. Make sure you have the source database credentials ready.

Select **Source Endpoint** as the Endpoint Type.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/upp2qc6f9o2fcw3bxsj6.png)

Specify your source database credentials. Make sure the MySQL user you specify here has SUPER privileges. Without that, the migration might fail.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/av545vvc4v6oof1vx7d2.png)

Finally, click on the save button.


## Creating Target endpoint.

The steps are similar to that of creating a source endpoint. There is one additional configuration to specify here. 

Under the **Endpoint settings**, check Use endpoint connection attributes. And in the textbox labeled **Extra connection attributes**, add the value `initstmt=SET FOREIGN_KEY_CHECKS=0`. This disables Foreign Key checks on your RDS database during migration and avoids foreign key-related errors.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1yfw6bnu7v9qr8eqqsju.png)

After creating source and target endpoints, you can test the connection from the **Connections** tab to make sure those databases can be reached by DMS.

## Creating Migration Target

A migration task is a process that copies the database from your source to the target. DMS offers three types of migration tasks.

**Migrate existing data** - Choose this type if you just want to copy the existing data from the source to the target once.

**Migrate existing data and replicate ongoing changes** - Choose this method if you want to copy the existing data from the source to the target and keep syncing new records as they are created on the source DB. This is an ongoing process. And we'll be using this method.

**Migrate existing data and replicate ongoing changes** - Choose this method if you already have some data migrated and now you want to sync the new data from source to target.

Navigate to **Database migration tasks** and click **Create task** button. Choose the replication instance you had created earlier, and select the source and target endpoints. Choose migration type as **Migrate existing data and replicate ongoing changes**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/92a6bsu0rmc705th49eg.png)

Under the **Task settings** section, choose **Target table preparation mode** as **do nothing**. This is because you had already migrated the table structure manually.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zskb8833zlwc132pftvi.png)

You can enable **Cloudwatch logs** to debug if something goes wrong.

Just click on **Create task** button.

After some time, your task status will be "In Progress". At first, DMS will copy the entire data from source to target, and after that, it will continuously keep the two in sync.

The initial time to copy the data might take anywhere from a few mins to hours depending on the size of your database.

Once you confirm that the entire data has been copied to your target DB, you can run a few tests by creating records in your source DB to ensure new records are being synced.

At this stage, you can update the database credentials of your production app to RDS and that's it! You have migrated your entire production database to RDS without any downtime!

Please let me know in the comments if you face any issues!