45

I have two VPS servers, and both have MySQL installed, one for testing and one for development. One of my servers won't let me connect from outside. With the user;

'mytestuser'@'%'

According to an open port finder, port 3306 for the offending server appears to be closed. I have C++ and Java programs of my own listening on arbitrary ports without any issues. Why is this happening and how can I fix it?

Ubuntu installed is Ubuntu 11.10 (GNU/Linux 2.6.32-042stab072.10 x86_64)

Results of netstat -tuple on each server

Offending server

tcp        0      0 localhost.lo:submission *:*                     LISTEN      root       39967664    -

Working server

tcp        0      0 *:mysql                 *:*                     LISTEN      mysql      1847519288  -
Improve this question

5 Answers 5

Reset to default
98

The problem was that the server was listening internally only.

Removing the line bind-address 127.0.0.1 from /etc/mysql/my.cnf solved the issue.

Newer versions of Ubuntu (≥16.04) may have this line in /etc/mysql/mysql.conf.d/mysqld.cnf.

Improve this answer
4
  • 2
    This worked for me. Note the correct filename is /etc/mysql/my.cnf, not /etc/mysql/my.conf.
    – Larry Battle
    Oct 6, 2013 at 2:03
  • I changed my bind-address to match the server's external ip address and that worked for me.
    – Buttle Butkus
    Jul 28, 2014 at 0:38
  • Remove the line completly and the server will listen to ALL interfaces
    – s1mmel
    Aug 14, 2017 at 14:29
  • You saved my day, been trying to solve this issue for a couple weeks, thanks!
    – Budianto IP
    Nov 12, 2017 at 9:42
12

My suggestion, if you are sure that the ports are closed (I find it weird for a VPS to have that port closed) is to change the configuration file of MySQL to use another.

Simply open the configuration file in the terminal, sudo nano /etc/mysql/mysql.conf, and look for the [mysqld] section. In it, look for the line that reads port = 3306. Change it to another port not used and save the file.

Then simply either restart the VPS or restart the service, like sudo service mysql restart.

Just to note that, if the file mysql.conf is not in the one I mentioned above it can be in this other places:

/etc/my.conf
/etc/my.cnf
/etc/mysql/my.conf

And if the service command does not work, then do this:

sudo /etc/init.d/mysql restart

If the problem persists then in my case I would check iptables (I would actually delete everything in iptables just to start fresh if this could be an option) or any other firewall-enabled option.

Since they are VPS, I would also check the VPS Control Panel to see if it has any option that can block ports.

Apart from that, I would run nmap on the VPS to see what ports you have opened. You need to run it from outside the VPS to see what ports they have opened.

netstat -tuplen is also a good idea to see what opened ports you have on the server and which ones are in LISTEN mode.

Improve this answer
4
  • Hey, I tried it on another port and its still miss behaving, I also tried 'iptables -A INPUT -i eth0 -p tcp -m tcp --dport PORT -j ACCEPT' but no luck :( .. edit im not o eth0 .. might stil have hope.
    – Andy
    Mar 25, 2013 at 2:27
  • @Andy Updated answer to include some more suggestions.
    – Luis Alvarado
    Mar 25, 2013 at 2:34
  • Thank you for the suggestions Luis I will look into this.
    – Andy
    Mar 25, 2013 at 2:41
  • I have updated the results of netstat -tuple to the question. Looks like there is an issue here.
    – Andy
    Mar 25, 2013 at 22:15
11

I fixed this by changing bind-address 127.0.0.1 to bind-address 0.0.0.0 in /etc/mysql/mysql.conf.d so that MySQL listens on all ports.

Also, I set up a mysql user account to be used remotely. For more details see Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server.

Improve this answer
10

I use this method:

sudo ufw status
sudo ufw allow XXXX/tcp
# use a port other than the default/predictable 3306
# work outside, and close the door when you are done
sudo ufw deny XXXX/tcp

No need to change other configuration files, and no extra ports opened by default.

Improve this answer
1
  • 1
    sudo ufw allow 3306/tcp I tried to edit but wouldn't let me
    – Adam
    Jan 10, 2016 at 20:27
2

It may happen that your configuration is in the directory /etc/mysql/mysql.conf.d/mysqld.cnf, verify if that is not the case.

Improve this answer
1
  • 1
    I was trying to figure it out for like three hours before I saw this, thanks!
    – Oliver Ni
    May 3, 2017 at 16:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .