DEV Community

Cover image for šŸš€ Exporting Data from Amazon RDS to Amazon S3 Using AWS DMS šŸŒšŸ“¤
Praful Patel
Praful Patel

Posted on

šŸš€ Exporting Data from Amazon RDS to Amazon S3 Using AWS DMS šŸŒšŸ“¤

Image description

šŸš€ Introduction:
The world of cloud computing demands streamlined and efficient data movement. Our exploration begins with understanding how to optimize this data transfer process, focusing on the synergy between Amazon RDS, a managed relational database service, and Amazon S3, a scalable and secure object storage solution. The star of our show is AWS Database Migration Service (DMS), which simplifies the complex task of migrating and replicating databases.

Features:

Data Replication:

  • Description: AWS DMS enables real-time data replication between various supported databases. Changes made in the source database are automatically reflected in the target, ensuring data consistency.
  • Use Cases: Continuous data synchronization, real-time reporting, and analytics.

Database Migration:

  • Description: DMS simplifies the migration of databases to and from the AWS Cloud. It supports homogeneous and heterogeneous migrations, allowing seamless transitions between different database engines.
  • Use Cases: Cloud migration, database version upgrades, and platform changes.

Change Data Capture (CDC):

  • Description: DMS captures and tracks changes in the source database, enabling incremental updates in the target. This feature is crucial for minimizing downtime during migrations.
  • Use Cases: Minimizing downtime during migrations, supporting ongoing application operations.

Schema Conversion:

  • Description: DMS assists in converting the source database schema to match the target database, ensuring compatibility during migrations between different database engines.
  • Use Cases: Migrating to a different database engine, ensuring seamless data structure transitions.

Data Filtering:

  • Description: DMS allows the selective migration of data based on specific criteria, reducing the need to migrate entire databases.
  • Use Cases: Migrating specific subsets of data, optimizing migration bandwidth.

Task Scheduling:

  • Description: DMS supports the scheduling of migration and replication tasks, allowing users to plan and automate data transfer activities.
  • Use Cases: Automating routine data transfer tasks, optimizing resource utilization.

Security and Encryption:

  • Description: DMS ensures the security of data during transit by supporting encryption options for data in motion.
  • Use Cases: Meeting security compliance standards, protecting sensitive data during migrations.

Pre-requisite:

Before embarking on this journey, ensure you have all the necessary prerequisites in place. This includes AWS credentials, appropriate access to your Amazon RDS instance, a configured S3 bucket, and the required permissions for AWS DMS.

šŸŽÆ Objective:
The primary goal of this blog post is to provide a step-by-step guide to exporting data from an Amazon RDS instance (specifically MySQL) to Amazon S3. We aim to empower users with the knowledge to automate and simplify this data export process, enabling them to harness the full potential of AWS services for their data management needs.

šŸš€ Use Case:
Consider a scenario where organizations frequently require the export of data from their Amazon RDS MySQL database to Amazon S3. This could be for various reasons such as running analytics, creating secure backups, or facilitating seamless collaboration across different services. Our use case centers around addressing the challenges and intricacies of this data export process, offering a practical and efficient solution.

Cloud Adoption:

  • Scenario: An organization is transitioning from an on-premises database to the AWS Cloud. DMS is employed to migrate the existing database to Amazon RDS with minimal downtime.

Database Version Upgrade:

  • Scenario: A company is upgrading its database engine version to leverage new features and improvements. DMS is utilized to perform the upgrade seamlessly, ensuring data integrity.

Continuous Data Synchronization:

  • Scenario: In a scenario where real-time data updates are critical, such as in e-commerce applications, DMS is used to replicate changes from the transactional database to a reporting database.

Data Warehousing:

  • Scenario: An organization wants to consolidate data from multiple databases into a central data warehouse on Amazon Redshift. DMS facilitates the ongoing data replication for analytics purposes.

Scenario Usage:

Scenario: Migrating from an On-Premises Oracle Database to Amazon Aurora MySQL Database

  1. Setup:

    • Set up source and target endpoints in AWS DMS for the Oracle database and Aurora MySQL database.
    • Configure the necessary connection details, security settings, and migration task settings.
  2. Data Replication:

    • Initiate a full load of existing data from Oracle to Aurora using DMS.
    • Activate Change Data Capture (CDC) to capture ongoing changes in the Oracle database.
  3. Continuous Synchronization:

    • Monitor the ongoing replication process to ensure real-time updates from Oracle to Aurora.
    • Test the synchronization by making changes in the Oracle database and verifying their timely reflection in Aurora.
  4. Schema Conversion:

    • Utilize DMS schema conversion tools to handle any necessary schema transformations during the migration.
    • Ensure compatibility between Oracle and Aurora MySQL data structures.
  5. Completion:

    • Once satisfied with the synchronization and migration, complete the DMS task.
    • Redirect applications to use the Aurora MySQL database as the new primary data source.

