Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am trying to connect to mysql using an ip, but I am getting this error:

ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (111)

I can connect using localhost instead of an ip though.

This doesn't work:

mysql -u ryan -h xxx.xxx.xxx.xxx -p

Yet this does work:

mysql -u ryan -h localhost -p

I was able to connect earlier today, then I installed iRedMail, then I uninstalled iRedMail, and the uninstall process did something to mysql I think so I reinstalled mysql from scratch using this:

apt-get purge mysql-server mysql-client mysql-common mysql-client-5.5 mysql-server-5.5

Then:

apt-get install mysql-server

After that I created my user, and now I can't connect using an IP address with both the new user or root user, but I can connect using localhost.

What is wrong?

share|improve this question
    
Why do you want to connect with anything other than localhost unless your trying to allow remote clients to connect –  Ramhound Oct 16 '13 at 20:16
    
I am trying to allow remote clients to connect –  Ryan Naddy Oct 16 '13 at 20:31

1 Answer 1

up vote 1 down vote accepted

MySQL in your configuration example is binded locally to the address localhost (or to be clear to the IP-Address 127.0.0.1). If you want to connect through your servers public IP-Address the access gets rejected.

You can see this by doing a netstat -ln or in new style with the command ss.

The removal and re-installation replaced some configuration files of MySQL. apt-get purge deletes configuration files, also for dependent packages. Typically MySQL is only binded to localhost on a new installation on Ubuntu so this looks like your config file of MySQL was replaced with the default. For other Distros I can't say anything about the default configuration.

If you want to use a MySQL-Administration-Tool (i. e. HeidiSQL) on your local machine you should not change the bind-address in the MySQL-Config to the public IP-Address. It's better if you use a SSH-Tunnel.

With the standard Linux-Command ssh this is very easy to achieve with ssh -L 9999:localhost:3306 <servername> where 9999 is the local port. You can also use the MySQL-Port 3306 as local port, but may this won't work if you have a local installation of MySQL. On Windows this can also be done with putty, just look on Google for "putty mysql tunnel", maybe this tutorial should do it, but as I am a pure Linux user i cannot verify it.

share|improve this answer
    
but the first time I installed it, I used the default mysql configuration, and it worked fine. –  Ryan Naddy Oct 16 '13 at 20:34
    
Maybe there was a change in the default configuration. I can't tell because you didn't mention which distribution you are using. Another cause could be that the installation of another application reconfigured your MySQL configuration. –  noggerl Oct 16 '13 at 20:36
    
ah, I had to change bind-address from 127.0.0.1 to my servers ip address –  Ryan Naddy Oct 16 '13 at 20:37
    
Just deleted my comment because I saw your comment on Ramhound's question. But to clarify it: the main question is what do you want to do with the access from your home computer to the database? Administration of the database with certain tools? Data Management? Installation of an application which uses this database? –  noggerl Oct 16 '13 at 20:44
    
I want to to be able to use HeidiSQL –  Ryan Naddy Oct 16 '13 at 21:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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