Migrate a Security Log DB with AWS DMS

Migrate a MySQL security log database to S3 and query it with Athena.

Introduction

30 Second Summary

Every security team generates incident logs. When those logs are trapped on a local database server, analyzing them means maintaining hardware, managing backups, and hitting storage limits right when you need answers most.

In this project, you will migrate a simulated security incidents database from Amazon RDS to Amazon S3 using AWS Database Migration Service, then query the migrated data with Amazon Athena.

What You'll Build

You will run a query in Athena and see your security incident records returned directly from CSV files sitting in S3, with no servers to manage and no database to keep running.

By the end of this project, you'll have:

  • A working cloud data migration pipeline that moves security incident records from a managed MySQL database to CSV files in Amazon S3, powered by AWS Database Migration Service.
  • A fully configured IAM security layer with roles and trust policies that grant each AWS service only the access it needs.
  • A serverless analytics layer in Amazon Athena where you can query migrated data by severity, source IP, or incident type without running any servers.
  • Secret Mission: Add server-side encryption (SSE-S3) and access logging to your S3 bucket, then audit the security posture of your entire migration pipeline.

Are there any prerequisites?

No prior AWS experience is required. This project walks you through creating an AWS account and setting up every service from scratch.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

Set Up Your AWS Account and Create an IAM Admin User

To build anything on AWS, you need an account. You also need a way to sign in that follows security best practices from day one.

In this step, you will create an AWS Free Tier account, then create a dedicated IAM admin user. Using an IAM user instead of the root account is the first security practice every cloud professional follows. Your security analyst instincts will immediately see why.

In this step, get ready to:
  • Create an AWS Free Tier account.
  • Create an IAM admin user with console access.
  • Set your default Region for all resources.
Create an AWS Free Tier account

Every AWS service you use in this project lives inside an AWS account. The Free Tier gives you $100 in credits immediately, with the chance to earn up to $100 more as you explore services.

  • Open your browser and navigate to the AWS Free Tier page:
  • https://aws.amazon.com/free/
  • Click Create free account.
  • Enter your email address in the Root user email address field.
  • Enter a name for your account in the AWS account name field.
  • Click Verify email address.
  • Check your email for a verification code, then enter it on the signup page and click Verify.
  • Create a strong root user password and click Continue.

What is a root user?

The root user is the master identity for your AWS account. It has unrestricted access to everything. Because it is so powerful, you should avoid using it for everyday work.

That is exactly why you will create a separate IAM admin user in the next substep.

  • When prompted to choose your account plan, select Free plan.
  • Enter your contact information and accept the AWS Customer Agreement.
  • Enter your billing information and a valid payment method.
  • Complete the identity verification step by entering your phone number and the code you receive via SMS.
  • Choose a Support plan (the free Basic Support plan is fine for this project).
  • Click Complete sign up.

Why do I need a payment method for a free account?

AWS requires a payment method to verify your identity. On the Free plan, you will not be charged unless you upgrade to a Paid plan. Your credits cover the services in this project.

Account activation usually takes a few minutes but can take up to 24 hours. You will receive a confirmation email once your account is active.

  • After receiving the activation email, sign in to the AWS Management Console at https://console.aws.amazon.com/ using the email and password you registered with.

Account not activating?

Check your email inbox and spam folder for the activation message. If it has been more than a few hours and you have not received it, try signing in anyway. Some accounts activate before the email arrives.

help me troubleshoot my AWS account activation

Create an IAM admin user

Right now you are signed in as the root user. The root user has unlimited power over the account, which makes it risky for daily use. Creating a dedicated IAM admin user gives you full admin access with a clear separation from root.

  • In the AWS Management Console, type IAM in the search bar at the top and select IAM from the results.
  • In the left navigation pane, click Users.
  • Click Create user.

The Specify user details page appears.

  • In the User name field, enter a name for your admin user (for example, Admin).
  • Select the checkbox next to Provide user access to the AWS Management Console.
  • Select I want to create an IAM user.
  • Under Console password, select Custom password and enter a strong password you will remember.
  • You can leave User must create a new password at next sign-in unchecked since you are the only person using this account.
  • Click Next.

The Set permissions page appears.

  • Select Attach policies directly.
  • In the search box under Permissions policies, type AdministratorAccess.
  • Select the checkbox next to the AdministratorAccess policy.
  • Click Next.

What is AdministratorAccess?

AdministratorAccess is an AWS managed policy that grants full access to all AWS services and resources. It is the broadest permission level available.

For this learning project, attaching it directly to your user is fine. In a production environment, you would use groups and more restrictive policies following the principle of least privilege.

  • On the Review and create page, review your settings.
  • Click Create user.

A success notification appears confirming the user was created. Before you move on, you need to note your account ID so you can sign in as this new user.

  • Look at the top right corner of the console. Click on your account name or number to see a dropdown.
  • Note your 12-digit Account ID: your AWS Account ID

Now sign out of the root account and sign back in as your new IAM user.

  • Click your account name in the top right corner, then click Sign out.
  • Go to https://console.aws.amazon.com/.
  • Select IAM user.
  • Enter your 12-digit Account ID (your AWS Account ID) in the Account ID field.
  • Click Next.
  • Enter the User name and password you created for your IAM admin user.
  • Click Sign in.

