1

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
Buzut
  • 765
  • 3
  • 9
  • 23
  • Is your SSH session timing out or is this failure occurring when you try to connect? – Andrew Jun 19 '14 at 18:57
  • What are the various timeouts for SSH connections and MySQL connections. I have seen instances where the mysql client timesout while waiting on the ssh tunnel to connect. In many cases this was due to reverse DNS lookups on the server side of the connection. You can disable these reverse DNS lookups in sshd's config files. – jeffatrackaid Jun 19 '14 at 19:31
  • 1
    Try a telnet to 172.0.0.1 on port 3306. telnet 172.0.0.1 3306 Also see if it is listening netstat -naptu|grep 3306 – ThatGuy Jun 19 '14 at 19:29
  • It seems to listen. I edited my question with the netstat output – Buzut Jun 19 '14 at 19:36
  • @Andrew I had nothing set up in my sshd_config except `TCPKeepAlive yes` I'm going to add `ClientAliveInterval 60` and `ClientAliveCountMax 99999` – Buzut Jun 19 '14 at 20:46
  • @jeffatrackaid timeouts for MySQL are shown in my question. As for ssh, it's the defaults. Is there a way to display them? Because there's nothing but `TCPKeepAlive yes` in sshd_config… Nothing about DNS in sshd's config files neither? Are they activated by default eventhough nothing is mentionned in the config file? – Buzut Jun 19 '14 at 20:50
  • Setting `ClientAliveInterval 60` and `ClientAliveCountMax 99999` didn't solve the problem. – Buzut Jun 20 '14 at 08:48

1 Answers1

1

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!

Community
  • 1
Aziraphale
  • 11
  • 1