For security reasons we want to connect to MySQL running on EC2 over ssh. We have other servers where we do this with no issues, but for some reason on EC2 it's not working. The instance is running Amazon Linux, MySQL is 5.5.42.
I have verified that the MySQL user has appropriate permissions. I opened 3306 in the AWS settings and I am able to connect to MySQL with the user and password with no issues. Obviously we don't want this hole in the firewall for production.
I have verified that the user (currently using ec2-user for testing will use a user with restricted permissions for production) can run MySQL from the command line on EC2 and has no problems making an SSH connection using the private key.
This is the ssh command I am using to establish the tunnel on my Mac:
ssh -nNT -L 3306:IPADDRESS:3306 -i /path-to/key.pem ec2-user@IPADDRESS
I then attempt to connect to MySQL. It just hangs. If I kill the tunnel I get the following message:
Lost connection to MySQL server at 'reading initial communication packet', system error: 0
If I let it time out I get the same basic error:
Lost connection to MySQL server at 'reading initial communication packet', system error: 35
and the ssh command reports:
channel 2: open failed: connect failed: Connection timed out
I should note that I use this basic command (minus the private key) to establish tunnels to non-EC2 serves with success all the time.
If I add vvvv to the ssh command, at the point I try to connect to MySQL to the point of timeout timeout this it what is reported:
debug1: Connection to port 3306 forwarding to IPADDRESS port 3306 requested.
debug2: fd 7 setting TCP_NODELAY
debug3: fd 7 is O_NONBLOCK
debug3: fd 7 is O_NONBLOCK
debug1: channel 2: new [direct-tcpip]
channel 2: open failed: connect failed: Connection timed out
debug2: channel 2: zombie
debug2: channel 2: garbage collecting
debug1: channel 2: free: direct-tcpip: listening port 3306 for IPADDRESS port 3306, connect from 127.0.0.1 port 49316, nchannels 3
debug3: channel 2: status: The following connections are open:
I have also tried using a client called bitvise Tunnelier on Windows and using the MySQL ODBC driver for testing and I get the same results – when I kill the tunnel I get the same error message, which leads me to the conclusion that the issue is on the server.
I have tried some suggestions that I have found regarding adding skip-networking to my.cnf and adding mysqld: ALL : ALLOW and mysqld-max: ALL : ALLOW to /etc/hosts.allow with ho changes in behavior.
I'm at a complete loss at this point. I don't know if this is a MySQL issue, an SSH issue or some other EC2 networking issue.
Best Answer
You don't want to try to connect the tunneled connection to port 3306 of the EC2 instance's public IP address, and that appears to be what you are doing.
In EC2, machines have private IP addresses bound to their IP stack, and the AWS network hardware does 1:1 NAT to attach the public IP. It won't NAT you to yourself, hence the timeout.
Use
-L 3306:127.0.0.1:3306. SSH knows you don't mean your local rollback interface, but the remote system's. Or, use the remote system's private IP address, which you can find in the AWS console, or withifconfigon the instance itself.