30

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  -
65

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.

  • 2
    This worked for me. Note the correct filename is /etc/mysql/my.cnf, not /etc/mysql/my.conf. – Larry Battle Oct 6 '13 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 '14 at 0:38
  • Remove the line completly and the server will listen to ALL interfaces – s1mmel Aug 14 '17 at 14:29
  • You saved my day, been trying to solve this issue for a couple weeks, thanks! – budiantoip Nov 12 '17 at 9:42
8

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.

  • 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 '13 at 2:27
  • @Andy Updated answer to include some more suggestions. – Luis Alvarado Mar 25 '13 at 2:34
  • Thank you for the suggestions Luis I will look into this. – Andy Mar 25 '13 at 2:41
  • I have updated the results of netstat -tuple to the question. Looks like there is an issue here. – Andy Mar 25 '13 at 22:15
4

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.

  • sudo ufw allow 3306/tcp I tried to edit but wouldn't let me – Adam Jan 10 '16 at 20:27
3

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 (see https://stackoverflow.com/a/24171107/132374).

1

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

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

protected by Community Mar 25 '18 at 17:38

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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