Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I don't often work with linux or need to SSH tunnel into remote MySQL databases, so pardon my ignorance. I'm using Windows 7 and am needing to connect to a remote MySQL instance on a Linux server.

For months I had been using the HeidiSQL client application successfully. Today two things happened: the DB moved to a new server and I updated HeidiSQL, now I cannot log in to the MySQL server, when attempting I get this message from Heidi:

SQL Error (2003) in statement #0: Can't connect to MySQL server on 'localhost' (10061)

If I use Putty, I can connect to the server and get MySQL access through command line, including fetching data from the DB. I assume this means my credentials and address are correct, but do not understand why putting those same details into HeidiSQL's SSH tunnel info won't work. I also downloaded the MySQL Workbench and attempted to set up a connection through that client and got this message:

Cannot Connect to Database Server

Your connection attempt failed for user 'myusername' from your host to server at localhost:3306:
Lost connection to MySQL server at 'reading initial communication packet', system error: 0

Please:
1 Check that mysql is running on server localhost
2 Check that mysql is running on port 3306 (note: 3306 is the default, but this can be changed)
3 Check the myusername has rights to connect to localhost from your address (mysql rights define what clients can connect to the server and from which machines) 
4 Make sure you are both providing a password if needed and using the correct password for localhost connecting from the host address you're connecting from

From Googling around I see that it could be related to the MySQL bind-address, but I am a third party sub-contractor with no access to the MySQL settings of this box and the system admin is assuring me that I'm an idiot and need to figure it out on my end. This is completely possible but I don't know what else to try.

Edit 1 - The client settings I am using

In Heidi and MySQL Workbench I am using the following:

SSH host + port: theHostnameOfTheRemoteServer.com:22 {this is the same host I can Putty to}

SSH Username: mySSHusername {the same user name I use for my Putty connection}

SSH Password: mySSHpassword {the same password for the Putty connection}

Local port: 3307 {this is on the SSH settings tab and was defaulted to 3307 by Heidi, changing it to 3306 gives me a different error: SQL Error (1045) in statement #0: Access denied for user 'mySQLusername'@'localhost' (using password: YES)"}

MySQL host: theHostnameOfTheRemoteServer.com {consensus seems to be I should use 'localhost' here}

MySQL User: mySQLusername {which I can connect with once in with Putty}

MySQL Password: mySQLpassword {which works once in with Putty}

Port: 3306

share|improve this question

closed as off-topic by Andrew B, Ward, Falcon Momot, Rex, Magellan Nov 13 '13 at 8:55

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions must be relevant to professional system administration. Server Fault is dedicated to professional system and network administrators. End user and enthusiast questions are off-topic (contact your system administrator or hire a professional to help you out). Please see the Help Center for more information." – Andrew B, Ward, Rex, Magellan
If this question can be reworded to fit the rules in the help center, please edit the question.

6 Answers 6

your heidi mysql client is attempting to connect to "localhost", not the remote mysq.

replace the server addr in heidi config to the IP address or hostname you put in your putty client when you shell in.

the system admin is assuring me that I'm an idiot and need to figure it out on my end

1o1

share|improve this answer
    
Thanks for replying. Nowhere in my Heidi settings am I putting 'localhost', I am using the remote URL. –  kscott Nov 8 '13 at 21:34
    
localhost is the default. you will need to override –  nandoP Nov 8 '13 at 21:37
    
I have values in every field (added to my question) of the connection settings on the Heidi and MySQL Workbench clients, leaving nothing as default. Is this something I need to override in a config file usually? –  kscott Nov 8 '13 at 21:46
    
didnt realize you were ssh tunnel'ing..... localhost is correct in this case....so, as stated, make it: MySQL host: localhost..... also, i would look at this setting ===> Local port: 3307 –  nandoP Nov 8 '13 at 21:54
    
If I change the Local port from 3307 to 3306 I instead get this message: "SQL Error (1045) in statement #0: Access denied for user 'mySQLusername'@'localhost' (using password: YES)" –  kscott Nov 8 '13 at 21:59

Are you sure you've configured your client correctly to point to the new server? Based on these errors:

Can't connect to MySQL server on 'localhost'

...

from your host to server at localhost:3306

it appears you're trying to connect to mysql on your local machine (aka localhost) and not the new server. If you have the mysql CLI client installed locally, you can test my theory with this:

mysql -h newservername -u myusername

If that works, you'll be prompted for your password. Then go back to your GUI client(s) and check your config settings.

Else, make sure you can access the host and that mysql is in fact listening on port 3306 on that host:

ping -c 4 newservername

telnet newservername 3306

If ping fails, check w/ your friendly sysadmin to confirm 1) the host is up, and 2) ICMP packets aren't blocked. ping could fail due to firewall rules.