Can't sign in as the IAM user?

Make sure you are signing in as an IAM user, not as the Root user. The sign-in page has separate options for each.

Double check that the Account ID, username, and password are correct. The username is case-sensitive.

help me sign in to AWS as an IAM user

Set your default Region

Every AWS resource lives in a specific Region. All the resources you create in this project must be in the same Region, or they will not be able to communicate with each other.

  • Look at the top right of the console, next to your username. You will see a Region name (for example, US East (N. Virginia)).
  • Click on the Region name to open the Region selector dropdown.
  • Select US East (N. Virginia) us-east-1 from the list.

Why us-east-1?

us-east-1 is the most commonly used Region and has the broadest service availability. You can choose a different Region if you prefer, but make sure you use the same Region for every resource you create in this project.

  • Record the Region you selected: your AWS Region

Before you move on, what do you expect to see on the console now that you are signed in as your IAM user with AdministratorAccess? Take a look at the Console Home dashboard.

You should see the Console Home dashboard with your chosen Region displayed in the top right corner. The account dropdown should show your IAM user name instead of the root email address.

What does the IAM user sign-in prove?

Seeing the Console Home as your IAM user confirms three things: your account is active, your IAM user has the AdministratorAccess policy working, and you are no longer using the root account for everyday tasks.

Your AWS account is set up and secured. Next up, you will create the source database that holds the security log data you are going to migrate.

Create the Source Database and Load Security Log Data

Your AWS account is ready and you are signed in as an IAM admin user. Now you need a database to migrate.

In this step, you will create a MySQL database in Amazon RDS and fill it with sample security incident records. This simulates the "on-premises" incident log database that a security team needs to move to the cloud.

In this step, get ready to:
  • Create an Amazon RDS MySQL database instance using the Free Tier template.
  • Load sample security incident data into the database.
  • Verify the data is ready for migration.
Create an RDS MySQL instance

Amazon RDS is a managed database service. It handles the heavy lifting of provisioning, patching, and backups so you can focus on the data inside.

  • In the AWS Management Console, type RDS in the search bar at the top and select RDS from the results.
  • In the left navigation pane, choose Databases.
  • Choose Create database.
  • For the creation method, choose Standard create.
  • Under Engine options, select MySQL.
  • Under Templates, select Free tier.

What if I see Sandbox instead of Free tier?

If your AWS account is on the paid plan, you will see Sandbox instead of Free tier. Select Sandbox. It uses the same small instance types.

  • Under Settings, enter security-logs-db for the DB instance identifier.
  • For Master username, keep the default value admin.
  • Clear the Auto generate a password checkbox.
  • Enter a password you will remember in the Master password and Confirm password fields.

Keep your password handy

You will use this master username and password to connect to the database in the next substep. Write them down somewhere safe.

  • Under Instance configuration, confirm the DB instance class is set to db.t3.micro (or db.t4g.micro).
  • Under Storage, keep the defaults.
  • Under Connectivity, find the Public access setting and select Yes.

Why enable public access?

You will connect to this database from AWS CloudShell, which runs outside your VPC. Enabling public access lets CloudShell reach the database endpoint over the internet.

In a production environment you would keep the database private and connect through a VPC. For this learning project, public access is the simplest path.

  • Under VPC security group (firewall), select Create new.
  • Enter security-logs-sg as the New VPC security group name.
  • Leave all other settings at their defaults.
  • Click Create database at the bottom of the page.

Your database will appear in the Databases list with a status of Creating. Wait until the status changes to Available. This can take up to 20 minutes.

  • Once the status is Available, click the database name security-logs-db to open its details.
  • On the Connectivity & security tab, find the Endpoint value and record it here: your RDS endpoint hostname.

What does the endpoint look like?

The endpoint is a long DNS name like security-logs-db.abc123xyz.us-east-1.rds.amazonaws.com. This is the address you use to connect to your database. The port is 3306 (the MySQL default).

Still seeing Creating after 20 minutes?

RDS provisioning can occasionally take longer depending on your Region. Refresh the page by clicking the refresh icon in the Databases list. If the status has not changed after 30 minutes, check that you selected the Free tier template and a db.t3.micro or db.t4g.micro instance class.

Help me troubleshoot my RDS instance stuck in Creating status

Connect to the database and create the schema

Your RDS instance is running, but it has no databases or tables inside it yet. You need to connect to it and create the structure that will hold your security incident data.

You will use AWS CloudShell, a browser-based terminal built into the AWS console. It has the mysql client pre-installed, so you can connect to your database without installing anything on your computer.

  • In the AWS console, click the CloudShell icon in the bottom-left corner of the navigation bar (it looks like a terminal prompt).
  • Wait for CloudShell to initialize. You will see a command prompt when it is ready.

Before you can connect from CloudShell, you need to allow its traffic through the database security group.

  • In a separate browser tab, navigate back to your RDS instance details.
  • On the Connectivity & security tab, under Security, click the security group link (it starts with sg-).
  • Select the Inbound rules tab, then click Edit inbound rules.
  • Click Add rule.
  • Set Type to MYSQL/Aurora.
  • Set Source to Anywhere-IPv4 (0.0.0.0/0).
  • Click Save rules.

Why Anywhere-IPv4?

