Why isn’t the port forwarding with thesql working on the server

MySQLport-forwardingssh

I have a mysql server running on some server in our network. Now, only connections from localhost are accepted by the server and I can't connect to it remotely. Now, I want to set up a port forwarding, using ssh to be able to use some GUI tools with this server. However, somehow I can not get authenticated, when I connect via tunnel! It goes like this:

On the remote machine:

mysql -u exporter --password=test -h localhost -P 3306 mydatabase

Everything works perfectly and I get a connected mysql shell.

Now, on my local machine, to set up port forwarding:

ssh remotemachine -L127.0.0.1:33066:127.0.0.1:3306

TO connect to the remote mysql server, using this tunnel, on the local machine I do:

mysql -u exporter --password=test -h localhost -P 33066 mydatabase

Here is the error I get:

ERROR 1045 (28000): Access denied for user 'exporter'@'localhost' (using password: YES)

How is that even possible and what can I do about it?
Note: the problem is totally reproducible with another mysql server on another remote machine.

Best Answer

Did you try using

ssh -L 33066:localhost:3306 remotemachine

?

Please make also sure that inside the mysql privilege system, both 127.0.0.1 and localhost are allowed sources for your database user.

Related Solutions

MySql server only working with localhost

The computers that are allowed to connect to a mysql server are defined in /etc/my.cnf

You will need to edit the file to allow connection from all computers

[mysqld]
bind-address = localhost #Change from localhost to '0.0.0.0' (all zeros) 
skip-networking   #Comment out this line if it exits
enable-named-pipe #Comment out this line if it exists

Be careful, once you have changed the bind-address from localhost to 0.0.0.0, mysql will allow connections from anywhere. You should lock down your firewall to only allow specific machines in.

You also may need to modify the database to allow remote connections. If your ip of the remote machine is 192.168.1.100

 GRANT ALL PRIVILEGES ON *.* TO db_user @'192.168.1.100' IDENTIFIED BY 'db_passwd';

SSH local port forwarding to a website

This technique works, but is not very good for accessing websites since you need to forward a port for each and every domain being accessed. If you load slashdot in chrome and use the developer tools, you can see that when you load slashdot it is actually loading content from at least 21 different domains. If you are only forwarding one domain then you won't see all the content.

Another problem is that you are tunneling to port 80 on a site that is serving content on port 443. If you run "curl -i slashdot.org" you will see that it serves you a 301 redirect to HTTPS. When the browser tries to follow the redirect to HTTPS everything will fail.

You would be better off using ssh as a SOCKS proxy and configuring your browser to use it. You can open a socks proxy on a local port like this:

ssh -D 8080 myhost.com

myhost.com could be "localhost" in your case, but usually is a remote server. Once you have this session open, just go into browser preferences and configure the browser to use a SOCKS v5 proxy pointed at localhost port 8080 (easier to do in firefox than chrome imho). Once that's done the browser will run all of its traffic through the ssh tunnel.

This can be incredibly useful for accessing websites hosted on private networks, or encrypting your data across a network you think is not secure.

Related Question