MySQL connexion over SSH

Question

I'm getting an error trying to connect to a distant MySQL server from my local MySQL client. (server is running Debian and client runs OSX)

I can manage to ssh to the server ...

local$ ssh john@serverip

(note: i'm using ssh public key so i don't enter any password) ... and from there connecting to MySQL ...

distant$ mysql -u sqluser -p myDatabase

(ok i'm connected, i can do any MySQL command i want)

Below is how my client config looks like :

Below are the debugging trace of my client when trying to connect over ssh:

Used command:  /usr/bin/ssh -v -N -o ControlMaster=no -o ExitOnForwardFailure=yes -o ConnectTimeout=10 -o NumberOfPasswordPrompts=3 -o TCPKeepAlive=no -o ServerAliveInterval=60 -o ServerAliveCountMax=1 john@serverip -L 52004/127.0.0.1/3306

OpenSSH_6.2p2, OSSLShim 0.9.8r 8 Dec 2011
debug1: Reading configuration data /etc/ssh_config
debug1: /etc/ssh_config line 20: Applying options for *
debug1: /etc/ssh_config line 102: Applying options for *
debug1: Connecting to serverip [serverip] port 22.
debug1: fd 3 clearing O_NONBLOCK
debug1: Connection established.
debug1: identity file /Users/john/.ssh/id_rsa type 1
debug1: identity file /Users/john/.ssh/id_rsa-cert type -1
debug1: identity file /Users/john/.ssh/id_dsa type -1
debug1: identity file /Users/john/.ssh/id_dsa-cert type -1
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_6.2
debug1: Remote protocol version 2.0, remote software version OpenSSH_6.0p1 Debian-4+deb7u2
debug1: match: OpenSSH_6.0p1 Debian-4+deb7u2 pat OpenSSH*
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-ctr hmac-md5 none
debug1: kex: client->server aes128-ctr hmac-md5 none
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
debug1: Server host key: RSA 4b:56:4e:f2:71:50:ed:fd:25:aa:bf:0d:1a:a0:89:40
debug1: Host 'serverip' is known and matches the RSA host key.
debug1: Found key in /Users/john/.ssh/known_hosts:1
debug1: ssh_rsa_verify: signature correct
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug1: Roaming not allowed by server
debug1: SSH2_MSG_SERVICE_REQUEST sent
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: publickey
debug1: Next authentication method: publickey
debug1: Offering RSA public key: /Users/john/.ssh/id_rsa
debug1: Server accepts key: pkalg ssh-rsa blen 279
debug1: read PEM private key done: type RSA
debug1: Authentication succeeded (publickey).
Authenticated to serverip ([serverip]:22).
debug1: Local connections to LOCALHOST:52004 forwarded to remote address 127.0.0.1:3306
debug1: Local forwarding listening on ::1 port 52004.
debug1: channel 0: new [port listener]
debug1: Local forwarding listening on 127.0.0.1 port 52004.
debug1: channel 1: new [port listener]
debug1: Requesting no-more-sessions@openssh.com
debug1: Entering interactive session.
debug1: Connection to port 52004 forwarding to 127.0.0.1 port 3306 requested.
debug1: channel 2: new [direct-tcpip]
channel 2: open failed: connect failed: Connection refused
debug1: channel 2: free: direct-tcpip: listening port 52004 for 127.0.0.1 port 3306, connect from 127.0.0.1 port 52006, nchannels 3

Any idea what's going wrong ?

(note: i replaced my real server ip with serverip, my real sql username with sqluser and my real ssh username with john)

Edit: Typing this on the server:

netstat -an | grep 3306

Gives :

tcp        0      0 serverip:3306       0.0.0.0:*               LISTEN     

Edit : Solution to my problem was to change "127.0.0.1" to serverip in my client config options. I thought in the first place that serverip would only be used for the SSH server, and then MySQL Server would be 127.0.0.1 (localhost), but i was wrong. Many thanks to @Honore Doktorr and @Kenster for pointing me the right solution

Answer 1

This could be one of a few problems:

1. MySQL is listening on a UNIX socket (via localhost) but not to TCP socket. A typical reason for this is the use of the skip-networking configuration option in my.cnf. You can see if this option is enabled for you by running the SQL command SHOW VARIABLES LIKE 'skip_networking';

2. You changed the port MySQL is listening on in my.cnf. To see which port it's listening on (if at all) run sudo netstat -lnp | grep mysql and see which port is listed. The default port is 3306.

3. Another reason this can happen, though not likely in your case, is that a firewall is blocking access to the port. I don't think it's likely for you since you're trying to connect to 127.0.0.1 which is usually not blocked by firewalls. If the netstat above showed 3306 as the listening port, then this may be your problem.

Answer 2

debug1: Connection to port 52004 forwarding to 127.0.0.1 port 3306 requested.
debug1: channel 2: new [direct-tcpip]
channel 2: open failed: connect failed: Connection refused

The remote ssh server tried to connect to 127.0.0.1 port 3306 in order to service the forward request, but it got a "Connection refused" error. "Connection refused" normally means that there is nothing listening for connections at the requested address and port.

The simplest explanation is that the mysql server that is supposed to be running on the remote server is not actually running, or it's listening on a different address and/or port than what you're expecting.

You should check the remote server to make sure that mysql is actually running. If it is, then find the address and port that it's actually listening on and correct your port-forwarding settings to forward to the correct address and port.

Edit:

tcp        0      0 serverip:3306       0.0.0.0:*               LISTEN     

This indicates that the mysql process is specifically listening on the serverip interface, and not the localhost interface. It will only receive connection attempts directed at the serverip address, not 127.0.0.1.

Changing the value of the Hôte MySql field to the serverip address (ie, whatever appeared in the netstate output) would probably resolve the problem. Alternately, the mysql server could be reconfigured to listen on the 127.0.0.1 address, or to listen on 0.0.0.0 (the "wildcard" address which accepts connections on any interface).