CloudShell's IP address changes each session, so you cannot lock the rule to a fixed IP. Using 0.0.0.0/0 is acceptable for a temporary learning database with no real data. You will delete this database at the end of the project.

  • Switch back to your CloudShell tab and connect to the database by running this command:
mysql -h [[RDS_ENDPOINT="your RDS endpoint hostname"]] -P 3306 -u admin -p
  • When prompted, type the master password you set earlier and press Enter. The password will not appear as you type.

You should see a welcome message ending with a mysql> prompt. You are now connected to your RDS MySQL instance.

Connection timed out or refused?

Check that you set Public access to Yes when creating the database. You can verify this on the Connectivity & security tab under Publicly accessible.

Also confirm that your security group inbound rule allows MYSQL/Aurora traffic from 0.0.0.0/0.

Help me connect to my RDS MySQL instance from CloudShell

  • Create the security_logs database and switch into it by running these SQL commands:
-- Create the database for security log data
CREATE DATABASE security_logs;

-- Switch to the new database
USE security_logs;

What does this do?

CREATE DATABASE makes a new empty database inside your RDS instance. USE tells MySQL that all following commands should run inside that database.

You should see Database changed confirming you are now inside security_logs.

  • Create the incidents table by running this SQL:
CREATE TABLE incidents (
    incident_id INT AUTO_INCREMENT PRIMARY KEY,
    incident_date DATETIME,
    severity VARCHAR(10),
    source_ip VARCHAR(45),
    destination_ip VARCHAR(45),
    incident_type VARCHAR(50),
    description VARCHAR(255),
    status VARCHAR(20)
);

What does this table store?

Each row represents one security incident. The columns capture key details a security analyst would track: when it happened (incident_date), how serious it was (severity), where it came from (source_ip), and what happened (description).

incident_id auto-increments so each new record gets a unique number automatically.

You should see Query OK confirming the table was created.

Load sample security incident data

The table exists but is empty. Time to fill it with realistic sample records.

  • Insert the first batch of security incidents by running this SQL:
INSERT INTO incidents (incident_date, severity, source_ip, destination_ip, incident_type, description, status) VALUES
('2025-08-01 08:15:00', 'Critical', '203.0.113.45', '10.0.1.5', 'Ransomware', 'Ransomware payload detected on endpoint', 'Open'),
('2025-08-01 09:30:00', 'High', '198.51.100.22', '10.0.1.12', 'Brute Force', 'Multiple failed SSH login attempts', 'Investigating'),
('2025-08-02 14:45:00', 'Medium', '192.0.2.88', '10.0.2.3', 'Phishing', 'Suspicious email with malicious attachment', 'Resolved'),
('2025-08-02 16:00:00', 'Low', '10.0.3.15', '10.0.1.5', 'Policy Violation', 'Unauthorized software installation detected', 'Resolved'),
('2025-08-03 02:20:00', 'Critical', '203.0.113.99', '10.0.1.8', 'Data Exfiltration', 'Large outbound data transfer to unknown IP', 'Open'),
('2025-08-03 11:10:00', 'High', '198.51.100.55', '10.0.2.7', 'Malware', 'Trojan detected in downloaded file', 'Investigating'),
('2025-08-04 07:45:00', 'Medium', '192.0.2.10', '10.0.1.20', 'Unauthorized Access', 'Login from unrecognized geographic location', 'Investigating'),
('2025-08-04 13:30:00', 'Low', '10.0.4.22', '10.0.1.5', 'Policy Violation', 'Access to restricted website detected', 'Resolved');

What do these records represent?

These are simulated security incidents you would find in a real security operations center. Each row includes a severity level (Critical, High, Medium, Low), network IPs, and a description of what happened.

The IP addresses use reserved documentation ranges (203.0.113.x, 198.51.100.x, 192.0.2.x) so they will never conflict with real networks.

You should see Query OK, 8 rows affected.

  • Insert the second batch of incidents by running this SQL:
INSERT INTO incidents (incident_date, severity, source_ip, destination_ip, incident_type, description, status) VALUES
('2025-08-05 04:55:00', 'Critical', '203.0.113.12', '10.0.1.3', 'DDoS', 'Distributed denial of service attack detected', 'Open'),
('2025-08-05 10:20:00', 'High', '198.51.100.77', '10.0.2.15', 'SQL Injection', 'SQL injection attempt on login form', 'Resolved'),
('2025-08-06 15:40:00', 'Medium', '192.0.2.33', '10.0.3.9', 'Phishing', 'Credential harvesting page reported by user', 'Resolved'),
('2025-08-06 22:15:00', 'High', '203.0.113.60', '10.0.1.5', 'Privilege Escalation', 'Unauthorized admin access attempt', 'Investigating'),
('2025-08-07 06:30:00', 'Low', '10.0.2.44', '10.0.1.12', 'Policy Violation', 'Use of personal email for work data transfer', 'Resolved'),
('2025-08-07 09:00:00', 'Medium', '192.0.2.91', '10.0.4.2', 'Unauthorized Access', 'Failed MFA bypass attempt', 'Investigating'),
('2025-08-08 01:45:00', 'Critical', '198.51.100.5', '10.0.1.8', 'Ransomware', 'Encryption activity detected on file server', 'Open'),
('2025-08-08 12:00:00', 'Low', '10.0.1.30', '10.0.2.3', 'Policy Violation', 'USB device connected against policy', 'Resolved');

