AWS RDS Proxy For Aurora Global Database [MYSQL]

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