Data Migration: MySQL Database OnPrem to RDS Part 1

In my previous post, I shared some insights into the importance of data in the IT and business world.. I also highlighted the broad classification of data and explained why data migration is such an important part of our work today. This is especially clear from the perspective of a Solutions Architect working in both on-premises and cloud environments. In this post, I want to explore data migration services in AWS in more detail.

So what is this actually? Data migration is the process of transferring data from one system, storage format, or application to another. Different types of data migration include Database migration(transferring data between database systems),Storage migration(transferring data between physical devices), Application migration(Shifting data to support a new software application) and Cloud migration(Moving data to or between cloud platforms) to mention a few.

This article explains how to migrate a MySQL database from an on-premises environment to a managed RDS in AWS. It clearly and simply explains how to use tools like the Database Migration Service (DMS), Amazon Linux EC2 server, and AWS RDS for MySQL to perform a basic migration task.

This exercise is divided into two parts. The first part will demonstrate how to set up the source and target databases.. It will also cover how to set up the replication instance in DMS. The second and final part explains how to create the source and target endpoints, set up the replication task, and perform the actual migration job.. We will also clean up the resources used.

Let's move forward with these steps:

Step 1 - Set up a VPC Before starting any migration task, a VPC needs to be created. This isolated network area in the AWS environment would ensure that the necessary security group and availability zones are designated for both the source and target migration zones. Creating a VPC in AWS is quite straightforward.

Our VPC is named, DMSVPC with the IPV4 CIDR block of 10.0.0.0.0/24. It should have an Internet gateway and a rule for MySQL/Aurora.

Step 2 - Set up the Source database We will first set up our source MySQL database on an Amazon Linux EC2 instance. Then we will install MySQL server on it and run a database task afterwards. Launching an instance is easy; it only takes a few clicks in the Management Console to choose the right instance type. I have a previous post on how to launch an EC2 instance. Be sure to check it out if you need guidance.

For this demonstration, our EC2 instance represents our on-premises environment and will work well for our exercise.. We name our instance MySQL_DB and create it within the previously defined DMSVPC.

Next, we install the MySQL server for database administration on our Amazon Linux server. This process is straightforward with the following steps:

1. Update the system using the command: sudo dnf update -y

2. Install MySQL Server:
   Use the command: sudo yum install mysql-community-server

3. 

   Start and enable the MySQL service using the following commands:

   • sudo systemctl start mysqld

   • sudo systemctl enable mysqld

After logging in, we can create a database (with one or two tables for demonstration purposes), create a user, grant all privileges, and then populate the database.

With this set up and running, we can now focus on the target instance: AWS RDS for MySQL.

Step 3 - Set up the target database AWS RDS, or Amazon Relational Database Service, is a cloud-based service that simplifies setting up, operating, and scaling a relational database. It allows you to do this without managing the underlying infrastructure yourself. It supports popular database engines like MySQL, PostgreSQL, Oracle, SQL Server, MariaDB, and Amazon Aurora. AWS RDS supports such key attributes as Fully Managed, Scalability, High Availability and Integrated Security.

To set up the target database using AWS RDS for MySQL, follow these steps:

1. Sign in to the AWS Management Console and navigate to the RDS service.

2. Create a new RDS instance:

   • Click on "Create database."

   • Choose the "Standard Create" option for more control over the configuration.

   • Select "MySQL" as the database engine.

   • Choose the version of MySQL you want to use.

3. Configure the database instance:

   • Select the instance class based on your performance requirements.

   • Choose the storage type and allocate the necessary storage size.

   • Configure the availability and durability options, such as Multi-AZ deployment for high availability.

4. Set up the database settings:

   • Provide a unique identifier for your database instance.

   • Set the master username and password for database access(admin and password for eg).

5. Configure network and security settings:

   • Select the VPC you created earlier (DMSVPC).

   • Choose the appropriate subnet group and security group to control access to the database.

6. Additional configuration:

   • Enable automatic backups and set the backup retention period.

   • Configure maintenance and monitoring options as needed.

7. Launch the RDS instance:

   • Review your settings and click "Create database" to launch the RDS instance.

Once the RDS instance is up and running, it will serve as the target database for your migration task.

Step 4 - Set up Replication Instance To proceed with the migration, we need to create a replication instance. A replication instance in AWS is the engine that drives data migration using AWS Database Migration Service (DMS). It connects to your source and target databases, reads the data, transforms it if necessary, and then transfers it to the destination. It runs on an Amazon EC2 instance managed by AWS, and you can select the instance size according to your workload. For high availability, you can deploy it in Multi-AZ mode, which creates a standby replica in another Availability Zone.

To create a replication instance, follow these steps:

1. Sign in to the AWS DMS console, go to Replication Instances, and click "Create Instance."

2. Configure the instance by selecting these attributes: Name(myrepinstance), Description (optional but helpful), Instance class, Allocated storage, Engine version, and Multi-AZ (optional).

3. Configure Network and Security Settings - These include the following:

   VPC - We selected the existing VPC, DMSVPC.
   Replication subnet group
   Publicly accessible - Useful if connecting from outside the VPC
   VPC security groups - Important for allowing traffic to and from our database

   Advanced Settings - This is optional for KMS key selection or Maintenance window

4. Now, we can click on "Create." The graphic below shows the key details of the new instance.

   

   With the replication instance now running as desired, we can move on to setting up the endpoints. I will cover this in the second and final part of this lab. Until then, thanks for stopping by, and see you in the next one.