AWS CLI examples for custom endpoints for Amazon Aurora
The following tutorial uses AWS CLI examples with Unix shell syntax to show how you might define a cluster with several "small" DB instances and a few "big" DB instances, and create custom endpoints to connect to each set of DB instances. To run similar commands on your own system, you should be familiar enough with the basics of working with Aurora clusters and AWS CLI usage to supply your own values for parameters such as region, subnet group, and VPC security group.
This example demonstrates the initial setup steps: creating an Aurora cluster and
adding DB instances to it. This is a heterogeneous cluster, meaning not all the DB instances
have the same capacity. Most instances use the AWS instance class
db.r4.4xlarge
, but the last two DB instances use db.r4.16xlarge
.
Each of these sample create-db-instance
commands prints its output to the
screen and saves a copy of the JSON in a file for later inspection.
aws rds create-db-cluster --db-cluster-identifier custom-endpoint-demo --engine aurora-mysql \ --engine-version 8.0.mysql_aurora.3.02.0 --master-username $MASTER_USER --manage-master-user-password \ --db-subnet-group-name $SUBNET_GROUP --vpc-security-group-ids $VPC_SECURITY_GROUP \ --region $REGION for i in 01 02 03 04 05 06 07 08 do aws rds create-db-instance --db-instance-identifier custom-endpoint-demo-${i} \ --engine aurora --db-cluster-identifier custom-endpoint-demo --db-instance-class db.r4.4xlarge \ --region $REGION \ | tee custom-endpoint-demo-${i}.json done for i in 09 10 do aws rds create-db-instance --db-instance-identifier custom-endpoint-demo-${i} \ --engine aurora --db-cluster-identifier custom-endpoint-demo --db-instance-class db.r4.16xlarge \ --region $REGION \ | tee custom-endpoint-demo-${i}.json done
The larger instances are reserved for specialized kinds of reporting queries. To make
it unlikely for them to be promoted to the primary instance, the following example changes
their promotion tier to the lowest priority. This example specifies the
--manage-master-user-password
option to generate the master user password and
manage it in Secrets Manager. For more information, see Password management with
Amazon Aurora
and AWS Secrets Manager. Alternatively, you can use the
--master-password
option to specify and manage the password yourself.
for i in 09 10 do aws rds modify-db-instance --db-instance-identifier custom-endpoint-demo-${i} \ --region $REGION --promotion-tier 15 done
Suppose that you want to use the two "bigger" instances only for the most
resource-intensive queries. To do this, you can first create a custom read-only endpoint.
Then you can add a static list of members so that the endpoint connects only to those DB
instances. Those instances are already in the lowest promotion tier, making it unlikely
either of them will be promoted to the primary instance. If one of them is promoted to the
primary instance, it becomes unreachable through this endpoint because we specified the
READER
type instead of the ANY
type.
The following example demonstrates the create and modify endpoint commands, and sample JSON output showing the initial and modified state of the custom endpoint.
$
aws rds create-db-cluster-endpoint --region $REGION \ --db-cluster-identifier custom-endpoint-demo \ --db-cluster-endpoint-identifier big-instances --endpoint-type reader{ "EndpointType": "CUSTOM", "Endpoint": "big-instances.cluster-custom-c7tj4example.ca-central-1.rds.amazonaws.com", "DBClusterEndpointIdentifier": "big-instances", "DBClusterIdentifier": "custom-endpoint-demo", "StaticMembers": [], "DBClusterEndpointResourceIdentifier": "cluster-endpoint-W7PE3TLLFNSHXQKFU6J6NV5FHU", "ExcludedMembers": [], "CustomEndpointType": "READER", "Status": "creating", "DBClusterEndpointArn": "arn:aws:rds:ca-central-1:111122223333:cluster-endpoint:big-instances" }
$
aws rds modify-db-cluster-endpoint --db-cluster-endpoint-identifier big-instances \ --static-members custom-endpoint-demo-09 custom-endpoint-demo-10 --region $REGION{ "EndpointType": "CUSTOM", "ExcludedMembers": [], "DBClusterEndpointIdentifier": "big-instances", "DBClusterEndpointResourceIdentifier": "cluster-endpoint-W7PE3TLLFNSHXQKFU6J6NV5FHU", "CustomEndpointType": "READER", "DBClusterEndpointArn": "arn:aws:rds:ca-central-1:111122223333:cluster-endpoint:big-instances", "StaticMembers": [ "custom-endpoint-demo-10", "custom-endpoint-demo-09" ], "Status": "modifying", "Endpoint": "big-instances.cluster-custom-c7tj4example.ca-central-1.rds.amazonaws.com", "DBClusterIdentifier": "custom-endpoint-demo" }
The default READER
endpoint for the cluster can connect to either the
"small" or "big" DB instances, making it impractical to predict
query performance and scalability when the cluster becomes busy. To divide the workload
cleanly between the sets of DB instances, you can ignore the default READER
endpoint and create a second custom endpoint that connects to all other DB instances. The
following example does so by creating a custom endpoint and then adding an exclusion list.
Any other DB instances you add to the cluster later will be added to this endpoint
automatically. The ANY
type means that this endpoint is associated with eight
instances in total: the primary instance and another seven Aurora Replicas. If the example
used the READER
type, the custom endpoint would only be associated with the
seven Aurora Replicas.
$
aws rds create-db-cluster-endpoint --region $REGION --db-cluster-identifier custom-endpoint-demo \ --db-cluster-endpoint-identifier small-instances --endpoint-type any{ "Status": "creating", "DBClusterEndpointIdentifier": "small-instances", "CustomEndpointType": "ANY", "EndpointType": "CUSTOM", "Endpoint": "small-instances.cluster-custom-c7tj4example.ca-central-1.rds.amazonaws.com", "StaticMembers": [], "ExcludedMembers": [], "DBClusterIdentifier": "custom-endpoint-demo", "DBClusterEndpointArn": "arn:aws:rds:ca-central-1:111122223333:cluster-endpoint:small-instances", "DBClusterEndpointResourceIdentifier": "cluster-endpoint-6RDDXQOC3AKKZT2PRD7ST37BMY" }
$
aws rds modify-db-cluster-endpoint --db-cluster-endpoint-identifier small-instances \ --excluded-members custom-endpoint-demo-09 custom-endpoint-demo-10 --region $REGION{ "DBClusterEndpointIdentifier": "small-instances", "DBClusterEndpointArn": "arn:aws:rds:ca-central-1:c7tj4example:cluster-endpoint:small-instances", "DBClusterEndpointResourceIdentifier": "cluster-endpoint-6RDDXQOC3AKKZT2PRD7ST37BMY", "CustomEndpointType": "ANY", "Endpoint": "small-instances.cluster-custom-c7tj4example.ca-central-1.rds.amazonaws.com", "EndpointType": "CUSTOM", "ExcludedMembers": [ "custom-endpoint-demo-09", "custom-endpoint-demo-10" ], "StaticMembers": [], "DBClusterIdentifier": "custom-endpoint-demo", "Status": "modifying" }
The following example checks the state of the endpoints for this cluster. The cluster
still has its original cluster endpoint, with EndPointType
of
WRITER
, which you would still use for administration, ETL, and other write
operations. It still has its original READER
endpoint, which you wouldn't use
because each connection to it might be directed to a "small" or
"big" DB instance. The custom endpoints make this behavior predictable, with
connections guaranteed to use one of the "small" or "big" DB
instances based on the endpoint you specify.
$
aws rds describe-db-cluster-endpoints --region $REGION{ "DBClusterEndpoints": [ { "EndpointType": "WRITER", "Endpoint": "custom-endpoint-demo.cluster-c7tj4example.ca-central-1.rds.amazonaws.com", "Status": "available", "DBClusterIdentifier": "custom-endpoint-demo" }, { "EndpointType": "READER", "Endpoint": "custom-endpoint-demo.cluster-ro-c7tj4example.ca-central-1.rds.amazonaws.com", "Status": "available", "DBClusterIdentifier": "custom-endpoint-demo" }, { "Endpoint": "small-instances.cluster-custom-c7tj4example.ca-central-1.rds.amazonaws.com", "CustomEndpointType": "ANY", "DBClusterEndpointArn": "arn:aws:rds:ca-central-1:111122223333:cluster-endpoint:small-instances", "ExcludedMembers": [ "custom-endpoint-demo-09", "custom-endpoint-demo-10" ], "DBClusterEndpointResourceIdentifier": "cluster-endpoint-6RDDXQOC3AKKZT2PRD7ST37BMY", "DBClusterIdentifier": "custom-endpoint-demo", "StaticMembers": [], "EndpointType": "CUSTOM", "DBClusterEndpointIdentifier": "small-instances", "Status": "modifying" }, { "Endpoint": "big-instances.cluster-custom-c7tj4example.ca-central-1.rds.amazonaws.com", "CustomEndpointType": "READER", "DBClusterEndpointArn": "arn:aws:rds:ca-central-1:111122223333:cluster-endpoint:big-instances", "ExcludedMembers": [], "DBClusterEndpointResourceIdentifier": "cluster-endpoint-W7PE3TLLFNSHXQKFU6J6NV5FHU", "DBClusterIdentifier": "custom-endpoint-demo", "StaticMembers": [ "custom-endpoint-demo-10", "custom-endpoint-demo-09" ], "EndpointType": "CUSTOM", "DBClusterEndpointIdentifier": "big-instances", "Status": "available" } ] }
The final examples demonstrate how successive database connections to the custom
endpoints connect to the various DB instances in the Aurora cluster. The
small-instances
endpoint always connects to the db.r4.4xlarge
DB
instances, which are the lower-numbered hosts in this cluster.
$
mysql -h small-instances.cluster-custom-c7tj4example.ca-central-1.rds.amazonaws.com -u $MYUSER -pmysql>
select @@aurora_server_id;+-------------------------+ | @@aurora_server_id | +-------------------------+ | custom-endpoint-demo-02 | +-------------------------+
$
mysql -h small-instances.cluster-custom-c7tj4example.ca-central-1.rds.amazonaws.com -u $MYUSER -pmysql>
select @@aurora_server_id;+-------------------------+ | @@aurora_server_id | +-------------------------+ | custom-endpoint-demo-07 | +-------------------------+
$
mysql -h small-instances.cluster-custom-c7tj4example.ca-central-1.rds.amazonaws.com -u $MYUSER -pmysql>
select @@aurora_server_id;+-------------------------+ | @@aurora_server_id | +-------------------------+ | custom-endpoint-demo-01 | +-------------------------+
The big-instances
endpoint always connects to the
db.r4.16xlarge
DB instances, which are the two highest-numbered hosts in this
cluster.
$
mysql -h big-instances.cluster-custom-c7tj4example.ca-central-1.rds.amazonaws.com -u $MYUSER -pmysql>
select @@aurora_server_id;+-------------------------+ | @@aurora_server_id | +-------------------------+ | custom-endpoint-demo-10 | +-------------------------+
$
mysql -h big-instances.cluster-custom-c7tj4example.ca-central-1.rds.amazonaws.com -u $MYUSER -pmysql>
select @@aurora_server_id;+-------------------------+ | @@aurora_server_id | +-------------------------+ | custom-endpoint-demo-09 | +-------------------------+