You should see Query OK, 8 rows affected.

You now have 16 security incident records in your database, spanning all four severity levels.

Verify the data is loaded

Before moving on, confirm that every row made it into the table.

  • Run a count query to check the total number of rows:
SELECT COUNT(*) FROM incidents;

You should see 16 as the result. This is the number you will compare against after the migration in a later step.

Before you run the final check, take a moment to predict: what do you expect to see when you query for the first five rows?

  • Run a SELECT query to preview the data:
SELECT * FROM security_logs.incidents LIMIT 5;

You should see five rows of security incident data. The first row should be the Critical ransomware incident from 2025-08-01.

Seeing zero rows or an error?

If the count returns 0, make sure both INSERT statements ran successfully. Re-run them. MySQL will reject duplicates only if there is a unique constraint on a non-auto-increment column, so re-running is safe if the first attempt failed.

If you see Table 'security_logs.incidents' doesn't exist, confirm you ran USE security_logs; and then the CREATE TABLE statement.

Help me fix my security_logs database setup

Your source database is loaded and verified. Next up, you will create the S3 bucket and configure AWS DMS to move this data to the cloud.

Create the S3 Target Bucket and Configure DMS

Your security incidents database is loaded and ready. Now you need two things: a destination for the data to land, and a service to move it there.

In this step, you will create an S3 bucket as the migration target and configure AWS DMS (Database Migration Service) to connect your source database to that bucket. Along the way, you will run into a common first-time obstacle that teaches an important IAM lesson.

In this step, get ready to:
  • Create an S3 bucket as the migration target.
  • Set up the IAM roles that DMS requires to operate.
  • Create a DMS replication instance with source and target endpoints.
Create the S3 migration bucket

Every migration needs a landing zone. Your migrated data will arrive as flat CSV files in an S3 bucket.

  • In the AWS Management Console, type S3 into the search bar at the top and select S3.
  • Click Create bucket.
  • For Bucket name, enter a globally unique name like security-logs-migration- followed by your initials (for example, security-logs-migration-jd).
  • Confirm Block all public access is checked. This is the default and keeps your data private.
  • Leave all other settings at their defaults.
  • Scroll down and click Create bucket.

You should see a green success banner and your new bucket in the bucket list.

  • Note your bucket name. You will need it several times throughout this step.

Record your bucket name here: your-bucket-name.

Why does the bucket name need to be unique?

S3 bucket names are globally unique across all AWS accounts worldwide. No two buckets anywhere in AWS can share the same name. Adding your initials ensures you do not collide with another learner's bucket.

Attempt to create a DMS replication instance

With your S3 bucket ready, it is time to set up the DMS replication instance. This is the compute resource that reads from your source database and writes to S3.

  • In the console search bar, type DMS and select AWS Database Migration Service.
  • In the left navigation pane, click Replication instances.
  • Click Create replication instance.

Before you fill anything in, think about this: do you expect this to succeed on a brand-new AWS account, or will something block you?

  • For Name, enter security-logs-dms.
  • For Instance class, select dms.t3.micro.
  • For High Availability, choose Dev or test workload (Single-AZ).
  • Leave all other settings at their defaults.
  • Click Create replication instance.

You will see an error. The creation fails because DMS requires two IAM roles that do not exist yet in a new account.

Why did this fail?

DMS needs to manage VPC networking and write CloudWatch logs on your behalf. It looks for two IAM roles by their exact names: dms-vpc-role and dms-cloudwatch-logs-role. These roles are not created automatically when you sign up for AWS.

DMS searches for these roles by name. If they are missing or named differently, the service refuses to start. This is the obstacle you need to fix before moving forward.

Create the required DMS IAM roles

You need to create three IAM roles total. Two are required by DMS itself, and one grants DMS permission to write to your S3 bucket.

Start with the first required role: dms-vpc-role.

  • In the console search bar, type IAM and select IAM.
  • In the left navigation pane, click Roles.
  • Click Create role.
  • Under Select trusted entity, choose AWS service.
  • Under Use case, search for and select DMS.
  • Click Next.
  • On the Add permissions page, search for AmazonDMSVPCManagementRole and select it.
  • Click Next.
  • For Role name, enter exactly dms-vpc-role. This name must be exact. DMS looks for it by name.
  • Click Create role.

You should see a success banner confirming dms-vpc-role was created.

Now create the second required role: dms-cloudwatch-logs-role.

  • Click Create role again.
  • Under Select trusted entity, choose AWS service.
  • Under Use case, search for and select DMS.
  • Click Next.
  • On the Add permissions page, search for AmazonDMSCloudWatchLogsRole and select it.
  • Click Next.
  • For Role name, enter exactly dms-cloudwatch-logs-role.
  • Click Create role.

You should see a success banner confirming dms-cloudwatch-logs-role was created. You now have two of the three roles.

What do these two roles do?

The dms-vpc-role role lets DMS manage VPC networking so the replication instance can reach your source database and S3 bucket.

The dms-cloudwatch-logs-role role lets DMS write operational logs to CloudWatch, so you can troubleshoot if anything goes wrong during migration.

