Home / server / Questions / 586651
In Process
Temnovit
Asked: 2014-04-05 01:43:47 +0800 CST

MySQL refuses to accept remote connections

  • 772

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?

mysql

6 Answers

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

    • 29

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

    • 4

  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

  4. 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.

    • 0

  5. 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

    • 0

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

    • -1