Mysql through ssh often can't connect or timeout
I have a MariaDB server (10.0.12) on a machine (Ubuntu 14.04) that other servers connect to through ssh for security purpose.
The ssh tunnel is openned via autossh like so:
autossh -M 3306 -f -Ng -L 3306:127.0.0.1:3306 mariaDB@server-ip
Unfortunately I often end up with 'can't connect' or 'Lost connection' errors:
SQLSTATE[HY000] [2003] Can't connect to MySQL server on '127.0.0.1' (111)
or
SQLSTATE[HY000] [2013] Lost connection to MySQL server at 'reading authorization packet', system error: 0.
Here is some additionnal info as it might help:
MariaDB [(none)]> show global variables like '%timeout';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 30 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| thread_pool_idle_timeout | 60 |
| wait_timeout | 3600 |
+-----------------------------+----------+
12 rows in set (0.00 sec)
Netstat output while doing telnet 172.0.0.1 3306:
netstat -naptu|grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 5249/ssh
tcp 0 0 127.0.0.1:46701 127.0.0.1:3306 TIME_WAIT -
tcp 0 0 127.0.0.1:46712 127.0.0.1:3306 TIME_WAIT -
tcp 0 0 127.0.0.1:46702 127.0.0.1:3306 TIME_WAIT -
tcp 0 0 127.0.0.1:46647 127.0.0.1:3306 TIME_WAIT -
tcp 0 0 127.0.0.1:46704 127.0.0.1:3306 TIME_WAIT -
tcp 0 0 127.0.0.1:46705 127.0.0.1:3306 TIME_WAIT -
tcp 0 0 127.0.0.1:46703 127.0.0.1:3306 TIME_WAIT -
tcp 0 0 127.0.0.1:46709 127.0.0.1:3306 TIME_WAIT -
tcp 0 1 37.187.91.194:52674 172.0.0.1:3306 SYN_SENT 5109/telnet
tcp 0 0 127.0.0.1:46706 127.0.0.1:3306 TIME_WAIT -
tcp6 0 0 :::3306 :::* LISTEN 5249/ssh
Telnet ends up timing out:
telnet 172.0.0.1 3306
Trying 172.0.0.1...
telnet: Unable to connect to remote host: Connection timed out
I'm a bit late to the party, but I've been struggling with this issue for most of the day: an SSH tunnel for MySQL just wouldn't get a response and netstat on the remote host showed SYN_SENT (from 127.0.0.1 on an ephemeral port to 127.0.0.1:3306).
For me, the problem was that iptables was running but wasn't configured to allow loopback connections (sidenote: if anyone knows why that's a sensible default, I'd love to find out!) and, as such, the connection (the SYN packet) from the SSH daemon to MySQL was being dropped by iptables.
The fix is as simple as adding a -i lo -j ACCEPT rule in your INPUT chain before any global DROP rules. For me this was iptables -I INPUT 4 -i lo -j ACCEPT but this will vary depending on your existing rules.
Credit for helping me with this one goes to this answer: https://serverfault.com/a/319267
Edit: Don't forget to save your iptables changes afterwards!