Recently, at Engagespot, 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) 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
- AWS Account
- 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)
- Source MySQL database that can be accessed remotely.
- Binary log enabled in source MySQL database with few parameters in your
my.cnffile configured as per AWS documentation here.
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.
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.
Specify your source database credentials. Make sure the MySQL user you specify here has SUPER privileges. Without that, the migration might fail.
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.
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.
Under the Task settings section, choose Target table preparation mode as do nothing. This is because you had already migrated the table structure manually.
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!
Top comments (0)