Mysql Connection problem

[chez123]

hello,
i have problem with connection from other server.
root@second:~# mysql -u da_admin -p --host=xxxxxxx -p 3306
Enter password:


ERROR 2003 (HY000): Can't connect to MySQL server on 'xxxxx' (110)

on server xxxxx I setup bind address in /etc/my.cnf in [mysqld] directive. both servers accepts connections on port 3306

 

[Zhenyapan]

mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

 

[chez123]

problem solved. after reboot server, server add iptables rules.

 

[Janyer]

Hi @chez123, can you please post here what kinds of rules did you added on the server? Thank you

 

[LawsHosting]

Open outgoing port 3306 on the server that needs external access, and comment out bind-address (if exists) in /etc/my.cnf

if using csf

tcp|out|d=3306|d=The.IP.of.the.other.server

in /etc/csf/csf.allow

 

[Janyer]

@Peter Laws Server is running on Ubuntu and here are content of my.cnf

```
[mysqld]
max_allowed_packet=64M
local-infile=0
innodb_file_per_table

[client]
socket=/usr/local/mysql/data/mysql.sock
```

 

[Janyer]

Open outgoing port 3306 on the server that needs external access, and comment out bind-address (if exists) in /etc/my.cnf

if using csf

tcp|out|d=3306|d=The.IP.of.the.other.server

in /etc/csf/csf.allow

So I should open 3306 in iptables rules. Is that right one?

 

[LawsHosting]

So I should open 3306 in iptables rules. Is that right one?

Yes, but only to the IP of the other server

 

[Janyer]

Ok Can you please send me a best blog to set that 3306 port in iptables since I have not much experience for that. Thank you so much

 

[LawsHosting]

LMGTFY

IPTables: Allow outgoing MySQL connections but not incoming connections

I have a server which doesn't allow connections to mysql from external sources - all of my databases and connections take place on localhost. The default policy of iptables is to drop connections ...

serverfault.com

Ignore any INPUT lines

Just add -d flag for the destination IP, so if 35.89.89.39 is the remote MySQL

iptables -t filter -A OUTPUT -p tcp -d 35.89.89.39 --dport 3306 -j ACCEPT

 

[Janyer]

Could you please take a look at it? I just tried to run those iptables commands and tried to connect again but failed again. Seems 3306 still off now. Please advise me. Thank you

 

[LawsHosting]

You need to add the IP to the user, to allow external HOSTS...... Go to the MySQL section in Directadmin, click the user, then add a HOST and add the IP

 

[LawsHosting]

Or.... The user "VideoGuruSql" on the remote MySQL server needs to allow the calling HOST IP.

 

[Janyer]

I added VideoGuruSql user to the DB server by typing those commands:

```
CREATE USER 'VideoGuruSql'@'app.server.ip' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON db_name.* TO 'VideoGuruSql'@'app.server.ip';
FLUSH PRIVILEGES;
exit;
```

 

[LawsHosting]

That should now work...... I assume you opened port 3306 on the external server?

 

[Janyer]

I have a question. Do I need to open port 3306 on the app server or DB server? Please let me know. I tried to run iptables commands on App server. Thank you

 

[Janyer]

iptables -t filter -A OUTPUT -p tcp -d DB.Server.IP.Address --dport 3306 -j ACCEPT

Is that right one? on app server?

 

[Janyer]

That should now work...... I assume you opened port 3306 on the external server?

I think I opened that

 

[LawsHosting]

I have a question. Do I need to open port 3306 on the app server or DB server? Please let me know. I tried to run iptables commands on App server. Thank you

Both.

OUT on app server
IN on DB server