MySQL hosts cache: whitelist IP?

Question

Is there a way to whitelist an IP address, so that max_connect_errors doesn't apply?

The IP address for my office was blocked today due to too many connection errors to our new development MySQL server. I had to FLUSH HOSTS to allow connections again.

Is there a way that I can just whitelist our IP address, so that the max_connect_errors can still stay in effect for all other connections?

You really don't want to do this. The next round of connection errors could be malicious. — Michael Hampton, Mar 29 '13 at 23:12
From my office? I was hoping to just whitelist that IP address, so I could leave the max_connect_errors setting in place for other attempts but prevent the office from getting blocked again. — balleyne, Mar 30 '13 at 03:32

score 1 · Accepted Answer · answered Mar 29 '13 at 23:13

1

No, there is no way of doing that, max_connect_errors is a global setting, not a per user/session variable.

answered Mar 29 '13 at 23:13

Dennis Kaarsemaker

19,277
2
44
70

Other servers have global settings and whitelists (thinking of cPanel, Postfix...), i.e. global settings to counter brute force attacks, but a whitelist of hosts exempt from that rate limiting. So, being a global setting doesn't seem to preempt the possibility of a whitelist... But, I guess MySQL just doesn't have one? =\ – balleyne Mar 30 '13 at 03:35
Mysql doesn't do any whitelisting. You either have global settings, per session settings or per user settings. `max_connections` is an eaxmple of a global setting, which can be overridden per user. `max_connect_errors` cannot be overridden. – Dennis Kaarsemaker Mar 30 '13 at 08:25
But you can set limits on the number of connections from different sources on most firewalls including Linux iptables). – symcbean Mar 30 '13 at 23:55

score 1 · Answer 2 · answered Jul 23 '13 at 07:57

You can modify global setting to high number then use different users for local and other connections like this;

grant usage on *.* to outsideuser@'%' with max_user_connections 10;

grant usage on *.* to localuser@'%' with max_user_connections 1000;

You can try same user with different host too, im not sure about it but you can try like this;

grant usage on *.* to user@'%' with max_user_connections 10;

grant usage on *.* to user@'localip' with max_user_connections 1000;

You can also do these for users;

 GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

MySQL hosts cache: whitelist IP?

2 Answers2