DEV Community: Md. Mizanur Rahman

Data Export: Aurora-s3-Redshift

Md. Mizanur Rahman — Wed, 23 Sep 2026 10:20:00 +0000

Exporting Data from Amazon Aurora MySQL to S3 and Loading It into Amazon Redshift

This guide explains how to export data from Amazon Aurora MySQL into Amazon S3 and then import those CSV files into Amazon Redshift.

The examples use generic testing resources:

S3 bucket: test-etl-bucket
IAM role: test-etl-role
Aurora user: TestEtlUser
AWS account ID: 123456789012
Aurora version: MySQL-compatible Aurora 3.x
AWS Region: us-east-1

Architecture

The data flow is:

Aurora MySQL
    ↓ SELECT INTO OUTFILE S3
Amazon S3
    ↓ Redshift COPY
Amazon Redshift

The IAM role is used by Aurora to write files to S3 and by Redshift to read those files.

Step 1: Create the IAM permissions policy

Create an IAM policy with access to the testing bucket:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ListBucket",
      "Effect": "Allow",
      "Action": [
        "s3:ListBucket"
      ],
      "Resource": [
        "arn:aws:s3:::test-etl-bucket"
      ]
    },
    {
      "Sid": "ReadWriteObjects",
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:PutObject",
        "s3:AbortMultipartUpload"
      ],
      "Resource": [
        "arn:aws:s3:::test-etl-bucket/*"
      ]
    }
  ]
}

Attach this policy to the IAM role:

test-etl-role

For stricter production security, separate roles are preferable:

Aurora export role with PutObject
Redshift import role with GetObject

A shared role is acceptable for initial testing.

Step 2: Configure the IAM trust policy

Use the following trust policy if the role needs to support Aurora, provisioned Redshift, and Redshift Serverless:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": [
          "rds.amazonaws.com",
          "redshift.amazonaws.com",
          "redshift-serverless.amazonaws.com"
        ]
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

The role ARN in this example is:

arn:aws:iam::123456789012:role/test-etl-role

If the S3 bucket uses a customer-managed KMS key, also grant the role kms:Decrypt and kms:GenerateDataKey as appropriate, and allow the role in the KMS key policy.

Step 3: Associate the role with Aurora

Creating the IAM role is not enough. It must also be associated with the Aurora cluster.

Open:

Amazon RDS
→ Databases
→ Select the Aurora cluster
→ Connectivity & security
→ Manage IAM roles
→ Add test-etl-role

Wait until the role status becomes Available.

Be sure to select the Aurora cluster, not only its writer instance.

Step 4: Configure the Aurora cluster parameter

For Aurora MySQL version 3, set this DB cluster parameter:

aws_default_s3_role

Set its value to:

arn:aws:iam::123456789012:role/test-etl-role

The parameter must be configured in a custom DB cluster parameter group.

Verify it from MySQL:

SHOW VARIABLES LIKE 'aws_default_s3_role';

Expected result:

arn:aws:iam::123456789012:role/test-etl-role

Reboot the Aurora writer if the parameter change is waiting for a reboot.

Step 5: Grant the Aurora database role

Grant S3 export access to the testing database user:

GRANT AWS_SELECT_S3_ACCESS
TO `TestEtlUser`@`%`;

Make all granted roles active automatically when the user reconnects:

SET DEFAULT ROLE ALL
TO `TestEtlUser`@`%`;

For the current MySQL session, activate the role immediately:

SET ROLE ALL;

Verify it:

SELECT CURRENT_USER(), CURRENT_ROLE();

The result should include:

`AWS_SELECT_S3_ACCESS`@`%`

Application connection pools

MySQL roles are session-specific unless they are configured as default roles. If the application uses a connection pool, SET ROLE ALL and the export must run on the same physical connection.

Example with Node.js:

const connection = await pool.getConnection();