If the host is there, but you can't telnet to 3306, then either that port is restricted or the service isn't running. Again, verify with a friendly colleauge. HTH!

share|improve this answer
    
Thanks for your response! Ping succeeds, telent results in a response, but I don't understand it and therefore am not comfortable posting it here. Once I press enter after the string of letters, numbers, and symbols, I get the response "Connection to host lost." So I assume that means I'm making some kind of connection to port 3306 –  kscott Nov 8 '13 at 22:15

The MySQL host when you SSH in is localhost. So this:

MySQL host: theHostnameOfTheRemoteServer.com

Should be this:

MySQL host: localhost

Remember, when you SSH tunnel into anything, the remote host is only a factor for the initial SSH connection. Past that you are essentially “on” the server, so you are now in the realm where you can indeed connect to “localhost.”

EDIT: Also, I am not understanding what the value of setting the local port to 3307 is:

Local port: 3307

That should be 3306 unless explicitly changed in some setting somewhere.

Local port: 3306

If you can get a connection but a credential error when using 3306 instead of 3307 you are closer to logging in than before! Also, there have been cases where I could not connect to MySQL via localhost for odd reasons, so I would connect with the IP address of 127.0.0.1

MySQL host: 127.0.0.1

Try that and see what happens.

share|improve this answer
    
Thanks for you response, leaving all other settings as in my question and changing the MySQL host to localhost I get the same error message: "SQL Error (2003) in statement #0: Can't connect to MySQL server on 'localhost' (10061)" –  kscott Nov 8 '13 at 21:52
    
How about Local port: 3307. Why is that there? That seems odd. Try it with the standard 3306 port instead. –  JakeGould Nov 8 '13 at 22:20
1  
Changing 3307 to 3306 changes the error to: "SQL Error (1045) in statement #0: Access denied for user 'TheSSHandSQLUsernamesAreFrustratinglyTheSameSoIDon'tKnowWhichThisIs'@'localhost‌​' (using password: YES)" –  kscott Nov 8 '13 at 22:22
    
@kscott you realize that the Access denied for user is much better that before? With port 3307 you were not getting a connection. Now it is clear you are getting a connection on port 3306, but your credentials are mixed up. Much closer than before. –  JakeGould Nov 8 '13 at 22:25
    
I have double checked that the mySQL credentials I am putting into the client are the same that I am able to successfully use through command line through putty. Setting both ports to 3306 and changing the hostname from localhost to 127.0.0.1 gives me the same error: "SQL Error (1045) in statement #0: Access denied for user 'mySQLuser'@'localhost' (using password: YES)" –  kscott Nov 8 '13 at 22:39

Try changing your settings this way:

...

Local port: 3307

MySQL host: localhost

...

Port: 3306

Use non standart ports so, if you are running an instance of MySQL on your local machine, we are sure you are not connecting to it. I don't know HeidiSQL but I think it will tunnel to the default port on the remote server

share|improve this answer
    
Thank you for your response. Changing both Port options to 3307 results in the same error message: "SQL Error (2003) in statement #0: Can't connect to MySQL server on 'localhost' (10061)" –  kscott Nov 8 '13 at 22:25
    
