16

I've just installed a fresh ubuntu server with mysql (percona 5.5), but it refuses to accept connections from remote hosts

Here is what happens if I try to connect to this server remotely:

mysql -h10.0.0.2 -uroot -pmypassowrd
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.2' (111)

telnet 10.0.0.2 3306
Trying 10.0.0.2...
telnet: Unable to connect to remote host: Connection refused

When I checked if mysql listens to remote connections I saw this:

sudo netstat -ntlup | grep mysql
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      127018/mysqld 

As you can see it says 127.0.0.1:3306 which means "I accept only local connections".

I checked my skip_networking and bind-address variables - everything is turned off:

mysql> show variables like '%skip_networking%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| skip_networking | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%bind%';
Empty set (0.00 sec)

I have another server with absolutely the same config and it works great:

sudo netstat -ntlup | grep mysql
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      2431/mysqld 

What can be the reason for this? How do I make mysql respond to remote connections?

3
  • Note that the variable bind_address is only available since version 5.6 (bugs.mysql.com/bug.php?id=44355), so it will return an empty set in v5.5 even if it is set.
    – faker
    Apr 4, 2014 at 10:51
  • Show us your my.cnf settings.
    – user9517
    Apr 4, 2014 at 11:34
  • Have you checked for issues outside of mySQL, such as a firewall or a routing issue?
    – Stese
    Apr 3, 2019 at 6:56

6 Answers 6

29

Try to add bind-address = 0.0.0.0 to your [mysqld] section of your my.cnf and restart mysqld.

4
  • 3
    The file is at /etc/mysql/mysql.conf.d/mysqld.cnf Jun 29, 2018 at 17:58
  • Mine was in /etc/my.cnf.d/bind-addr.cnf
    – joseph
    Nov 21, 2018 at 20:49
  • 1
    I have done that 1000 times by now for the last 3 days and still my remote device can't connect to the MySQL server machine at all...
    – ThN
    Jun 9, 2020 at 18:52
  • @ThN If your MySQL is listening on the right interface, then you may need to configure your firewall Jun 11, 2020 at 7:40
5

I have mariadb installed. The soulution was to modify the bind-address located at /etc/mysql/mariadb.conf.d/50-server.cnf

2
  • I have done that and still my remote device can't connect to the MySQL server machine.
    – ThN
    Jun 9, 2020 at 18:47
  • Thanks man. You saved my day.
    – Harijith R
    Aug 4, 2020 at 5:00
3

I just had this issue and my issue seemed to be firewall related. BTW - Thanks @Temnovit for the Mysql troubleshooting commands.

I used netstat to tell the server was running and on the correct port. I could also tell my server wasn't accepting connections on that port with a simple telnet command.

# On the remote machine
➜  ~ echo X | telnet -e X 192.168.1.57 3306
Telnet escape character is 'X'.
Trying 192.168.1.57...
telnet: connect to address 192.168.1.57: Connection refused
➜  ~ 

Following another helpful answer on Fedora firewall commands I could open the correct ports.

# On the server
sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reload

My telnet command was then successful.

# On the remote machine
➜  ~ echo X | telnet -e X 192.168.1.57 3306
Telnet escape character is 'X'.
Trying 192.168.1.57...
Connected to 192.168.1.57.
Escape character is 'X'.

telnet> Connection closed.
➜  ~

If you still have problems, it's likely a user permissions issue.

2
  • I have tested my connection with Telnet and it was successfully connect to my MySQL server IP, but my program can't make the remote connection to the MySQL server machine.
    – ThN
    Jun 9, 2020 at 18:51
  • Man, this saved the day for me...thanks
    – PanDe
    Dec 7, 2022 at 1:20
0

It could be due to the mysql database user table (use mysql; show tables;). If the bind to 0.0.0.0 don't work for you, try to give your user the host '%' in place of 'localhost' in that table.

For example, try creating a user like:

CREATE USER yourusername@'%' IDENTIFIED BY 'yourpassword'

ant try to connect with that user.

4
  • 3
    Connection refused generally means nothing is listening on the relevant IP:port.
    – user9517
    Apr 4, 2014 at 11:34
  • True, I've been flashed by 'Can't connect to MySQL server', I don't recall on the telnet. Sorry. Apr 4, 2014 at 11:36
  • "Can't connect to MySQL" doesn't originate from a privilege being unavailable to a user. It is a {IP:port} pair that is not matched by a connection attempt. Jul 31, 2018 at 3:07
  • I have done that and still my remote device can't connect to the MySQL server machine. In fact, for the same user I have 'user'@'%' and 'user'@'localhost' and I still can't make a remote connection to my MySQL server machine.
    – ThN
    Jun 9, 2020 at 18:48
0

When everything else fails and you are sure that the server IS listening on the default port and you are trying to connect with mysql client desperately from other host, try to specify port on the URL of the mysql command.

Strange as it is I had to use the following syntax:

mysql -h someHost --port=3306 -u someUser -p someDb

I have found this (bug?) totally by accident (after loosing my hairs :) ).

My setup: debian jessie, fresh mysql 5.7.18, users/db created, bind-address commented-out, mysqld restarted

1
  • A rational explanation is that the someHost MySQL server is actually listening on 3306, as demonstrated by the --port directive. But your mysql client binary comes from the MariaDB version...therefore uses port 3307 as default, unless specified otherwise. Check the origin of the mysql client binary you are using. Hint: check your PATH environment variable too... Jul 31, 2018 at 3:11
-1

The solution described in the link bellow should solve your problem http://www.debianhelp.co.uk/remotemysql.htm

2
  • 2
    bml13, quoting a link in support of your answer is excellent practice, but we much prefer people to write the answer out here and add a link, not just put the link and leave it at that.
    – MadHatter
    Apr 4, 2014 at 11:25
  • 1
    ...especially because that's now a dead link.
    – haz
    Feb 1, 2018 at 6:02

You must log in to answer this question.

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