Can't connect to database from a remote server after setting it up to allow connections

Question

I'm trying to allow remote connections to one of my mysql databases, but after I set everything up, I keep getting a time out error. Can you tell me if I perhaps missed a step?

I'm running Ubuntu 12.04 with MySQL 5.5.38-0

Here's my /etc/mysql/my.cnf file

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
# skip-external-locking
bind-address    = 0.0.0.0

Once I updated the my.cnf file, I restarted MySQL and then ran the following to open TCP port 3306

sudo /sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

Then saved the new rules using:

sudo /sbin/iptables-save

I can see it when I run sudo iptables -L

Chain INPUT (policy DROP)
target     prot opt source               destination         
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere             state RELATED,ESTABLISHED
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:ssh
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:http
DROP       udp  --  anywhere             anywhere             udp spt:bootps
LOG        all  --  anywhere             anywhere             LOG level warning prefix "INPUT__"
DROP       all  --  anywhere             anywhere            
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:mysql

I then created a test database:

> create database kentest;

Granted it all privileges from any host:

> GRANT ALL ON kentest.* TO kentest@'%' IDENTIFIED BY 'mypassword';

And flushed the privileges:

> flush privileges

But when I try and connect from another box:

$ mysql -u kentest -h x.x.x.x -p

I get the timeout message:

ERROR 2003 (HY000): Can't connect to MySQL server on 'x.x.x.x' (60)

I did notice that I don't see the port being used when I run

$ lsof -i -P | grep :3306

Any ideas what I could be doing wrong or missing?

Thanks!

Yes, it's started. I can connect to the database just fine using the box it's installed on. — Ken, Aug 08 '14 at 04:16
The port is right, look at the iptables rule are there any packets logged `iptables -vxn INPUT|grep 3306` — LHristov, Aug 08 '14 at 04:18
http://stackoverflow.com/questions/1673530/error-2003-hy000-cant-connect-to-mysql-server-on-127-0-0-1-111 — smali, Aug 08 '14 at 04:18
http://stackoverflow.com/questions/11758339/error-2003-hy000-cant-connect-to-mysql-server-111 — smali, Aug 08 '14 at 04:19
Just in case this helps, error code 60 is OS error code 60: Device not a stream — Matt Healy, Aug 08 '14 at 04:22
LHristov, I get `Bad argument INPUT Try iptables -h or iptables --help for more information.` When I run that command. — Ken, Aug 08 '14 at 04:25
Is this what you're looking for? `0 0 ACCEPT tcp -- eth0 * 0.0.0.0/0 0.0.0.0/0 tcp dpt:3306` — Ken, Aug 08 '14 at 04:37

score 0 · Accepted Answer · answered Aug 11 '14 at 15:17

0

I was able to figure out the issue. We are using CSF for our firewall and needed to add the IP to:

sudo vi /etc/csf/csf.allow

Then restart CSF:

$ csf --restart

answered Aug 11 '14 at 15:17

Ken

626
1
8
22

Can't connect to database from a remote server after setting it up to allow connections

1 Answers1