AWS MySQL connection frequently times out

Question

I have a MySQL database in AWS (RDS), and I connect to it from command line via this command:

mysql -u _usernme_ -p_mypassword_ -h  _aws_mysql_host_ _dbname_

It connects fine, problem is, if it remains idle for 1-2 minutes, the connection dies, subsequent commands just hang. I have to kill the process and start a new one.

What configuration changes do I need to do, and where, so that it stays alive forever, just like localhost, until I expressly terminate the connection?

Answer 1

This error happens when try to connect to RDS from the local computer and security group only allow to connect from within VPC.

To solve this issue go to your RDS security group and allow the particular IP or anyone can connect with a password and user_name in your Security Rule.

Below can connect anyone

Although it's not good practice just try to connect with RDS and track the issue

go to RDS instance

select the RDS instance and check in the description for the security group

select security group

select inbound rule

edit the security rule

allow your IP to access it or make it publicly accessible

Or if you think this error occurred in RDS you can simply check the logs of RDS

if the above setting seem fine then AWS says that

Connecting from Outside of Amazon EC2 —Firewall Timeout Issue

Example issue:

Your client connection to the database appears to hang or timeout when running long queries, such as a COPY command. In this case, you might observe that the Amazon Redshift console displays that the query has completed, but the client tool itself still appears to be running the query. The results of the query might be missing or incomplete depending on when the connection stopped.

This happens when you connect to Amazon Redshift from a computer other than an Amazon EC2 instance, and idle connections are terminated by an intermediate network component, such as a firewall, after a period of inactivity. This behavior is typical when you log in from a Virtual Private Network (VPN) or your local network.

To avoid these timeouts, we recommend the following changes:

• Increase client system values that deal with TCP/IP timeouts. You should make these changes on the computer you are using to connect to your cluster. The timeout period should be adjusted for your client and network. See To change TCP/IP timeout settings.
• Optionally, set keep-alive behavior at the DSN level. See To change DSN timeout settings.

To make these changing check the link

AWS:connecting-firewall-guidance.html

Answer 2

You can find out relevant timeout values as follows:

SHOW VARIABLES LIKE '%_timeout';

You might want to check to make sure wait_timeout and interactive_timeout are adequately set. Both are default to 28800 (i.e. 8 hours):

SET GLOBAL wait_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;