Migrating Data from Aurora MySQL to S3 with AWS DMS Serverless

This is a data pipeline project using AWS DMS Serverless for Python development with CDK.

The cdk.json file tells the CDK Toolkit how to execute your app.

This project is set up like a standard Python project. The initialization process also creates a virtualenv within this project, stored under the .venv directory. To create the virtualenv it assumes that there is a python3 (or python for Windows) executable in your path with access to the venv package. If for any reason the automatic creation of the virtualenv fails, you can create the virtualenv manually.

To manually create a virtualenv on MacOS and Linux:

$ python3 -m venv .venv

After the init process completes and the virtualenv is created, you can use the following step to activate your virtualenv.

$ source .venv/bin/activate

If you are a Windows platform, you would activate the virtualenv like this:

% .venv\Scripts\activate.bat

Once the virtualenv is activated, you can install the required dependencies.

(.venv) $ pip install -r requirements.txt

To add additional dependencies, for example other CDK libraries, just add them to your setup.py file and rerun the pip install -r requirements.txt command.

Prerequisites

Set up cdk.context.json

Then, before deploying the CloudFormation, you should set approperly the cdk context configuration file, cdk.context.json.

For example,

{
  "db_cluster_name": "db-cluster-name",
  "dms_data_source": {
    "database_name": "testdb",
    "table_name": "retail_trans"
  },
  "dms_data_target": {
    "s3_bucket_name": "target-s3-bucket",
    "s3_bucket_folder_name": "target-s3-prefix"
  }
}

Bootstrap AWS environment for AWS CDK app

Also, before any AWS CDK app can be deployed, you have to bootstrap your AWS environment to create certain AWS resources that the AWS CDK CLI (Command Line Interface) uses to deploy your AWS CDK app.

Run the cdk bootstrap command to bootstrap the AWS environment.

(.venv) $ cdk bootstrap

Now you can deploy the CloudFormation template for this code.

List all CDK Stacks

(.venv) $ export CDK_DEFAULT_ACCOUNT=$(aws sts get-caller-identity --query Account --output text)
(.venv) $ export CDK_DEFAULT_REGION=$(aws configure get region)
(.venv) $ cdk list
DMSAuroraMysqlToS3VPCStack
AuroraMysqlStack
AuroraMysqlBastionHost
DMSServerlessTargetS3Stack
DMSRequiredIAMRolesStack
DMSServerlessAuroraMysqlToS3Stack

At this point you can now synthesize the CloudFormation template for this code.

(.venv) $ cdk synth --all

We can provision each CDK stack shown above one at a time like this:

Create Aurora MySQL cluster

  (.venv) $ cdk deploy DMSAuroraMysqlToS3VPCStack AuroraMysqlStack AuroraMysqlBastionHost
  

Confirm that binary logging is enabled

In order to set up the Aurora MySQL, you need to connect the Aurora MySQL cluster on an EC2 Bastion host.

ℹ️ The Aurora MySQL username and password are stored in the AWS Secrets Manager as a name such as DatabaseSecret-xxxxxxxxxxxx.

To retrieve a secret (AWS console)

• (Step 1) Open the Secrets Manager console at https://console.aws.amazon.com/secretsmanager/.
• (Step 2) In the list of secrets, choose the secret you want to retrieve.
• (Step 3) In the Secret value section, choose Retrieve secret value.
  Secrets Manager displays the current version (AWSCURRENT) of the secret. To see other versions of the secret, such as AWSPREVIOUS or custom labeled versions, use the AWS CLI.

To confirm that binary logging is enabled

