Can't connect to mysql over ssh

1
votes

I am trying to connect to a MariaDB database (v 5.5.52) that is running on port 3306 on a CentOS (v 7.3.1611) box For security reasons the server's port 3306 is firewalled (telnet mysite.com 3306 times out)

Because I can ssh into the server with a configured connection: ssh projectx

and then connect to the database: mysql -u remote -p use staff;

I am expecting this connection in Sequel Pro to get me connected from my laptop:

enter image description here

But I am getting this error message:

Unable to connect to host 127.0.0.1, or the request timed out. MySQL said: Lost connection to MySQL server at 'reading initial communication packet', system error: 0

The user has these grants: show grants for 'remote'@'%'; +----------------------------------------------------------------------------------+ | Grants for remote@% | +----------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'remote'@'%' IDENTIFIED BY PASSWORD 'redacted' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `staff`.* TO 'remote'@'%' | +----------------------------------------------------------------------------------+

And I have this in the [mysqld] section of my.cnf bind-address = *

No errors logged in /var/log/mariadb/mariadb.log

1
mysqlssh
Are you sure that your software actually uses ~/.ssh/config? // Nevermind - it seems it does: sequelpro.com/docs/get-started/get-connected/remote – Xatenev
@Xatenev good thought, but yes, I have connected to other servers like this without problem. – Jannie Theunissen
You wrote 'you can ssh into the server with a configured configuration' => Does that mean you are trying to connect to a 3rd party server? In that case, MysQL Host 127.0.0.1 would be wrong and should be replaced by the server IP (I think so atleast? - not 100% sure on that1). – Xatenev
@Xatenev that is true if you use the "Standard" connection tab. Because the remote server only has ports 80, 443 and 22 open, the ssh configuration gets me connected to the server and then I connect to mysql form the "inside", thus the loopback address. – Jannie Theunissen

1 Answers

3
votes

You don't need to define a remote user @'%' if you are using an SSH connection. You also don't need to bind any interface other than 127.0.0.1, since you are essentially connecting from the local machine.

Anyway, bind-address should 127.0.0. in your case (for connecting from localhost). To get MySQL to bind to all interfaces you need 0.0.0.0 (but again, this is not needed if you ssh).

Also make sure that MySQL is configured to use TCP and not a local unix socket. Try ssh-ing into the machine and running mysql -h 127.0.0.1 .... If this doesn't work and mysql -h localhost works, the server is setup to use a unix socket.

The remote sshd daemon must be setup so that it allows port forwarding. Add the following to /etc/ssh/sshd_config:

AllowTcpForwarding yes