The third role is a custom one that gives DMS permission to write files into your specific S3 bucket. This role needs an inline policy you will write yourself.

  • Click Create role again.
  • Under Select trusted entity, choose AWS service.
  • Under Use case, search for and select DMS.
  • Click Next.
  • On the Add permissions page, do not select any managed policy. Click Next to skip this for now.
  • For Role name, enter dms-s3-target-role.
  • Click Create role.

The role exists but has no permissions yet. Now add an inline policy that grants S3 access.

  • In the IAM Roles list, click on dms-s3-target-role to open it.
  • Click the Permissions tab, then click Add permissions and select Create inline policy.
  • Click the JSON tab in the policy editor.
  • Replace the existing content with the following policy. Make sure to replace the bucket name with your actual bucket name in both places:
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:DeleteObject",
        "s3:PutObjectTagging"
      ],
      "Resource": [
        "arn:aws:s3:::[[BUCKET_NAME=\"your-bucket-name\"]]/*"
      ]
    },
    {
      "Effect": "Allow",
      "Action": [
        "s3:ListBucket"
      ],
      "Resource": [
        "arn:aws:s3:::[[BUCKET_NAME=\"your-bucket-name\"]]"
      ]
    }
  ]
}

What does this policy do?

This policy grants DMS four specific permissions on your bucket. s3:PutObject lets DMS write migrated data files. s3:DeleteObject lets DMS clean up if needed. s3:PutObjectTagging lets DMS tag the objects it creates.

