3

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?

Steven Linden
  • 31
  • 1
  • 1
  • 3
  • 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 07 '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 07 '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 07 '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 07 '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 -j ACCEPT`. If this works you can always add 'better' filters, like source-ips/nets and so on. – Lenniey Nov 07 '18 at 16:44
  • Sorry, `iptables -I INPUT 1 -i --dport 3306 -j ACCEPT` of course – Lenniey Nov 07 '18 at 16:57
  • This is almost always because a firewall is blocking the traffic. Check your firewall. If you don't understand whether it blocks or allows specific traffic, post a copy of it in your question and someone will be able to help. – Michael Hampton Nov 07 '18 at 18:30

1 Answers1

3

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.

vish
  • 156
  • 5