RDS Proxy command-line examples - Amazon Relational Database Service

RDS Proxy command-line examples

To see how combinations of connection commands and SQL statements interact with RDS Proxy, look at the following examples.

Example Preserving connections to a MySQL database across a failover

This MySQL example demonstrates how open connections continue working during a failover, for example 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 -u admin_user -p $ # Initially, instance-9814 is the writer. $ aws rds failover-db-cluster --db-cluster-identifier cluster-56-2019-11-14-1399 JSON 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 -u admin_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 -u admin_user -p $ aws rds failover-db-cluster --db-cluster-identifier cluster-56-2019-11-14-1399 JSON 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 -u admin_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.6 or 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-56-max-connections-demo export CLUSTER_NAME=rds-proxy-mysql-56 aws rds create-db-parameter-group --region $REGION \ --db-parameter-group-family aurora5.6 \ --db-parameter-group-name $CLUSTER_PARAM_GROUP \ --description "Aurora MySQL 5.6 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"