try {
  await connection.query("SET ROLE ALL");

  const [roles] = await connection.query(`
    SELECT CURRENT_USER() AS current_user,
           CURRENT_ROLE() AS current_roles
  `);

  console.log(roles);

  await connection.query(`
    SELECT *
    FROM test_database.test_table
    INTO OUTFILE S3
      's3://test-etl-bucket/exports/test_table/run_001'
    FORMAT CSV HEADER
    OVERWRITE ON
  `);
} finally {
  connection.release();
}

Do not run SET ROLE ALL with one pooled connection and the export with another.

Step 6: Export Aurora data to S3

Run:

SELECT *
FROM test_database.test_table
INTO OUTFILE S3
  's3://test-etl-bucket/exports/test_table/run_001'
FORMAT CSV HEADER
OVERWRITE ON;

Aurora treats the S3 destination as an object prefix. It automatically creates files such as:

exports/test_table/run_001.part_00000
exports/test_table/run_001.part_00001

This naming is expected. The files contain CSV data even though their names do not end with .csv.

Aurora does not provide an option to remove the .part_00000 suffix.

If the selected dataset is empty, Aurora can produce a zero-byte object. Avoid including unwanted empty objects when loading data into Redshift.

Step 7: Associate the role with Redshift

Provisioned Redshift

Open:

Amazon Redshift
→ Provisioned clusters
→ Select the cluster
→ Properties
→ Cluster permissions
→ Manage IAM roles

Associate:

arn:aws:iam::123456789012:role/test-etl-role

The equivalent AWS CLI command is:

aws redshift modify-cluster-iam-roles \
  --cluster-identifier test-redshift-cluster \
  --add-iam-roles \
  arn:aws:iam::123456789012:role/test-etl-role

Redshift Serverless

Open:

Amazon Redshift
→ Redshift Serverless
→ Namespace configuration
→ Select the namespace
→ Security and encryption
→ Permissions

Associate test-etl-role with the namespace. It can optionally be configured as the default IAM role.

Step 8: Create the Redshift destination table

The Redshift table must already exist before running COPY.

Its column order and compatible data types must match the Aurora export:

CREATE SCHEMA IF NOT EXISTS staging;

CREATE TABLE staging.test_table (
    record_id       BIGINT,
    record_name     VARCHAR(500),
    record_status   VARCHAR(100),
    created_at      TIMESTAMP
);

Replace these example columns with the actual columns returned by the Aurora SELECT.

Step 9: Validate the files without loading

Use NOLOAD to validate the CSV format and data types:

COPY staging.test_table
FROM 's3://test-etl-bucket/exports/test_table/run_001'
IAM_ROLE 'arn:aws:iam::123456789012:role/test-etl-role'
FORMAT AS CSV
IGNOREHEADER 1
REGION 'us-east-1'
NOLOAD;

Use IGNOREHEADER 1 only when the Aurora export used:

FORMAT CSV HEADER

If the export did not contain a header, remove IGNOREHEADER 1.

If S3 and Redshift are in the same Region, the REGION option is optional. It is required when the bucket and Redshift are in different Regions.

Step 10: Load the data into Redshift

After NOLOAD succeeds, remove it and run the actual import:

COPY staging.test_table
FROM 's3://test-etl-bucket/exports/test_table/run_001'
IAM_ROLE 'arn:aws:iam::123456789012:role/test-etl-role'
FORMAT AS CSV
IGNOREHEADER 1
REGION 'us-east-1';

If the IAM role is configured as Redshift’s default role, the command can use:

IAM_ROLE default

For example:

COPY staging.test_table
FROM 's3://test-etl-bucket/exports/test_table/run_001'
IAM_ROLE default
FORMAT AS CSV
IGNOREHEADER 1;

Redshift loads every S3 object whose key matches the specified prefix.

Therefore:

s3://test-etl-bucket/exports/test_table/run_001

can load:

run_001.part_00000
run_001.part_00001
run_001.part_00002

Loading multiple files safely

If every object under a folder has the same table structure, the complete folder can be loaded:

COPY staging.test_table
FROM 's3://test-etl-bucket/exports/test_table/'
IAM_ROLE default
FORMAT AS CSV
IGNOREHEADER 1;

Do not load an entire folder when it contains:

Files for different destination tables
Different column structures
Temporary files
Previous exports
Unwanted zero-byte files
Manifest files mixed with data files

In these cases, use a specific prefix or a Redshift manifest.

Example manifest:

{
  "entries": [
    {
      "url": "s3://test-etl-bucket/exports/test_table/run_001.part_00000",
      "mandatory": true
    },
    {
      "url": "s3://test-etl-bucket/exports/test_table/run_001.part_00001",
      "mandatory": true
    }
  ]
}

Load it with:

COPY staging.test_table
FROM 's3://test-etl-bucket/manifests/test_table_run_001.json'
IAM_ROLE default
FORMAT AS CSV
IGNOREHEADER 1
MANIFEST;

Avoiding duplicate data

Redshift COPY appends rows. It does not automatically replace existing rows or perform an upsert.

For a complete refresh:

BEGIN;

TRUNCATE TABLE staging.test_table;

COPY staging.test_table
FROM 's3://test-etl-bucket/exports/test_table/run_001'
IAM_ROLE default
FORMAT AS CSV
IGNOREHEADER 1;

COMMIT;

For incremental imports, load into a staging table first and then use MERGE based on the business key.

Common errors

Aurora error 1227: SELECT INTO S3 privilege required

Example:

Access denied; you need the SELECT INTO S3 privilege

Cause:

The AWS_SELECT_S3_ACCESS role is granted but not active.

Solution:

SET ROLE ALL;
SELECT CURRENT_ROLE();

For a permanent solution:

SET DEFAULT ROLE ALL TO `TestEtlUser`@`%`;

Aurora error 63985: Missing Credentials

Example:

S3 API returned error:
Missing Credentials: Cannot instantiate S3 Client

Possible causes:

aws_default_s3_role is empty or incorrect
The role is not associated with the Aurora cluster
The associated role is not in Available status
The IAM trust policy does not allow rds.amazonaws.com
The parameter was changed but the writer still requires a reboot

Export works in terminal but fails from application code

Cause:

The terminal session ran SET ROLE ALL, but the application opened a different connection where the role was inactive.

Solution:

Configure the AWS role as a default database role, or
Execute SET ROLE ALL on every new application connection

Redshift S3 AccessDenied

Check the following:

The role is associated with the correct Redshift cluster or namespace
The COPY command uses the correct role ARN
The role has s3:ListBucket and s3:GetObject
The bucket policy does not contain an explicit deny
The KMS key policy allows the role when SSE-KMS is used
Redshift Serverless trust includes redshift-serverless.amazonaws.com

Redshift data-format errors

Validate first:

COPY staging.test_table
FROM 's3://test-etl-bucket/exports/test_table/run_001'
IAM_ROLE default
FORMAT AS CSV
IGNOREHEADER 1
NOLOAD;

For provisioned Redshift, inspect recent load errors:

SELECT *
FROM stl_load_errors
ORDER BY starttime DESC
LIMIT 20;

Final checklist

Before running the pipeline, confirm:

The IAM role has S3 read and write permissions
The role trusts Aurora and the relevant Redshift service
The role is associated with the Aurora cluster
aws_default_s3_role contains the correct role ARN
AWS_SELECT_S3_ACCESS is active for TestEtlUser
The role is associated with Redshift
The Redshift destination table already exists
The column order matches the CSV data
Header handling is consistent
The selected S3 prefix contains only the intended files
Duplicate-load behavior has been considered

Official references:

Build Node.js app in Replit & use s3 as static web hosting serving with CDN

Md. Mizanur Rahman — Mon, 14 Jul 2025 12:19:36 +0000

In this AI Era, there's lot of prompt module are available to ease our daily life. Among them i have found one good one which is 'replit'. link: https://replit.com/

I have developed my portfolio in replit. it's node.js app. i have given a prompt to develop like this and gave all kinds of information in replit.

After building the project, i have downloaded the code and then build the application code locally.

npm init -y npm run build

Then i have created a S3 bucket and upload assets folder and index.html of that build project. [S3 has all public access blocked]

