0

I have several machines running CentOS, one of which is set up as the mysql server. I had been running this for a while, and was able to connect to mysql from the remote machines with no problem, but after a power outage and restart of all the PCs, I am now getting

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.22.6' (113)

On the server machine, I am able to locally connect to the MySQL server with no problem.

Some cursory searching tells me that 113 means "no route to host", so it looks like this is a network problem. I am able to ping 192.168.22.6 from the client machine, as well as SSHing into it.

On the server PC, if I run netstat -lnp | grep mysql, I get:

tcp        0      0 0.0.0.0:3306                0.0.0.0:*                    LISTEN      1588/mysqld
unix  2      [ ACC ]     STREAM     LISTENING     13448  1588/mysqld         /var/lib/mysql/mysql.sock

So it appears that it is listening on port 3306. However, if I go to the client machine and run nc -vz 192.168.22.6 3306, I get:

nc: connect to 192.168.22.6 port 3306 (tcp) failed: No route to host

I'm thinking this might be a firewall issue, and when on the server I run iptables --list-rule | grep 3306 I get no results. Does this mean that port 3306 is blocked by the firewall (even though netstat shows it as listening)? If so, how do I add an exception for that port? Or am I on completely the wrong track here?

Edit: I should also note that my my.cnf file looks like this:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
max_connections=500
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#bind-address = 192.168.22.6
slow_query_log=1
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 10
log-queries-not-using-indexes

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

So, I don't have any skip-networking. I'm not sure if the bind-address not being set is an issue?

  • If you can ping and ssh into your server the problem (certainly) isn't related to networking itself. Check the output of iptables -nvL to view all settings, as iptables --list-rule is just a brief overview. Netstat also can't discern if something is blocking a port. Can you connect to mysql on your server via IP or via socket? The problem persists on all clients? I'd bet on firewall. – Lenniey Nov 7 '18 at 15:56
  • With iptables -nvL I still see no rules pertaining to port 3306. I had assumed that if netstat showed the port as listening, that meant it was not blocked by the firewall, but I'm guessing maybe that's not the case? Yeah, the problem exists on all clients. On the server, I can successfully connect to mysql using either -h 192.168.22.6 or -h 127.0.0.1 or -h localhost. – Steven Linden Nov 7 '18 at 16:10
  • No, netstat only shows the processes regardless of iptables or any other firewall. Are your clients in the same subnet as the server, no router between etc.? If you don't set a bind address the server listens to 0.0.0.0:3306. – Lenniey Nov 7 '18 at 16:28
  • Yeah, all the clients and the server are on the same subnet, with no router in between. Should I add a rule to iptables allowing tcp connections on 3306? – Steven Linden Nov 7 '18 at 16:41
  • You could have a default DROP rule for INPUT. Check iptables -nvL for e.g. port 22. To add the rule try iptables -I INPUT 1 -i <interface> -j ACCEPT. If this works you can always add 'better' filters, like source-ips/nets and so on. – Lenniey Nov 7 '18 at 16:44
1

Check port is open,

firewall-cmd --get-active-zones

If not open the port,

firewall-cmd --zone=public --add-port=port_id/tcp --permanent

Then reload firewall

firewall-cmd --reload

Allow mysql user to access via the another host,

GRANT ALL PRIVILEGES ON user_name.* TO 'db_name'@'192.168.1.%' IDENTIFIED BY 'password';

'192.168.1.%' : Give access from all hosts on 192.168.1.x range or you can specify IP.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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