Too many TIME_WAIT connections on mysql from an outside host

Question

My netstat is showing over 2,000 mysql connections with the state of TIME_WAIT that seems to be stuck and won't go away. It's been like that for several hours and many of the connections are coming from an IP address that doesn't have privilege to my database server. It seems to be hanging, how do I clear this? Is this a brute force attack? All my user privileges have specific hosts and I don't use any wildcard.

Here's a snippet of netstat:

tcp        0      0 server:mysql       static.98.17.76.1:45222 TIME_WAIT  
tcp        0      0 server:mysql       static.98.17.76.1:34341 TIME_WAIT  
tcp        0      0 server:mysql       static.98.17.76.1:51888 TIME_WAIT  
tcp        0      0 server:mysql       static.98.17.76.1:54459 TIME_WAIT  
tcp        0      0 server:mysql       static.98.17.76.1:49599 TIME_WAIT  
tcp        0      0 server:mysql       static.98.17.76.1:50751 TIME_WAIT  
tcp        0      0 server:mysql       static.98.17.76.1:50731 TIME_WAIT  
tcp        0      0 server:mysql       static.98.17.76.1:54658 TIME_WAIT  
tcp        0      0 server:mysql       static.98.17.76.1:58974 TIME_WAIT  
tcp        0      0 server:mysql       static.98.17.76.1:33800 TIME_WAIT  
tcp        0      0 server:mysql       static.98.17.76.1:59840 TIME_WAIT  
tcp        0      0 server:mysql       static.98.17.76.1:53495 TIME_WAIT  
tcp        0      0 server:mysql       static.98.17.76.1:51561 TIME_WAIT 

Also, my PROCESSLIST in mysql doesn't show these connections so I assume they get dropped right away but not sure why they won't go away. Will this cause any issues with max connections for mysql?

Solution

If you find some connections in netstat output in TIME_WAIT state, this can be normal. You can get too many of these when you have too many short lived connections.

I got some of these even without the need to supply any username, password, or database name. Just type:

mysql -h your_server_ip
ERROR 1045 (28000): Access denied for user 'khaled'@'your_pc_ip' (using password: NO)

and you will get one left connection in TIME_WAIT state:

sudo netstat -anp | grep 3306
tcp      0      0 server_ip:3306      your_pc_ip:50464      TIME_WAIT   -

However, it is recommended to deny access from this IP especially if you don't recognize this IP as a legitimate client. A simple iptables rule like this can deny further requests from this IP:

sudo iptables -A INPUT -s bad_ip -p tcp --dport 3306 -j DROP

You may need to change -A to -I depending on whether you have other rules or not.

Comments

[Luciano Afranllie]

Take a look at this blog to understand TIME_WAIT state vincent.bernat.im/en/blog/2014-tcp-time-wait-state-linux.

[user3186337]

I do have a firewall in place (iptables) that only allows certain hosts to connect to 3306 and drops the rest but would this still cause TIME_WAIT state as it isn't a packet-filtering firewall? Also why does this specific connection have "static." prefixed to the source IP when other hosts don't? When blocking using iptables would I have to add the "static." to the rule?

[Tero Kilkanen]

When IPTables drops the packets to the port, they never show up in netstat list. TIME_WAIT is a state of TCP connection, but such a connection is never made when IPTables drops / rejects connections. The prefix is there because netstat shows reverse DNS entries for the IP addresses used for those connections' IP addresses. Reverse DNS entries can be basically anything, and in this case have this prefix based form. You can use -n argument for netstat to make it show IP addresses only.

[user3186337]

@TeroKilkanen thanks now they are showing as the right server IP addresses, I wonder why the reverse lookups show as somewhere out of the datacenter my servers are hosted on.. Is that typically normal?

[Mark Riddell]

@Khaled Changing the tcp_fin_timeout will have no effect on how long a socket stays in the TIME_WAIT state. That particular variable controls how long the closing side stays in FIN_WAIT_2 state (waiting on FIN from other side).

[Khaled]

@MarkoPolo: It seems you are right. I removed this part from answer!

[Tero Kilkanen]

Normally when IP addresses are encoded to the reverse domain name, the IP address is put to the DNS name in reverse order. So, for example, 192.168.10.12 would be server-12.10.168.192.intra for example. And since netstat truncates the host name by default, it doesn't show all parts of the IP address.