For static web hosting, i had to enable the "Static website hosting" from S3--> Properties.
So, if that option is enabled then a weblink you will get and try to open it and found that your app is available on that link.[If public access enabled]

Now the main part, CDN configuration. I have created an CDN with Default config.[CDN takes time to be created fully]
I have configured altenate domain name with my domain 'mizaniftee.xyz'

Then i have configured the SSL from AWS ACM. Records were automatically added in my hosted zone.

N.B: If you want to use CDN with your wildcard domain then it couldn't be added manually. like i wanted to forward mizaniftee.xyz to CDN URL but couldn't. here www.mizaniftee.xyz was doable

As i have set S3 as private, so i had to add some permission in s3 bucket by which CDN could access the files.
going to s3--> Permissions then add below

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AllowCloudFrontServicePrincipal",
            "Effect": "Allow",
            "Principal": {
                "Service": "cloudfront.amazonaws.com"
            },
            "Action": "s3:GetObject",
            "Resource": "arn:aws:s3:::s3_Bucket_name/*",
            "Condition": {
                "StringEquals": {
                    "AWS:SourceArn": "arn:aws:cloudfront::YOUR_ACCOUNT_ID:distribution/DISTRIBUTION_ID"
                }
            }
        }
    ]
}

Now i could easily access my portfolio website with my domain which is serving through CDN to s3 bucket Files.

How to Create & Setting Up IAM Identity Center

Md. Mizanur Rahman — Wed, 27 Nov 2024 15:52:37 +0000

IAM Identity Center is the AWS solution for connecting your workforce users to AWS managed application.
Identity Center Permission Sets are basically templates of IAM roles that will be provisioned in the account. When you assign a permission set to an account, the role is created and a trust policy to handle the federation is configured automatically.
It also supports SSO[Single Sign-On] as well as you could integrate 3rd party like AAD[Azure Active Directory] to this.

Getting Started:

First Go to that account Console with https://Account_Number.signin.aws.amazon.com/console

Then Search IAM Identity Center and Press on Enable. You can Enable in any region as you wish.

N.B- Though you have enabled IAM Identity Center, you could also use specific account console as you have used before. Like https://Account_Number.signin.aws.amazon.com/console but for that you need to have IAM Role for that Or Root Account Holder.

After Enabling, Got Successful Message

Now, Edit the Instance name as it will be showed when you want to access through AWS Access Portal.

If you want to customize your access portal URL and provide the URL to the USER. Go to Dashboard

So, Link will be like that https://mizanzone.awsapps.com/start. And After login, we could see the Mizan tech Account For that specific account.

Permission & Others:

You need to create permissions sets. There are some predefined sets like below:

Here session is = aws access portal session after login.
Relay State: No need right now [it will forward to that URL what is set in the section]
You could set Custom Permission set there. Like only Ec2-admin/ S3-Access as You want per requirements.

You will create groups and set users to that group.

Now we will create users and assign to the required groups. Please create users with mail-wise for the company. So that anyone could use their mail as Username.

After Creating the IAM Identity center, Root user will create another user to work with him in the same account or in the identity center.

N.B: You could add multiple AWS Accounts under Same organization. Just need to send invite from AWS Organization page.After that, all the Accounts will be listed under IAM Identity Center AWS Account

So, You need to assign permission sets & Groups to the accounts of that AWS Organization. What Permission set and groups are added in the AWS Account, only those could access that account permission wise.