The s3:ListBucket permission on the bucket itself lets DMS confirm the bucket exists and see its contents. Notice how the object-level actions target bucket/* while the bucket-level action targets just bucket. This is the principle of least privilege in action.

  • Click Next.
  • For the policy name, enter dms-s3-write-policy.
  • Click Create policy.

You should see the inline policy appear under the Permissions tab for dms-s3-target-role.

  • While still viewing dms-s3-target-role, copy the ARN displayed near the top of the page. You will need this when creating the DMS target endpoint.

Record your S3 target role ARN here: your-role-arn.

Confirm all three roles exist by checking the IAM Roles list. Search for dms and you should see dms-vpc-role, dms-cloudwatch-logs-role, and dms-s3-target-role in the results.

Not seeing all three roles?

Double-check the role names are spelled exactly right. DMS looks for dms-vpc-role and dms-cloudwatch-logs-role by their exact names. Any variation in spelling will cause DMS to fail. Also make sure you selected DMS as the use case under AWS service when creating each role.

Help me troubleshoot my DMS IAM role setup. I created the roles but DMS still cannot find them.

Create the DMS replication instance and endpoints

With all three IAM roles in place, it is time to go back to DMS and create the replication instance. This time it should work.

  • Return to the DMS console by typing DMS in the search bar and selecting AWS Database Migration Service.
  • In the left navigation pane, click Replication instances.
  • Click Create replication instance.
  • For Name, enter security-logs-dms.
  • For Instance class, select dms.t3.micro.
  • For High Availability, choose Dev or test workload (Single-AZ).
  • Leave all other settings at their defaults (your default VPC will be selected automatically).
  • Click Create replication instance.

This time the creation should succeed. The instance status starts at Creating and takes a few minutes to reach Available. Wait for it to show Available before continuing.

Use the refresh button

The DMS console does not auto-refresh. Click the refresh icon near the top of the replication instances list every 30 seconds or so to check the status.

While you wait (or once it reaches Available), create the source endpoint that points to your RDS MySQL database.

  • In the left navigation pane, click Endpoints.
  • Click Create endpoint.
  • Select Source endpoint.
  • Check the box for Select RDS DB instance and choose your RDS MySQL instance from the dropdown.
  • For Endpoint identifier, enter security-logs-source.
  • Confirm the Source engine is set to MySQL.
  • The Server name and Port (3306) should be auto-populated from your RDS instance.
  • For User name, enter the master username you set when creating your RDS instance.
  • For Password, enter the master password you set when creating your RDS instance.

Before creating this endpoint, test the connection to make sure DMS can reach your database.

  • Expand the Test endpoint connection (optional) section at the bottom.
  • Select your VPC from the dropdown.
  • Select your replication instance (security-logs-dms) from the dropdown. It must be in Available status to appear.
  • Click Run test.

Wait for the status to show successful. This confirms DMS can connect to your RDS MySQL database.

  • Click Create endpoint.

Connection test failed?

Make sure your replication instance is in the same VPC as your RDS instance. Check that the VPC security group on your RDS instance allows inbound traffic on port 3306 from the replication instance's security group. Also double-check the master username and password match what you set in Step 2.

Help me troubleshoot my DMS source endpoint connection test failure.

Now create the target endpoint that points to your S3 bucket.

  • Click Create endpoint.
  • Select Target endpoint.
  • For Endpoint identifier, enter security-logs-s3-target.
  • For Target engine, select Amazon S3.
  • For Service Access Role ARN, paste the ARN of the dms-s3-target-role you copied earlier (your-role-arn).
  • For Bucket name, enter your S3 bucket name (your-bucket-name).

Test this endpoint connection the same way you tested the source.

  • Expand the Test endpoint connection (optional) section.
  • Select your VPC and replication instance (security-logs-dms).
  • Click Run test.
  • Wait for the status to show successful.
  • Click Create endpoint.

S3 target connection test failed?

The most common cause is a mismatch between the bucket name in your IAM policy and the bucket name you entered in the endpoint. The names must match exactly. Also confirm dms.amazonaws.com is listed as the trusted entity on dms-s3-target-role. You can check this by opening the role in IAM and clicking the Trust relationships tab.

Help me troubleshoot my DMS S3 target endpoint connection failure.

Before you check the DMS console, what do you expect to see for each of your three resources: the replication instance, the source endpoint, and the target endpoint?

  • In the DMS console, click Replication instances in the left pane. Confirm your instance shows Available status.
  • Click Endpoints in the left pane. Confirm you see both security-logs-source and security-logs-s3-target listed.

You should see one replication instance in Available status, and two endpoints that both passed their connection tests. Your DMS infrastructure is fully configured.

Your migration infrastructure is locked and loaded. You have an S3 bucket waiting for data, three IAM roles granting the right permissions, and a DMS replication instance connected to both your database and your bucket. Next up, you will pull the trigger and watch DMS move your security log data from MySQL to S3.

Run the Migration and Verify the Data in S3

Your replication instance is available, and both endpoints passed their connection tests. The DMS infrastructure is ready to move data.

In this step, you'll create a migration task that tells AWS DMS to transfer your security incident records from RDS to S3 as CSV files. This is the core of the entire migration pipeline.

In this step, get ready to:
  • Create a DMS migration task with table mapping rules.
  • Monitor the task until it reaches Load complete status.
  • Verify the migrated CSV files in your S3 bucket.
Create the DMS migration task

A DMS migration task connects your replication instance, source endpoint, and target endpoint into one job. It also defines which tables to migrate and when to start.

  • In the DMS console, select Database migration tasks in the left navigation pane.
  • Click Create task.
  • The Create database migration task page opens. In the Task configuration section, enter a Task identifier such as security-logs-migration-task.
  • Select your replication instance from the Replication instance dropdown.
  • Select your RDS source endpoint from the Source database endpoint dropdown.
  • Select your S3 target endpoint from the Target database endpoint dropdown.
  • Set Migration type to Migrate existing data.

What does Migrate existing data mean?

This migration type performs a full load. DMS reads every row from your source table and writes it to the target. It does not track ongoing changes after the initial load completes.

For a small dataset like yours, a full load is the simplest approach. In production, you would choose a type that also replicates ongoing changes to keep source and target in sync.

  • Scroll down to the Table mappings section and expand Selection rules.
  • Click Add new selection rule.
  • For Schema, select Enter a schema.
  • Enter security_logs in the Schema name field.
  • Enter incidents in the Table name field. You can also enter % to include all tables in the schema.
  • Leave the Action set to Include.

What does the table mapping do?

The selection rule tells DMS exactly which schema and table to migrate. Without it, DMS would not know which data to pull from your source database.

The % wildcard acts like a "select all" filter. Using it for the table name would migrate every table inside the security_logs schema.

  • Scroll to the Migration task startup configuration section.
  • Select Start automatically on create.
  • Click Create task at the bottom of the page.

DMS immediately begins creating your migration task. You are redirected to the task details page.

Monitor the migration task

Your task starts automatically once DMS finishes creating it. For a small dataset of 15-20 rows, the migration completes quickly.

  • Watch the task Status at the top of the task details page. It progresses through several stages.
  • Wait for the status to change from Creating to Starting to Running to Load complete. This should take 1-2 minutes for your small dataset.
  • Click the Table statistics tab below the task details.
  • Confirm the row count in the Full load rows column matches the number of rows you inserted into your source table (15-20 rows).

Task stuck or showing an error?

If the task stays in Creating for more than a few minutes, try refreshing the page. DMS tasks can take a moment to initialize.

If the task fails, check the task status details for an error message. Common causes include incorrect endpoint credentials or missing IAM role permissions on the S3 target role.

Help me troubleshoot my DMS migration task error. The task is not reaching Load complete status.

Verify the migrated data in S3

DMS writes the migrated data as CSV files into your S3 bucket. The files are organized into a folder path based on your schema and table name.

Before you navigate to S3, what do you think the folder structure looks like inside your bucket? Think about the schema name and table name you configured in the table mapping.

  • Open the Amazon S3 console by searching for S3 in the AWS console search bar.
  • Click on your migration bucket (e.g. security-logs-migration- followed by your initials).
  • Click the security_logs folder.
  • Click the incidents folder inside it.
  • You should see one or more CSV files listed here.

The folder path security_logs/incidents/ matches the schema name and table name from your source database. DMS automatically creates this structure.

  • Select the checkbox next to one of the CSV files.
  • Click Download.
  • Open the downloaded file in a text editor or spreadsheet application.
  • Confirm the file contains your security incident records. You should see comma-separated values matching the columns you created: incident IDs, dates, severity levels, source IPs, destination IPs, incident types, descriptions, and statuses.

What should I see in the CSV file?

Each row in the CSV file corresponds to one row from your incidents table. The columns appear in the same order as your table definition. DMS uses a comma as the delimiter and a newline for each row.

The data should match what you saw when you ran SELECT * FROM security_logs.incidents in the previous step. Your security incident records have successfully moved from a managed database to flat files in cloud storage.

Don't see any files in the folder?

Make sure your migration task status shows Load complete. If it still shows Running, wait another minute and refresh the S3 page.

If the security_logs folder does not exist at all, check your table mapping in the DMS task. The schema name must match security_logs exactly.

Help me find my migrated CSV files in S3. My DMS task shows Load complete but I don't see any files.

Your data has made the journey from a managed database to flat files in the cloud. Next up, you'll use Amazon Athena to query those CSV files with standard SQL. No database server required.

Query the Migrated Data with Amazon Athena

Your security incident data is sitting in S3 as CSV files. That is a great start, but flat files in a bucket are not much use on their own. You need a way to query them.

Amazon Athena lets you run standard SQL directly on data stored in Amazon S3. No database to provision, no servers to manage. In this step, you will connect Athena to the CSV files that DMS migrated, then run analytical queries against your security incident records.

In this step, get ready to:
  • Set up Athena and create an analytics database.
  • Create an external table pointing to your migrated CSV data in S3.
  • Run SQL queries that prove the migration worked.
Set up Athena and create a database

Before you can run any queries, Athena needs a place to store its own query results. You will configure that first, then create the database that will hold your external table definition.

  • Open the Athena console at:
https://console.aws.amazon.com/athena/
  • If this is your first time visiting the Athena console, click Explore the query editor to open the query editor.
  • Click Edit Settings to configure where Athena stores query results.

Why does Athena need a query result location?

Every query Athena runs writes its output to an S3 path. You must specify this location before running your first query, or Athena will not let you proceed.

  • In the Location of query result box, enter the path to a folder in your existing migration bucket. Use the format:
s3://[[BUCKET_NAME="your-bucket-name"]]/athena-results/
  • Click Save.
  • Click Editor in the navigation bar to return to the query editor.

You should see the Athena query editor with a blank query pane on the right.

Seeing an error about the S3 location?

Make sure the bucket name matches your migration bucket exactly. The bucket must be in the same Region (us-east-1) as the one you set in the console top bar.

help me fix the Athena query result location error

Now create your analytics database. This database is a logical container inside Athena that will hold the external table you create next.

  • Create the database by pasting the following SQL into the query editor:
CREATE DATABASE security_logs_lake;
  • Click Run (or press Ctrl+Enter).

You should see a success message below the query editor confirming the database was created.

  • In the Database dropdown on the left-hand side, select security_logs_lake to make it your current database.

Don't see security_logs_lake in the dropdown?

Click the refresh icon next to the Database dropdown. If it still does not appear, check that the query completed successfully (no red error banner below the editor).

help me troubleshoot why my Athena database is not showing up

Create an external table pointing to your migrated data

An external table in Athena does not store data itself. It defines a schema (column names and types) and points to a location in S3 where the actual data lives. Athena reads the CSV files on demand every time you run a query.

  • Clear the query editor, then paste the following CREATE EXTERNAL TABLE statement:
CREATE EXTERNAL TABLE security_logs_lake.incidents (
  incident_id INT,
  incident_date TIMESTAMP,
  severity STRING,
  source_ip STRING,
  destination_ip STRING,
  incident_type STRING,
  description STRING,
  status STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 's3://[[BUCKET_NAME="your-bucket-name"]]/security_logs/incidents/';

What does this statement do?

  • CREATE EXTERNAL TABLE tells Athena to register a table that reads from an external data source instead of storing its own copy.
  • The column definitions (incident_id INT, severity STRING, etc.) match the columns in your RDS incidents table so Athena knows how to interpret each CSV field.
  • ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' tells Athena the data is comma-separated, which matches the default CSV format that DMS writes.
  • LOCATION points to the S3 folder where DMS placed the migrated CSV files. The trailing slash is required.
  • Click Run (or press Ctrl+Enter).

You should see a success message confirming the table was created. In the left-hand panel under Tables, you should now see incidents listed under the security_logs_lake database.

Seeing a FAILED: SemanticException error?

Double check that you selected security_logs_lake from the Database dropdown before running the query. Also verify the S3 LOCATION path matches the folder path in your bucket exactly (security_logs/incidents/) and ends with a trailing slash.

help me fix this Athena CREATE EXTERNAL TABLE error

Now verify the table actually returns data from your migrated CSV files.

  • Clear the query editor, then run the following query to pull a sample of your migrated data:
SELECT * FROM security_logs_lake.incidents LIMIT 10;
  • Click Run.

You should see up to 10 rows of security incident data in the results pane below the editor. The columns should match the ones you defined: incident_id, incident_date, severity, and so on.

Where is this data coming from?

Athena is reading directly from the CSV files in your S3 bucket. It is not connecting to your RDS database. The data flows from your bucket through the external table definition and into the query results. This is the serverless analytics pattern in action.

Run analytical queries on your security data

Returning raw rows proves the connection works. The real value of Athena is running analytical queries across your data without any database infrastructure. This is the moment your migration pays off.

Before you run the next query, think about what you expect to see. You inserted security incidents with different severity levels (Critical, High, Medium, Low) in Step 2. What do you think the breakdown will look like?

  • Clear the query editor, then paste and run the following aggregation query:
SELECT severity, COUNT(*) as incident_count
FROM security_logs_lake.incidents
GROUP BY severity
ORDER BY incident_count DESC;
  • Click Run.

You should see a breakdown of your incidents by severity level. Each row shows a severity value (Critical, High, Medium, or Low) and the count of incidents at that level. These counts should match the data you inserted into the RDS database in Step 2.

What just happened?

You ran a SQL query against flat CSV files in S3 without provisioning a single database server. Athena scanned your migrated data, grouped it by severity, and returned the results. This is the same pattern used in production data lakes to analyze terabytes of log data.

In a real security operations centre, you could extend this to find all Critical incidents from a specific source IP, identify attack patterns over time, or feed the data into a dashboard.

Your data migration pipeline is complete. You moved security log data from a managed MySQL database to S3, then made it queryable with serverless SQL. No infrastructure to babysit, no database to keep running for analytics.

Secret mission

Secure Your Migration Pipeline

You built a working migration pipeline, but a security analyst knows that moving data without encryption and audit trails is a serious gap. In this mission, you will lock down your S3 bucket with server-side encryption, enable access logging, review every IAM role for least-privilege compliance, and write a brief security assessment of your pipeline.

Clean Up Your Resources

Clean Up Your Resources

This project created several AWS resources that consume your Free Tier credits while running. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Cost warning

Your RDS MySQL instance and DMS replication instance both consume Free Tier credits every hour they are running. If you are done with this project, delete these resources to preserve your remaining credit balance. You can check your credits in the AWS Billing dashboard.

Resources you used:

  • Amazon RDS MySQL instance (db.t3.micro or db.t4g.micro) with the security_logs database.
  • AWS DMS replication instance (dms.t3.micro), migration task, source endpoint, and target endpoint.
  • S3 migration bucket containing migrated CSV files and Athena query results.
  • S3 logging bucket or prefix storing server access logs.
  • Three IAM roles: dms-vpc-role, dms-cloudwatch-logs-role, and the DMS S3 target role.
  • Athena database (security_logs_lake) and external table (incidents).

Keep everything running

No action needed. Choose this if you are still actively building or want to continue querying your data in Athena.

  • Your RDS instance and DMS replication instance will continue consuming Free Tier credits while running.
  • Monitor your remaining credit balance by navigating to the Billing and Cost Management dashboard in the AWS console.
  • AWS sends email alerts when your credits drop to 50%, 25%, and 10% remaining.

Pause - I'll come back to this later

Reduce credit consumption by removing the resources that cost the most while keeping your data intact in S3.

  • Stop your RDS instance to pause compute charges: navigate to Amazon RDS in the console, select Databases, choose your DB instance, then select Actions > Stop temporarily. The instance restarts automatically after 7 days.
  • Delete the DMS replication instance since your migration is already complete. Navigate to AWS DMS > Replication instances, select your instance, then choose Actions > Delete.
  • Your S3 bucket, IAM roles, and Athena database remain intact and do not consume significant credits.

Delete - I don't want to use this again

Remove all project resources completely. This stops all credit consumption from this project.

Delete DMS resources first (they depend on the replication instance):

  • Navigate to AWS DMS in the console. Select Database migration tasks, choose your migration task, then select Actions > Delete. Wait for the task to be removed.
  • Select Endpoints in the DMS sidebar. Delete the source endpoint (MySQL/RDS) and the target endpoint (S3) one at a time using Actions > Delete.
  • Select Replication instances. Choose your replication instance, then select Actions > Delete. Wait for the instance to finish deleting.

Delete the RDS database:

  • Navigate to Amazon RDS > Databases. Select your DB instance, then choose Actions > Delete.
  • Uncheck Create final snapshot and check the acknowledgement box. Then select Delete.

Delete the S3 buckets:

  • Navigate to Amazon S3 in the console. Select your migration bucket, then choose Empty. Confirm by typing permanently delete in the confirmation field, then select Empty.
  • After the bucket is empty, select it again and choose Delete. Confirm by typing the bucket name.
  • Repeat the empty and delete steps for your logging bucket (if you created a separate bucket for server access logs).
  • If you created a separate bucket or prefix for Athena query results, empty and delete that as well.

Delete the IAM roles:

  • Navigate to IAM > Roles in the console. Search for dms-vpc-role, select it, then choose Delete. Confirm the deletion.
  • Repeat for dms-cloudwatch-logs-role and your DMS S3 target role.

Clean up Athena metadata (optional, no cost):

  • If you want a clean slate, navigate to Amazon Athena in the console and run these queries in the query editor:
DROP TABLE IF EXISTS security_logs_lake.incidents;
DROP DATABASE IF EXISTS security_logs_lake;
  • These remove the Athena table and database definitions. The underlying S3 data is already gone from the bucket deletion above.

Nice Work!

Nice Work!

Nice work! You just built a complete cloud data migration pipeline that moves security log data from a managed database to cloud storage and makes it queryable on demand with serverless SQL.

You've learned how to:

  • Build an end-to-end data migration pipeline using AWS DMS that moved security incident records from an Amazon RDS MySQL database to Amazon S3 as CSV files.
  • Diagnose and resolve IAM role requirements for DMS by creating trust policies and applying least-privilege access to connect cloud services securely.
  • Create a serverless analytics layer in Amazon Athena with an external table that queries migrated data directly from S3 using standard SQL.
  • Secret Mission: Took on an optional challenge to push your cloud security skills further.

Ready to quiz yourself?