Are you using putty or plink for the tunnel? If you are using plink, try connecting once with it from the command line: plink.exe -P 22 -l username remote-host –  Guido Vaccarella Nov 8 '13 at 22:43
    
Heidi is using plink for the tunnel, I am manually using putty to test my credentials. Using the plink command line, I am able to successfully connect to the server and then also the mysql DB through command line –  kscott Nov 8 '13 at 22:52
    
Did plink ask you to store the key in cache? If so, try connecting again with HeidiSQL using local port 3307 and port 3306. It seems there's a problem in HeidSQL to manage the first connection to a remote host. –  Guido Vaccarella Nov 8 '13 at 22:55
    
the plink command line did not mention anything about storing the key in cache, is there a way to explicitly make it do so? Attempting through Heidi again with local 3307 and port 3306 results in error: "SQL Error (2003) in statement #0: Can't connect to MySQL server on 'localhost' (10061)" –  kscott Nov 8 '13 at 22:57

First, make sure the bind-address= in /etc/my.conf, under [mysqld], is set to the new server IP address.

Second, you'll need to make sure you're user still has access to connect to MySQL remotely from whatever IP address you're attempting to remotely connect from.

Run the following query from the MySQL command line where 'username' is the user name you're attempting to log in with and 'ipaddress' is the IP address you're attempting to connect from.

SHOW GRANTS FOR 'username'@'ipaddress';

If you don't get any valid results from the above query, then you need to grant access to your user at the IP address that you're attempting to connect remotely from. You can do this by running a query like the one below.

GRANT ALL PRIVILGES TO 'username'@'ipaddress' IDENTIFIED BY 'yourpassword';

Also, make sure you're connecting to the correct port set in /etc/my.cnf. If it is not explicitly set by port= in my.cnf, then it uses the default port of 3306. You can also check the port that MySQL is running on in the MySQL command line by running the query below.

SHOW VARIABLES LIKE 'port';

share|improve this answer
    
Thanks for the response. I don't think I have access to view the bind-address. I am able to connect to the mySQL server through putty using command line. If I'm not mistaken, and based on the error messages I'm getting, the 'ipaddress' in your example should be 'localhost' since I appear to be getting SSH access fine. Running SHOW GRANTS FOR 'mySQLusername'@'localhost' shows two records: "GRANT USAGE ON . TO 'mySQLusername'@'localhost'" and "GRANT ALL PRIVILEGES ON 'myDatabase'.* TO 'mySQLusername'@'localhost'" –  kscott Nov 9 '13 at 0:06
    
If you're remotely connecting to MySQL, the IP address needs to be the IP address that you're connecting from, not localhost. It operates on a separate port and protocol from SSH. Localhost would only possibly be applicable if you had a direct VPN connection to that server. –  nojak Nov 9 '13 at 0:18
    
So when I am getting the error message "SQL Error (1045) in statement #0: Access denied for user 'mySQLusername'@'localhost' (using password: YES)" from the SQL client, what is it referring to when it says 'localhost'? –  kscott Nov 9 '13 at 0:21
    
I guess I should have read some of your other comments about you using an SSH tunnel to connect to the server. Since that's the case, and you're still getting an error, make sure you're using the correct password (which I'm sure you've already checked). The main problem is most likely that you just have USAGE privileges, which is basically "no privileges". Try granting more access, like SELECT on the database you need to access remotely, and SHOW DATABASES if you need it. –  nojak Nov 9 '13 at 0:31
    
Thanks. There are two records in the table, usage is on star dot star, ALL PRIVILEGES is granted on the database that I can successfully access through putty and command line, but not with a SQL client. –  kscott Nov 9 '13 at 0:33
up vote 0 down vote accepted

So, in this case I was able to get the sys admin to go back and doublecheck and it turned out to be permissions after all, but I sincerely thank everyone who helped me exhaust my own troubleshooting so I could go back to him fairly confident the problem was not on my end.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.