Mysql through ssh often can’t connect or timeout

mariadbMySQLssh-tunnel

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

Best Answer

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!

Related Topic