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=$(curl -s 169.254.169.254/latest/dynamic/instance-identity/document | jq -r .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

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.

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)

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

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)

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

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

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

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

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}

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

Security

See CONTRIBUTING for more information.

License

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

Name		Name	Last commit message	Last commit date
Latest commit History 4 Commits
cdk_stacks		cdk_stacks
utils		utils
.gitignore		.gitignore
CODE_OF_CONDUCT.md		CODE_OF_CONDUCT.md
CONTRIBUTING.md		CONTRIBUTING.md
LICENSE		LICENSE
README.md		README.md
app.py		app.py
cdk.context.json		cdk.context.json
cdk.json		cdk.json
dms_serverless-mysql-to-s3-arch.svg		dms_serverless-mysql-to-s3-arch.svg
requirements.txt		requirements.txt
source.bat		source.bat

License

aws-samples/aws-dms-serverless-mysql-to-s3-migration

Folders and files

Latest commit

History

Repository files navigation

Migrating Data from Aurora MySQL to S3 with AWS DMS Serverless

Prerequisites

List all CDK Stacks

Create Aurora MySQL cluster

Confirm that binary logging is enabled

Create a sample database and table

Create Amazon S3 bucket for AWS DMS target endpoint

Create AWS DMS Replication Task

Run Test

Clean Up

Useful commands

References

Security

License

About

Topics

Resources

License

Code of conduct

Security policy

Stars

Watchers

Forks

Languages