0

I have a remote mysql server that I want to connect to. I added my local IP address to ufw and am able to connect to the remote mysql server from my local machine.

However, when I try to connect to the mysql server from a second cloud server, I can't connect. I get this error:

ERROR: Error while retrieving users: SQLSTATE[HY000] [2002] Connection timed out (Connection: remote_mysql, SQL: select * from users)

Which is the same error I can reproduce on local if I remove my IP address from ufw. So, the problem is, the IP address of the cloud server being added to ufw doesn't solve the problem. It is possible that the server IP is being proxied by the network of my cloud provider, and unfortunately they're unable to help me. Yes, I am going to switch providers, but I want to solve the problem for now which is that this failed attempt to connect doesn't show up in any of my logs.

It should be possible for me to see somewhere that a remote server is trying to connect to my mysql server, but that's the thing: it doesn't show up anyhere. Not in the mysql logs, ufw logs, syslog, or kern.log. Why not?

Citizen
  • 570

2 Answers2

2

If the MySQL connection is failing and you're not seeing any logs, it's likely that the connection is getting blocked before it even reaches your server — that's why there's nothing in MySQL, UFW, or syslog.

To see if any connection attempt is actually reaching your server, try this:

sudo tcpdump -n -i any port 3306

Then try connecting from the other server. If nothing shows up, the request is getting blocked somewhere in between — maybe at the cloud provider level (firewall, NAT, etc.).

Also, check the public IP of your second server with:

curl ifconfig.me

Make sure that exact IP is allowed in UFW.

If you want to log all attempts to port 3306, you can also add this rule:

sudo iptables -I INPUT -p tcp --dport 3306 -j LOG --log-prefix "MYSQL_CONN: "

This way you can track any incoming attempts in syslog.

HBruijn
  • 84,206
  • 24
  • 145
  • 224
Bora
  • 31
1

Thanks to @ChrisDavies from this thread:https://unix.stackexchange.com/questions/794791/how-can-i-get-the-ip-address-of-a-failed-mysql-connection?noredirect=1#comment1526759_794791

I was able to determine that the request was never hitting my server using this:

sudo tshark -i any -f 'port 3306 and not host 127.0.0.1 and not host ::1'

Which does answer the question of how to capture the IP of a failed login. The solution to the underlying problem was to add the IP to the cloud network firewall, which was preventing the request prior to actually hitting the server.

Citizen
  • 570