RDS Proxy command-line examples
To see how combinations of connection commands and SQL statements interact with RDS Proxy, look at the following examples.
Examples
Example Preserving connections to a MySQL database across a failover
This MySQL example demonstrates how open connections continue working during a
failover. An example is when you reboot a database or it becomes unavailable due to a
problem. This example uses a proxy named the-proxy
and an Aurora DB cluster with
DB instances instance-8898
and instance-9814
. When you run the
failover-db-cluster
command from the Linux command line, the writer instance
that the proxy is connected to changes to a different DB instance. You can see that the DB
instance associated with the proxy changes while the connection remains open.
$ mysql -h the-proxy.proxy-demo.us-east-1.rds.amazonaws.com -u
admin_user
-p Enter password: ... mysql> select @@aurora_server_id; +--------------------+ | @@aurora_server_id | +--------------------+ | instance-9814 | +--------------------+ 1 row in set (0.01 sec) mysql> [1]+ Stopped mysql -h the-proxy.proxy-demo.us-east-1.rds.amazonaws.com -uadmin_user
-p $ # Initially, instance-9814 is the writer. $ aws rds failover-db-cluster --db-cluster-identifier cluster-56-2019-11-14-1399JSON output
$ # After a short time, the console shows that the failover operation is complete. $ # Now instance-8898 is the writer. $ fg mysql -h the-proxy.proxy-demo.us.us-east-1.rds.amazonaws.com -uadmin_user
-p mysql> select @@aurora_server_id; +--------------------+ | @@aurora_server_id | +--------------------+ | instance-8898 | +--------------------+ 1 row in set (0.01 sec) mysql> [1]+ Stopped mysql -h the-proxy.proxy-demo.us-east-1.rds.amazonaws.com -uadmin_user
-p $ aws rds failover-db-cluster --db-cluster-identifier cluster-56-2019-11-14-1399JSON output
$ # After a short time, the console shows that the failover operation is complete. $ # Now instance-9814 is the writer again. $ fg mysql -h the-proxy.proxy-demo.us-east-1.rds.amazonaws.com -uadmin_user
-p mysql> select @@aurora_server_id; +--------------------+ | @@aurora_server_id | +--------------------+ | instance-9814 | +--------------------+ 1 row in set (0.01 sec) +---------------+---------------+ | Variable_name | Value | +---------------+---------------+ | hostname | ip-10-1-3-178 | +---------------+---------------+ 1 row in set (0.02 sec)
Example Adjusting the max_connections setting for an Aurora DB cluster
This example demonstrates how you can adjust the max_connections
setting for an Aurora MySQL DB cluster. To do so,
you create your own DB cluster parameter group based on the default parameter settings for clusters that are compatible with
MySQL 5.7. You specify a value for the max_connections
setting, overriding the formula that sets the default
value. You associate the DB cluster parameter group with your DB cluster.
export REGION=us-east-1 export CLUSTER_PARAM_GROUP=rds-proxy-mysql-57-max-connections-demo export CLUSTER_NAME=rds-proxy-mysql-57 aws rds create-db-parameter-group --region $REGION \ --db-parameter-group-family aurora-mysql5.7 \ --db-parameter-group-name $CLUSTER_PARAM_GROUP \ --description "Aurora MySQL 5.7 cluster parameter group for RDS Proxy demo." aws rds modify-db-cluster --region $REGION \ --db-cluster-identifier $CLUSTER_NAME \ --db-cluster-parameter-group-name $CLUSTER_PARAM_GROUP echo "New cluster param group is assigned to cluster:" aws rds describe-db-clusters --region $REGION \ --db-cluster-identifier $CLUSTER_NAME \ --query '*[*].{DBClusterParameterGroup:DBClusterParameterGroup}' echo "Current value for max_connections:" aws rds describe-db-cluster-parameters --region $REGION \ --db-cluster-parameter-group-name $CLUSTER_PARAM_GROUP \ --query '*[*].{ParameterName:ParameterName,ParameterValue:ParameterValue}' \ --output text | grep "^max_connections" echo -n "Enter number for max_connections setting: " read answer aws rds modify-db-cluster-parameter-group --region $REGION --db-cluster-parameter-group-name $CLUSTER_PARAM_GROUP \ --parameters "ParameterName=max_connections,ParameterValue=$$answer,ApplyMethod=immediate" echo "Updated value for max_connections:" aws rds describe-db-cluster-parameters --region $REGION \ --db-cluster-parameter-group-name $CLUSTER_PARAM_GROUP \ --query '*[*].{ParameterName:ParameterName,ParameterValue:ParameterValue}' \ --output text | grep "^max_connections"