Hi there,
I've seen similar posts on this board, but did not find any solutions in them and thought to post a new topic instead of hijacking one.

I' trying to connect to mysql remotely from my windows machine using MySQL Administrator or HeidiSQL.

I get the same error from both:


I've googled around and found copious information related to the issue but I have not found a solution. My disability with Linux does not help...

I have checked my.cnf, and issued mysqladmin commands to verify that the port being used is 3306 and mysql ip is binded to localhost ip (127.0.0.1)
Mysql seems to be running.

I also issued other commands that I might not fully understand the result:




I also followed the Securely Administer MySQL with an SSH Tunnel guide using putty, but I might not fully understand it, since I did not make it work.

Please, directions!

----------------------------------------------------------

Configuration Profile:
My LAMP Stack Profile

Linux Distro:
Ubuntu 10.04

Check in my.cnf and remove the line that specifies it should bind to 127.0.0.1.

I have commented it out, and it works!!

Like I wrote before, I was using HeidiSQL and MySQL Administrator.

Using HeidiSQL did not help me out. After creating my ssr keys and uploading to the server, it gave me a different error while trying to connect through a SSH tunnel.


The thing is that i had previously added a tunnel with putty, which did not work. I guess that commenting that ip out made it ok, but i was trying to connect with a HeidiSQL tunnel (using my servers ip) and not with a normal tcp ip connection to 127.0.0.1

I would add here tow key tutorials that complemented linode´s Securely Administer MySQL with an SSH Tunnel greatly:
Key-Based SSH Logins With PuTTY
Using putty with OpenSSH

Thanks BarkerJr, succinct answer but right on...

NOOOOOOOOOOOOO!

You DO NOT WANT to have your MySQL server listen on the internet-accesible interface, EXCEPT when you're doing replication or other server-server stuff, and then only after you set up iptables to block 3306 from everyone except that other server.

Keep it bound to 127.0.0.1 only.

For HeidiSQL and co. you SSH into your VPS, and use SSH port forwarding.

In case of Putty, you'd go to Connection->SSH->Tunnels,
put Source port: XXXX, Destination: 127.0.0.1:3306, then put 127.0.0.1:XXXX in HeidiSQL as the IP to connect to.

Re: second part of the post - sounds to me like you were trying to put your server's IP instead of 127.0.0.1 as the destination. You want to access the server's own loop-back interface.

_________________
rsk, providing useless advice on the Internet since 2005.

So long as you have passwords on your mysql users, it shouldn't be any less secure than allowing sshd to listen at the public interface, right?

If you're using passwords in sshd, it's a little less secure (mysql uses 40 bit encryption IIRC). If you're using keys in sshd, it's definitely less secure.

That also assumes that MySQL's access control methods are as secure as OpenSSH's, or at least get as much active scrutiny. Since most reasonable default configurations have MySQL bound to localhost (unlike OpenSSH, which is open to the world and actively attacked by default), this is a dangerous assumption.

_________________

/* TODO: need to add signature to posts */

So, by commenting the localhost ip from the config file, it is listening to all?
I will try to use localhost in the config file and check if i can still connect.


Yes, at one point that is exactly what I was doing...

I wish there was a way to flag posts like this for future reference as a certain reply here is going to save me a lot of headaches...

Would the "Watch this topic for replies" function (link at bottom of a topic page) serve?

(Either that, or since you added a post, checking the "Notify me when a reply is posted" option when you make your post should serve similarly).

-- David

hot damn, I hadn't noticed either of those. I figured someone would point SOMETHING like that out. Thanks!

I did bind to 127.0.0.1 in my.conf and everything still works fine.

So, that leaves me with the impression that I was having issues with putty and tunneling rather than with mysql itself.