1. Connect to the Aurora cluster writer node.

    $ BASTION_HOST_ID=$(aws cloudformation describe-stacks --stack-name AuroraMysqlBastionHost | \
    jq -r '.Stacks[0].Outputs | .[] | select(.OutputKey | endswith("EC2InstanceId")) | .OutputValue')
   
    $ aws ec2-instance-connect ssh --instance-id ${BASTION_HOST_ID} --os-user ec2-user
   
    [ec2-user@ip-172-31-7-186 ~]$ mysql -hdb-cluster-name.cluster-xxxxxxxxxxxx.region-name.rds.amazonaws.com -uadmin -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 947748268
    Server version: 5.7.12-log MySQL Community Server (GPL)
   
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
   
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
   
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
    MySQL [(none)]>
   

   ℹ️ AuroraMysqlBastionHost is a CDK Stack to create the bastion host.

   ℹ️ You can connect to an EC2 instance using the EC2 Instance Connect CLI: aws ec2-instance-connect ssh. For more information, see Connect using the EC2 Instance Connect CLI.

2. At SQL prompt run the below command to confirm that binary logging is enabled:

    MySQL [(none)]> SHOW GLOBAL VARIABLES LIKE "log_bin";
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | log_bin       | ON    |
    +---------------+-------+
    1 row in set (0.00 sec)
   

3. Also run this to AWS DMS has bin log access that is required for replication

    MySQL [(none)]> CALL mysql.rds_set_configuration('binlog retention hours', 24);
    Query OK, 0 rows affected (0.01 sec)
   

Create a sample database and table

1. Run the below command to create the sample database named testdb.

    MySQL [(none)]> SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    4 rows in set (0.00 sec)
   
    MySQL [(none)]> CREATE DATABASE IF NOT EXISTS testdb;
    Query OK, 1 row affected (0.01 sec)
   
    MySQL [(none)]> USE testdb;
    Database changed
    MySQL [testdb]> SHOW TABLES;
    Empty set (0.00 sec)
   

2. Also run this to create the sample table named retail_trans

    MySQL [testdb]> CREATE TABLE IF NOT EXISTS testdb.retail_trans (
             trans_id BIGINT(20) AUTO_INCREMENT,
             customer_id VARCHAR(12) NOT NULL,
             event VARCHAR(10) DEFAULT NULL,
             sku VARCHAR(10) NOT NULL,
             amount INT DEFAULT 0,
             device VARCHAR(10) DEFAULT NULL,
             trans_datetime DATETIME DEFAULT CURRENT_TIMESTAMP,
             PRIMARY KEY(trans_id),
             KEY(trans_datetime)
           ) ENGINE=InnoDB AUTO_INCREMENT=0;
    Query OK, 0 rows affected, 1 warning (0.04 sec)
   
    MySQL [testdb]> SHOW TABLES;
    +------------------+
    | Tables_in_testdb |
    +------------------+
    | retail_trans     |
    +------------------+
    1 row in set (0.00 sec)
   
    MySQL [testdb]> DESC retail_trans;
    +----------------+-------------+------+-----+-------------------+-------------------+
    | Field          | Type        | Null | Key | Default           | Extra             |
    +----------------+-------------+------+-----+-------------------+-------------------+
    | trans_id       | bigint      | NO   | PRI | NULL              | auto_increment    |
    | customer_id    | varchar(12) | NO   |     | NULL              |                   |
    | event          | varchar(10) | YES  |     | NULL              |                   |
    | sku            | varchar(10) | NO   |     | NULL              |                   |
    | amount         | int         | YES  |     | 0                 |                   |
    | device         | varchar(10) | YES  |     | NULL              |                   |
    | trans_datetime | datetime    | YES  | MUL | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
    +----------------+-------------+------+-----+-------------------+-------------------+
    7 rows in set (0.00 sec)
   
    MySQL [testdb]>
   

After setting up the Aurora MySQL, you should come back to the terminal where you are deploying stacks.

Create Amazon S3 bucket for AWS DMS target endpoint

  (.venv) $ cdk deploy DMSServerlessTargetS3Stack
  

Create AWS DMS Replication Task

In the previous step we already created the sample database (i.e. testdb) and table (retail_trans).

Now let's create a migration task.

  (.venv) $ cdk deploy DMSRequiredIAMRolesStack DMSServerlessAuroraMysqlToS3Stack
  

Run Test

