Get Hands-on with Amazon EKS - Workshop Event Series

Whether you're taking your first steps with Kubernetes or you're an experienced practitioner looking to sharpen your skills, our Amazon EKS workshop series delivers practical, real-world experience that moves you forward. Learn directly from AWS solutions architects and EKS specialists through hands-on sessions designed to build your confidence with Kubernetes. Register now and start building with Amazon EKS!

How do I troubleshoot issues when I connect to my Amazon RDS DB instance?

9 minute read

I want to troubleshoot why I can't connect to my Amazon Relational Database Service (Amazon RDS) DB instance.

Short description

The following are reasons the connection to your Amazon RDS DB instance might fail:

Your source to connect to the DB instance doesn't have authorization access in your security group, network access control lists (ACLs), or local firewalls.
Network path from the client application to the DB instance is missing in the route table.
You use the wrong DNS name or endpoint to connect to the DB instance.
Your DB instance is not publicly accessible and you can't connect over the internet.
The Multi-AZ DB instance failed over, and the secondary DB instance uses a subnet or route table that doesn't allow inbound connections.
The user authentication isn't correct.

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

To troubleshoot your Amazon RDS DB instance connection issues, complete the following tasks:

Use automated diagnosis

Start with automated tools to quickly identify connection issues.

Complete the following steps:

To run the automated diagnosis tool, use the following start-automation-execution AWS Systems Manager command:

aws ssm start-automation-execution \
    --document-name "AWSSupport-TroubleshootConnectivityToRDS" \
    --parameters "InstanceId=EC2-INSTANCE-ID,DBInstanceIdentifier=DB-INSTANCE-NAME"

Note: Replace EC2-INSTANCE-ID with your EC2 instance ID and DB-INSTANCE-NAME with your RDS instance identifier.

To check the automation results, run the following get-automation-execution command:

aws ssm get-automation-execution \
    --automation-execution-id EXECUTION-ID

Note: Replace EXECUTION-ID with the execution ID from step 1.

For instructions on how to run the runbook and details about what it checks, see AWSSupport-TroubleshootConnectivityToRDS.

Verify DB instance status

Confirm your DB instance is available and ready for connections.

Complete the following steps:

To check your DB instance status, run the following describe-db-instances command:

aws rds describe-db-instances \
    --db-instance-identifier DB-INSTANCE-NAME \
    --query 'DBInstances[*].DBInstanceStatus'

Note: Replace DB-INSTANCE-NAME with your Amazon RDS instance identifier.

Wait for the status to show available before you attempt connections. New or rebooted instances can take up to 20 minutes to become available.

Check security group rules

Security groups control network access to your Amazon RDS instance. Verify they allow traffic from your source.

Complete the following steps:

To identify the security groups attached to your Amazon RDS instance, run the following describe-db-instances command:|

aws rds describe-db-instances \
    --db-instance-identifier DB-INSTANCE-NAME \
    --query 'DBInstances[*].VpcSecurityGroups[*].VpcSecurityGroupId'

Note: Replace DB-INSTANCE-NAME with your RDS instance identifier.

To check inbound rules for each security group, run the following describe-security-groups command:

aws ec2 describe-security-groups \
    --group-ids SECURITY-GROUP-ID \
    --query 'SecurityGroups[*].IpPermissions'

Note: Replace SECURITY-GROUP-ID with each security group ID from step 1.

To add a rule allowing your IP address, run the following authorize-security-group-ingress command:

aws ec2 authorize-security-group-ingress \
    --group-id SECURITY-GROUP-ID \
    --protocol tcp \
    --port DATABASE-PORT \
    --cidr YOUR-IP-ADDRESS/32

Note: Replace SECURITY-GROUP-ID with your security group ID. Replace DATABASE-PORT with your database port (3306 for MySQL, 5432 for PostgreSQL). Replace YOUR-IP-ADDRESS with your public IP address.

Verify network ACL rules

Network access control lists (ACLs) provide subnet-level security. Check that they allow database traffic.

Complete the following steps:

To identify the network access control list (ACL) for your Amazon RDS subnet, run the following describe-network-acls command:

aws ec2 describe-network-acls \
    --filters "Name=association.subnet-id,Values=SUBNET-ID" \
    --query 'NetworkAcls[*].NetworkAclId'

Note: Replace SUBNET-ID with your RDS subnet ID:

To check the network access control list (ACL) rules, run the following describe-network-acls command:

aws ec2 describe-network-acls \
    --network-acl-ids NETWORK-ACL-ID \
    --query 'NetworkAcls[*].Entries'

Note: Replace NETWORK-ACL-ID with the network ACL ID from step 1.

Verify that inbound rules allow traffic on your database port and outbound rules allow ephemeral ports (1024-65535).

Check route table configuration

Route tables direct network traffic. Verify they provide proper routing to your Amazon RDS instance.

Complete the following steps:

To check the route table for your Amazon RDS subnet, run the following describe-route-tables command:
CMD
Note: Replace SUBNET-ID with your RDS subnet ID.
For public access, verify there's a route to an internet gateway ([IP_ADDRESS]).
For private access, verify routes to NAT gateways, VPC peering connections, or VPN gateways as appropriate.

For more information, see How do I troubleshoot issues with the IP addresses for my Amazon RDS DB instances?

Check DNS resolution failures

Verify that your Amazon RDS endpoint resolves to the correct IP address.

Complete the following steps:

To test DNS resolution from your client, run the following command:
```
nslookup RDS-ENDPOINT
```
Note: Replace RDS-ENDPOINT with your RDS instance endpoint.
To test with dig command, run the following command:
```
dig RDS-ENDPOINT
```
If DNS resolution fails, check your Amazon VPC DNS settings or use a different DNS server.

Test network connectivity

Verify network connectivity to your RDS instance before testing database authentication.

Complete the following steps:

To test connectivity to your Amazon RDS instance, run the following telnet command:
```
telnet RDS-ENDPOINT DATABASE-PORT
```
Note: Replace RDS-ENDPOINT with your RDS endpoint and DATABASE-PORT with your database port.

To test with netcat, run the following command:

nc -zv RDS-ENDPOINT DATABASE-PORT

If the connection succeeds, then the issue is likely authentication-related. If it fails, the issue is network-related.

For more information, see Troubleshoot network connectivity to Amazon RDS databases using VPC Reachability Analyzer.

Check database authentication

Verify your database credentials and authentication method.

Complete the following steps:

To test password authentication for MySQL, run the following command:
```
mysql -h RDS-ENDPOINT -P DATABASE-PORT -u USERNAME -p
```
Note: Replace RDS-ENDPOINT with your endpoint, DATABASE-PORT with your port, and USERNAME with your database username.

To test password authentication for PostgreSQL, run the following command:

psql -h RDS-ENDPOINT -p DATABASE-PORT -U USERNAME -d DATABASE-NAME

Note: Replace RDS-ENDPOINT, DATABASE-PORT, USERNAME, and DATABASE-NAME with your values.

To reset the master password if needed, run the following modify-db-instance command:

aws rds modify-db-instance \
    --db-instance-identifier DB-INSTANCE-NAME \
    --master-user-password NEW-PASSWORD \
    --apply-immediately

Note: Replace DB-INSTANCE-NAME with your instance identifier and NEW-PASSWORD with your new password.

Check connection limits

Verify that your Amazon RDS instance hasn't reached connection limits.

Complete the following steps:

To check current connections for MySQL, connect to your database and run the following query:

SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

To check current connections for PostgreSQL, run the following query:

SELECT count(*) FROM pg_stat_activity;
SHOW max_connections;

To check for connection errors in MySQL, run the following query:

SHOW STATUS LIKE 'Connection_errors%';
SHOW STATUS LIKE 'Max_used_connections';

If your Amazon RDS instance reaches connection limits, then complete the following steps:

To identify idle connections that can be terminated, run the following query for MySQL:

SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command = 'Sleep' AND time > 300
ORDER BY time DESC;

To terminate idle connections in MySQL, run the following command:
```
CALL mysql.rds_kill(CONNECTION-ID);
```
Note: Replace CONNECTION-ID with the connection ID from step 1.

