CODEWITHSUNDEEP
mysqlssh
Jannie Theunissen
Jannie Theunissen

Reputation: 30093

Can't connect to mysql over ssh

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

Upvotes: 1

Views: 4128

Answers (1)

nimrodm
nimrodm

Reputation: 23829

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

Upvotes: 4

Related Questions