1. Generate test data.

    $ BASTION_HOST_ID=$(aws cloudformation describe-stacks --stack-name AuroraMysqlBastionHost \
    | jq -r '.Stacks[0].Outputs | .[] | select(.OutputKey | endswith("EC2InstanceId")) |.OutputValue')
   
    $ aws ec2-instance-connect ssh --instance-id ${BASTION_HOST_ID} --os-user ec2-user
   
    [ec2-user@ip-172-31-7-186 ~]$ cat <<EOF >requirements-dev.txt
    > boto3
    > dataset==1.5.2
    > Faker==13.3.1
    > PyMySQL==1.0.2
    > EOF
    [ec2-user@ip-172-31-7-186 ~]$ pip install -r requirements-dev.txt
    [ec2-user@ip-172-31-7-186 ~]$ python3 gen_fake_mysql_data.py \
                   --database your-database-name \
                   --table your-table-name \
                   --user user-name \
                   --password password \
                   --host db-cluster-name.cluster-xxxxxxxxxxxx.region-name.rds.amazonaws.com \
                   --max-count 200
   

2. Start the DMS Replication task by replacing the ARN in below command.
   After ingesting data, you need to come back to the terminal where you are deploying stacks.

   (.venv) $ DMS_REPLICATION_CONFIG_ARN=$(aws cloudformation describe-stacks --stack-name DMSServerlessAuroraMysqlToS3Stack \
   | jq -r '.Stacks[0].Outputs | map(select(.OutputKey == "DMSReplicationConfigArn")) | .[0].OutputValue')
   (.venv) $ aws dms start-replication \
                     --replication-config-arn ${DMS_REPLICATION_CONFIG_ARN} \
                     --start-replication-type start-replication
   

3. Check s3 and you will see data in the s3 location such as:

    s3://{target-s3-bucket}/{target-s3-prefix}/{your-database-name}/{your-table-name}/
   

Clean Up

1. Stop the DMS Replication task by replacing the ARN in below command.

   (.venv) $ DMS_REPLICATION_CONFIG_ARN=$(aws cloudformation describe-stacks --stack-name DMSServerlessAuroraMysqlToS3Stack \
   | jq -r '.Stacks[0].Outputs | map(select(.OutputKey == "DMSReplicationConfigArn")) | .[0].OutputValue')
   (.venv) $ aws dms stop-replication \
                     --replication-config-arn ${DMS_REPLICATION_CONFIG_ARN}
   

2. Delete the CloudFormation stack by running the below command.

   (.venv) $ cdk destroy --force --all
   

Useful commands

• cdk ls list all stacks in the app
• cdk synth emits the synthesized CloudFormation template
• cdk deploy deploy this stack to your default AWS account/region
• cdk diff compare deployed stack with current state
• cdk docs open CDK documentation

Enjoy!

References

• AWS DMS Serverless: Automatically Provisions and Scales Capacity for Migration and Data Replication (2023-06-01)
• aws-dms-deployment-using-aws-cdk - AWS DMS deployment using AWS CDK (Python)
• aws-dms-msk-demo - Streaming Data to Amazon MSK via AWS DMS
• How to troubleshoot binary logging errors that I received when using AWS DMS with Aurora MySQL as the source?(Last updated: 2019-10-01)
• AWS DMS - Using Amazon Kinesis Data Streams as a target for AWS Database Migration Service
• Specifying task settings for AWS Database Migration Service tasks
• Working with AWS DMS Serverless
• How AWS DMS handles open transactions when starting a full load and CDC task (2022-12-26)
• AWS DMS key troubleshooting metrics and performance enhancers (2023-02-10)
• Connect using the EC2 Instance Connect CLI

  $ sudo pip install ec2instanceconnectcli
  $ mssh ec2-user@i-001234a4bf70dec41EXAMPLE # ec2-instance-id
  

Related Works

• aws-msk-serverless-cdc-data-pipeline-with-debezium
• aws-msk-cdc-data-pipeline-with-debezium
• aws-dms-cdc-data-pipeline
• aws-dms-serverless-to-kinesis-data-pipeline

Security

See CONTRIBUTING for more information.

License

This library is licensed under the MIT-0 License. See the LICENSE file.