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?

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

6 Answers6

29

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

Eduardo Cuomo
  • 192
  • 1
  • 5
Guido Vaccarella
  • 1,418
  • 14
  • 13
4

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

alexander.polomodov
  • 1,068
  • 3
  • 10
  • 14
Alex Balcome
  • 41
  • 1
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.

Eric Fossum
  • 225
  • 3
  • 11
  • 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 09 '20 at 18:51
  • Man, this saved the day for me...thanks – PanDe Dec 07 '22 at 01: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.

periket2000
  • 248
  • 2
  • 9
  • 3
    Connection refused generally means nothing is listening on the relevant IP:port. – user9517 Apr 04 '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 04 '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 03: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 09 '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

Ondrej
  • 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 03:11
-1

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

bml13
  • 37
  • 5
  • 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 04 '14 at 11:25
  • 1
    ...especially because that's now a dead link. – haz Feb 01 '18 at 06:02