šŸŒ Solution Diagram:

Image description

Tools & Technologies Covered:

  • AWS Cloud ā˜ļø: Foundation for the solution, providing limitless possibilities for building and deploying applications.
  • Networking, VPC, Security Group, VPC Endpoint šŸžļø: Ensures secure and efficient communication between services.
  • RDS (MySQL) šŸ—„ļø: Manages and maintains the MySQL database.
  • S3 šŸ“¤: Scalable object storage for securely storing and retrieving data.
  • AWS Secret Manager šŸ”: Safely stores and manages sensitive information.
  • AWS DMS (Database Migration Service) šŸ”„: Facilitates seamless data migration between databases.

Image description

Create VPC Endpoint:

Image description

Image description

Select VPC, Subnets and Security Group:

Image description

Click to create endpoint:

Image description

Copy the S3 VPC Endpoint ID:

Image description

Endpoint ID: vpce-08ec6969fd89be2fc

Go to S3 Service:

Select the Bucket created earlier:

Image description

Click to Permission - Edit:

![Image description](https://dev-to-uploads.s3.amazonaws

.com/uploads/articles/3x5rvrg346oijvs1a308.png)

Enter the policy to policy section:

Image description

{
    "Version": "2012-10-17",
    "Id": "Access-to-bucket-using-specific-endpoint",
    "Statement": [
        {
            "Sid": "Access-to-specific-VPCE",
            "Effect": "Allow",
            "Principal": "*",
            "Action": ["s3:List*", "s3:Put*", "s3:Get*"],
            "Resource": [
                "arn:aws:s3:::bucket-lab-rds-export-nbtwmnxdjwpsjtdi",
                "arn:aws:s3:::bucket-lab-rds-export-nbtwmnxdjwpsjtdi/*"
            ],
            "Condition": {
                "StringEquals": {
                    "aws:sourceVpce": "vpce-08ec6969fd89be2fc"
                }
            }
        },
        {
            "Sid": "Access-to-specific-iam-user",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::715900322913:user/username"
            },
            "Action": ["s3:List*", "s3:Get*"],
            "Resource": [
                "arn:aws:s3:::bucket-lab-rds-export-nbtwmnxdjwpsjtdi",
                "arn:aws:s3:::bucket-lab-rds-export-nbtwmnxdjwpsjtdi/*"
            ]
        }
    ]
}
Enter fullscreen mode Exit fullscreen mode

S3 Bucket policy successfully updated.

Go to Secret Manager:

Image description

Image description

Image description

Image description

Image description

Copy ARN of the stored secret:

Image description

Go to Database Migration Service:

Ensure that Replication instance is available:

Image description

Go to Migration Data > Endpoints Create Endpoint:

Image description

Image description

Endpoint created:

Image description

Create Target Endpoint:

Image description

Image description

Endpoint created:

Image description

Test Source Endpoint Connection:

Image description

Image description

Status: Testing:

Image description

Status: Successful:

Image description

Database Migration Tasks Create Task:

Image description

Image description

Task is in progress:

Image description

Task: Started:
Image description

Status: Load Complete:

Image description

Verify that "export" folder created in AWS S3 bucket:

Image description

Image description

Image description

Verify that .csv file is loaded into the S3 folder:

Image description

)

Open .csv file and verify that sample data is loaded

Image description

Conclusion:
In conclusion, the efficiency of exporting data from Amazon RDS to Amazon S3 plays a pivotal role in optimizing data workflows. Leveraging the capabilities of AWS DMS, we aim to simplify and automate this process, ensuring data integrity and accessibility. Join us on this journey as we unlock the power of AWS services in enhancing your data management strategies. šŸŒšŸ“ˆšŸš€

AWS Ref: https://aws.amazon.com/dms/

Connect with me on these platforms and stay updated with the latest in technology and development! šŸš€šŸ”—šŸ˜Š

šŸŒ Website: praful.cloud šŸš€

šŸ”— LinkedIn: Connect with me on LinkedIn šŸ¤

šŸ’» GitHub: Explore my projects on GitHub šŸ“‚

šŸŽ„ YouTube: Check out my tech tutorials on YouTube šŸŽ¬

šŸ“ Medium: Read my tech articles on Medium šŸ“š

šŸ”— Dev: Follow me on Dev for developer-centric content šŸ–„ļø

AWS #CloudEngineering #CloudComputing #AmazonWebServices #AWSArchitecture #DevOps #CloudSolutions #CloudSecurity #InfrastructureAsCode #AWSCertification #Serverless #AWSCommunity #TechBlogs #CloudExperts #CloudMigration #CloudOps #AWSJobs #TechIndustry #CareerInTech #InnovationInCloud #devops #cloudengineerjobs #devopsjobs #azure #gcp #oci #cloudjobs, #kubernetes

PRAFUL PATEL

Top comments (0)