To increase the max_connections parameter, run the following modify-db-parameter-group command:

aws rds modify-db-parameter-group \
    --db-parameter-group-name PARAMETER-GROUP-NAME \
    --parameters "ParameterName=max_connections,ParameterValue=NEW-VALUE,ApplyMethod=pending-reboot"

Note: Replace PARAMETER-GROUP-NAME with your parameter group name and NEW-VALUE with your desired connection limit. A reboot is required after you change max_connections.

To apply the parameter changes, reboot your Amazon RDS instance. Run the following reboot-db-instance command:

aws rds reboot-db-instance \
    --db-instance-identifier DB-INSTANCE-NAME

Note: Replace DB-INSTANCE-NAME with your Amazon RDS instance identifier.

To implement connection pooling with RDS Proxy, run the following create-db-proxy command:

aws rds create-db-proxy \
    --db-proxy-name PROXY-NAME \
    --engine-family mysql \
    --target-group-config DBInstanceIdentifiers=DB-INSTANCE-NAME \
    --vpc-subnet-ids SUBNET-ID-1,SUBNET-ID-2 \
    --auth AuthScheme=SECRETS,SecretArn=SECRET-ARN

Note: Replace PROXY-NAME, DB-INSTANCE-NAME, SUBNET-ID-1, SUBNET-ID-2, and SECRET-ARN with your values.

Check user-specific connection limits

Complete the following steps:

To check the current max_user_connections, run the following query:

SHOW VARIABLES LIKE 'max_user_connections';

To check current connections per user, run the following query:

SELECT user, COUNT(*) as connection_count
FROM information_schema.processlist
GROUP BY user
ORDER BY connection_count DESC;

To check if a specific user has reached their connection limit, run the following query:

SELECT user, max_user_connections
FROM mysql.user
WHERE user = 'USERNAME';

Note: Replace USERNAME with the specific username experiencing connection issues.

The max_user_connections parameter can prevent specific users from connecting even when overall connection limits aren't reached.

If your Amazon RDS instance reaches maximum user connections, then complete the following steps:

To increase the max_user_connections parameter globally, run the following modify-db-parameter-group command:

aws rds modify-db-parameter-group \
    --db-parameter-group-name PARAMETER-GROUP-NAME \
    --parameters "ParameterName=max_user_connections,ParameterValue=NEW-VALUE,ApplyMethod=immediate"

Note: Replace PARAMETER-GROUP-NAME with your parameter group name and NEW-VALUE with your desired per-user connection limit.

To modify connection limits for a specific user account, run the following query:

ALTER USER 'USERNAME'@'%' WITH MAX_USER_CONNECTIONS NEW-LIMIT;

Note: Replace USERNAME with the username and NEW-LIMIT with the desired connection limit for that user.

For more information, see Setting Account Resource Limits on the MySQL website.

Check connection error limits

Complete the following steps:

To check the current max_connect_errors for Amazon RDS for MySQL, run the following query:

SHOW VARIABLES LIKE 'max_connect_errors';

To check if any hosts are currently blocked, run the following query:

SELECT * FROM performance_schema.host_cache
WHERE SUM_CONNECT_ERRORS > 0;

To check the current connection error count for your host, run the following query:

SELECT HOST, SUM_CONNECT_ERRORS, COUNT_HANDSHAKE_ERRORS, COUNT_AUTHENTICATION_ERRORS
FROM performance_schema.host_cache
WHERE HOST = 'YOUR-CLIENT-IP';

Note: Replace YOUR-CLIENT-IP with your actual client IP address.

To flush the host cache and unblock all hosts, run the following query:
```
FLUSH HOSTS;
```

To increase the max_connect_errors parameter to prevent future blocking, run the following modify-db-parameter-group command:

aws rds modify-db-parameter-group \
    --db-parameter-group-name PARAMETER-GROUP-NAME \
    --parameters "ParameterName=max_connect_errors,ParameterValue=NEW-VALUE,ApplyMethod=immediate"

Note: Replace PARAMETER-GROUP-NAME with your parameter group name and NEW-VALUE with a higher limit, such as 1000000.