13

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 '14 at 10:51
  • Show us your my.cnf settings. – user9517 Apr 4 '14 at 11:34
  • Have you checked for issues outside of mySQL, such as a firewall or a routing issue? – Stese Apr 3 '19 at 6:56
23

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 – Eduardo Cuomo Jun 29 '18 at 17:58
  • Mine was in /etc/my.cnf.d/bind-addr.cnf – joseph Nov 21 '18 at 20:49
  • 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 '20 at 18:52
  • @ThN If your MySQL is listening on the right interface, then you may need to configure your firewall – Guido Vaccarella Jun 11 '20 at 7:40
2

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 '20 at 18:47
  • Thanks man. You saved my day. – Harijith R Aug 4 '20 at 5:00
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 '14 at 11:34
  • True, I've been flashed by 'Can't connect to MySQL server', I don't recall on the telnet. Sorry. – periket2000 Apr 4 '14 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. – Fabien Haddadi Jul 31 '18 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 '20 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... – Fabien Haddadi Jul 31 '18 at 3:11
0

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.

1
  • 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 '20 at 18:51
-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 '14 at 11:25
  • 1
    ...especially because that's now a dead link. – haz Feb 1 '18 at 6:02

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