When we have multiple accounts that time all the accounts will be listed above.
Now, we will use Access portal URL [https://mizanzone.awsapps.com/start/] and after login, we will get views like that

References:

How to Update Aurora Global Database Version

Md. Mizanur Rahman — Sun, 15 Sep 2024 16:09:32 +0000

From time to time we need to update our database version to latest to get the new changes. it could be a major change or minor change.
For minor changes, AWS has provided a option "Auto Minor Version Upgrade" feature for Single RDS, Multi AZ & Aurora single Cluster.
But that feature doesn't apply to the following kinds of Aurora Global clusters:

Clusters that are part of an Aurora global database
Clusters that have cross-Region replicas

So, we need to follow some process to update that global DB.

First, Remove all secondary Regions from the global cluster.

Just Select the secondary cluster and go to “Actions“
So, that secondary cluster will be a regional cluster and a seperate cluster.

Upgrade the engine version of the primary Region to desired version, as applicable from Portal or CLI or Terraform[any IAC]

Now, if "Enable Deletion Protection" is enabled then please disable that option for secondary cluster.
Now delete that secondary region cluster/ you could use as another cluster if needed. if you don't delete then either you have to add with another DB identifier or you will get thise error.

After Deletion, please select the Global Database and go to "Actions" and "select Add AWS Region". [check references no-3]
If you use Terraform code then run "terraform apply" and it will take time to create those instances.

References:

AWS RDS Proxy For Aurora Global Database [MYSQL]

Md. Mizanur Rahman — Wed, 17 Jul 2024 10:12:52 +0000

Using Amazon RDS Proxy, you can allow your applications to pool and share database connections to improve their ability to scale.
It does so in an active way first by understanding the database protocol. It then adjusts its behavior based on the SQL operations from your application and the result sets from the database.

Quotas/Limitation:

you can have up to 20 proxies for each AWS account ID
Each proxy has a default endpoint. You can also add up to 20 proxy endpoints for each proxy.
Each proxy can have up to 200 associated Secrets Manager secrets
RDS Proxy must be in the same virtual private cloud (VPC) as the database. The proxy can't be publicly accessible
Each proxy can be associated with a single target DB cluster [For Primary need 1 and For Secondary Need another 1]
can't use RDS Proxy with an RDS for MySQL DB instance that has the read_only parameter in its DB parameter group set to 1.

Transactions By RDS Proxy:

Connection reuse can happen after each individual statement when the Aurora MySQL autocommit setting is turned on.
Conversely, when the autocommit setting is turned off, the first statement you issue in a session begins a new transaction. For example, suppose that you enter a sequence of SELECT, INSERT, UPDATE, and other data manipulation language (DML) statements. In this case, connection reuse doesn't happen until you issue a COMMIT, ROLLBACK, or otherwise end the transaction.
Entering a data definition language (DDL) statement causes the transaction to end after that statement completes.

Failover:

Without RDS Proxy, a failover involves a brief outage. During DB failovers, RDS Proxy continues to accept connections at the same IP address and automatically directs connections to the new primary DB instance. [When Failover happens, the secondary cluster becomes primary]

When the database writer is unavailable, RDS Proxy queues up incoming requests.

IP Address Capacity For RDS Proxy:

Aurora Global DB and RDS Proxy should be in same VPC should have a minimum of two subnets that are in different Availability Zones.
Following are the recommended minimum numbers of IP addresses to leave free in subnets for proxy based on DB instance class sizes.

In this case, assume the following:

Aurora DB cluster has 1 writer instance of size db.r5.8xlarge and 1 reader instance of size db.r5.2xlarge.
The proxy that's attached to this DB cluster has the default endpoint and 1 custom endpoint with the read-only role.

In this case, the proxy needs approximately 63 free IP addresses (45 for the writer instance, 15 for reader instance, and 3 for the additional custom endpoint).

Database Credentials in AWS Secrets:

For each proxy that we will create, we will first use the Secrets Manager service to store sets of user name and password credentials. Need to create a separate Secrets Manager secret for each database user account that the proxy connects to on the Aurora DB cluster.

To do this, you can use the setting Credentials for other database, Credentials for RDS database, or Other type of secrets.
Fill in the appropriate values for the User name and Password fields, and values for any other required fields.

{"username":"db_user",
"password":"db_user_password"}

IAM Policy to access:

After you create the secrets in Secrets Manager, you create an IAM policy that can access those secrets.

You could create IAM Role automatically when you create the rds proxy.
You could create policy first, then create role and add assign that role when creating the proxy

Role Creation:

then go for "next"

Policy Creation:
Use inline policy and add below

{ "Version": "2012-10-17", "Statement": [ { "Sid": "VisualEditor0", "Effect": "Allow", "Action": "secretsmanager:GetSecretValue", "Resource": [ "arn:aws:secretsmanager:us-east-2:account_id:secret:secret_name_1", "arn:aws:secretsmanager:us-east-2:account_id:secret:secret_name_2" ] }, { "Sid": "VisualEditor1", "Effect": "Allow", "Action": "kms:Decrypt", "Resource": "arn:aws:kms:us-east-2:account_id:key/key_id", "Condition": { "StringEquals": { "kms:ViaService": "secretsmanager.us-east-2.amazonaws.com" } } } ] }

Configuration Points: [Main Points]

Idle client connection timeout: Default time 1800s(30m) where a connection could be idle.A client connection is considered idle when the application doesn't submit a new request within the specified time after the previous request completed.
Connection pool maximum connections: Specify a value from 1 through 100. This setting represents the percentage of the max_connections value that RDS Proxy can use for its connections.
Like our Prod DB max connection is 4000, so what percentage we will set , rds proxy will use that [percentage*4000]/100
Connection borrow timeout: If proxy use all available connection then can specify how long the proxy waits for a database connection to become available before returning a timeout error. We can specify a period up to a maximum of five minutes.
VPC security group: must configure the Inbound rules to allow your applications to access the proxy. We must also configure the Outbound rules to allow traffic from our DB targets.

Endpoint for RDS Proxy:

Each proxy handles connections to a single Aurora DB cluster. If Global DB has a Primary & Secondary Cluster, so you need two RDS Proxy in this regard.
Add Reader Proxy Endpoint in RDS Proxy will create a read endpoint that points to Aurora DB Cluster Reader.
Default [Read/Write] Proxy endpoint works with Write instance

You could connect directly to DB or through RDS Proxy, but if we connect with RDS Proxy then you need to create secrets for every user.

COST:

RDS Proxy pricing correlates to the number of vCPUs for each database instance in your Aurora cluster.
If Aurora cluster that has a db.r6.large writer instance (2 vCPUs) and a db.r6.large reader instance (2 vCPUs $0.015 per vCPU-hour)
So, Monthly bill → 2,880 vCPU-hours (4 vCPU x 24 hours x 30 days)==$43.20

References:
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/rds-proxy-network-prereqs.html

Monitoring user login through Cloudtrail for IAM Identity Center[AWS]

Md. Mizanur Rahman — Thu, 07 Sep 2023 15:29:56 +0000

AWS IAM Identity Center helps you securely create or connect your identities and manage their access centrally across AWS accounts and applications. IAM Identity Center is the recommended approach for workforce authentication and authorization on AWS for organizations of any size and type.

As there is no lockout/notification system for wrong login attempt in IAM Identity Center, so we will discuss how to configure a system by which we could be notified/get wrong login info.

Config Procedure:

Create a Cloudtrail in that AWS account where the IAM Identity center is configured
Enable Cloudwatch log + Cloudwatch log group + S3 Storage location
Create Cloudwatch Logs Metric Filter
Create SNS and send alarm notifications.

Creating Cloudtrail:

Event in Cloudtrail

Create Cloudwatch Metric Filter:
Now we have to go to Cloudwatch group and set the metric by which Cloudwatch alert will be generated.

Add below pattern

{ $.eventSource = "signin.amazonaws.com" && $.serviceEventDetails.CredentialVerification = "Failure" }

After setting the pattern, you could test the pattern at the time of metric creation.

Now we need to put some values for the metric

Save the changes, so metric will be created and it's time to create a cloudwatch alarm.

Now we set like below to set the threshold values for login attempts.

Set conditions **per requirements and press "next**"

it's time to set notification policies with SNS.

Before that we need create a **SNS **with email endpoint to get the alert to the mail.

How to create SNS:

Create SNS with email subscription

First create a topic with **standard **type. Give a name and description.

After creating the "topic", go to that topic.

Create a "Subscription" where protocol "Email" and set the *endpoint * [email address]to which we want to get the email.

So, we will get alert mail like below if any wrong attempt for login crosses the threshold value.